AWS Redshift中按dim分组识别value总和最高bucket的SQL问题

问题描述 #

需求：为每个dim分组下的每一行，标记出该分组内value总和最高的bucket对应的行。

原始数据集 #

期望结果 #

当前尝试的SQL代码未按dim分组下每个bucket的value总和排序，而是按单行value排序，无法得到正确结果。

解决方法 #

需要先计算每个dim分组下各bucket的value总和，再确定分组内的最优bucket，最后关联原始数据完成标记。

正确SQL代码 #

WITH bucket_totals AS (
    -- 计算每个dim分组下每个bucket的value总和
    SELECT 
        dim,
        bucket,
        SUM(value) AS total_value
    FROM (
        SELECT 1 as bucket, 45 as value, 1 as add_dim, 1 as dim  
        UNION ALL
        SELECT 2 as bucket, 41 as value, 2 as add_dim, 1 as dim
        UNION ALL
        SELECT 2 as bucket, 42 as value, 3 as add_dim, 1 as dim
        UNION ALL
        SELECT 3 as bucket, 31 as value, 4 as add_dim, 2 as dim
        UNION ALL
        SELECT 3 as bucket, 32 as value, 5 as add_dim, 2 as dim
        UNION ALL
        SELECT 3 as bucket, 33 as value, 6 as add_dim, 2 as dim
    ) raw_data
    GROUP BY dim, bucket
),
top_bucket AS (
    -- 找出每个dim分组下total_value最高的bucket
    SELECT 
        dim,
        bucket AS top_bucket
    FROM (
        SELECT 
            dim,
            bucket,
            ROW_NUMBER() OVER (PARTITION BY dim ORDER BY total_value DESC, bucket ASC) AS rn
        FROM bucket_totals
    ) ranked
    WHERE rn = 1
)
-- 关联原始数据，标记是否属于最优bucket
SELECT 
    r.dim,
    r.add_dim,
    r.bucket,
    r.value,
    CASE WHEN r.bucket = t.top_bucket THEN TRUE ELSE FALSE END AS flag
FROM (
    SELECT 1 as bucket, 45 as value, 1 as add_dim, 1 as dim  
    UNION ALL
    SELECT 2 as bucket, 41 as value, 2 as add_dim, 1 as dim
    UNION ALL
    SELECT 2 as bucket, 42 as value, 3 as add_dim, 1 as dim
    UNION ALL
    SELECT 3 as bucket, 31 as value, 4 as add_dim, 2 as dim
    UNION ALL
    SELECT 3 as bucket, 32 as value, 5 as add_dim, 2 as dim
    UNION ALL
    SELECT 3 as bucket, 33 as value, 6 as add_dim, 2 as dim
) r
JOIN top_bucket t ON r.dim = t.dim
ORDER BY r.dim DESC, r.add_dim DESC;

代码说明 #

1. bucket_totals CTE：按dim和bucket聚合，计算每个bucket的总value，这是判断最优bucket的核心依据。
2. top_bucket CTE：使用ROW_NUMBER()窗口函数，按dim分区，优先按total_value降序、再按bucket升序排序，筛选出每个分组的第一个bucket作为最优bucket。
3. 最终查询：将原始数据与最优bucket关联，通过CASE语句标记每行是否属于当前分组的最优bucket，输出符合要求的结果。

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