如何基于CAT_QTY总和拆分表行？Hive SQL实现连续序号展开

现有临时表T1包含两行数据，需求是按CAT_ID分组后，根据每行的CAT_QTY拆分行：每个CAT_CD对应自身CAT_QTY数量的行，且CAT_QTY为全局连续递增的序号。

原表定义 #

CREATE OR REPLACE TEMP VIEW T1 AS
SELECT 'ISAC' AS CAT_ID, 'STC-5-DM' AS CAT_CD, 3 AS CAT_QTY
UNION ALL
SELECT 'ISAC', 'STC-15-DM', 2;

尝试的错误代码 #

WITH TotalQty AS (
    SELECT CAT_ID, CAT_CD, CAT_QTY,
           SUM(CAT_QTY) OVER (PARTITION BY CAT_ID) AS total_qty
    FROM T1
),
ExpandedRows AS (
    SELECT CAT_ID, CAT_CD, 
           EXPLODE(SEQUENCE(1, (SELECT MAX(total_qty) FROM TotalQty))) AS CAT_QTY
    FROM TotalQty
)
SELECT CAT_ID, CAT_CD, CAT_QTY
FROM ExpandedRows
ORDER BY CAT_ID, CAT_QTY;

预期输出 #

CAT_ID  CAT_CD      CAT_QTY
ISAC    STC-5-DM    1
ISAC    STC-5-DM    2
ISAC    STC-5-DM    3
ISAC    STC-15-DM   4
ISAC    STC-15-DM   5

原代码问题 #

这段代码的问题在于，每个CAT_CD都会生成从1到总数量的所有行，导致重复生成不符合需求的行（比如STC-5-DM和STC-15-DM都会生成1-5的行），最终结果冗余，无法得到每个CAT_CD专属的连续递增序号。

正确解决方案 #

要实现全局连续递增的序号，需要先计算每个CAT_CD的序号起止范围，再生成对应行：

WITH CumulativeQty AS (
    SELECT 
        CAT_ID, 
        CAT_CD, 
        CAT_QTY,
        -- 计算当前CAT_CD的起始序号：前面所有CAT_CD的数量之和 +1，用COALESCE处理第一个分组的空值
        COALESCE(SUM(CAT_QTY) OVER (PARTITION BY CAT_ID ORDER BY CAT_CD ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) + 1 AS start_seq,
        -- 计算当前CAT_CD的结束序号：累计到当前的总数量
        SUM(CAT_QTY) OVER (PARTITION BY CAT_ID ORDER BY CAT_CD) AS end_seq
    FROM T1
),
ExpandedRows AS (
    SELECT 
        CAT_ID, 
        CAT_CD, 
        EXPLODE(SEQUENCE(start_seq, end_seq)) AS CAT_QTY
    FROM CumulativeQty
)
SELECT CAT_ID, CAT_CD, CAT_QTY
FROM ExpandedRows
ORDER BY CAT_ID, CAT_QTY;

代码说明 #

1. CumulativeQty阶段：使用窗口函数计算每个CAT_CD的序号起止值。SUM(CAT_QTY) OVER (...)按CAT_ID分组、CAT_CD排序，累计前面所有行的数量，配合COALESCE处理第一个CAT_CD的起始空值问题，得到准确的起始和结束序号。
2. ExpandedRows阶段：用EXPLODE(SEQUENCE(start_seq, end_seq))生成每个CAT_CD对应的连续序号行。
3. 最后排序输出即可得到预期结果。

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