如何无需硬编码生成30天时间序列统计的SQL查询？

要实现无需硬编码获取1到30天的统计值，核心思路是生成一个包含1-30天的维度序列表，再将其与主数据关联，批量计算每天的统计结果。以下分不同SQL方言给出具体实现：

通用逻辑说明 #

1. 先计算每条记录中first_seen_video_time与subscription_purchase_time的时间差（秒数）；
2. 生成1到30的连续天数序列；
3. 交叉关联主数据和天数序列，按分组维度（title、item_name、category）+ 天数分组，统计符合时间差 <= 天数*86400秒的记录数（即累计到该天的总数）。

PostgreSQL 实现 #

利用generate_series快速生成天数序列：

WITH days AS (
    -- 生成1到30的连续天数
    SELECT generate_series(1, 30) AS day_num
),
time_diff_data AS (
    -- 预计算每条记录的时间差（秒）
    SELECT 
        title,
        item_name,
        category,
        (first_seen_video_time - subscription_purchase_time) AS diff_seconds
    FROM table1
)
SELECT 
    t.title,
    t.item_name,
    t.category,
    d.day_num,
    -- 统计累计到该天的符合条件的记录数
    SUM(CASE WHEN t.diff_seconds <= d.day_num * 86400 THEN 1 ELSE 0 END) AS cumulative_count
FROM time_diff_data t
CROSS JOIN days d
GROUP BY t.title, t.item_name, t.category, d.day_num
ORDER BY t.title, t.item_name, t.category, d.day_num;

MySQL 实现 #

使用递归CTE生成天数序列（MySQL 8.0+支持）：

WITH RECURSIVE days AS (
    SELECT 1 AS day_num
    UNION ALL
    SELECT day_num + 1 FROM days WHERE day_num < 30
),
time_diff_data AS (
    SELECT 
        title,
        item_name,
        category,
        (first_seen_video_time - subscription_purchase_time) AS diff_seconds
    FROM table1
)
SELECT 
    t.title,
    t.item_name,
    t.category,
    d.day_num,
    SUM(CASE WHEN t.diff_seconds <= d.day_num * 86400 THEN 1 ELSE 0 END) AS cumulative_count
FROM time_diff_data t
CROSS JOIN days d
GROUP BY t.title, t.item_name, t.category, d.day_num
ORDER BY t.title, t.item_name, t.category, d.day_num;

SQL Server 实现 #

同样用递归CTE生成序列，时间差用DATEDIFF计算：

WITH days AS (
    SELECT 1 AS day_num
    UNION ALL
    SELECT day_num + 1 FROM days WHERE day_num < 30
),
time_diff_data AS (
    SELECT 
        title,
        item_name,
        category,
        DATEDIFF(SECOND, subscription_purchase_time, first_seen_video_time) AS diff_seconds
    FROM table1
)
SELECT 
    t.title,
    t.item_name,
    t.category,
    d.day_num,
    SUM(CASE WHEN t.diff_seconds <= d.day_num * 86400 THEN 1 ELSE 0 END) AS cumulative_count
FROM time_diff_data t
CROSS JOIN days d
GROUP BY t.title, t.item_name, t.category, d.day_num
ORDER BY t.title, t.item_name, t.category, d.day_num;

扩展：统计每日新增记录 #

如果需要的不是累计值，而是第N天内新增的首次观看记录（即时间差在(N-1)86400到N86400秒之间），只需修改CASE条件：

SUM(CASE 
    WHEN t.diff_seconds > (d.day_num - 1) * 86400 
         AND t.diff_seconds <= d.day_num * 86400 
    THEN 1 ELSE 0 
END) AS daily_new_count

这种方式完全避免了硬编码30个统计列，后续要调整天数范围（比如改成1-60天），只需要修改序列生成的参数即可。

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