ChatGPT在综合数据处理中的应用（二）

接上文ChatGPT在综合数据处理中的应用（一）https://developer.aliyun.com/article/1620279

1.4案例4: 累计销量计算
直接聚合运算比较简单，比它稍微难一点的是累计聚合运算，我们来问ChatGPT如何实现。发送如下内容给ChatGPT。

ChatGPT收到上述内容后，返回如下结果。

ChatGPT收到上述内容后，返回如下结果。

在 Hive SQL 中，您可以使用窗口函数（Window Function）来计算随着时间推移每个suid的累计销量。以下是一个示例代码，展示如何在 Hive SQL 中实现这一需求：

-- 创建示例表 sale_table
CREATE TABLE sale_table (
    suid INT,
    sdate DATE,
    sales INT
);

-- 插入示例数据
INSERT INTO sale_table VALUES
(1, '2024-01-01', 100),
(1, '2024-01-02', 150),
(1, '2024-01-03', 200),
(2, '2024-01-01', 50),
(2, '2024-01-02', 75);

-- 使用窗口函数计算每个suid的累计销量
SELECT 
    suid,
    sdate,
    sales,
    SUM(sales) OVER (PARTITION BY suid ORDER BY sdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM sale_table
ORDER BY suid, sdate;

在上面的示例代码中，首先创建了一个名为 sale_table 的表，包含 suid（销售人员ID）、sdate（日期）、sales（销量）字段。然后插入了示例数据。

接着，使用窗口函数 `SUM() OVER (PARTITION BY suid ORDER BY sdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)` 来计算每个 suid 的累计销量。窗口函数按照 suid 分区，并按照 sdate 排序，计算从最早日期到当前日期的销量累计值。

最后，查询结果会按照 suid 和 sdate 排序，显示每个销售人员在不同日期的销量以及累计销量。

您可以根据实际情况调整字段名、表名和数据类型。这样，您就可以在 Hive SQL 中计算每个销售人员随着时间推移的累计销量。

ChatGPT不仅给出了实现代码，还给出了关于代码中各部分的解释说明。

1.5 案例5: 用户留存率计算
留存率是数据分析中一个比较关键的指标，其计算也成了一个高频需求，我们来问ChatGPT如何实现。发送如下内容给ChatGPT。

ChatGPT收到上述内容以后，返回如下结果。

在 Hive SQL 中，您可以使用子查询和连接来计算每天活跃用户的次日留存率、3日留存率和7日留存率。以下是一个示例代码，展示如何在 Hive SQL 中实现这一需求：

sql
Copy code

-- 创建示例表 user_table
CREATE TABLE user_table (
    uid INT,
    actday DATE
);

-- 插入示例数据
INSERT INTO user_table VALUES
(1, '2024-01-01'),
(2, '2024-01-01'),
(3, '2024-01-01'),
(1, '2024-01-02'),
(2, '2024-01-02'),
(4, '2024-01-02');

-- 计算每天活跃用户的次日留存率、3日留存率和7日留存率
WITH active_users AS (
    SELECT DISTINCT uid, actday AS active_date
    FROM user_table
),
day1_retention AS (
    SELECT 
        a.active_date AS reference_date,
        COUNT(DISTINCT b.uid) AS day1_retained_users
    FROM active_users a
    JOIN user_table b ON a.uid = b.uid AND date_add(a.active_date, 1) = b.actday
    GROUP BY a.active_date
),
day3_retention AS (
    SELECT 
        a.active_date AS reference_date,
        COUNT(DISTINCT b.uid) AS day3_retained_users
    FROM active_users a
    JOIN user_table b ON a.uid = b.uid AND date_add(a.active_date, 3) = b.actday
    GROUP BY a.active_date
),
day7_retention AS (
    SELECT 
        a.active_date AS reference_date,
        COUNT(DISTINCT b.uid) AS day7_retained_users
    FROM active_users a
    JOIN user_table b ON a.uid = b.uid AND date_add(a.active_date, 7) = b.actday
    GROUP BY a.active_date
)
SELECT 
    d1.reference_date,
    d1.day1_retained_users,
    d3.day3_retained_users,
    d7.day7_retained_users
FROM day1_retention d1
LEFT JOIN day3_retention d3 ON d1.reference_date = d3.reference_date
LEFT JOIN day7_retention d7 ON d1.reference_date = d7.reference_date;


在上面的示例代码中，首先创建了一个名为 user_table 的表，包含 uid（用户ID）和 actday（活跃日期）字段，并插入了示例数据。

然后，通过使用 WITH 子句创建了三个子查询，分别计算次日留存率、3日留存率和7日留存率。每个子查询都是基于活跃用户表 active_users 和原始用户表 user_table 进行连接计算留存用户数。

最后，将每天的次日留存率、3日留存率和7日留存率结果连接在一起并输出。您可以根据实际情况调整字段名、表名和数据类型，以计算每天活跃用户的留存率。

ChatGPT给出的结果有点繁琐，代码重复使用，是否应该重构一下？我们给出修改提示，发送如下内容给ChatGPT，内容如下。

ChatGPT收到上述内容以后，返回如下结果。

当计算次日留存率、3日留存率和7日留存率时，确实存在一定程度的代码重复。您可以通过创建一个通用的子查询来避免重复，并在查询结果中计算不同的留存率。以下是重构后的代码：

-- 创建示例表 user_table
CREATE TABLE user_table (
    uid INT,
    actday DATE
);

-- 插入示例数据
INSERT INTO user_table VALUES
(1, '2024-01-01'),
(2, '2024-01-01'),
(3, '2024-01-01'),
(1, '2024-01-02'),
(2, '2024-01-02'),
(4, '2024-01-02');

-- 计算每天活跃用户的次日留存率、3日留存率和7日留存率
WITH active_users AS (
    SELECT DISTINCT uid, actday AS active_date
    FROM user_table
),
retention_data AS (
    SELECT 
        a.active_date AS reference_date,
        COUNT(DISTINCT CASE WHEN b.actday = date_add(a.active_date, 1) THEN b.uid END) AS day1_retained_users,
        COUNT(DISTINCT CASE WHEN b.actday = date_add(a.active_date, 3) THEN b.uid END) AS day3_retained_users,
        COUNT(DISTINCT CASE WHEN b.actday = date_add(a.active_date, 7) THEN b.uid END) AS day7_retained_users
    FROM active_users a
    JOIN user_table b ON a.uid = b.uid
    GROUP BY a.active_date
)
SELECT 
    reference_date,
    day1_retained_users,
    day3_retained_users,
    day7_retained_users
FROM retention_data;


在上述代码中，通过创建一个名为 retention_data 的子查询，使用 CASE WHEN 语句对不同的留存天数进行计数，避免了重复代码。子查询中计算了次日留存用户数、3日留存用户数和7日留存用户数，并将结果输出。

通过这种方式，您可以更加简洁和高效地计算每天活跃用户的次日留存率、3日留存率和7日留存率，避免了代码重复使用的问题。您可以根据实际情况调整字段名、表名和数据类型。

这个代码好像还有点问题，user_table a 与user_table b 之间是用JOIN连接的，这会忽略那些没有留存的用户，而在计算留存率时，没有留存的应该算在分母里。我们再给出修改提示，发送如下提示内容给ChatGPT。

ChatGPT收到上述内容后，返回如下结果。

在进行自连接时，通常应该使用 LEFT JOIN 而不是 INNER JOIN，以确保即使没有匹配的记录也能够保留左表（自连接中的主表）的所有记录。这样可以避免丢失活跃用户的数据，确保计算留存率时不会漏掉任何活跃用户。使用 LEFT JOIN 还可以更好地处理活跃用户表中可能存在的空数据或者缺失数据的情况。

在上面重构后的代码中，使用了 INNER JOIN 进行自连接，这可能会导致在某些情况下丢失部分活跃用户的数据，特别是当某些活跃用户在后续日期没有相应的记录时。因此，建议在这种情况下使用 LEFT JOIN 来确保所有活跃用户的数据都被考虑在内，以计算正确的留存率。以下是修改后的代码示例：

-- 计算每天活跃用户的次日留存率、3日留存率和7日留存率
WITH active_users AS (
    SELECT DISTINCT uid, actday AS active_date
    FROM user_table
),
retention_data AS (
    SELECT 
        a.active_date AS reference_date,
        COUNT(DISTINCT CASE WHEN b.actday = date_add(a.active_date, 1) THEN b.uid END) AS day1_retained_users,
        COUNT(DISTINCT CASE WHEN b.actday = date_add(a.active_date, 3) THEN b.uid END) AS day3_retained_users,
        COUNT(DISTINCT CASE WHEN b.actday = date_add(a.active_date, 7) THEN b.uid END) AS day7_retained_users
    FROM active_users a
    LEFT JOIN user_table b ON a.uid = b.uid
    GROUP BY a.active_date
)
SELECT 
    reference_date,
    day1_retained_users,
    day3_retained_users,
    day7_retained_users
FROM retention_data;


通过使用 LEFT JOIN，确保了即使活跃用户在后续日期没有相应的记录，也会被保留在结果中，从而更准确地计算每天活跃用户的次日留存率、3日留存率和7日留存率。

修改以后的 结果是正确的。