Hive SQL实现每笔交易后续7天内交易数量统计求助

需求：统计数据集中每笔交易发生后7天内（含该交易本身）的交易数量，滚动周统计逻辑为：交易日期2020-01-01需统计2020-01-01至2020-01-08的交易数，预期结果如下：

┌─────────────┬──────────────┐
│ Txn_Date    │        Count │
├─────────────┼──────────────┤
│ 2020-01-01  │            3 │
│ 2020-01-04  │            3 │
│ 2020-01-05  │            2 │
│ 2020-01-10  │            1 │
│ 2020-01-18  │            3 │
│ 2020-01-20  │            2 │
│ 2020-01-24  │            2 │
│ 2020-01-28  │            1 │
└─────────────┴──────────────┘

用户编写的错误Hive SQL代码：

select Txn_Date,
count(Txn_Date) over (partition by Txn_Date(column) where Txn_Date(rows) between Txn_Date and date_add('day', 14, Txn_Date) as Count

正确实现方案 #

你的代码存在语法错误，窗口函数的分区、范围逻辑使用完全不当，以下是两种可行的实现方式：

方式一：范围窗口函数（推荐） #

利用Hive的范围窗口函数，基于日期转时间戳的数值范围统计：

SELECT 
    Txn_Date,
    COUNT(*) OVER (
        ORDER BY unix_timestamp(Txn_Date) 
        RANGE BETWEEN CURRENT ROW AND 604800 FOLLOWING -- 604800秒 = 7天
    ) AS Count
FROM your_table_name;

• 逻辑说明：将日期转换为秒级时间戳，窗口范围设为当前行时间戳到之后7天的时间戳，统计该范围内的所有交易数。
• 优势：性能优于自关联，适合大数据量场景，自动处理同一天多笔交易的统计。

方式二：自关联统计 #

如果窗口函数逻辑不好理解，可采用自关联方式实现：

SELECT 
    a.Txn_Date,
    COUNT(b.Txn_Date) AS Count
FROM your_table_name a
LEFT JOIN your_table_name b 
    ON b.Txn_Date BETWEEN a.Txn_Date AND date_add(a.Txn_Date, 7)
GROUP BY a.Txn_Date
ORDER BY a.Txn_Date;

• 逻辑说明：每一条交易记录a，关联所有在a交易日期及之后7天内的交易记录b，统计关联到的b的数量。
• 注意：数据集较大时，自关联性能较低，优先选择窗口函数方案。

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