如何通过动态SQL或循环自动化实现月度员工入职（Joiners）与离职（Leavers）统计？

Hey there! Let's break down how to solve this problem in SQL—both with a clean static approach (no loops needed) and an automated solution using loops/dynamic SQL, which is exactly what you're asking for.

First, let's start with a straightforward approach that avoids manual table/CTE creation for each month. This works great for one-time reports or when you need stats for all available months in your dataset.

Assuming your base table is named employee_snapshots with columns snapshot_date, employee_id, and employer_id, here's the query:

WITH monthly_snapshots AS (
    -- Create a clean, distinct list of employees per month (to handle append-only duplicates)
    SELECT
        DATE_TRUNC('month', snapshot_date)::DATE AS report_month,
        employee_id,
        employer_id
    FROM employee_snapshots
    GROUP BY DATE_TRUNC('month', snapshot_date)::DATE, employee_id, employer_id
),
all_report_months AS (
    -- Get every unique month present in your data
    SELECT DISTINCT report_month FROM monthly_snapshots ORDER BY report_month
)
SELECT
    arm.report_month,
    COALESCE(curr.employer_id, prev.employer_id) AS employer_id,
    -- Count joiners: employees in current month but not the previous
    COUNT(DISTINCT CASE WHEN prev.employee_id IS NULL THEN curr.employee_id END) AS joiner_count,
    -- Count leavers: employees in previous month but not current
    COUNT(DISTINCT CASE WHEN curr.employee_id IS NULL THEN prev.employee_id END) AS leaver_count
FROM all_report_months arm
LEFT JOIN monthly_snapshots curr
    ON arm.report_month = curr.report_month
LEFT JOIN monthly_snapshots prev
    ON arm.report_month - INTERVAL '1 month' = prev.report_month
    AND curr.employee_id = prev.employee_id
    AND curr.employer_id = prev.employer_id
GROUP BY arm.report_month, COALESCE(curr.employer_id, prev.employer_id)
ORDER BY arm.report_month, employer_id;

This query:

1. Cleans up your append-only data by creating a distinct monthly snapshot of employees
2. Compares each month to the prior one using a left join
3. Calculates joiners and leavers by checking presence in adjacent months

If you need to run this for a specific date range repeatedly (or schedule it), using a stored procedure with a loop is absolutely feasible. The exact syntax varies by database, but here's an example for PostgreSQL (with notes for SQL Server below):

PostgreSQL Stored Procedure #

CREATE OR REPLACE PROCEDURE generate_joiner_leaver_stats(start_date DATE, end_date DATE)
LANGUAGE plpgsql
AS $$
DECLARE
    current_month DATE := DATE_TRUNC('month', start_date)::DATE;
    prev_month DATE;
BEGIN
    -- Create a results table if it doesn't exist (adjust column types to match your data)
    CREATE TABLE IF NOT EXISTS joiner_leaver_stats (
        report_month DATE,
        employer_id INT,
        joiner_count INT DEFAULT 0,
        leaver_count INT DEFAULT 0,
        PRIMARY KEY (report_month, employer_id)
    );

    -- Loop through each month in the specified range
    WHILE current_month <= DATE_TRUNC('month', end_date)::DATE LOOP
        prev_month := current_month - INTERVAL '1 month'::DATE;

        -- Insert/update stats for the current month
        INSERT INTO joiner_leaver_stats (report_month, employer_id, joiner_count, leaver_count)
        SELECT
            current_month,
            COALESCE(curr.employer_id, prev.employer_id) AS employer_id,
            COUNT(DISTINCT CASE WHEN prev.employee_id IS NULL THEN curr.employee_id END),
            COUNT(DISTINCT CASE WHEN curr.employee_id IS NULL THEN prev.employee_id END)
        FROM (
            SELECT employee_id, employer_id 
            FROM employee_snapshots 
            WHERE DATE_TRUNC('month', snapshot_date) = current_month
            GROUP BY employee_id, employer_id
        ) curr
        FULL OUTER JOIN (
            SELECT employee_id, employer_id 
            FROM employee_snapshots 
            WHERE DATE_TRUNC('month', snapshot_date) = prev_month
            GROUP BY employee_id, employer_id
        ) prev ON curr.employee_id = prev.employee_id AND curr.employer_id = prev.employer_id
        GROUP BY COALESCE(curr.employer_id, prev.employer_id)
        ON CONFLICT (report_month, employer_id) DO UPDATE -- Handle updates if re-running for the same month
        SET joiner_count = EXCLUDED.joiner_count,
            leaver_count = EXCLUDED.leaver_count;

        -- Move to the next month
        current_month := current_month + INTERVAL '1 month'::DATE;
    END LOOP;
END;
$$;

-- Call the procedure (example: stats for Jan 2023 to May 2024)
CALL generate_joiner_leaver_stats('2023-01-01', '2024-05-31');

SQL Server Adjustments #

For SQL Server, tweak the syntax slightly:

• Use DATEADD(month, -1, current_month) instead of current_month - INTERVAL '1 month'
• Replace CREATE OR REPLACE PROCEDURE with CREATE PROCEDURE
• Use WHILE current_month <= DATEFROMPARTS(YEAR(end_date), MONTH(end_date), 1) for date boundary handling
• Use MERGE instead of ON CONFLICT to handle upserts

• Static SQL is ideal for ad-hoc reports: It's faster, easier to debug, and avoids loop overhead.
• Loops/Dynamic SQL work great for automation: Use stored procedures if you need to run this on a schedule or for custom date ranges without rewriting the query each time.
• Append-only data handling: The GROUP BY in our monthly snapshots ensures we don't count duplicate entries from your append storage.

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