如何用Apache Hive/Spark SQL实现相邻行数值重计算逻辑？

需求回顾 #

针对环形布局的12小时时区数据，需计算每个时区的重算值：自身数值 + 前2个时区数值 + 后2个时区数值（环形处理边界，如12点的后2个时区为1、2点，1点的前2个时区为12、11点）。

方案1：自连接+条件匹配（直观易理解） #

通过自连接关联目标时区及其需要聚合的相邻时区，再分组求和。假设原始表名为hourly_data，结构为hh INT, val INT。

SELECT 
    t.hh,
    SUM(t2.val) AS recalculated_val
FROM 
    hourly_data t
JOIN 
    hourly_data t2 ON 
        -- 匹配自身
        t2.hh = t.hh
        -- 匹配前1个时区（环形处理）
        OR t2.hh = CASE WHEN t.hh = 1 THEN 12 ELSE t.hh - 1 END
        -- 匹配前2个时区（环形处理）
        OR t2.hh = CASE WHEN t.hh = 1 THEN 11 WHEN t.hh = 2 THEN 12 ELSE t.hh - 2 END
        -- 匹配后1个时区（环形处理）
        OR t2.hh = CASE WHEN t.hh = 12 THEN 1 ELSE t.hh + 1 END
        -- 匹配后2个时区（环形处理）
        OR t2.hh = CASE WHEN t.hh = 11 THEN 1 WHEN t.hh = 12 THEN 2 ELSE t.hh + 2 END
GROUP BY 
    t.hh
ORDER BY 
    t.hh;

验证结果

以12点为例，关联的时区为12、11、10、1、2，对应数值求和：4+5+1+5+4=19，完全符合需求。

方案2：窗口函数+数据扩展（高性能） #

通过复制一份数据并将小时数+12，构造环形连续的数据集，再用滑动窗口聚合求和，适合大数据量场景。

WITH extended_data AS (
    -- 原始数据 + 复制一份小时数+12的扩展数据
    SELECT hh, val FROM hourly_data
    UNION ALL
    SELECT hh + 12 AS hh, val FROM hourly_data
)
SELECT 
    -- 将扩展的小时数还原为1-12
    CASE WHEN hh > 12 THEN hh - 12 ELSE hh END AS hh,
    -- 滑动窗口：取当前行前后各2行（共5行）求和
    SUM(val) OVER (ORDER BY hh ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS recalculated_val
FROM 
    extended_data
-- 仅保留原始1-12小时的计算结果
WHERE hh BETWEEN 3 AND 14
ORDER BY 
    hh;

逻辑说明

扩展数据后，12点的后续时区（1、2点）对应扩展数据中的13、14点，前序时区（10、11点）对应原始数据的10、11点，滑动窗口会自动包含这5个点的数值并求和。

方案对比 #

• 自连接方案：逻辑直白，调试简单，但数据量较大时会产生笛卡尔积，性能稍差。
• 窗口函数方案：避免数据膨胀，性能更优，适合大规模数据处理，但需要理解环形数据扩展的思路。

内容的提问来源于stack exchange，提问作者이영표