Функция generate_series

Буквально вчера смотрел выпуск RuPostgres (я думал что его не будет, майские праздники же у нас), но напоминалка пришла и я включил прямой эфир. Там много чего интересного обсуждали, однако мне запала в душу фраза про функцию generate_series.

С помощью этой функции можно генерировать ряды значений, чем я регулярно пользуюсь. Даже статью в документации читал про эту функцию. Но читал её уже давно, и тогда я хотел понять, как именно работает generate_series для моей конкретной задачи, так что мало что сверх этого запомнил.

В общем, с её помощью можно не только последовательные ряды генерировать, но и даты. Причем, можно это делать с определенным шагом.

Посмотрим, как это делается.

Самое простое — генерация последовательного ряда:

SELECT n FROM generate_series(1,5) n;
 n 
---
 1
 2
 3
 4
 5
(5 rows)

Обратите внимание — если второй параметр будет больше первого, функция отработает, но будет возвращено ноль строк:

SELECT n FROM generate_series(5,1) n;
 n 
---
(0 rows)

Всё просто — два параметр, минимальный и максимальный. Но есть и третий параметр — шаг. Например, нам нужны все нечетные числа в этом диапазоне:

SELECT n FROM generate_series(1,5, 2) n;
 n 
---
 1
 3
 5
(3 rows)

Шаг можно указывать и дробным:

SELECT n FROM generate_series(1,5, 0.5) n;
  n  
-----
   1
 1.5
 2.0
 2.5
 3.0
 3.5
 4.0
 4.5
 5.0
(9 rows)

Или еще мельче:

SELECT n FROM generate_series(1,5, 0.1) n;
  n
-----
   1
 1.1
 1.2
 1.3
...
 4.8
 4.9
 5.0
(41 rows)

А можно даже не выводить то, что возвращает функция:

SELECT 'Hello' n FROM generate_series(1,5) n;
   n   
-------
 Hello
 Hello
 Hello
 Hello
 Hello
(5 rows)

И не только функций — можем для каждой строки запроса сгенерировать последовательные числа:

SELECT lanname, count FROM pg_language, generate_series(1,3) count;
 lanname  | count 
----------+-------
 internal |     1
 c        |     1
 sql      |     1
 plpgsql  |     1
 internal |     2
 c        |     2
 sql      |     2
 plpgsql  |     2
 internal |     3
 c        |     3
 sql      |     3
 plpgsql  |     3
(12 rows)

Можно сгенерировать нужное количество случайных чисел:

SELECT random() n FROM generate_series(1,5) n;
          n          
---------------------
 0.08815921096429236
  0.8631646754119195
 0.06004375843658494
  0.7940473366465746
 0.09313062237612257
(5 rows)

Или, например, случайные целые числа:

SELECT trunc(random()*10) n FROM generate_series(1,5) n;
 n 
---
 5
 7
 8
 6
 0
(5 rows)

Это даёт возможность генерировать данные, о чем я уже немного писал: Генерация кода с помощью запросов в PostgreSQL.

А теперь — даты.

Принцип такой же. Указываем от — до, но интервал (третий параметр) нужно указывать. Пример взял из документации:

SELECT trunc(random()*10) n FROM generate_series(1,5) n;
 n 
---
 5
 7
 8
 6
 0
(5 rows)

Интервал можно менять. В следующем примере date_trunc «обрезает» сегодняшнюю дату до начала суток:

SELECT * FROM generate_series(date_trunc('day', now()), now(),'2.5 hour');
    generate_series     
------------------------
 2021-05-05 00:00:00+03
 2021-05-05 02:30:00+03
 2021-05-05 05:00:00+03
 2021-05-05 07:30:00+03
 2021-05-05 10:00:00+03
 2021-05-05 12:30:00+03
 2021-05-05 15:00:00+03
(7 rows)

Или даже так (ко второй дате прибавлю месяц):

SELECT * FROM generate_series(now(), now() + interval '1 month','1 week');
       generate_series        
------------------------------
 2021-05-05 16:01:20.17855+03
 2021-05-12 16:01:20.17855+03
 2021-05-19 16:01:20.17855+03
 2021-05-26 16:01:20.17855+03
 2021-06-02 16:01:20.17855+03
(5 rows)

Функцию generate_series можно использовать и некоторым другим образом. Как именно? Расскажу в следующий раз.