如何用单条SQL查询获取日期间新增与关闭的记录

原始数据表 #

期望输出 #

问题说明 #

如何在SQL中获取指定日期区间内新增或关闭的记录？目前通过逐天执行EXCEPT对比再插入表的方式实现，能否用单条SQL直接得到期望输出？

当前使用的解决方案 #

方案1：每日EXCEPT+UNION（需逐天执行） #

SELECT column1, 'Added' AS Status 
FROM mytable 
WHERE date = '2023-02-03' 
EXCEPT 
SELECT column1 
FROM mytable 
WHERE date = '2023-02-02'

UNION

SELECT column1, 'Closed' AS Status 
FROM mytable 
WHERE date = '2023-02-02' 
EXCEPT 
SELECT column1 
FROM mytable 
WHERE date = '2023-02-03'

该方案只能处理单日对比，无法直接覆盖整个日期区间。

方案2：CTE分组（无法标记再次新增的记录） #

WITH cte AS (
  SELECT column1, DATEADD(day, 1, MIN(Date)) AS Date
  FROM mytable
  WHERE Date < (SELECT MAX(Date) FROM mytable)
  GROUP BY column1
  HAVING COUNT(1) = 1
  UNION 
  SELECT column1, Date
  FROM mytable
),
cte2 AS (
  SELECT c.*, IIF(t.Date IS NOT NULL, 'Added', 'Closed') AS status
  FROM cte c 
  LEFT JOIN mytable t ON t.column1 = c.column1 AND t.Date = c.Date
)
SELECT column1, MIN(Date) AS Date, status 
FROM cte2
GROUP BY column1, status
ORDER BY MIN(Date), column1;

此方案无法处理类似Value1在2月1日新增、2月2日关闭、2月3日再次新增的场景。

单条SQL解决方案 #

可以通过窗口函数LAG和LEAD判断每条记录的前后状态，一次性生成目标结果：

WITH date_status AS (
  SELECT 
    column1,
    Date,
    -- 获取当前记录的前一天同column1的日期
    LAG(Date) OVER (PARTITION BY column1 ORDER BY Date) AS prev_date,
    -- 获取当前记录的后一天同column1的日期
    LEAD(Date) OVER (PARTITION BY column1 ORDER BY Date) AS next_date
  FROM mytable
),
status_flags AS (
  SELECT
    column1,
    Date,
    CASE
      -- 前一天无记录/间隔超过1天 → 标记为新增
      WHEN prev_date IS NULL OR DATEDIFF(day, prev_date, Date) > 1 THEN 'Added（新增）'
      -- 后一天无记录/间隔超过1天 → 标记为关闭
      WHEN next_date IS NULL OR DATEDIFF(day, Date, next_date) > 1 THEN 'Closed（关闭）'
      -- 连续存在的记录不标记状态
      ELSE NULL
    END AS Status
  FROM date_status
)
SELECT column1, Date, Status
FROM status_flags
WHERE Status IS NOT NULL
ORDER BY Date, column1;

逻辑说明 #

1. date_status CTE：通过LAG和LEAD窗口函数，为每个column1的每条记录关联其前一天、后一天的存在情况。
2. status_flags CTE：根据前后日期的间隔判断状态：
   • 首次出现或间隔多日后再次出现 → 新增
   • 最后一次出现或间隔多日不再出现 → 关闭
   • 连续存在的记录不生成状态标记
3. 最后过滤掉无状态的记录，按日期和column1排序，得到期望输出。

该方案支持全日期区间的一次性计算，同时兼容记录多次新增、关闭的场景。

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