sql - 递归 CTE - 合并开始和结束日期

Question

我有下表:

row_num customer_status    effective_from_datetime
------- ------------------ -----------------------
1       Active             2011-01-01
2       Active             2011-01-02
3       Active             2011-01-03
4       Suspended          2011-01-04
5       Suspended          2011-01-05
6       Active             2011-01-06

并且我正在尝试实现以下结果，即将具有相同状态的连续行合并到具有有效起始日期和截止日期范围的一行中:

customer_status effective_from_datetime effective_to_datetime
--------------- ----------------------- ---------------------
Active          2011-01-01              2011-01-04
Suspended       2011-01-04              2011-01-06
Active          2011-01-06              NULL

我可以使用递归 CTE 来根据下一行输出正确的 effective_to_datetime，但在合并范围时遇到问题。

生成示例数据的代码:

CREATE TABLE #temp
(
row_num INT IDENTITY(1,1),
customer_status VARCHAR(10),
effective_from_datetime DATE
)

INSERT INTO #temp
VALUES 
('Active','2011-01-01')
,('Active','2011-01-02')
,('Active','2011-01-03')
,('Suspended','2011-01-04')
,('Suspended','2011-01-05')
,('Active','2011-01-06')

Answer 1

编辑 SQL 根据评论更新。

WITH
  group_assigned_data AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY customer_status ORDER BY effective_from_date) AS status_sequence_id,
    ROW_NUMBER() OVER (                             ORDER BY effective_from_date) AS sequence_id,
    customer_status,
    effective_from_date
  FROM
    your_table
)
,
  grouped_data AS
(
  SELECT
    customer_status,
    MIN(effective_from_date)   AS min_effective_from_date,
    MAX(effective_from_date)   AS max_effective_from_date
  FROM
    group_assigned_data
  GROUP BY
    customer_status,
    sequence_id - status_sequence_id
)
SELECT
  [current].customer_status,
  [current].min_effective_from_date       AS effective_from,
  [next].min_effective_from_date          AS effective_to
FROM
  grouped_data   AS [current]
LEFT JOIN
  grouped_data   AS [next]
    ON [current].max_effective_from_date = [next].min_effective_from_date + 1
ORDER BY
  [current].min_effective_from_date

这不是递归，但这可能是一件好事。

它不处理数据中的空白。为了解决这个问题，您可以创建一个日历表，其中包含每个相关日期，并加入该表以用“未知”状态填充缺失的日期，然后针对该日期运行查询。 (事实上​​，您可以使用上面的 CTE 使用的 CTE 来执行此操作)。

目前...
- 如果缺少第 2 行，则不会改变结果
- 如果缺少第 3 行，则第一行的 end_date 将更改

可以通过准备数据或其他方法来确定不同的行为。不过，我们需要了解您需要的业务逻辑。

如果任何一个日期可以有多个状态条目，您需要定义您希望它遵循的逻辑。目前，该行为尚未定义，但您只需将 customer_status 添加到 ROW_NUMBER() 的 ORDER BY 部分即可更正该问题。