Hive按日期3天回溯窗口统计Distinct Name问题求助

原始数据 #

需求 #

对每个日期，统计包含当日在内的过去3天窗口内的唯一姓名数量。

原SQL及问题 #

你编写的SQL：

select distinct(
         date_key, 
         count(*) over (
           partition by date_key 
           order by unix_timestamp(date_key, 'yyyy-MM-dd')
           range between 259200 preceding and current row   -- 259200 is 3 days in seconds
       )
from my_schema.names_table

存在的问题 #

1. 分区错误：partition by date_key 将每个日期单独划分分区，窗口函数只能访问当前日期的数据，无法跨日期统计过去3天的范围。
2. 统计逻辑错误：count(*) 统计的是当前分区内的总行数，而非唯一姓名数，且未处理同一日期同一姓名的重复数据。
3. 语法错误：distinct() 不是正确的多列去重写法，应该用 select distinct date_key, ...。

错误结果 #

{"col1":"2024-02-01","col2":4}
{"col1":"2024-02-02","col2":3}
{"col1":"2024-02-06","col2":4}
{"col1":"2024-02-07","col2":2}
{"col1":"2024-02-03","col2":3}
{"col1":"2024-02-04","col2":3}
{"col1":"2024-02-05","col2":3}

预期结果 #

解决方案 #

正确SQL（Hive兼容） #

WITH unique_daily_names AS (
    -- 先去重，确保每个日期每个姓名只出现一次
    SELECT DISTINCT date_key, name
    FROM my_schema.names_table
)
SELECT 
    date_key AS 日期,
    -- 用collect_set收集窗口内的唯一姓名，再取集合大小
    SIZE(COLLECT_SET(name) OVER (
        ORDER BY unix_timestamp(date_key, 'yyyy-MM-dd')
        -- 过去2天到当前（共3天），2*86400=172800秒
        RANGE BETWEEN 172800 PRECEDING AND CURRENT ROW
    )) AS 唯一姓名数量
FROM unique_daily_names
-- 按日期分组，确保每个日期只返回一行结果
GROUP BY date_key
ORDER BY date_key;

逻辑说明 #

1. 去重处理：通过unique_daily_names CTE先过滤掉同一日期下的重复姓名，避免后续统计时重复计算。
2. 窗口范围设置：RANGE BETWEEN 172800 PRECEDING AND CURRENT ROW 表示当前日期往前推2天（172800秒）到当前日期的范围，刚好覆盖包含当日的过去3天。
3. 唯一值统计：COLLECT_SET(name) 会将窗口内的姓名去重后存入集合，SIZE() 函数返回集合的元素数量，即窗口内的唯一姓名数。
4. 分组排序：最后按日期分组并排序，得到每个日期的统计结果。

内容的提问来源于stack exchange，提问作者Juan Cruz Carrau