需求：按item_id计算未来30天item_sub_id的总和

需求说明 #

针对每个item_id，基于其记录的date字段，统计该日期未来30天内所有唯一item_sub_id的数量，记为sum_of_item_sub_id_for_next_30_days，同时需输出当前日期对应的唯一item_sub_id数量。

输入数据示例 #

item_sub_id  item_id     date
123          20221213   7/13/2021 0:00
456          20221213   7/16/2021 0:00
789          20221213   7/21/2021 0:00
989          20221213   7/23/2021 0:00
131          20221213   7/27/2021 0:00
132          20221213   7/27/2021 0:00
133          20221213   8/3/2021 0:00
134          20221213   8/3/2021 0:00
134          20221213   8/3/2021 0:00
135          20221213   8/4/2021 0:00
135          20221213   8/4/2021 0:00
136          20221213   8/10/2021 0:00
137          20221213   8/10/2021 0:00
138          20221213   8/17/2021 0:00
139          20221213   8/17/2021 0:00
140          20221213   8/18/2021 0:00

预期输出结果 #

count( distinct item_sub_id)  item_id     date               sum_of_item_sub_id_for_next_30_days
1                         20221213  7/13/2021 0:00                       11
1                         20221213  7/16/2021 0:00                       10     
1                         20221213  7/21/2021 0:00                       12  
1                         20221213  7/23/2021 0:00                       11
2                         20221213  7/27/2021 0:00                       10
2                         20221213  8/3/2021 0:00                         8
1                         20221213  8/4/2021 0:00                         6
2                         20221213  8/10/2021 0:00                        5
2                         20221213  8/17/2021 0:00                        3
1                         20221213  8/18/2021 0:00                        1 

SQL实现示例 #

以Hive SQL为例，通过自连接实现需求：

SELECT 
    COUNT(DISTINCT t1.item_sub_id) AS `count( distinct item_sub_id)`,
    t1.item_id,
    t1.date,
    COUNT(DISTINCT t2.item_sub_id) AS sum_of_item_sub_id_for_next_30_days
FROM 
    your_table t1
LEFT JOIN 
    your_table t2
ON 
    t1.item_id = t2.item_id
    AND t2.date >= t1.date
    AND t2.date <= DATE_ADD(t1.date, 30)
GROUP BY 
    t1.item_id, t1.date
ORDER BY 
    t1.date;

若使用MySQL等其他SQL方言，将日期函数替换为DATE_ADD(t1.date, INTERVAL 30 DAY)即可。

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