ChatGPT在综合数据处理中的应用（续篇）
1.1 案例1: 用户连续活跃天数获取
用户连续活跃天天数有点类似于留存率指标，也能反映用户留存情况，实现逻辑稍微有些难度，我们来问ChatGPT如何实现。发送如下内容给ChatGPT。
ChatGPT收到上述内容以后，返回如下结果。
要计算每个用户的最长连续活跃天数，您可以使用 Hive SQL 中的窗口函数和递归查询来实现。以下是一个示例代码，展示如何在 Hive SQL 中实现这一需求：
-- 创建示例表 user_table
CREATE TABLE user_table (
uid INT,
actday DATE
);
-- 插入示例数据
INSERT INTO user_table VALUES
(1, '2024-01-01'),
(1, '2024-01-02'),
(1, '2024-01-03'),
(1, '2024-01-05'),
(2, '2024-01-01'),
(2, '2024-01-02'),
(2, '2024-01-03'),
(2, '2024-01-05'),
(2, '2024-01-06');
-- 计算每个用户的最长连续活跃天数
WITH recursive_cte AS (
SELECT
uid,
actday,
ROW_NUMBER() OVER (PARTITION BY uid ORDER BY actday) AS rn
FROM user_table
),
cte AS (
SELECT
uid,
actday,
rn,
DATEDIFF(actday, lag(actday, 1, actday) OVER (PARTITION BY uid ORDER BY actday)) AS diff
FROM recursive_cte
),
max_consecutive_days AS (
SELECT
uid,
MAX(rn - ROW_NUMBER() OVER (PARTITION BY uid, diff ORDER BY actday)) AS max_consecutive_days
FROM cte
GROUP BY uid, diff
)
SELECT
uid,
MAX(max_consecutive_days) AS longest_consecutive_days
FROM max_consecutive_days
GROUP BY uid;
在上述代码中，首先创建了一个名为 user_table 的表，包含 uid（用户ID）和 actday（活跃日期）字段，并插入了示例数据。
然后，通过使用递归公共表表达式（CTE） recursive_cte，计算每个用户的活跃日期的行号 rn，并在 cte 中计算每个活跃日期与前一天日期的差值 diff。
接下来，在 max_consecutive_days CTE 中，利用 ROW_NUMBER 和 lag 函数计算每个活跃日期与前一天日期的差值，并找出每个用户最长的连续活跃天数。
最后，通过对最长连续活跃天数进行分组和聚合，得到每个用户的最长连续活跃天数。
通过这种方式，您可以计算每个用户的最长连续活跃天数，而不需要创建临时表。您可以根据实际情况调整字段名、表名和数据类型。
上述代码有些繁琐，让ChatGPT简化一些。向它发送如下内容。
ChatGPT收到后，返回结果如下。
当然，我可以帮您简化代码，让逻辑更加清晰和简洁。下面是经过简化的 Hive SQL 代码，用于计算每个用户的最长连续活跃天数：
WITH consecutive_days AS (
SELECT
uid,
actday,
DATEDIFF(actday, lag(actday, 1, actday) OVER (PARTITION BY uid ORDER BY actday)) AS diff
FROM user_table
),
consecutive_groups AS (
SELECT
uid,
actday,
SUM(CASE WHEN diff = 1 THEN 0 ELSE 1 END) OVER (PARTITION BY uid ORDER BY actday) AS group_id
FROM consecutive_days
)
SELECT
uid,
MAX(count(*)) AS longest_consecutive_days
FROM consecutive_groups
GROUP BY uid, group_id;
在这个简化后的代码中，我们使用了两个公共表表达式（CTE）：
1.`consecutive_days`：计算每个活跃日期与前一天日期的差值 diff。
2.`consecutive_groups`：根据 diff 的变化，将连续的活跃日期分为不同的组，并为每个组分配一个 group_id。
最后，通过对 `consecutive_groups` 中的 uid 和 group_id 进行分组和计数，找出每个用户的最长连续活跃天数。
这样简化后的代码保留了逻辑的清晰性，同时更加简洁和易读。您可以根据需要调整字段名、表名和数据类型。
我们可以让ChatGPT分步给出子查询的结果，这样更容易理解。发送如下内容给ChatGPT。
ChatGPT收到上述内容后，返回如下结果。
假设我们有以下示例数据：
user_table:
uid actday
----- ------------
1 2024-01-01
1 2024-01-02
1 2024-01-03
1 2024-01-05
2 2024-01-01
2 2024-01-02
2 2024-01-03
2 2024-01-05
2 2024-01-06
现在，让我们逐步演示上述每一个子查询的过程：
1. consecutive_days 子查询：
uid actday diff
----- ------------ ------
1 2024-01-01 3
1 2024-01-02 1
1 2024-01-03 1
1 2024-01-05 2
2 2024-01-01 3
2 2024-01-02 1
2 2024-01-03 1
2 2024-01-05 2
2 2024-01-06 1
2. consecutive_groups 子查询：
uid actday group_id
----- ------------ ----------
1 2024-01-01 0
1 2024-01-02 1
1 2024-01-03 1
1 2024-01-05 2
2 2024-01-01 0
2 2024-01-02 1
2 2024-01-03 1
2 2024-01-05 2
2 2024-01-06 3
在 consecutive_days 子查询中，我们计算了每个活跃日期与前一天日期的差值 diff。
在 consecutive_groups 子查询中，我们根据 diff 的变化，将连续的活跃日期分为不同的组，并为每个组分配一个 group_id。
通过这样的演示，您可以更好地理解每个子查询的作用，并最终得出每个用户的最长连续活跃天数。
1.2 案例2: 用户共同好友数获取
共同好友数获取是一个逻辑简单但不太容易解决的问题，我们来问ChatGPT如何处理。发送如下内容给ChatGPT。
ChatGPT收到上述内容以后，返回如下结果。
在Hive中实现计算任意两个用户之间的共同好友数可以通过以下步骤来完成：
1.首先，创建一个用户好友表 friend_table，包含 uid 和 tuid 两列。
2.使用Hive SQL编写以下查询来计算任意两个用户之间的共同好友数：
sql
Copy code
SELECT f1.uid AS user1, f2.uid AS user2, COUNT(DISTINCT f1.tuid) AS common_friends_count
FROM friend_table f1
JOIN friend_table f2 ON f1.tuid = f2.tuid
WHERE f1.uid < f2.uid -- 避免重复计算相同的用户对
GROUP BY f1.uid, f2.uid
ORDER BY user1, user2;
在这个查询中，我们首先将 `friend_table` 与自身进行自联接，条件是 `f1.tuid = f2.tuid`，这样可以找出共同的好友。然后，通过 `COUNT(DISTINCT f1.tuid)` 来计算共同好友的数量。最后，使用 `GROUP BY` 对用户对进行分组，并通过 `ORDER BY` 进行排序，以便更好地展示结果。
请根据实际情况调整表名和列名，并确保表中的数据格式正确。如果您有任何问题或需要进一步帮助，请随时告诉我。
这个实现逻辑比较简单，通过一个自连接即可完成，ChatGPT给出了正确答案。
1.3 案例3: 表格样式设置
在Python中，表格样式设置的代码比较繁琐，但有时很有必要，这时我们可以向ChatGPT寻求帮助。发送如下内容ChatGPT。
ChatGPT收到上述内容后，返回如下结果。
要使用 openpyxl 库来实现对 Excel 工作簿的格式设置，按照您的要求，需要执行以下步骤：
1.安装 openpyxl 库（如果尚未安装）：
pip install openpyxl
2.编写 Python 代码来实现对 Excel 工作簿的格式设置：
from openpyxl import Workbook
from openpyxl.styles import Alignment, PatternFill, Font
# 创建一个工作簿
wb = Workbook()
ws = wb.active
# 合并单元格并设置值
ws.merge_cells('A1:F1')
ws['A1'] = "xxx融媒体中心"
# 设置单元格样式
center_alignment = Alignment(horizontal='center', vertical='center')
orange_fill = PatternFill(start_color='FFA500', end_color='FFA500', fill_type='solid')
white_font = Font(color='FFFFFF')
ws['A1'].alignment = center_alignment
ws['A1'].fill = orange_fill
ws['A1'].font = white_font
# 保存工作簿
wb.save('融媒体中心.xlsx')
在这段代码中，我们首先创建一个 Excel 工作簿，然后合并单元格 `'A1:F1'` 并设置值为 `"xxx融媒体中心"`，接着分别设置单元格的对齐方式、背景颜色和字体颜色。最后，将工作簿保存为 `daily_report.xlsx` 文件。
您可以根据需要调整文件名和路径，并根据实际情况修改代码以适应更复杂的格式设置。如果您需要进一步帮助或有任何疑问，请随时告诉我。
继续向ChatGPT提问，帮我生成一个具体的职工信息表，发送如下内容。
ChatGPT收到后返回的结果如下。
from openpyxl import Workbook
from openpyxl.styles import Alignment, PatternFill, Font
import random
# 创建一个工作簿
wb = Workbook()
ws = wb.active
# 合并单元格并设置值
ws.merge_cells('A1:I1')
ws['A1'] = "xxx融媒体中心"
# 设置单元格样式
center_alignment = Alignment(horizontal='center', vertical='center')
orange_fill = PatternFill(start_color='FFA500', end_color='FFA500', fill_type='solid')
white_font = Font(color='FFFFFF')
ws['A1'].alignment = center_alignment
ws['A1'].fill = orange_fill
ws['A1'].font = white_font
# 写入表头
headers = ['职工ID', '职工姓名', '部门', '出生年月', '参加工作时间', '学历', '职称', '工资', '备注']
ws.append(headers)
# 随机生成10行数据
departments = ['部门A', '部门B', '部门C']
for _ in range(10):
row = [
f'ID{random.randint(1, 100)}',
f'员工{random.randint(1, 100)}',
random.choice(departments),
f'1990-0{random.randint(1, 9)}-01',
f'2010-0{random.randint(1, 9)}-01',
random.choice(['本科', '硕士', '博士']),
random.choice(['初级', '中级', '高级']),
random.randint(2000, 10000),
'无'
]
ws.append(row)
# 设置不同部门不同颜色
department_colors = {
'部门A': 'FFC000', '部门B': '00B0F0', '部门C': '92D050'}
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=3, max_col=3):
for cell in row:
fill_color = department_colors.get(cell.value, 'FFFFFF') # 默认白色
cell.fill = PatternFill(start_color=fill_color, end_color=fill_color, fill_type='solid')
# 保存工作簿
wb.save('职工信息表.xlsx')
这段代码将在 Excel 工作簿中创建首行'A1:I1'，并在'A1'单元格中显示"xxx融媒体中心"，同时确保表头正确显示在第二行。随机生成10行数据，并根据部门设置不同的背景颜色。最后将数据填充到工作簿中并保存为 `职工信息表.xlsx` 文件。
希望这次能符合您的要求。如果您需要进一步帮助或有任何疑问，请随时告诉我。
由于ChatGPT的回答具有一定的随机性，即使对于相同的问题，也可能做出不同的回答，因此，如果你在实践过程中发现所得结果与书中不同，也属正常情况。重要的是理解如何利用ChatGPT，而不必过于关心细微差别。
