agregirovanie_i_gruppirovka_v

Агрегирование и группировка в Postgresql

AVG

AVG - среднее значение в столбце таблицы

demo=# select avg(total_amount) from bookings;
        avg         
--------------------
 79299.069731089332
(1 row)

MAX

MAX - максимальное значение в столбце

demo=# select max(total_amount) from bookings;
    max     
------------
 1308700.00
(1 row)

MIN

MIN - минимальное значение

demo=# select min(total_amount) from bookings;
   min   
---------
 3400.00
(1 row)

Сколько рейсов выполняется в неделю, за 3 дня, за 2 и за 1 день, здесь вычисляется длина массива, подсчитывается кол-во рейсов за каждый день недели, группировка и сортировка по дням недели

demo=# SELECT array_length( days_of_week, 1 ) AS days_per_week, count( * ) AS num_routes
FROM routes GROUP BY days_per_week ORDER BY 1 desc ;
 days_per_week | num_routes 
---------------+------------
             7 |        482
             3 |         54
             2 |         88
             1 |         86
(4 rows)

В функции array_length() в качестве второго параметра указывается размерность массива (массив одномерный)
При выполнении группировок можно задать дополнительное условие HAVING "условие". HAVING прописывается после группировки.

Оконные функции

Простой пример без соединений и дополнительных условий:

demo=# select book_ref, book_date, extract( 'month' from b.book_date ) AS month, extract( 'day' from b.book_date ) AS day,
count( * ) OVER (
PARTITION BY date_trunc( 'month', b.book_date )
ORDER BY b.book_date ) AS count FROM bookings b ;

Более сложный пример:

demo=# select b.book_ref, b.book_date,
extract( 'month' from b.book_date ) AS month,
extract( 'day' from b.book_date ) AS day,
count( * ) OVER ( PARTITION BY date_trunc( 'month', b.book_date )
ORDER BY b.book_date ) AS count
FROM ticket_flights tf
JOIN tickets t ON tf.ticket_no = t.ticket_no
JOIN bookings b ON t.book_ref = b.book_ref
WHERE tf.flight_id = 93852
ORDER BY b.book_date ;
 book_ref | book_date | month | day | count 
----------+-----------+-------+-----+-------
(0 rows)

С помощью EXTRACT выбраны месяц и день атрибута book_date из таблицы bookings
Группировка выполнена по атрибуту book_date таблицы bookings
В запросе так же участвуют таблицы ticket_flights и tickets
Оконная функция начинается с count( * ) OVER ...
Функция count ( * ) это агрегатная функция, но если вслед за ней идет ключевое слово OVER, она становится оконной
PARTITION BY - задает разбиение строк на разделы.
ORDER BY - задает порядок строк в разделах
Строки выборки в пределах раздела имеют что то общее ( свойства, значения выражений, выражения полученные из одного или нескольких атрибутов таблицы или таблиц ), которые задаются с помощью предложения PARTITION BY
При формировании границ кадры, важным является использование ORDER BY
В оконных функциях могут использоваться и другие агрегатные функции
Если в запросе присутствуют предложения GROUP BY и HAVING, оконные функции вызываются после них и будут работать с результатом группировки.