Oracle中基于状态变更表计算指定人员年度累计离岗天数的方法

核心思路 #

将每一次离岗的起始、结束日期配对，逐段计算离岗天数后求和即可，你可以直接用单条SQL实现，不需要额外的编码逻辑，支持传入PersonID和统计年度两个参数。

注意前提 #

假设ScheduleHistory表的DateChanged字段为Oracle DATE类型，若存储为字符串格式，需先通过TO_DATE(DateChanged, 'MM/DD/YYYY')转换为日期类型再计算。

实现SQL #

SELECT 
    SUM(leave_end_date - leave_start_date) AS total_leave_days
FROM (
    SELECT
        DateChanged AS leave_start_date,
        -- 匹配本次离岗对应的下一次返岗日期
        LEAD(DateChanged, 1) OVER (PARTITION BY PersonID ORDER BY DateChanged) AS leave_end_date,
        NextStatusID
    FROM ScheduleHistory
    WHERE PersonID = :input_person_id -- 传入的人员ID参数，示例为111
        AND EXTRACT(YEAR FROM DateChanged) = :input_year -- 传入的统计年度参数，示例为2020
) t
-- 仅筛选离岗开始的记录
WHERE NextStatusID = 2
-- 过滤掉还未结束的离岗记录（如果需要统计当前未结束的，可将结束日期替换为SYSDATE）
AND leave_end_date IS NOT NULL;

逻辑说明 #

• 内层查询用LEAD开窗函数，按人员分组、变更时间排序，自动将每一条离岗起始记录（NextStatusID=2）和下一条状态变更记录的日期绑定，也就是对应的返岗日期
• 日期相减在Oracle中直接得到两个日期的间隔天数，符合你给出的测试样例计算逻辑：
  • 2020-03-11 至 2020-03-13：间隔2天
  • 2020-06-03 至 2020-06-05：间隔2天
  • 2020-09-14 至 2020-09-17：间隔3天
  • 累计总和2+2+3=7，和预期结果完全一致
• 如果需要处理跨年度的离岗记录，比如离岗开始于上一年12月、结束于统计年度，可以将年度过滤逻辑调整为判断日期范围即可
• 如果需要关联Statuses表校验状态合法性，只需要在WHERE条件中加关联逻辑即可，当前仅用状态ID判断已满足需求

特殊场景处理 #

如果人员当前处于离岗状态，还没有对应的返岗记录，要把这段时间算入累计天数，可以修改求和逻辑：

SUM(NVL(leave_end_date, TRUNC(SYSDATE)) - leave_start_date)

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