如何使用GROUP_CONCAT拼接行值并按GAP标志拆分分组？

要实现根据GAP列拆分路径的需求，可以借助SQLite的窗口函数为每个子路径生成分组标识，再进行分组拼接。以下是具体实现：

完整SQL语句 #

WITH path_groups AS (
    SELECT
        PATH_ID,
        PATH_SEQ,
        FROM_CITY,
        SUM(CASE WHEN prev_gap = 'Y' THEN 1 ELSE 0 END) OVER (PARTITION BY PATH_ID ORDER BY PATH_SEQ) + 1 AS SUBPATH_ID
    FROM (
        SELECT
            PATH_ID,
            PATH_SEQ,
            FROM_CITY,
            LAG(GAP) OVER (PARTITION BY PATH_ID ORDER BY PATH_SEQ) AS prev_gap
        FROM your_table_name -- 替换为实际表名
    ) t
)
SELECT
    PATH_ID,
    SUBPATH_ID,
    GROUP_CONCAT(FROM_CITY ORDER BY PATH_SEQ) AS CITIES
FROM path_groups
GROUP BY PATH_ID, SUBPATH_ID;

逻辑说明 #

1. 获取前一行的GAP标识：内层子查询使用LAG()窗口函数，针对每个PATH_ID按PATH_SEQ排序，获取当前行的前一行的GAP值并命名为prev_gap，以此判断当前行是否为新子路径的起点。
2. 生成子路径分组ID：外层CTE通过SUM() OVER()窗口函数，对每个PATH_ID累计统计到当前行为止prev_gap = 'Y'的次数，再加1得到SUBPATH_ID。每次遇到前一行GAP='Y'，说明当前行属于新子路径，分组ID自动递增。
3. 分组拼接城市：最后按PATH_ID和SUBPATH_ID分组，使用GROUP_CONCAT()按PATH_SEQ排序拼接城市，得到拆分后的独立子路径。

执行该语句后，即可得到符合需求的结果：将存在断点的路径拆分为独立行，同时保留原PATH_ID和子路径标识。

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