generate_series() 中表中的开始日期和结束日期

Question

我正在尝试在PostgreSQL中计算某个日期范围之间的工作日。

SELECT 
SUM(CASE WHEN extract (dow FROM foo) IN(1,2,3,4,5) THEN 1 ELSE 0 
END) 
FROM (SELECT ('2007-04-01'::date + 
(generate_series(0,'2007-04-30'::date 
- '2007-04-01'::date)||'days')::interval) AS foo) foo

我想用名为myTable的表中的start_date和end_date替换日期start_date和end_date格式yyyy-mm-dd

实际上我需要它来显示每行的工作日日期差异

|start_date |end_date |
------------------------
|2018-04-01 |2018-04-30| 
|2018-05-01 |2018-05-30| 

这是我的代码：

SELECT pto.start_date, pto.end_date, 
SUM(CASE WHEN extract (dow FROM foo) IN(1,2,3,4,5) THEN 1 ELSE 0 END) as theDIFF 
FROM (
SELECT start_date, (start_date::date + 
(generate_series(0,end_date::date 
- start_date::date)||'days')::interval) AS foo
FROM pto
) foo inner join pto pto
on pto.start_date = foo.start_date 
group by pto.start_date, pto.end_date

我的输出：

|start_date(date)| end_date(date) |theDiff(integer)
---------------------------------------------------
|2017-06-01      |  2017-06-01    |        29     |
|2017-05-29      |  2017-06-02    |        12     |
---------------------------------------------------

预期输出：

|start_date(date)| end_date(date) |theDiff(integer)
---------------------------------------------------
|2017-06-01      |  2017-06-01    |        1      |
|2017-05-29      |  2017-06-02    |        5      |
---------------------------------------------------

Answer 1

您的示例代码有点混乱，因此仅考虑工作日和表，这将给出预期的输出：

CREATE TABLE myTable (start_date date, end_date date);
INSERT INTO myTable VALUES('2017-06-01', '2017-06-01'),('2017-05-29', '2017-06-02');
SELECT start_date, end_date,
SUM(CASE WHEN EXTRACT(dow FROM days) BETWEEN 1 AND 5 THEN 1 ELSE 0 END)
FROM myTable
CROSS JOIN LATERAL generate_series(start_date, end_date, interval '1 day') AS days
GROUP BY start_date, end_date;

它将生成行，每行都有start_date、end_date和中间的一天。然后，它将按星期几介于 1 到 5(星期一至星期五(之间的 start_date、end_date 和 SUM(( 日期进行聚合。

如果没有聚合，它看起来像这样：

start_date |  end_date  |          days          | dow
------------+------------+------------------------+-----
2017-06-01 | 2017-06-01 | 2017-06-01 00:00:00+02 |   4
2017-05-29 | 2017-06-02 | 2017-05-29 00:00:00+02 |   1
2017-05-29 | 2017-06-02 | 2017-05-30 00:00:00+02 |   2
2017-05-29 | 2017-06-02 | 2017-05-31 00:00:00+02 |   3
2017-05-29 | 2017-06-02 | 2017-06-01 00:00:00+02 |   4
2017-05-29 | 2017-06-02 | 2017-06-02 00:00:00+02 |   5

并按以下方式分组：

start_date |  end_date  | sum
------------+------------+-----
2017-06-01 | 2017-06-01 |   1
2017-05-29 | 2017-06-02 |   5