SQL/Hive中如何为同Key的不同日期追加历史行数据？

我明白你想要的效果了——每个日期行需要包含该日期及之前所有同用户的交易金额，对吧？之前用JOIN结合LAG/LEAD没成功很正常，因为LAG/LEAD是用来取窗口内相邻行的数据，而你需要的是把历史所有金额都关联到后续日期上，换用自连接的思路就能解决。

具体SQL代码 #

假设你的数据表名为transactions，可以用以下查询实现需求：

SELECT 
    t.name,
    target_date.date AS date,
    t.amount
FROM 
    transactions t
INNER JOIN 
    (SELECT DISTINCT name, date FROM transactions) target_date
ON 
    t.name = target_date.name
    AND t.date <= target_date.date
ORDER BY 
    target_date.date, t.amount;

代码逻辑解释 #

1. 子查询target_date先提取出所有唯一的name和date组合，也就是你需要展示的目标日期；
2. 将原表t和target_date做自连接，条件是同用户且原表日期小于等于目标日期；
3. 最后按目标日期和金额排序，就能得到你想要的结果。

验证结果 #

用你提供的测试数据执行上述查询，会得到：

name date amount
abc 04/06/2018 100
abc 04/06/2018 200
abc 04/13/2018 100
abc 04/13/2018 200
abc 04/13/2018 300

完全符合你的期望输出。

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