HIVE SQL实现value值向下顺延3周填充new_value字段方案咨询

实现思路 #

1. 先对周日期字段做格式转换，按时间升序排序，为每行生成连续唯一序号，避免周序错乱
2. 按照规则（每个value覆盖当前周及后续2周，共3周）对行做分组，每组取该组对应基准周的value作为new_value

实现代码 #

WITH week_order AS (
    SELECT 
        wk_date,
        value,
        -- 转换字符串日期为标准日期类型排序，保证周顺序正确
        ROW_NUMBER() OVER(ORDER BY TO_DATE(wk_date, 'MM/dd/yyyy')) AS rn
    FROM test
),
base_val AS (
    SELECT rn AS base_rn, value AS group_val FROM week_order
)
SELECT 
    w.wk_date,
    w.value,
    b.group_val AS new_value
FROM week_order w
LEFT JOIN base_val b
-- 每3行分为一组，每组取第一行的value作为该组所有行的new_value
ON b.base_rn = FLOOR((w.rn - 1)/3) + 1
ORDER BY w.rn;

规则适配说明 #

如果需要调整填充的周数，比如每个value要覆盖当前周及后续3周共4周，只需要修改FLOOR((w.rn - 1)/3)中的分母为4即可，可根据实际业务规则灵活调整。 #

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