如何按Activity分组基于StartDate序列推导对应EndDate的SQL实现

原语句的核心问题 #

之前写的SQL无法得到正确结果，主要原因有三点：

• 同表关联逻辑错误：子查询中t.Activity=Activity没有通过表别名区分内、外层表的同名字段，会被解析为同表字段自等值判断，完全无法匹配同活动分组下的后续记录
• 语法不完整：GROUP BY 子句后没有指定具体分组字段，本身不符合SQL执行规范，会触发语法报错
• 过滤逻辑错误：使用WHERE条件筛选存在后续日期的记录，会直接把分组下最后一条没有后续日期的记录筛除，导致最终结果缺行

可落地的实现代码 #

方案1：窗口函数实现（适配MySQL 8.0+、PostgreSQL、SQL Server、Oracle等现代数据库） #

使用内置的LEAD()窗口函数可以最高效实现需求，该函数的作用就是在指定分组、排序规则下，提取当前行后N行的字段值：

SELECT
  Activity,
  ID,
  StartDate,
  -- 同Activity分组内，按StartDate、ID升序，取下一条记录的StartDate作为EndDate；无后续值时替换为'??'
  -- Oracle环境可将IFNULL替换为NVL，逻辑保持一致
  IFNULL(
    LEAD(StartDate) OVER (
      PARTITION BY Activity
      ORDER BY StartDate, ID
    ),
    '??'
  ) AS EndDate
FROM t
ORDER BY Activity, ID;

执行后输出结果和预期完全一致：

方案2：自关联实现（适配MySQL 5.x等不支持窗口函数的老版本数据库） #

如果运行环境没有窗口函数能力，可以通过左关联+分组聚合的方式实现相同逻辑：

SELECT
  cur.Activity,
  cur.ID,
  cur.StartDate,
  -- 同活动下，取比当前记录StartDate大的最小StartDate（即相邻的下一条记录日期），无匹配时替换为'??'
  IFNULL(MIN(nxt.StartDate), '??') AS EndDate
FROM t cur
-- 左连接保留所有原表记录，即使没有后续日期也不会被筛除
LEFT JOIN t nxt
  ON cur.Activity = nxt.Activity
  AND nxt.StartDate > cur.StartDate
-- 按当前表的全量字段分组，避免开启ONLY_FULL_GROUP_BY模式时报错
GROUP BY cur.Activity, cur.ID, cur.StartDate
ORDER BY cur.Activity, ID;

两种方案的执行效果完全一致，实际使用时根据数据库版本选择即可。

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