请求生成按年月统计Waitinglists各等待列表客户数及环比差值的SQL方案

Got it, let's tackle this problem step by step. You need a statistical view that tracks the number of active waiting clients per month for each waiting list, plus the month-over-month difference in those counts. Let's walk through how to build this effectively.

First, let's formalize the Waitinglists table structure based on your description (I'll fill in the missing columns that make sense for this use case):

CREATE TABLE Waitinglists (
    Id INT PRIMARY KEY,
    ClientId INT NOT NULL,
    WaitingListName VARCHAR(100) NOT NULL, -- Name/ID of the specific waiting list
    StartDate DATE NOT NULL,
    EndDate DATE NULL -- NULL indicates the client is still on the waitlist
);

We need a complete list of months to report on—starting from the earliest StartDate in the table up to the current month (or the latest EndDate if you prefer). A recursive CTE is a clean way to generate this sequence:

WITH MonthlyDates AS (
    -- Get the first month present in our waitlist data
    SELECT 
        DATE_TRUNC('month', MIN(StartDate)) AS MonthStart,
        DATE_TRUNC('month', MIN(StartDate)) + INTERVAL '1 month - 1 day' AS MonthEnd
    FROM Waitinglists
    UNION ALL
    -- Recursively add months until we reach the current month
    SELECT 
        MonthStart + INTERVAL '1 month',
        (MonthStart + INTERVAL '1 month') + INTERVAL '1 month - 1 day'
    FROM MonthlyDates
    WHERE MonthStart < DATE_TRUNC('month', CURRENT_DATE)
)

Next, we'll join our monthly date range to the Waitinglists table to count active waiters at the end of each month. A client is considered active if:

• Their wait started on or before the month's end date
• They haven't been removed from the waitlist (EndDate is NULL) OR their wait ended after the month's end date

, MonthlyWaitCounts AS (
    SELECT
        md.MonthStart AS ReportMonth,
        wl.WaitingListName,
        COUNT(DISTINCT wl.ClientId) AS ActiveWaiters
    FROM MonthlyDates md
    LEFT JOIN Waitinglists wl
        ON wl.StartDate <= md.MonthEnd
        AND (wl.EndDate IS NULL OR wl.EndDate > md.MonthEnd)
    GROUP BY md.MonthStart, wl.WaitingListName
)

Use the LAG() window function to pull the previous month's count for each waiting list, then calculate the difference between the current and prior month:

, MonthlyWithMoM AS (
    SELECT
        ReportMonth,
        WaitingListName,
        ActiveWaiters,
        -- Get the previous month's count; default to 0 if there's no prior data
        LAG(ActiveWaiters, 1, 0) OVER (
            PARTITION BY WaitingListName 
            ORDER BY ReportMonth
        ) AS PreviousMonthWaiters,
        -- Calculate the net change from last month
        ActiveWaiters - LAG(ActiveWaiters, 1, 0) OVER (
            PARTITION BY WaitingListName 
            ORDER BY ReportMonth
        ) AS MoMDifference
    FROM MonthlyWaitCounts
)

Put all the pieces together into a reusable view that's easy to query:

CREATE VIEW WaitingListMonthlyStats AS
SELECT
    TO_CHAR(ReportMonth, 'YYYY-MM') AS YearMonth,
    WaitingListName,
    ActiveWaiters,
    PreviousMonthWaiters,
    MoMDifference
FROM MonthlyWithMoM
ORDER BY ReportMonth, WaitingListName;

• Handling Ongoing Waits: The logic treats NULL EndDate as "still waiting," so these clients are counted in every month after their StartDate up to the current month.
• Duplicate Protection: We use COUNT(DISTINCT ClientId) to avoid counting the same client multiple times for the same waitlist (a safe guard even if your primary key prevents duplicate entries).
• Date Range Adjustment: If you want to limit the report to a specific end date instead of the current month, modify the WHERE clause in the MonthlyDates CTE.

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