Oracle中如何去除查询0值并修正打卡时间差计算的错误行问题？

原SQL的核心问题 #

你的SQL存在几个关键问题：

1. TO_CHAR语法错误：TO_CHAR(ce.clockin_dt_tm), 'MM/DD/YYYY HH24:MI' 格式字符串应放在函数括号内，正确写法是TO_CHAR(ce.clockin_dt_tm, 'MM/DD/YYYY HH24:MI')。
2. 排序逻辑错误：OVER(PARTITION BY e.emp_id ORDER BY e.emp_id) 里的排序字段误用了员工ID，同一员工的打卡记录需按打卡时间排序，否则LAG无法正确匹配上一条打卡记录。
3. 函数使用错误：若要计算当前打卡与下一次打卡的间隔，应使用LEAD函数而非LAG——LAG取当前行的上一条记录，LEAD才是取当前行的下一条记录。
4. 多余的DISTINCT：如果打卡记录本身无重复，DISTINCT会干扰结果，甚至催生错误行。

问题1：去除0值的方法 #

方法1：外层查询过滤0值 #

将原查询作为子查询，在外层用WHERE排除间隔为0的记录：

SELECT *
FROM (
    SELECT 
        p.emp_name,
        e.emp_id,
        TO_CHAR(ce.clockin_dt_tm, 'MM/DD/YYYY HH24:MI') AS clockin,
        (LEAD(ce.clockin_dt_tm) OVER(PARTITION BY e.emp_id ORDER BY ce.clockin_dt_tm) - ce.clockin_dt_tm)*24 AS between_clockins
    FROM 
        -- 补充你的表关联逻辑，例如员工表、人员表、打卡表的JOIN条件
        employee_table e
        JOIN person_table p ON e.emp_id = p.emp_id
        JOIN clockin_events ce ON e.emp_id = ce.emp_id
) t
WHERE between_clockins IS NOT NULL 
  AND between_clockins != 0;

方法2：计算时将0值替换为NULL #

用CASE WHEN把无效的0值转为NULL，避免显示无意义的间隔：

SELECT 
    p.emp_name,
    e.emp_id,
    TO_CHAR(ce.clockin_dt_tm, 'MM/DD/YYYY HH24:MI') AS clockin,
    CASE 
        WHEN (LEAD(ce.clockin_dt_tm) OVER(PARTITION BY e.emp_id ORDER BY ce.clockin_dt_tm) - ce.clockin_dt_tm)*24 = 0 
        THEN NULL 
        ELSE (LEAD(ce.clockin_dt_tm) OVER(PARTITION BY e.emp_id ORDER BY ce.clockin_dt_tm) - ce.clockin_dt_tm)*24 
    END AS between_clockins
FROM 
    employee_table e
    JOIN person_table p ON e.emp_id = p.emp_id
    JOIN clockin_events ce ON e.emp_id = ce.emp_id;

问题2：修正结果的其他SQL调整方案 #

方案1：修正核心逻辑（优先推荐） #

将LAG改为LEAD，按打卡时间排序，同时移除多余的DISTINCT：

SELECT 
    p.emp_name,
    e.emp_id,
    TO_CHAR(ce.clockin_dt_tm, 'MM/DD/YYYY HH24:MI') AS clockin,
    -- 计算当前打卡到下一次打卡的小时差，最后一条记录因无后续打卡会返回NULL
    (LEAD(ce.clockin_dt_tm) OVER(PARTITION BY e.emp_id ORDER BY ce.clockin_dt_tm) - ce.clockin_dt_tm)*24 AS between_clockins
FROM 
    employee_table e
    JOIN person_table p ON e.emp_id = p.emp_id
    JOIN clockin_events ce ON e.emp_id = ce.emp_id;

方案2：先去重再计算 #

如果存在同一时间的重复打卡（导致间隔为0），可以先对打卡记录去重：

SELECT 
    emp_name,
    emp_id,
    TO_CHAR(clockin_dt_tm, 'MM/DD/YYYY HH24:MI') AS clockin,
    (LEAD(clockin_dt_tm) OVER(PARTITION BY emp_id ORDER BY clockin_dt_tm) - clockin_dt_tm)*24 AS between_clockins
FROM (
    -- 按员工和打卡时间去重
    SELECT DISTINCT
        p.emp_name,
        e.emp_id,
        ce.clockin_dt_tm
    FROM 
        employee_table e
        JOIN person_table p ON e.emp_id = p.emp_id
        JOIN clockin_events ce ON e.emp_id = ce.emp_id
) t;

方案3：保留原生日期类型计算 #

避免提前将日期时间转为字符串，确保跨天间隔（如夜班）计算准确：

SELECT 
    p.emp_name,
    e.emp_id,
    ce.clockin_dt_tm,
    TO_CHAR(ce.clockin_dt_tm, 'MM/DD/YYYY HH24:MI') AS clockin_str,
    (LEAD(ce.clockin_dt_tm) OVER(PARTITION BY e.emp_id ORDER BY ce.clockin_dt_tm) - ce.clockin_dt_tm)*24 AS between_clockins
FROM 
    employee_table e
    JOIN person_table p ON e.emp_id = p.emp_id
    JOIN clockin_events ce ON e.emp_id = ce.emp_id;

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