SQL如何按ID、QTY分组计算新建TYPE_NUMERIC列和、最早日期及行数

实现方案 #

从你给出的预期输出倒推，实际分组维度为ID、QTY、DATE，对应SQL如下：

WITH calc_type AS (
    -- 第一步计算TYPE_NUMERIC字段
    SELECT 
        *,
        CASE TYPE
            WHEN 'IN' THEN -1 
            WHEN 'OUT' THEN 1
        END AS TYPE_NUMERIC
    FROM `SALES_TABLE`
),
rank_rows AS (
    -- 给每个分组内的行按日期排序，标记排序第一的行用于后续取对应TYPE
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ID, QTY, DATE ORDER BY DATE ASC) AS rn
    FROM calc_type
)
-- 分组聚合得到最终结果
SELECT 
    ID,
    QTY,
    SUM(TYPE_NUMERIC) AS SUM_TYPE_NUMERIC,
    MIN(DATE) AS first_DATE,
    MAX(CASE WHEN rn = 1 THEN TYPE END) AS TYPE,
    COUNT(*) AS count_rows
FROM rank_rows
GROUP BY ID, QTY, DATE
ORDER BY ID, QTY, first_DATE;

如果你确实需要仅按ID和QTY两个维度分组，调整窗口函数的分区规则和聚合的分组规则即可：

WITH calc_type AS (
    SELECT 
        *,
        CASE TYPE
            WHEN 'IN' THEN -1 
            WHEN 'OUT' THEN 1
        END AS TYPE_NUMERIC
    FROM `SALES_TABLE`
),
rank_rows AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ID, QTY ORDER BY DATE ASC) AS rn
    FROM calc_type
)
SELECT 
    ID,
    QTY,
    SUM(TYPE_NUMERIC) AS SUM_TYPE_NUMERIC,
    MIN(DATE) AS first_DATE,
    MAX(CASE WHEN rn = 1 THEN TYPE END) AS TYPE,
    COUNT(*) AS count_rows
FROM rank_rows
GROUP BY ID, QTY
ORDER BY ID, QTY;

注意事项 #

如果同一个最早日期下存在多条不同TYPE的记录，可以调整ROW_NUMBER()的排序规则，比如补充其他字段作为排序依据，来确定最终取哪个TYPE值。

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