Oracle 11g如何查询过去指定小时数登录数据并排除周末

核心需求拆解 #

• 查询过去指定小时数（如10小时）的登录记录
• 完全排除周六、周日的数据
• 跨周末时自动回溯至上一个周五的对应时间段（如周一8点时，取周一0-8点+周五22-24点的记录）

实现思路 #

1. 排除周末数据：通过to_char函数结合固定英文星期缩写判断日期是否为周末，规避数据库语言环境差异问题
2. 处理跨周末场景：
   • 若当前为周一且回溯时间覆盖周日，自动拆分时间区间为「周一0点至当前时间」+「上周五对应时间段至周五24点」
   • 其他工作日直接取「当前时间-N小时至当前时间」的非周末数据
   • 若当前为周末，仅取上周五的对应回溯时间段

最终查询语句 #

SELECT *
FROM logins
WHERE
  -- 过滤周六、周日的记录
  TO_CHAR(end_time, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN')
  AND (
    -- 场景1：当前为周一至周五，回溯区间未跨周末
    (TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('MON', 'TUE', 'WED', 'THU', 'FRI')
     AND end_time >= GREATEST(TRUNC(SYSDATE), SYSDATE - 10/24)
     AND end_time <= SYSDATE)
    -- 场景2：当前为周一，回溯区间覆盖周日，补充上周五的对应时段
    OR (TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'MON'
        AND SYSDATE - 10/24 < TRUNC(SYSDATE)
        AND end_time >= TRUNC(SYSDATE) - 3 + (TRUNC(SYSDATE) - (SYSDATE - 10/24))
        AND end_time <= TRUNC(SYSDATE) - 3 + 1)
    -- 场景3：当前为周六/周日，仅取上周五的对应回溯时段
    OR (TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN')
        AND end_time >= TRUNC(SYSDATE) - (CASE TO_CHAR(SYSDATE, 'DY') WHEN 'SAT' THEN 1 WHEN 'SUN' THEN 2 END) - (10/24 - (TRUNC(SYSDATE) - SYSDATE))
        AND end_time <= TRUNC(SYSDATE) - (CASE TO_CHAR(SYSDATE, 'DY') WHEN 'SAT' THEN 1 WHEN 'SUN' THEN 2 END) + 1)
  );

语句细节说明 #

• TO_CHAR(..., 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')：强制返回英文星期缩写（'MON'-'SUN'），避免中文环境下星期名不一致的问题
• GREATEST(TRUNC(SYSDATE), SYSDATE - 10/24)：当回溯时间覆盖周日时，自动取周一0点作为区间起始，只保留周一的有效数据
• TRUNC(SYSDATE) - 3：获取上周五的0点（周一0点减3天），再通过时间差计算需要回溯的起始小时（如周日22点到周一0点的2小时差，对应周五22点的起始时间）
• 周末场景处理：当前为周六时回溯到周五对应时间段；当前为周日时回溯到周五更早的时段，确保凑够10小时的非周末数据

灵活调整小时数 #

如果需要修改回溯的小时数，只需把语句中所有的10/24替换为N/24（N为目标小时数），也可以使用绑定变量简化修改：

DEFINE v_hours = 10;
SELECT *
FROM logins
WHERE
  TO_CHAR(end_time, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN')
  AND (
    (TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('MON', 'TUE', 'WED', 'THU', 'FRI')
     AND end_time >= GREATEST(TRUNC(SYSDATE), SYSDATE - &&v_hours/24)
     AND end_time <= SYSDATE)
    OR (TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'MON'
        AND SYSDATE - &&v_hours/24 < TRUNC(SYSDATE)
        AND end_time >= TRUNC(SYSDATE) - 3 + (TRUNC(SYSDATE) - (SYSDATE - &&v_hours/24))
        AND end_time <= TRUNC(SYSDATE) - 3 + 1)
    OR (TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN')
        AND end_time >= TRUNC(SYSDATE) - (CASE TO_CHAR(SYSDATE, 'DY') WHEN 'SAT' THEN 1 WHEN 'SUN' THEN 2 END) - (&&v_hours/24 - (TRUNC(SYSDATE) - SYSDATE))
        AND end_time <= TRUNC(SYSDATE) - (CASE TO_CHAR(SYSDATE, 'DY') WHEN 'SAT' THEN 1 WHEN 'SUN' THEN 2 END) + 1)
  );

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