按时间段统计采购数据：秒级小数处理与周期判定求助

我需要按采购时间追踪用户的采购模式，将一天划分为三个时间周期：

• DAY：07:15:00 - 15:14:59.999999
• SWING：15:15:00 - 23:14:59.999999
• GRAVE：23:15:00 - 07:14:59.999999

我知道可以用extract()提取时间，但不确定如何处理秒的小数部分，也不清楚怎么判定每条采购记录属于哪个周期并累加统计，需要技术帮助。

环境设置与测试数据 #

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Aaron' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Jones' FROM DUAL UNION ALL
SELECT 3, 'Roz', 'Doyle' FROM DUAL;

CREATE TABLE purchases(
      ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
      customer_id   NUMBER,
      PRODUCT_ID NUMBER,
      QUANTITY NUMBER,
      purchase_date TIMESTAMP
    );

INSERT INTO purchases (customer_id, product_id, quantity, purchase_date)
    SELECT  1 customer_id, 102 product_id, 1 quantity,
   TIMESTAMP '2024-04-03 00:00:00' + INTERVAL '23:27' HOUR TO MINUTE + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)  * -1 +   ((LEVEL-1) * INTERVAL '0.007125' SECOND) 
           AS purchase_date
    FROM    DUAL
    CONNECT BY LEVEL <= 3 UNION ALL
SELECT  1, 101, 1,
   TIMESTAMP '2024-05-10 00:00:57' + INTERVAL '07:17' HOUR TO MINUTE + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)  * -1 +   ((LEVEL-1) * INTERVAL '0.000120' SECOND) 
    FROM    DUAL
    CONNECT BY LEVEL <= 2 UNION ALL
SELECT  1, 101, 1,
   TIMESTAMP '2024-06-13 00:00:59.999999' + INTERVAL '23:14' HOUR TO MINUTE + ((LEVEL-1) * INTERVAL '1 00:00:00' DAY TO SECOND)  * -1 +   ((LEVEL-1) * INTERVAL '0.999999' SECOND) 
    FROM    DUAL
    CONNECT BY LEVEL <= 1 UNION ALL
SELECT  3, 103, 3,
   TIMESTAMP '2024-06-09 00:00:00' + INTERVAL '17:37' HOUR TO MINUTE + ((LEVEL-1) * INTERVAL '1 00:00:00' DAY TO SECOND)  * -1 +   ((LEVEL-1) * INTERVAL '0.009120' SECOND) 
    FROM    DUAL
    CONNECT BY LEVEL <= 6;

预期结果 #

解决方案 #

实现思路 #

无需单独提取秒的小数部分，直接将purchase_date的当日时间部分转换为间隔值（INTERVAL），Oracle会自动处理小数秒的精度，再与各周期的时间边界比较即可完成周期判定。

完整查询SQL #

SELECT 
    c.CUSTOMER_ID,
    UPPER(c.FIRST_NAME) AS FIRST_NAME,
    UPPER(c.LAST_NAME) AS LAST_NAME,
    COUNT(CASE WHEN time_interval BETWEEN INTERVAL '07:15:00' HOUR TO SECOND AND INTERVAL '15:14:59.999999' HOUR TO SECOND THEN 1 END) AS DAY_PERIOD,
    COUNT(CASE WHEN time_interval BETWEEN INTERVAL '15:15:00' HOUR TO SECOND AND INTERVAL '23:14:59.999999' HOUR TO SECOND THEN 1 END) AS SWING_PERIOD,
    COUNT(CASE WHEN time_interval >= INTERVAL '23:15:00' HOUR TO SECOND OR time_interval < INTERVAL '07:15:00' HOUR TO SECOND THEN 1 END) AS GRAVE_PERIOD,
    COUNT(p.purchase_date) AS NUM_PURCHASES
FROM customers c
LEFT JOIN (
    SELECT 
        customer_id,
        purchase_date,
        -- 提取当日时间部分为间隔值，保留完整精度
        CAST(purchase_date AS TIMESTAMP) - TRUNC(purchase_date) AS time_interval
    FROM purchases
) p ON c.CUSTOMER_ID = p.CUSTOMER_ID
GROUP BY c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME
ORDER BY c.CUSTOMER_ID;

代码解释 #

1. 时间间隔转换：CAST(purchase_date AS TIMESTAMP) - TRUNC(purchase_date)将采购时间的当日时间部分提取为INTERVAL DAY TO SECOND类型，自动包含秒的小数部分，无需手动拆分处理。
2. 周期判定：
   • DAY周期：直接判断时间间隔是否在07:15:00到15:14:59.999999之间
   • SWING周期：判断时间间隔是否在15:15:00到23:14:59.999999之间
   • GRAVE周期：包含跨午夜的两个时间段，用OR连接：时间间隔大于等于23:15:00，或者小于07:15:00
3. 统计与关联：使用LEFT JOIN确保无采购记录的用户也会被统计（对应字段值为0），通过COUNT(CASE...)实现各周期的累加计数。

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