在SQL/BigQuery中实现多时间序列聚合的单查询方案

现有一张包含user_id、date、conversions字段的表，示例数据如下：user_id为1，date为11/08/2022，conversions为3；user_id为2，date为11/08/2022，conversions为1。请问能否通过单条SQL查询生成包含user_id、conversions_last_7_days、conversions_last_30_days字段的结果表？

当然可以，利用SQL的窗口函数（或关联子查询）就能实现单条查询生成目标结果。下面分不同场景给出实现方案：

一、用窗口函数实现（推荐，性能更优） #

窗口函数可以按用户分组，针对每条数据计算其所在时间窗口内的转化总和。不同数据库的日期语法略有差异，以下是常见写法：

1. 标准SQL/PostgreSQL写法 #

假设表名为user_conversions，若date是字符串类型，先用TO_DATE转成日期格式：

SELECT
    user_id,
    -- 计算近7天（含当天）的转化总和
    SUM(conversions) OVER (
        PARTITION BY user_id
        ORDER BY TO_DATE(date, 'MM/DD/YYYY')
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS conversions_last_7_days,
    -- 计算近30天（含当天）的转化总和
    SUM(conversions) OVER (
        PARTITION BY user_id
        ORDER BY TO_DATE(date, 'MM/DD/YYYY')
        RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
    ) AS conversions_last_30_days
FROM user_conversions;

2. MySQL 8.0+写法 #

MySQL的窗口函数范围语法略有不同，若date是字符串类型用STR_TO_DATE转换：

SELECT
    user_id,
    SUM(conversions) OVER (
        PARTITION BY user_id
        ORDER BY STR_TO_DATE(date, '%m/%d/%Y')
        RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS conversions_last_7_days,
    SUM(conversions) OVER (
        PARTITION BY user_id
        ORDER BY STR_TO_DATE(date, '%m/%d/%Y')
        RANGE BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS conversions_last_30_days
FROM user_conversions;

3. 若需每个用户仅返回一行（截至最新日期的统计） #

如果不需要每条日期数据的统计，只需要每个用户截至最新日期的近7/30天总和，可以在外层加聚合：

SELECT
    user_id,
    MAX(conversions_last_7_days) AS conversions_last_7_days,
    MAX(conversions_last_30_days) AS conversions_last_30_days
FROM (
    SELECT
        user_id,
        SUM(conversions) OVER (
            PARTITION BY user_id
            ORDER BY TO_DATE(date, 'MM/DD/YYYY')
            RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
        ) AS conversions_last_7_days,
        SUM(conversions) OVER (
            PARTITION BY user_id
            ORDER BY TO_DATE(date, 'MM/DD/YYYY')
            RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
        ) AS conversions_last_30_days
    FROM user_conversions
) AS sub_query
GROUP BY user_id;

二、用关联子查询实现（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用关联子查询来计算，但数据量大时性能会差一些：

SELECT
    uc.user_id,
    -- 子查询计算当前用户近7天转化总和
    (SELECT SUM(conversions)
     FROM user_conversions uc2
     WHERE uc2.user_id = uc.user_id
       AND TO_DATE(uc2.date, 'MM/DD/YYYY') BETWEEN TO_DATE(uc.date, 'MM/DD/YYYY') - INTERVAL '6 days' AND TO_DATE(uc.date, 'MM/DD/YYYY')) AS conversions_last_7_days,
    -- 子查询计算当前用户近30天转化总和
    (SELECT SUM(conversions)
     FROM user_conversions uc2
     WHERE uc2.user_id = uc.user_id
       AND TO_DATE(uc2.date, 'MM/DD/YYYY') BETWEEN TO_DATE(uc.date, 'MM/DD/YYYY') - INTERVAL '29 days' AND TO_DATE(uc.date, 'MM/DD/YYYY')) AS conversions_last_30_days
FROM user_conversions uc;

内容的提问来源于stack exchange，提问作者new_sql_123