如何按rnk_id分组，基于message对应date生成新列？

现有如下数据表：

需要生成mess1、mess2等新列，每个新列提取对应message的date值，并在每个rnk_id分组内重复填充。例如：

• mess1列取每个rnk_id分组中message='mess1'的date，填充到该分组所有行
• mess2列取每个rnk_id分组中message='mess2'的date，填充到该分组所有行
• 以此类推

预期输出示例（展示前3个新列）：

目前已通过以下代码实现mess1列：

first_value(date) OVER (PARTITION BY rnk_id ORDER BY date) as mess1

但无法通过ROWS子句生成其他列，寻求解决方案。

解决方案一：CASE+窗口函数（通用SQL语法） #

不用纠结ROWS子句，直接用CASE配合窗口函数就能搞定，所有支持窗口函数的SQL方言都适用：

SELECT
    date,
    message,
    rnk_id,
    MAX(CASE WHEN message = 'mess1' THEN date END) OVER (PARTITION BY rnk_id) AS mess1,
    MAX(CASE WHEN message = 'mess2' THEN date END) OVER (PARTITION BY rnk_id) AS mess2,
    MAX(CASE WHEN message = 'mess3' THEN date END) OVER (PARTITION BY rnk_id) AS mess3,
    MAX(CASE WHEN message = 'mess4' THEN date END) OVER (PARTITION BY rnk_id) AS mess4,
    MAX(CASE WHEN message = 'mess5' THEN date END) OVER (PARTITION BY rnk_id) AS mess5,
    MAX(CASE WHEN message = 'mess6' THEN date END) OVER (PARTITION BY rnk_id) AS mess6,
    MAX(CASE WHEN message = 'mess7' THEN date END) OVER (PARTITION BY rnk_id) AS mess7
FROM your_table_name;

逻辑说明 #

• 每个CASE语句仅在当前行的message匹配目标值时返回date，否则返回NULL
• MAX()函数会在同一个rnk_id分组里自动忽略NULL，提取到唯一的对应message的日期
• 窗口函数OVER (PARTITION BY rnk_id)会把这个日期填充到分组内的每一行，完全符合需求

解决方案二：PIVOT+JOIN（支持PIVOT的数据库） #

如果使用PostgreSQL、SQL Server这类支持PIVOT语法的数据库，也可以先将数据行转列，再和原表关联：

WITH pivot_dates AS (
    SELECT
        rnk_id,
        mess1,
        mess2,
        mess3,
        mess4,
        mess5,
        mess6,
        mess7
    FROM your_table_name
    PIVOT (
        MAX(date)
        FOR message IN ('mess1' AS mess1, 'mess2' AS mess2, 'mess3' AS mess3, 
                       'mess4' AS mess4, 'mess5' AS mess5, 'mess6' AS mess6, 'mess7' AS mess7)
    ) AS p
)
SELECT
    t.date,
    t.message,
    t.rnk_id,
    pd.mess1,
    pd.mess2,
    pd.mess3,
    pd.mess4,
    pd.mess5,
    pd.mess6,
    pd.mess7
FROM your_table_name t
JOIN pivot_dates pd ON t.rnk_id = pd.rnk_id;

逻辑说明 #

• 先用PIVOT把每个rnk_id分组里的message转成列，对应的date作为值，得到一个仅包含rnk_id和各mess日期的临时表
• 再把这个临时表和原表按rnk_id关联，就能让原表每一行都带上对应分组的所有mess日期值

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