SQL新手求助：如何按月统计历史活跃员工数量

你的原有方法问题分析 #

你当前的查询存在两个核心问题：

1. 生成的是每日日期而非月份维度的数据，关联后每个活跃员工会在其活跃期内的每一天都生成一条记录，导致大量重复行；
2. 使用count(ID) over (order by Dt)是累计窗口函数，它会计算从最早日期到当前日期的累计员工数，而非单独统计每个月的活跃员工数量，因此统计结果完全不符合需求。

正确实现方案（Oracle环境） #

以下是适配你的数据和需求的SQL语句，核心思路是先生成需要统计的月份列表，再关联员工表判断活跃状态，最后按月统计：

WITH month_list AS (
    -- 生成覆盖所有员工入职到离职日期范围的月份列表
    SELECT ADD_MONTHS(TRUNC(MIN(start_dt), 'MM'), LEVEL - 1) AS month_start
    FROM employees
    CONNECT BY ADD_MONTHS(TRUNC(MIN(start_dt), 'MM'), LEVEL - 1) <= TRUNC(MAX(end_dt), 'MM')
)
SELECT 
    TO_CHAR(ml.month_start, 'FMMonth YYYY') AS "Date",
    COUNT(DISTINCT e.id) AS "Count"
FROM month_list ml
LEFT JOIN employees e 
    -- 判断员工活跃时间段与当前月份是否有重叠
    ON ml.month_start <= TRUNC(e.end_dt, 'MM') 
    AND ADD_MONTHS(ml.month_start, 1) > TRUNC(e.start_dt, 'MM')
GROUP BY ml.month_start
ORDER BY ml.month_start DESC;

逻辑说明 #

1. 生成月份列表：
   通过CONNECT BY语法，以员工表中最早的入职月份为起点，最晚的离职月份为终点，生成所有需要统计的月份的第一天。
2. 关联判断活跃状态：
   关联条件确保员工的活跃周期与当前统计月份存在重叠：
   • ml.month_start <= TRUNC(e.end_dt, 'MM')：员工的离职月份不早于统计月份
   • ADD_MONTHS(ml.month_start, 1) > TRUNC(e.start_dt, 'MM')：员工的入职月份不晚于统计月份
3. 统计与排序：
   用COUNT(DISTINCT e.id)避免同一员工在同一月份被重复计数，最后按月份倒序排列，得到你期望的结果格式。

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