дата начала и дата окончания из таблицы в generate_series()

Я пытаюсь подсчитать рабочие дни между определенным диапазоном дат в 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

Я хочу заменить даты на start_date и end_date из таблицы с именем myTable 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      |
  ---------------------------------------------------

postgresql sql generate-series
person noobeerp    schedule 24.05.2018    source источник
comment
Из какой строки таблицы вы хотите взять значения?   -  person Laurenz Albe    schedule 24.05.2018
comment
Показанный оператор недействителен, потому что вам нужен group by для sum() — есть ли у вас другие столбцы в этой таблице? Что такое первичный ключ?   -  person a_horse_with_no_name    schedule 24.05.2018
comment
@a_horse_with_no_name, который решает мою ошибку, но мои расчеты испорчены. За 2017-06-01 - 2017-06-01 я получаю 29.   -  person noobeerp    schedule 24.05.2018
comment
Вы действительно должны показать нам полную структуру таблицы и ожидаемый результат.   -  person a_horse_with_no_name    schedule 24.05.2018
comment
@a_horse_with_no_name Я добавил структуру таблицы и вывод.   -  person noobeerp    schedule 24.05.2018

Ответы (1)


arrow_upward
2
arrow_downward

Ваш пример кода немного сбивает с толку, поэтому, просто учитывая рабочие дни и вашу таблицу, это даст ожидаемый результат:

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 и один день между ними. Затем он будет агрегироваться по датам start_date, end_date и SUM(), которые имеют день недели от 1 до 5 (понедельник-пятница).

Без агрегации это выглядит так:

 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
person Łukasz Kamiński    schedule 24.05.2018