如何使用Teradata Period数据类型按日期聚合设备状态时长？

我正在为公司设备创建状态历史表，设备状态随切换更新，目标是统计截至当前日期每天各状态的设备时长，可假设单台设备无状态时段重叠。我计划使用Teradata的Period数据类型存储状态，因它具备实用的内置功能，但未找到符合需求的聚合方法。

现有表结构 #

CREATE SET TABLE STATUS(
    ID VARCHAR(10) NOT NULL,
    STATUS VARCHAR(10) NOT NULL,
    DURATION PERIOD(TIMESTAMP(0)) NOT NULL
)
PRIMARY INDEX(ID, STATUS, DURATION);

（注：原表主键索引中的EQ_INIT_NR应为笔误，修正为表中实际存在的ID列）

测试数据 #

INSERT INTO STATUS VALUES ('A1', 'ACTIVE', PERIOD(TIMESTAMP'2024-01-01 00:00:00', TIMESTAMP'2024-01-02 00:00:00'));
INSERT INTO STATUS VALUES ('A1', 'IDLE', PERIOD(TIMESTAMP '2024-01-02 00:00:00', TIMESTAMP'2024-01-03 00:00:00'));
INSERT INTO STATUS VALUES ('A1', 'ACTIVE', PERIOD(TIMESTAMP '2024-01-03 00:00:00', UNTIL_CHANGED));


INSERT INTO STATUS VALUES ('B2', 'ACTIVE', PERIOD(TIMESTAMP'2024-01-01 00:00:00', TIMESTAMP'2024-01-02 00:00:00'));
INSERT INTO STATUS VALUES ('B2', 'IDLE', PERIOD(TIMESTAMP '2024-01-02 00:00:00', TIMESTAMP'2024-01-02 12:00:00'));
INSERT INTO STATUS VALUES ('B2', 'ACTIVE', PERIOD(TIMESTAMP '2024-01-02 12:00:00', UNTIL_CHANGED));

预期结果 #

结果说明 #

• 2024-01-01的ACTIVE时长为48小时：A1和B2全天处于ACTIVE状态
• 2024-01-02的ACTIVE时长为12小时：B2仅半天活跃；IDLE时长为36小时：A1全天闲置+B2半天闲置
• 2024-01-03的ACTIVE时长为48小时：两台设备全天活跃
• 最后一个状态需持续至当前日期，直至状态变更（2024-01-04至CURRENT_DT）

核心思路是将每个状态时段拆分为单日有效片段，统计每日各状态总时长，同时补全所有日期的所有状态（包括时长为0的情况），具体实现如下：

完整SQL语句 #

WITH date_range AS (
    SELECT 
        CAST(day_start AS DATE) AS stat_date
    FROM 
        (
            -- 生成从最早状态起始日到当前日期的连续日期序列
            SELECT 
                TRUNC(MIN(CAST(DURATION.start AS TIMESTAMP)), 'DD') + INTERVAL '0' DAY + (i * INTERVAL '1' DAY) AS day_start
            FROM 
                STATUS
                CROSS JOIN TABLE(SEQUENCE(0, DATEDIFF(DAY, TRUNC(MIN(CAST(DURATION.start AS TIMESTAMP)), 'DD'), CURRENT_DATE))) AS dt(i)
        ) AS dates
),
daily_status AS (
    SELECT 
        dr.stat_date,
        s.STATUS,
        -- 计算状态在单日的有效时长（小时）
        EXTRACT(HOUR FROM (
            -- 取当日时段与状态时段的交集
            PERIOD(
                CAST(dr.stat_date AS TIMESTAMP(0)), 
                CAST(dr.stat_date + INTERVAL '1' DAY AS TIMESTAMP(0))
            ) INTERSECT 
            -- 处理UNTIL_CHANGED，替换为当前日期结束时间
            CASE WHEN s.DURATION.end = TIMESTAMP '9999-12-31 23:59:59' 
                 THEN PERIOD(s.DURATION.start, CAST(CURRENT_DATE + INTERVAL '1' DAY AS TIMESTAMP(0)))
                 ELSE s.DURATION
            END
        )) AS hourly_duration
    FROM 
        STATUS s
        CROSS JOIN date_range dr
    WHERE 
        -- 仅保留状态时段与当日有重叠的记录
        s.DURATION OVERLAPS PERIOD(CAST(dr.stat_date AS TIMESTAMP(0)), CAST(dr.stat_date + INTERVAL '1' DAY AS TIMESTAMP(0)))
),
all_statuses AS (
    -- 获取所有存在的状态类型
    SELECT DISTINCT STATUS FROM STATUS
)
SELECT 
    dr.stat_date AS DATE,
    asu.STATUS,
    -- 补全无数据的状态时长为0
    COALESCE(SUM(ds.hourly_duration), 0) AS DURATION
FROM 
    date_range dr
    CROSS JOIN all_statuses asu
    LEFT JOIN daily_status ds 
        ON dr.stat_date = ds.stat_date AND asu.STATUS = ds.STATUS
GROUP BY 
    dr.stat_date, asu.STATUS
ORDER BY 
    dr.stat_date, asu.STATUS;

关键逻辑说明 #

1. 日期序列生成：通过SEQUENCE函数生成从最早状态起始日到当前日期的连续日期，确保覆盖所有需要统计的日期范围。
2. 时段交集计算：利用Teradata Period类型的INTERSECT操作，自动计算每个状态在单日的有效时长，无需手动处理跨天时段。
3. UNTIL_CHANGED处理：将UNTIL_CHANGED对应的默认结束时间（9999-12-31 23:59:59）替换为当前日期的结束时间，保证统计到最新日期。
4. 补全0时长记录：通过日期序列与所有状态类型的笛卡尔积，再用COALESCE将空值替换为0，实现每个日期都包含所有状态的统计结果。

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