如何在Impala SQL中实现按月份递增的group_concat()效果

你需要的是按个体（id）分组，对每条记录累积拼接当前及之前所有出现过的月份，普通的GROUP_CONCAT是对整个分组聚合，而要实现每行的累积效果，需要结合窗口函数来完成。以下是主流SQL方言的实现方案：

1. MySQL 8.0+ 版本 #

利用GROUP_CONCAT作为窗口聚合函数，指定分组范围与累积窗口：

SELECT 
    id,
    date,
    GROUP_CONCAT(date ORDER BY date SEPARATOR ',') OVER (
        PARTITION BY id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS months
FROM your_table_name;

2. PostgreSQL 版本 #

使用STRING_AGG窗口函数实现累积拼接：

SELECT 
    id,
    date,
    STRING_AGG(date::TEXT, ',' ORDER BY date) OVER (
        PARTITION BY id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS months
FROM your_table_name;

3. BigQuery 版本 #

方案一：直接用STRING_AGG窗口 #

SELECT 
    id,
    date,
    STRING_AGG(CAST(date AS STRING), ',' ORDER BY date) OVER (
        PARTITION BY id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS months
FROM your_table_name;

方案二：数组转字符串 #

SELECT 
    id,
    date,
    ARRAY_TO_STRING(
        ARRAY_AGG(date ORDER BY date) OVER (
            PARTITION BY id 
            ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ), ','
    ) AS months
FROM your_table_name;

兼容低版本MySQL（5.x）的方案 #

如果使用不支持窗口函数的MySQL版本，可通过自连接实现：

SELECT 
    t1.id,
    t1.date,
    GROUP_CONCAT(t2.date ORDER BY t2.date SEPARATOR ',') AS months
FROM your_table_name t1
JOIN your_table_name t2 
    ON t1.id = t2.id 
    AND t2.date <= t1.date
GROUP BY t1.id, t1.date
ORDER BY t1.id, t1.date;

逻辑说明 #

• PARTITION BY id：限定仅在同一个体的记录范围内处理
• ORDER BY date：保证月份按时间顺序拼接，避免乱序
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：明确窗口范围为当前组从第一行到当前行，实现累积拼接效果

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