SQL日期间隔计数需求：生成首日期并统计区间记录

Hey, let's tackle this date-based grouping and counting problem step by step! I've broken down the solution into clear, actionable parts based on your requirements.

1. Extract the "First Dates" (Your Target FirstDate Table) #

First, we need to identify those key dates where each subsequent date is more than 7 days after the previous first date. A recursive CTE is perfect for this iterative logic—here's how to implement it (adjust date functions for your specific database, e.g., MySQL/PostgreSQL):

WITH FirstDates AS (
    -- Base case: Grab the earliest date for each ID as the first "first date"
    SELECT 
        ID,
        MIN(Date) AS FirstDate
    FROM Table1
    GROUP BY ID

    UNION ALL

    -- Recursive case: Find the next date that's more than 7 days after the last first date
    SELECT 
        fd.ID,
        MIN(t.Date) AS FirstDate
    FROM FirstDates fd
    JOIN Table1 t 
        ON t.ID = fd.ID 
        AND t.Date > DATEADD(day, 7, fd.FirstDate)
    -- Ensure we pick the earliest possible date that meets the 7-day gap
    WHERE NOT EXISTS (
        SELECT 1 
        FROM Table1 t2 
        WHERE t2.ID = fd.ID 
          AND t2.Date > DATEADD(day, 7, fd.FirstDate) 
          AND t2.Date < t.Date
    )
    GROUP BY fd.ID
)
-- Deduplicate and sort the results
SELECT DISTINCT ID, FirstDate 
FROM FirstDates 
ORDER BY ID, FirstDate;

This will output exactly the first dates you listed: 04/01/2018, 12/01/2018, 27/01/2018, 07/02/2018 for ID 12345.

2. Generate the Table with Date, ID, and MinDate #

Based on your example, MinDate is only populated for the first dates themselves (others are NULL). We can join our FirstDates CTE back to the original table to mark these records:

WITH FirstDates AS (
    -- Reuse the recursive CTE from step 1 here
    SELECT 
        ID,
        MIN(Date) AS FirstDate
    FROM Table1
    GROUP BY ID

    UNION ALL

    SELECT 
        fd.ID,
        MIN(t.Date) AS FirstDate
    FROM FirstDates fd
    JOIN Table1 t 
        ON t.ID = fd.ID 
        AND t.Date > DATEADD(day, 7, fd.FirstDate)
    WHERE NOT EXISTS (
        SELECT 1 
        FROM Table1 t2 
        WHERE t2.ID = fd.ID 
          AND t2.Date > DATEADD(day, 7, fd.FirstDate) 
          AND t2.Date < t.Date
    )
    GROUP BY fd.ID
),
DistinctFirstDates AS (
    SELECT DISTINCT ID, FirstDate FROM FirstDates
)
SELECT 
    t.Date,
    t.ID,
    -- Only set MinDate if the record is a first date
    CASE WHEN dfd.FirstDate IS NOT NULL THEN t.Date ELSE NULL END AS MinDate
FROM Table1 t
LEFT JOIN DistinctFirstDates dfd 
    ON t.ID = dfd.ID 
    AND t.Date = dfd.FirstDate
ORDER BY t.ID, t.Date;

This will produce exactly the output you provided, with MinDate populated only for the key first dates.

3. Create the Statistics View #

To count records within 7/30/60/90 days of each first date (and group first dates into 30-day buckets as you mentioned), here are two view implementations:

Option A: Count Records for Each First Date

This view calculates how many records fall within each time window relative to every first date:

