SQL数据处理需求：删除重复行并调整size_category分类

原查询语句 #

SELECT
    CAST(u.balance_date AS date) AS snapshot_date,
    p.process_name,
    f.function_name,
    u.unit_type,
    u.job_action,
    u.size_category,
    SUM(u.unit_count) AS units
FROM
    units u
    INNER JOIN processes p ON u.process_id = p.process_id
    INNER JOIN functions f ON u.function_id = f.function_id
GROUP BY
   1,2,3,4,5,6

当前查询结果 #

需求说明 #

• 同一维度组合（snapshot_date、process_name、function_name、unit_type、job_action）下，若size_category为Total和Undefined对应的units值相同，删除该Undefined行；
• 若size_category为Undefined但units值与Total不同，将其size_category改为Small。

期望输出结果 #

解决方案 #

通过窗口函数获取同一维度下Total的units值，再按条件筛选和修改分类：

WITH grouped_data AS (
    SELECT
        CAST(u.balance_date AS date) AS snapshot_date,
        p.process_name,
        f.function_name,
        u.unit_type,
        u.job_action,
        u.size_category,
        SUM(u.unit_count) AS units,
        -- 提取同一维度分组下Total对应的units值
        MAX(CASE WHEN u.size_category = 'Total' THEN SUM(u.unit_count) END) 
            OVER(PARTITION BY CAST(u.balance_date AS date), p.process_name, f.function_name, u.unit_type, u.job_action) AS total_units
    FROM
        units u
        INNER JOIN processes p ON u.process_id = p.process_id
        INNER JOIN functions f ON u.function_id = f.function_id
    GROUP BY
       1,2,3,4,5,6
)
SELECT
    snapshot_date,
    process_name,
    function_name,
    unit_type,
    job_action,
    -- 将Undefined替换为Small
    CASE 
        WHEN size_category = 'Undefined' THEN 'Small'
        ELSE size_category
    END AS size_category,
    units
FROM grouped_data
-- 过滤掉与Total值重复的Undefined行
WHERE NOT (size_category = 'Undefined' AND units = total_units)
ORDER BY snapshot_date, process_name, function_name, unit_type, job_action, size_category;

逻辑说明 #

1. 用CTEgrouped_data完成原查询的聚合，同时通过窗口函数MAX() OVER(PARTITION BY ...)计算每个维度分组下Total对应的units值；
2. 外层查询通过CASE语句将Undefined分类替换为Small；
3. 用WHERE子句过滤掉Undefined且units等于total_units的冗余行；
4. 最后添加排序保证结果顺序与期望一致。

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