如何在SQL中为重复数据组排名并实现长表转宽表？

1. 基础示例：为重复序列分组排名 #

给定序列[1,2,3,4,5,1,2,3,4,5]，为每组连续的1-5分配排名的SQL实现如下：

WITH seq_data AS (
    SELECT val, ROW_NUMBER() OVER () AS row_num
    FROM UNNEST([1,2,3,4,5,1,2,3,4,5]) AS val
)
SELECT 
    val,
    SUM(CASE WHEN val = 1 THEN 1 ELSE 0 END) OVER (ORDER BY row_num) AS group_rank
FROM seq_data;

输出结果：

核心逻辑：通过SUM(CASE...) OVER (ORDER BY row_num)累计计数，每次遇到val=1时触发计数加1，以此生成每组的排名。

2. 实际业务需求：长表转宽表 #

结合分组标识+条件聚合，实现长表转宽表的完整SQL方案：

步骤1：生成每组数据的分组标识 #

先为每个ID下的Refcol组（1-4）分配唯一组号：

WITH long_table AS (
    SELECT * FROM (
        VALUES
            (1, 1, '02/02/2022'),
            (1, 2, 'Adam'),
            (1, 3, 'Japan'),
            (1, 4, '1'),
            (1, 1, '03/02/2022'),
            (1, 2, 'Smith'),
            (1, 3, 'England'),
            (1, 4, '0')
    ) AS t(ID, Refcol, Metric)
),
grouped_data AS (
    SELECT 
        ID,
        Refcol,
        Metric,
        SUM(CASE WHEN Refcol = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS group_id
    FROM long_table
)

步骤2：条件聚合转宽表 #

基于分组标识，将不同Refcol的值映射到对应宽表列：

SELECT 
    ID,
    MAX(CASE WHEN Refcol = 1 THEN Metric END) AS time,
    MAX(CASE WHEN Refcol = 2 THEN Metric END) AS name,
    MAX(CASE WHEN Refcol = 3 THEN Metric END) AS location,
    MAX(CASE WHEN Refcol = 4 THEN Metric END) AS Available
FROM grouped_data
GROUP BY ID, group_id
ORDER BY ID, group_id;

输出结果：

说明 #

• SUM(CASE WHEN Refcol=1 THEN 1 ELSE 0 END)用于生成每组的唯一标识，确保同一组的Refcol1-4归为同一group_id
• 条件聚合MAX(CASE...)将不同Refcol的Metric值提取到对应宽表列中，兼容性强，适用于大多数数据库
• 若数据库支持PIVOT语法，也可替换为PIVOT实现，但条件聚合适配范围更广

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