PostgreSQL: сумма дней в диапазоне

ProgramBox

PostgreSQL: сумма дней в диапазоне

Post author:admin
Запись опубликована:13 марта, 2023
Post category:Вопросы по программированию

#sql #postgresql

Вопрос:

У меня есть таблица следующего вида:

                    Table "public.fish_schedule"
 Column |          Type          | Collation | Nullable | Default 
-------- ------------------------ ----------- ---------- ---------
 name   | character varying(255) |           | not null | 
 tr     | timerange              |           | not null | 
 mr     | int4range              |           | not null |

Пример данных:

      name     |         tr          |   mr   
-------------- --------------------- --------
 ray          | [04:00:00,21:00:00) | [8,12)
 moray eel    | [00:00:00,24:00:00) | [8,11)
 yellow perch | [00:00:00,24:00:00) | [1,4)
(3 rows)

Поле mr представляет диапазон месяцев. Я хотел бы добавить общее количество дней в диапазоне, например, мурены, которое было бы с августа по октябрь.

Пока мне удалось заставить работать только следующий SQL, и я не имею ни малейшего представления о том, как написать функцию, чтобы делать то, что мне нужно.

 SELECT generate_series(1,12) AS n, 
       generate_series('2020-01-01'::date,'2020-12-01'::date,'1 month'::interval)  '1 month'::interval - generate_series('2020-01-01'::date,'2020-12-01'::date,'1 month'::interval) as m;

Вот результат.

  n  |    m    
---- ---------
  1 | 31 days
  2 | 29 days
  3 | 31 days
  4 | 30 days
  5 | 31 days
  6 | 30 days
  7 | 31 days
  8 | 31 days
  9 | 30 days
 10 | 31 days
 11 | 30 days
 12 | 31 days
(12 rows)

Таким образом, функция должна была бы суммировать дни в августе (31), сентябре (30) и октябре (31) на основе диапазона в mr поле.

Был бы признателен за любые рекомендации или указания.

ОБНОВЛЕНИЕ: вот решение для любопытных.

 WITH feeding(name, the_hours, start_schedule, end_schedule) AS
(SELECT name,
EXTRACT(HOUR FROM upper(tr)-lower(tr)),
make_date(extract(year from now())::int4,lower(mr),1)::timestamp,
make_date(extract(year from now())::int4,upper(mr)-1,1)::timestamp
  interval '1 month' - interval '1 day'
from fish_schedule
)
SELECT name, SUM(the_hours * (EXTRACT (days from (end_schedule - start_schedule))   1)) "total_hours"
FROM feeding
GROUP by name
ORDER by total_hours;

1. Если вы сделаете это: select sum(generate_series) from generate_series(1,3); вы получите сумму из 1 2 3 . Итак, вам нужно будет сделать select generate_series from generate_series(...) , чтобы вы могли суммировать количество дней. Документы

2. Это полезный комментарий, спасибо. Да, я пытаюсь выполнить reduce операцию для элементов в mr диапазоне, где каждый элемент mr представляет месяц.

Ответ №1:

Вам не нужно количество дней в месяце, поскольку для любой заданной строки ваш временной интервал непрерывен. Следующее преобразует столбец MT в временные метки с 1-го дня нижнего значения диапазона до последней даты верхнего значения диапазона в CTE. Основная часть затем извлекает количество дней из разницы.

 with feeding( name, start_schedule, end_schedule) as 
     ( select name 
            , make_date(extract(year from now())::int4,lower(mr),1)::timestamp
            , make_date(extract(year from now())::int4,upper(mr),1)::timestamp
                interval '1 month' - interval '1 day'
         from fish_schedule
     )
select name, extract(days from (end_schedule - start_schedule))   1 "# of days" 
  from feeding;

Примечание: здесь небольшой вопрос мнения. «Интервал»1 день» и добавление 1 в main можно исключить и все равно получить тот же результат. IMO вышеизложенное более четко отражает намерение. Но их отсутствие делает запрос немного короче и «бесконечно малым» быстрее.

PS. Он также обрабатывает точку @Vesa в високосный год.

1. Я использую PostgreSQL 12, и фрагмент кода выдает следующую ошибку: ERROR: date field value out of range: 2020-13-01

2. Это потому, что нет месяца 13. Каково полное сообщение об ошибке и значения данных.

3. Это было полное сообщение об ошибке. Кажется, это не нравится UPPER(mr) , поскольку это возвращает верхний предел диапазона, когда предполагается, что он не является включающим пределом. т.е. [1,13) означает от 1 до 12 включительно, а не от 1 до 13. Но PostgreSQL сохраняет его как от 1 до 13, не включительно.

4. Похоже на ошибку входных данных. что дает следующее. выберите * из fish_schedule, где верхний (mr)> 12; выберите отдельный верхний (mr) из fish_schedule;

5. Проверьте, как был создан столбец MR. Возможно, диапазон был создан следующим образом: выберите int4range(8,12,'[]’) Postgres преобразует это в int4range(8,13,'[)’)

Ответ №2:

Чтобы получить количество дней в году, вы могли бы сделать:

 select sum(d) from (
   SELECT 
      date_part('month',generate_series) as n,
      generate_series as startOfMonth,
      date_trunc('month',generate_series)   '1 month'::interval - '1 day'::interval  as endOfMonth,
      date_part('days', date_trunc('month',generate_series)   '1 month'::interval - '1 day'::interval) as d
   FROM generate_series('2020-01-01'::date,'2020-12-01'::date,'1 month'::interval)
) x
;

Это возвращает 366 дней, возвращает внутренний запрос:

  n  |      startofmonth      |       endofmonth       | d
---- ------------------------ ------------------------ ----
  1 | 2020-01-01 00:00:00 01 | 2020-01-31 00:00:00 01 | 31
  2 | 2020-02-01 00:00:00 01 | 2020-02-29 00:00:00 01 | 29
  3 | 2020-03-01 00:00:00 01 | 2020-03-31 00:00:00 02 | 31
  4 | 2020-04-01 00:00:00 02 | 2020-04-30 00:00:00 02 | 30
  5 | 2020-05-01 00:00:00 02 | 2020-05-31 00:00:00 02 | 31
  6 | 2020-06-01 00:00:00 02 | 2020-06-30 00:00:00 02 | 30
  7 | 2020-07-01 00:00:00 02 | 2020-07-31 00:00:00 02 | 31
  8 | 2020-08-01 00:00:00 02 | 2020-08-31 00:00:00 02 | 31
  9 | 2020-09-01 00:00:00 02 | 2020-09-30 00:00:00 02 | 30
 10 | 2020-10-01 00:00:00 02 | 2020-10-31 00:00:00 01 | 31
 11 | 2020-11-01 00:00:00 01 | 2020-11-30 00:00:00 01 | 30
 12 | 2020-12-01 00:00:00 01 | 2020-12-31 00:00:00 01 | 31
(12 rows)

Я надеюсь, что это поможет изменить ваш запрос, чтобы получить правильные результаты.

Ответ №3:

 SELECT
    name,
    to_date('2020-' || upper(mr) || '-01', 'yyyy-mm-dd')
    - to_date ('2020-' || lower(mr) || '-01', 'yyyy-mm-dd')
FROM
    fish_schedule;

Нужно ли быть осторожным в високосные дни? 2020 год — високосный год.