如何按ID分区创建包含后续行值列表的新列？

现有如下结构的表（按ID分组，date_time递增排序）：

需要新增一列（比如命名为后续值列表），该列包含当前行之后同ID的所有s_val值组成的列表，最后一行的该列为空列表。

核心思路 #

先给每个ID分组内的行按date_time排序标记行号，再通过自连接聚合当前行之后的所有s_val值，生成目标列表。

1. PostgreSQL 实现 #

使用array_agg生成数组格式的列表，保留NULL值：

WITH ranked_data AS (
    SELECT 
        date_time,
        ID,
        s_val,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date_time) AS rn
    FROM your_table_name
)
SELECT 
    rd.date_time,
    rd.ID,
    rd.s_val,
    ARRAY_AGG(rd2.s_val ORDER BY rd2.rn) AS 后续值列表
FROM ranked_data rd
LEFT JOIN ranked_data rd2 
    ON rd.ID = rd2.ID AND rd.rn < rd2.rn
GROUP BY rd.date_time, rd.ID, rd.s_val, rd.rn
ORDER BY rd.ID, rd.rn;

2. MySQL 8.0+ 实现 #

使用JSON_ARRAYAGG生成JSON格式的列表，便于后续解析：

WITH ranked_data AS (
    SELECT 
        date_time,
        ID,
        s_val,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date_time) AS rn
    FROM your_table_name
)
SELECT 
    rd.date_time,
    rd.ID,
    rd.s_val,
    JSON_ARRAYAGG(rd2.s_val ORDER BY rd2.rn) AS 后续值列表
FROM ranked_data rd
LEFT JOIN ranked_data rd2 
    ON rd.ID = rd2.ID AND rd.rn < rd2.rn
GROUP BY rd.date_time, rd.ID, rd.s_val, rd.rn
ORDER BY rd.ID, rd.rn;

3. SQL Server 实现 #

使用STRING_AGG生成逗号分隔的字符串，如需列表格式可自行拼接括号：

WITH ranked_data AS (
    SELECT 
        date_time,
        ID,
        s_val,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date_time) AS rn
    FROM your_table_name
)
SELECT 
    rd.date_time,
    rd.ID,
    rd.s_val,
    '[' + STRING_AGG(ISNULL(CAST(rd2.s_val AS VARCHAR(100)), 'NULL'), ', ') + ']' AS 后续值列表
FROM ranked_data rd
LEFT JOIN ranked_data rd2 
    ON rd.ID = rd2.ID AND rd.rn < rd2.rn
GROUP BY rd.date_time, rd.ID, rd.s_val, rd.rn
ORDER BY rd.ID, rd.rn;

注：ISNULL函数用于保留原数据中的NULL值，若不需要可移除。

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