Hive中如何实现带100阈值的重置式累计求和？

可以实现这个需求，核心思路是通过递归CTE（Hive 2.1.0及以上版本支持）或者窗口函数结合条件判断来追踪上一次的累计值，当累计值超过100时，从当前金额重新开始计算。

方法一：递归CTE（推荐，逻辑直观） #

递归CTE可以逐行计算累计值，每一步根据上一行的结果决定当前行的求和方式：

1. 创建测试数据表 #

CREATE TABLE test_data (
    row_id INT,
    amount INT
);

INSERT INTO test_data VALUES
(1,20),
(2,30),
(3,60),
(4,70),
(5,90),
(6,10),
(7,120),
(8,110),
(9,40),
(10,30);

2. 执行递归求和查询 #

WITH RECURSIVE cte AS (
    -- 初始行：第一行的累计值等于自身金额
    SELECT 
        row_id,
        amount,
        amount AS sum_val
    FROM test_data
    WHERE row_id = 1
    
    UNION ALL
    
    -- 递归逻辑：判断上一行累计值是否超过100，决定当前行的计算方式
    SELECT 
        td.row_id,
        td.amount,
        CASE 
            WHEN c.sum_val > 100 THEN td.amount  -- 上一行累计超阈值，从当前金额重新开始
            ELSE c.sum_val + td.amount           -- 上一行未超阈值，继续累计
        END AS sum_val
    FROM test_data td
    JOIN cte c ON td.row_id = c.row_id + 1
)
SELECT row_id, amount, sum_val AS sum
FROM cte
ORDER BY row_id;

执行后会得到与预期完全一致的结果：

row_id  amount  sum
1       20      20
2       30      50
3       60      110
4       70      70
5       90      160
6       10      10
7       120     130
8       110     110
9       40      40
10      30      70

方法二：窗口函数分组累计（兼容低版本Hive） #

如果你的Hive版本低于2.1.0，不支持递归CTE，可以通过计算分组ID，在每个分组内做常规累计求和：

WITH grouped_data AS (
    SELECT 
        row_id,
        amount,
        -- 计算累计超过100的次数，生成分组ID
        SUM(CASE WHEN prev_sum > 100 THEN 1 ELSE 0 END) OVER (ORDER BY row_id) AS group_id
    FROM (
        SELECT 
            row_id,
            amount,
            -- 获取上一行的累计值（未重置的）
            LAG(SUM(amount) OVER (ORDER BY row_id), 1, 0) OVER (ORDER BY row_id) AS prev_sum
        FROM test_data
    ) t
)
-- 在每个分组内做累计求和
SELECT 
    row_id,
    amount,
    SUM(amount) OVER (PARTITION BY group_id ORDER BY row_id) AS sum
FROM grouped_data
ORDER BY row_id;

这个方法通过先计算未重置的累计和，再判断上一行是否超过阈值来划分分组，最后在分组内累计求和，同样能得到预期结果。

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