问题

以下是您要翻译的代码部分：

I have the following table : 

| entity_id | time_c                | next_time_c           |
|:---------:|-----------------------|-----------------------|
| 1         | '2023-01-02 10:34:36' | '2023-01-10 15:12:24' |
| 2         | '2023-03-01 16:10:12' | '2023-03-20 22:47:59' |

My goal is to calculate the interval between `time_c` and `next_time_c` without Saturday and Sunday and the full interval.

I come up with the following query : 

    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;

It works well when I have only 1 row in my table. When there is more than 1 row, the result is wrong.

With 1 row :

| 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} |

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

With 2 rows :

| 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}

Why the result is false when calculating several rows ?

Demo : https://www.db-fiddle.com/f/mDCS6cSwT1hbvit2WLYZbc/0

希望这对您有所帮助。如果您需要进一步的解释或帮助，请随时告诉我。

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;

答案1

得分: 1

你应该分别为每个 entity_id 计算周末天数：

...
    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
...

在Db-Fiddle中检查完整查询。

更新。上述解决方案假设 entity_id 是唯一的，因为它用于标识单个行。如果没有唯一的列，可以使用自定义函数或lateral子查询。这是第二个选项：

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 - start_date) as "Duration full"
from (
    select 
  		entity_id,
  		time_c as start_date,
  		next_time_c as end_date,
        (next_time_c - time_c) - weekend_days as diff
    from test_c,
	lateral (
		select sum((extract(isodow from d) in (6, 7))::int)* interval '1 day' as weekend_days
		from generate_series(time_c, next_time_c, '1 day') dn(d)
	) wd
) sq;

在Db<>Fiddle中检查。

...
    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 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 - start_date) as "Duration full"
from (
    select 
  		entity_id,
  		time_c as start_date,
  		next_time_c as end_date,
        (next_time_c - time_c) - weekend_days as diff
    from test_c,
	lateral (
		select sum((extract(isodow from d) in (6, 7))::int)* interval '1 day' as weekend_days
		from generate_series(time_c, next_time_c, '1 day') dn(d)
	) wd
) sq;