求编写SQL统计各季度活跃员工数的技术解决方案

Got it, let's work through this problem together. I’ve dealt with similar quarterly active user reporting scenarios before, so here’s a step-by-step solution that should fit your needs.

Core Approach #

The key here is to match each employee's active date range (from created to updated quarter) against every quarter in your Quarter_list_table, then count how many employees are active for each individual quarter. We'll use common table expressions (CTEs) to break this down into manageable parts.

SQL Implementation #

We’ll cover two common scenarios based on how your Employee_Table stores quarter values:

Scenario 1: created/updated store quarter strings (e.g., 'Q1-2023') #

If your quarters are stored as human-readable strings, we’ll convert them to dates first to ensure accurate range comparisons:

WITH All_Quarters AS (
    -- Get full list of quarters, with a sortable date for comparison
    SELECT 
        qtr,
        Id,
        -- Convert quarter string to a start date (adjust format if your qtr string differs)
        TO_DATE(REPLACE(qtr, 'Q', '') || '-01', 'Q-YYYY-MM-DD') AS qtr_start_date
    FROM Quarter_list_table
),
Employee_Active_Ranges AS (
    -- Map each employee to their active quarter range (as dates)
    SELECT 
        id AS emp_id,
        TO_DATE(REPLACE(created, 'Q', '') || '-01', 'Q-YYYY-MM-DD') AS active_start,
        TO_DATE(REPLACE(updated, 'Q', '') || '-01', 'Q-YYYY-MM-DD') AS active_end
    FROM Employee_Table
)
-- Count active employees per quarter
SELECT 
    aq.qtr,
    aq.Id,
    COUNT(DISTINCT ear.emp_id) AS active_employee_count
FROM All_Quarters aq
LEFT JOIN Employee_Active_Ranges ear 
    ON aq.qtr_start_date BETWEEN ear.active_start AND ear.active_end
GROUP BY aq.qtr, aq.Id
ORDER BY aq.Id ASC; -- Sort by quarter order (Id is incrementing, so this follows time)

Scenario 2: created/updated store Quarter_list_table.Id values #

If your employee table uses the foreign key Id from Quarter_list_table to track quarters, the query simplifies a lot (no date conversion needed):

WITH All_Quarters AS (
    SELECT Id, qtr FROM Quarter_list_table
),
Employee_Active_Ranges AS (
    SELECT 
        id AS emp_id,
        created AS active_start_id,
        updated AS active_end_id
    FROM Employee_Table
)
SELECT 
    aq.qtr,
    aq.Id,
    COUNT(DISTINCT ear.emp_id) AS active_employee_count
FROM All_Quarters aq
LEFT JOIN Employee_Active_Ranges ear 
    ON aq.Id BETWEEN ear.active_start_id AND ear.active_end_id
GROUP BY aq.qtr, aq.Id
ORDER BY aq.Id ASC;

Key Details to Note #

• COUNT(DISTINCT emp_id): Ensures we don’t double-count employees who are active across multiple quarters.
• LEFT JOIN: Guarantees every quarter from Quarter_list_table appears in the results, even if there are 0 active employees (critical for your bar chart to show all time periods).
• Ignoring status: Per your requirement, employees are counted as active for all quarters between created and updated—regardless of their current status (INACTIVE just means they’re not in the latest quarter, but their historical active quarters still count).

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