CREATE VIEW vw_DateWindowStats AS
WITH FirstDates AS (
    -- Recursive CTE to get first dates
    SELECT 
        ID,
        MIN(Date) AS FirstDate
    FROM Table1
    GROUP BY ID

    UNION ALL

    SELECT 
        fd.ID,
        MIN(t.Date) AS FirstDate
    FROM FirstDates fd
    JOIN Table1 t 
        ON t.ID = fd.ID 
        AND t.Date > DATEADD(day, 7, fd.FirstDate)
    WHERE NOT EXISTS (
        SELECT 1 
        FROM Table1 t2 
        WHERE t2.ID = fd.ID 
          AND t2.Date > DATEADD(day, 7, fd.FirstDate) 
          AND t2.Date < t.Date
    )
    GROUP BY fd.ID
),
DistinctFirstDates AS (
    SELECT DISTINCT ID, FirstDate FROM FirstDates
)
SELECT 
    dfd.ID,
    dfd.FirstDate,
    -- Count records within ±7 days of the first date
    COUNT(CASE WHEN t.Date BETWEEN DATEADD(day, -7, dfd.FirstDate) AND DATEADD(day, 7, dfd.FirstDate) THEN t.ID END) AS Count_7Days,
    -- Count records within ±30 days
    COUNT(CASE WHEN t.Date BETWEEN DATEADD(day, -30, dfd.FirstDate) AND DATEADD(day, 30, dfd.FirstDate) THEN t.ID END) AS Count_30Days,
    -- Count records within ±60 days
    COUNT(CASE WHEN t.Date BETWEEN DATEADD(day, -60, dfd.FirstDate) AND DATEADD(day, 60, dfd.FirstDate) THEN t.ID END) AS Count_60Days,
    -- Count records within ±90 days
    COUNT(CASE WHEN t.Date BETWEEN DATEADD(day, -90, dfd.FirstDate) AND DATEADD(day, 90, dfd.FirstDate) THEN t.ID END) AS Count_90Days
FROM DistinctFirstDates dfd
LEFT JOIN Table1 t ON t.ID = dfd.ID
GROUP BY dfd.ID, dfd.FirstDate
ORDER BY dfd.ID, dfd.FirstDate;

Option B: Group First Dates into 30-Day Buckets

If you want to combine first dates that fall within a 30-day window (like your example where 04/01, 12/01, 27/01 are grouped together), use this version:

CREATE VIEW vw_Grouped30DayStats AS
WITH FirstDates AS (
    -- Recursive CTE to get first dates
    SELECT 
        ID,
        MIN(Date) AS FirstDate
    FROM Table1
    GROUP BY ID

    UNION ALL

    SELECT 
        fd.ID,
        MIN(t.Date) AS FirstDate
    FROM FirstDates fd
    JOIN Table1 t 
        ON t.ID = fd.ID 
        AND t.Date > DATEADD(day, 7, fd.FirstDate)
    WHERE NOT EXISTS (
        SELECT 1 
        FROM Table1 t2 
        WHERE t2.ID = fd.ID 
          AND t2.Date > DATEADD(day, 7, fd.FirstDate) 
          AND t2.Date < t.Date
    )
    GROUP BY fd.ID
),
DistinctFirstDates AS (
    SELECT DISTINCT ID, FirstDate FROM FirstDates
),
GroupedFirstDates AS (
    -- Assign group IDs to first dates that are within 30 days of each other
    SELECT 
        ID,
        FirstDate,
        SUM(CASE WHEN DATEDIFF(day, LAG(FirstDate) OVER (PARTITION BY ID ORDER BY FirstDate), FirstDate) > 30 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY ID ORDER BY FirstDate) + 1 AS GroupID
    FROM DistinctFirstDates
)
SELECT 
    ID,
    MIN(FirstDate) AS GroupStartDate,
    COUNT(t.ID) AS TotalRecordsIn30Days
FROM GroupedFirstDates gfd
LEFT JOIN Table1 t 
    ON t.ID = gfd.ID 
    AND t.Date BETWEEN MIN(gfd.FirstDate) OVER (PARTITION BY gfd.ID, gfd.GroupID) 
                    AND DATEADD(day, 30, MIN(gfd.FirstDate) OVER (PARTITION BY gfd.ID, gfd.GroupID))
GROUP BY ID, GroupID
ORDER BY ID, GroupStartDate;

This will return 2 groups for your example: one starting at 04/01/2018 and another at 07/02/2018.

Quick Notes #

• Database Compatibility: Adjust date functions if you're not using SQL Server (e.g., use DATE_ADD in MySQL, + INTERVAL '7 days' in PostgreSQL).
• Multi-ID Support: All queries are grouped by ID, so they work if you have multiple users/entities in your table.
• Recursion Limits: If you have a lot of first dates, you might need to increase the recursive CTE limit (e.g., add OPTION (MAXRECURSION 0) at the end of the query in SQL Server).

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