多行列计算time_c与next_time_c间隔时结果异常的原因排查

数据表 #

需求 #

计算time_c与next_time_c之间的：

• 完整时间间隔
• 排除周六和周日的时间间隔

错误的查询语句 #

WITH parms (entity_id, start_date, end_date) AS (
 SELECT entity_id, time_c::timestamp, next_time_c::timestamp FROM test_c
), weekend_days (wkend) AS (
 SELECT 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)
)
SELECT entity_id AS "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 ON true
) sq;

问题现象 #

• 单条数据时结果正常：

  ID	Duration (excluding saturday and sunday)	Duration full
  1	6 days 4 hours 37 minutes 48 seconds	{"days":8,"hours":4,"minutes":37,"seconds":48}
  2	13 days 6 hours 37 minutes 47 seconds	{"days":19,"hours":6,"minutes":37,"seconds":47}

• 多条数据时排除周末的结果错误：

  ID	Duration (excluding saturday and sunday)	Duration full
  1	0 days 4 hours 37 minutes 48 seconds	{"days":8,"hours":4,"minutes":37,"seconds":48}
  2	11 days 6 hours 37 minutes 47 seconds	{"days":19,"hours":6,"minutes":37,"seconds":47}

错误原因 #

weekend_days CTE中，你将所有行的日期范围通过CROSS JOIN合并后，统计了所有行的周末天数总和，而不是按每个entity_id单独统计自身时间范围内的周末天数。当多行数据时，这个总和会被应用到每一行的计算中，导致减去的周末天数远大于当前行实际应扣除的数量，结果自然错误。

修正后的查询语句 #

需要按entity_id分组，单独统计每个时间区间内的周末天数：

WITH parms (entity_id, start_date, end_date) AS (
 SELECT entity_id, time_c::timestamp, next_time_c::timestamp FROM test_c
), weekend_days AS (
 SELECT 
  p.entity_id,
  SUM(CASE WHEN EXTRACT(isodow FROM d) IN (6, 7) THEN 1 ELSE 0 END) AS wkend
 FROM parms p
 CROSS JOIN generate_series(p.start_date, p.end_date, interval '1 day') dn(d)
 GROUP BY p.entity_id
)
SELECT 
 p.entity_id AS "ID",
 CONCAT(
  EXTRACT(day FROM diff), ' days ',
  EXTRACT(hour FROM diff), ' hours ',
  EXTRACT(minute FROM diff), ' minutes ',
  EXTRACT(second FROM diff)::INT, ' seconds '
 ) AS "Duration (excluding saturday and sunday)",
 justify_interval(p.end_date - p.start_date) AS "Duration full"
FROM parms p
JOIN weekend_days w ON p.entity_id = w.entity_id
CROSS JOIN LATERAL (
 SELECT (p.end_date - p.start_date) - (w.wkend * INTERVAL '1 day') AS diff
) sq;

修正后结果 #

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