如何在Presto中按用户分组并保留列值顺序聚合表列？

要实现按user分组聚合各列且保留原始数据顺序，核心是用GROUP BY配合字符串聚合函数，同时通过行号标记原始顺序——因为GROUP BY本身不会保留行的先后顺序，必须先给每条记录分配一个组内的序号来固定顺序。

以下是主流数据库的具体实现：

一、PostgreSQL 实现 #

使用STRING_AGG函数，配合窗口函数生成组内行号：

WITH ordered_data AS (
    SELECT 
        user,
        "Col 1",
        "Col 2",
        "Col 3",
        -- 生成组内行号，(SELECT NULL)保留原始插入顺序，有自增ID则替换为该字段
        ROW_NUMBER() OVER (PARTITION BY user ORDER BY (SELECT NULL)) AS rn
    FROM user_data
)
SELECT
    user,
    '(' || STRING_AGG("Col 1", ',' ORDER BY rn) || ')' AS "Col 1",
    '(' || STRING_AGG("Col 2", ',' ORDER BY rn) || ')' AS "Col 2",
    '(' || STRING_AGG("Col 3", ',' ORDER BY rn) || ')' AS "Col 3"
FROM ordered_data
GROUP BY user;

二、MySQL 实现 #

使用GROUP_CONCAT函数，通过变量生成组内行号：

-- 初始化变量
SET @row_num = 0;
SET @current_user = '';

WITH ordered_data AS (
    SELECT 
        user,
        "Col 1",
        "Col 2",
        "Col 3",
        -- 按用户分组生成连续行号
        @row_num := IF(@current_user = user, @row_num + 1, 1) AS rn,
        @current_user := user
    FROM user_data
    -- 保留原始顺序，有自增ID则替换为该字段
    ORDER BY user, (SELECT NULL)
)
SELECT
    user,
    CONCAT('(', GROUP_CONCAT("Col 1" ORDER BY rn SEPARATOR ','), ')') AS "Col 1",
    CONCAT('(', GROUP_CONCAT("Col 2" ORDER BY rn SEPARATOR ','), ')') AS "Col 2",
    CONCAT('(', GROUP_CONCAT("Col 3" ORDER BY rn SEPARATOR ','), ')') AS "Col 3"
FROM ordered_data
GROUP BY user;

三、SQL Server 实现 #

使用STRING_AGG函数，通过WITHIN GROUP指定排序：

WITH ordered_data AS (
    SELECT 
        user,
        "Col 1",
        "Col 2",
        "Col 3",
        -- 生成组内行号，(SELECT NULL)保留原始插入顺序，有自增ID则替换为该字段
        ROW_NUMBER() OVER (PARTITION BY user ORDER BY (SELECT NULL)) AS rn
    FROM user_data
)
SELECT
    user,
    CONCAT('(', STRING_AGG("Col 1", ',' WITHIN GROUP (ORDER BY rn)), ')') AS "Col 1",
    CONCAT('(', STRING_AGG("Col 2", ',' WITHIN GROUP (ORDER BY rn)), ')') AS "Col 2",
    CONCAT('(', STRING_AGG("Col 3", ',' WITHIN GROUP (ORDER BY rn)), ')') AS "Col 3"
FROM ordered_data
GROUP BY user;

关键注意事项 #

• 如果表中存在明确的排序依据（如自增ID、创建时间戳），请将所有ORDER BY (SELECT NULL)替换为该字段，这样顺序的稳定性更有保障（数据库原始存储顺序不保证永久不变）。
• 聚合结果的括号格式通过字符串拼接实现，可根据需求调整。

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