如何查询任意滚动6个月内no_show状态工单≥5次的人员

实现逻辑 #

• 核心思路：对每个人员的每一条符合状态的工单，统计以该工单时间为结束点、往前推6个月的滚动窗口内的同状态工单总量，只要有任意一个窗口的总量≥5，该人员就符合筛选要求
• 依赖前提：jobs表存在业务时间字段（以下示例假设为create_time，可根据实际业务替换为预约时间、工单完成时间等）

实现代码（支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、Oracle等） #

WITH staff_job_stats AS (
    SELECT
        s.id AS staff_id,
        j.id AS job_id,
        j.create_time,
        -- 计算当前工单往前6个月的同人员同状态工单数量
        COUNT(DISTINCT j.id) OVER (
            PARTITION BY s.id
            ORDER BY j.create_time
            RANGE BETWEEN INTERVAL 6 MONTH PRECEDING AND CURRENT ROW
        ) AS rolling_6m_count
    FROM staffs s
    JOIN jobs j ON s.id = j.staff_id
    WHERE j.status IN (6, 8) -- 对应no_show状态
)
SELECT DISTINCT staff_id
FROM staff_job_stats
WHERE rolling_6m_count >= 5
ORDER BY staff_id;

兼容老版本数据库的自连接实现 #

如果你的数据库不支持窗口函数，可以用自关联的方式计算滚动窗口：

SELECT DISTINCT j1.staff_id
FROM jobs j1
JOIN jobs j2 
    ON j1.staff_id = j2.staff_id
    AND j2.status IN (6, 8)
    AND j2.create_time BETWEEN DATE_SUB(j1.create_time, INTERVAL 6 MONTH) AND j1.create_time
WHERE j1.status IN (6, 8)
GROUP BY j1.staff_id, j1.id
HAVING COUNT(DISTINCT j2.id) >=5;

注意事项 #

• 请将示例中的create_time替换为你业务中定义6个月时间窗口的实际时间字段
• 如果需要过滤仅staffs表中存在的有效人员，可在自连接方案中补充JOIN staffs s ON j1.staff_id = s.id关联逻辑

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