Oracle 18c中计算时间戳小时差及累计求和的实现

现有数据表及数据 #

CREATE TABLE test (
    id            NUMBER(10),
    due_dt        TIMESTAMP(6),
    status        VARCHAR2(10),
    created_on    TIMESTAMP(6),
    act_taken_on  TIMESTAMP(6)
);

insert into test values(1,'21-SEP-22 02.53.10.016537 AM','created','19-SEP-22 02.53.10.016537 AM','20-SEP-22 02.53.10.016537 AM');
insert into test values(1,'21-SEP-22 02.53.10.016537 AM','created','20-SEP-22 02.53.10.016537 AM','21-SEP-22 02.53.10.016537 AM');
insert into test values(2,'21-SEP-22 02.53.10.016537 AM','Approved','21-SEP-22 02.53.10.016537 AM','22-SEP-22 02.53.10.016537 AM');

计算需求 #

• aging字段：
  • 当status为'created'时，计算act_taken_on与created_on的小时差值
  • 若id重复，需与前一行的aging值累计求和（如id=1的两行，aging分别为24Hours、48Hours）
• time_left字段：计算due_dt与sysdate的小时差值，格式化为带Hours的文本

用户尝试的SQL #

SELECT id,
CASE
when status = 'created' then (created_on - act_taken_on)
when status = 'Approved' then (created_on - act_taken_on)
end aging,
(due_dt - sysdate) time_left
from test;

正确实现方案 #

SELECT 
    id,
    TO_CHAR(
        SUM(
            EXTRACT(HOUR FROM (act_taken_on - created_on)) + 
            EXTRACT(DAY FROM (act_taken_on - created_on)) * 24
        ) OVER (PARTITION BY id ORDER BY created_on)
    ) || 'Hours' AS aging,
    TO_CHAR(
        EXTRACT(HOUR FROM (due_dt - SYSDATE)) + 
        EXTRACT(DAY FROM (due_dt - SYSDATE)) * 24
    ) || 'Hours' AS time_left
FROM test;

核心说明 #

1. aging累计计算：
   • 通过EXTRACT分别提取时间差的天数和小时数，转换为总小时数
   • 使用SUM() OVER (PARTITION BY id ORDER BY created_on)实现同id分组内的累计求和，按created_on排序保证行顺序符合预期
2. time_left计算：
   • 同样用EXTRACT提取天数和小时数转换为总小时数，拼接'Hours'形成目标格式
3. 直接操作INTERVAL类型的分量更准确，避免隐式转换带来的误差

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