如何在SQL中实现每满90天重置的迭代累计日期间隔计算

To get the desired cumulative day count that resets to 0 whenever the date exceeds 90 days from its group's start date, use a recursive CTE to dynamically track group boundaries and calculate intervals. Here's the implementation:

SQL Code #

WITH ordered_dates AS (
    -- Assign sequential row numbers to dates
    SELECT 
        Dt,
        ROW_NUMBER() OVER (ORDER BY Dt) AS rn
    FROM date_table
),
recursive_groups AS (
    -- Base case: first date starts the initial group
    SELECT 
        Dt,
        rn,
        0 AS group_id,
        Dt AS group_start,
        0 AS desired_output
    FROM ordered_dates
    WHERE rn = 1
    
    UNION ALL
    
    -- Recursive step: process each subsequent date
    SELECT 
        od.Dt,
        od.rn,
        -- Start new group if current date is over 90 days from group start
        CASE WHEN od.Dt > DATE_ADD(rg.group_start, INTERVAL 90 DAY) 
             THEN rg.group_id + 1 
             ELSE rg.group_id END,
        -- Update group start for new groups
        CASE WHEN od.Dt > DATE_ADD(rg.group_start, INTERVAL 90 DAY) 
             THEN od.Dt 
             ELSE rg.group_start END,
        -- Calculate days since group start (0 for new group)
        CASE WHEN od.Dt > DATE_ADD(rg.group_start, INTERVAL 90 DAY) 
             THEN 0 
             ELSE DATEDIFF(od.Dt, rg.group_start) END
    FROM recursive_groups rg
    JOIN ordered_dates od ON od.rn = rg.rn + 1
)
-- Final output with desired cumulative values
SELECT Dt, desired_output
FROM recursive_groups
ORDER BY rn;

Key Details #

1. Ordered Dates: We first assign row numbers to ensure we process dates in chronological order.
2. Recursive Grouping: The CTE starts with the first date as group 0. For each subsequent date, we check if it's more than 90 days after the current group's start—if so, we start a new group with the current date as the new start.
3. Interval Calculation: For each date, we compute days since its group's start (resetting to 0 for new groups).

Dialect Adjustments #

Modify date functions to match your SQL dialect:

• PostgreSQL: Replace DATE_ADD(rg.group_start, INTERVAL 90 DAY) with rg.group_start + INTERVAL '90 days', and DATEDIFF(od.Dt, rg.group_start) with (od.Dt - rg.group_start)::INTEGER.
• SQL Server: Replace DATE_ADD with DATEADD(day, 90, rg.group_start) and use DATEDIFF(day, rg.group_start, od.Dt) for the interval.
• MySQL: The code above uses native MySQL functions.

This code will produce exactly the Desired Output from your sample data, resetting the cumulative count every time the 90-day threshold is crossed.

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