编写Oracle SQL查询获取连续3个及以上员工数超100的ID

要求筛选出ID连续、total_employees大于100且连续ID数量不少于3个的记录。
示例说明：给定数据中，ID为5、6、7、8的记录符合要求，因为它们ID连续且员工数均超过100；而ID为10、13的记录虽员工数超100，但不满足连续3个及以上的条件，故不被选取。

输入数据 #

create table employee(id integer, enroll_date date, total_employees integer);

insert into employee values (1,to_date('01-04-2023','DD-MM-YYYY'),10);
insert into employee values (2,to_date('02-04-2023','DD-MM-YYYY'),109);
insert into employee values (3,to_date('03-04-2023','DD-MM-YYYY'),150);
insert into employee values (4,to_date('04-04-2023','DD-MM-YYYY'),99);
insert into employee values (5,to_date('05-04-2023','DD-MM-YYYY'),145);
insert into employee values (6,to_date('06-04-2023','DD-MM-YYYY'),1455);
insert into employee values (7,to_date('07-04-2023','DD-MM-YYYY'),199);
insert into employee values (8,to_date('08-04-2023','DD-MM-YYYY'),188);
insert into employee values (10,to_date('10-04-2023','DD-MM-YYYY'),188);
insert into employee values (12,to_date('12-04-2023','DD-MM-YYYY'),10);
insert into employee values (13,to_date('13-04-2023','DD-MM-YYYY'),200);

已尝试的SQL语句 #

尝试了以下分组标记员工数的SQL，但未得到预期结果：

select id, enroll_date,total_employees,
        case when total_employees>100 then 1 else 0 end emp_flag,
        SUM(case when total_employees>100 then 1 else 0 end) OVER (ORDER BY id) AS grp,
        id - row_number() over(order by id) as diff, -- group consecutive id's
        ROW_NUMBER() OVER (PARTITION BY CASE WHEN total_employees > 100
                           THEN 1 ELSE 0 END ORDER BY enroll_date) as sal_rn,
        id - ROW_NUMBER() OVER (PARTITION BY CASE WHEN total_employees > 100
                           THEN 1 ELSE 0 END ORDER BY enroll_date) AS sal_grp
from employee
;

解决方案 #

可以通过以下步骤实现需求：

1. 先过滤出total_employees > 100的记录；
2. 用id - ROW_NUMBER() OVER(ORDER BY id)生成连续ID组的唯一标识；
3. 统计每个组的记录数，筛选出记录数≥3的组；
4. 关联回原数据得到符合条件的所有记录。

最终SQL语句：

WITH filtered_emp AS (
    SELECT id, enroll_date, total_employees
    FROM employee
    WHERE total_employees > 100
),
grouped_emp AS (
    SELECT 
        *,
        id - ROW_NUMBER() OVER(ORDER BY id) AS group_id
    FROM filtered_emp
),
group_stats AS (
    SELECT group_id
    FROM grouped_emp
    GROUP BY group_id
    HAVING COUNT(*) >= 3
)
SELECT ge.id, ge.enroll_date, ge.total_employees
FROM grouped_emp ge
JOIN group_stats gs ON ge.group_id = gs.group_id
ORDER BY ge.id;

结果说明 #

执行上述SQL后，将返回ID为5、6、7、8的四条记录，完全符合题目要求。

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