如何计算各entity_id的Fulfill阶段工作日与总时长？

问题背景 #

原始数据 #

time和next_time为毫秒级Unix时间戳。

需求 #

计算每个entity_id下所有Fulfill阶段的时长总和，包含两种统计：

• 总时长（包含周六日）
• 排除周六日的工作日时长

数据库为只读模式，无法创建函数。

预期结果 #

当前错误输出 #

问题：单个entity_id存在多个Fulfill阶段时，输出拆分多条记录，且计算值错误。

当前使用的SQL语句 #

WITH temp2 AS (
    SELECT
        entity_id,
        old_phase,
        phase,
        time,
        next_time,
        to_timestamp(to_char(to_timestamp("time"/1000.0) at time zone 'Europe/Paris', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') at time zone 'Europe/Paris' AS "TIME 2",
        to_timestamp(to_char(to_timestamp("next_time"/1000.0) at time zone 'Europe/Paris', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') at time zone 'Europe/Paris'AS "NEXT TIME 2",
        ((next_time - time)/1000.0) AS DIFF
    FROM 
        tbl_history
    WHERE phase = 'Fulfill'
),parms (entity_id, start_date, end_date) AS
(
    SELECT 
        entity_id,
        "TIME 2"::timestamp,
        "NEXT TIME 2"::timestamp
    FROM 
        temp2
), weekend_days (entity_id, wkend) AS 
(
    SELECT 
        entity_id,
        SUM(case when extract(isodow from d) in (6, 7) then 1 else 0 end) 
    FROM 
        parms
    CROSS JOIN 
        generate_series(start_date, end_date, interval '1 day') dn(d)
    GROUP BY entity_id
)

SELECT 
    entity_id,
    CONCAT(
        extract(day from diff), ' days ', 
        extract( hours from diff)   , ' hours ', 
        extract( minutes from diff) , ' minutes ', 
        extract( seconds from diff)::int , ' seconds '
    ) AS "Duration (excluding saturday and sunday)",
    justify_interval(end_date::timestamp - start_date::timestamp) AS "Duration full"
FROM (
    SELECT 
        start_date,
        end_date,
        entity_id,
        (end_date-start_date) - (wkend * interval '1 day') AS diff
    FROM parms 
    JOIN weekend_days USING(entity_id)
) sq;

解决方案 #

问题根源：原SQL将单个Fulfill阶段的时长与全量周末天数关联计算，导致拆分出多条错误记录。需先按entity_id汇总总时长和总周末天数，再统一计算。

修正后的SQL语句：

WITH fulfill_phases AS (
    -- 筛选Fulfill阶段，转换时间戳为巴黎时区时间，计算单阶段秒数
    SELECT
        entity_id,
        to_timestamp("time"/1000.0) AT TIME ZONE 'Europe/Paris' AS start_ts,
        to_timestamp("next_time"/1000.0) AT TIME ZONE 'Europe/Paris' AS end_ts,
        (next_time - time) / 1000.0 AS single_seconds
    FROM tbl_history
    WHERE phase = 'Fulfill'
),
total_durations AS (
    -- 按entity_id汇总总秒数，以及所有Fulfill阶段的时间区间起止
    SELECT
        entity_id,
        SUM(single_seconds) AS total_full_seconds,
        MIN(start_ts) AS earliest_start,
        MAX(end_ts) AS latest_end
    FROM fulfill_phases
    GROUP BY entity_id
),
weekend_calculations AS (
    -- 统计所有Fulfill阶段覆盖时间内的总周末天数
    SELECT
        td.entity_id,
        SUM(CASE WHEN EXTRACT(isodow FROM day) IN (6, 7) THEN 1 ELSE 0 END) AS total_weekend_days
    FROM total_durations td
    CROSS JOIN generate_series(td.earliest_start, td.latest_end, INTERVAL '1 day') AS day
    GROUP BY td.entity_id
),
work_seconds AS (
    -- 计算工作日总秒数：总秒数 - 周末天数*86400秒
    SELECT
        td.entity_id,
        td.total_full_seconds - (wc.total_weekend_days * 86400) AS total_work_seconds,
        td.total_full_seconds
    FROM total_durations td
    JOIN weekend_calculations wc USING(entity_id)
)
-- 格式化输出结果
SELECT
    entity_id,
    -- 格式化工作日时长为可读字符串
    CONCAT(
        FLOOR(total_work_seconds / 86400), ' days ',
        FLOOR((total_work_seconds % 86400) / 3600), ' hours ',
        FLOOR((total_work_seconds % 3600) / 60), ' minutes ',
        (total_work_seconds % 60)::INT, ' seconds'
    ) AS "Duration (excluding saturday and sunday)",
    -- 格式化总时长为结构化JSON
    json_build_object(
        'years', EXTRACT(year FROM justify_interval(INTERVAL '1 second' * total_full_seconds)),
        'months', EXTRACT(month FROM justify_interval(INTERVAL '1 second' * total_full_seconds)),
        'days', EXTRACT(day FROM justify_interval(INTERVAL '1 second' * total_full_seconds)),
        'hours', EXTRACT(hour FROM justify_interval(INTERVAL '1 second' * total_full_seconds)),
        'minutes', EXTRACT(minute FROM justify_interval(INTERVAL '1 second' * total_full_seconds)),
        'seconds', EXTRACT(second FROM justify_interval(INTERVAL '1 second' * total_full_seconds))::INT
    ) AS "Duration full"
FROM work_seconds;

修正说明 #

1. fulfill_phases：筛选目标阶段，完成时间戳的时区转换和单阶段时长计算。
2. total_durations：按entity_id汇总总时长，同时确定所有Fulfill阶段的时间范围，为后续统计周末天数提供完整区间。
3. weekend_calculations：基于总时间区间生成每日记录，统计周六、周日的总天数。
4. work_seconds：通过总秒数扣除周末天数对应的秒数，得到工作日总时长。
5. 最终输出：将秒数格式化为可读的天/时/分/秒字符串，同时用justify_interval和json_build_object生成符合预期的结构化总时长。

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