SQL如何按顺序对连续相同Passenger、City的行分组统计

这是SQL场景下非常典型的「连续序列分组（岛屿问题）」，用窗口函数就能实现，以下是通用解法，兼容所有支持窗口函数的主流数据库（MySQL 8.0+、PostgreSQL、SQL Server、Hive、Spark SQL等）：

解法思路 #

1. 给所有行按PASSENGER分区，按DATE升序排序，比对当前行和上一行的CITY是否一致，不一致则生成一个新的分组标记
2. 对分组标记做累加求和，所有连续同乘客同城市的行会被分到同一个分组ID下
3. 按乘客、城市、分组ID聚合统计行数，再按每组的最小日期排序即可得到目标结果

示例代码 #

WITH step1 AS (
  SELECT 
    PASSENGER,
    CITY,
    DATE,
    -- 同乘客下当前行城市和上一行一致打标记0，不一致打1
    CASE WHEN LAG(CITY) OVER(PARTITION BY PASSENGER ORDER BY DATE) = CITY THEN 0 ELSE 1 END AS flag
  FROM your_table_name -- 替换为实际表名
),
step2 AS (
  SELECT
    PASSENGER,
    CITY,
    DATE,
    -- 累加标记得到唯一分组ID
    SUM(flag) OVER(PARTITION BY PASSENGER ORDER BY DATE) AS group_id
  FROM step1
)
SELECT 
  PASSENGER,
  CITY,
  COUNT(*) AS COUNT
FROM step2
GROUP BY PASSENGER, CITY, group_id
-- 按每组最早日期排序，保证结果顺序符合需求
ORDER BY MIN(DATE) ASC;

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