SQL агрегирующие функции, GROUP BY, ORDER BY и HAVING

Стандартные операторы + - * /

SELECT invoice_line_id,
       invoice_id,
       invoice_line_id + invoice_id
FROM invoice_line
LIMIT 5;

Модуль числа

SELECT number,
       ABS(number)
FROM table_1;

Округление до целого в меньшую сторону

SELECT number,
       FLOOR(number)
FROM table_1;

Округление до целого в большую сторону

SELECT number,
       CEILING(number)
FROM table_1;

Округление до ближайшего целого

SELECT number,
       ROUND(number)
FROM table_1;

Округление до заданного количества знаков

SELECT ROUND(21.5595743, 2);

Возведение в степень

SELECT number,
       POWER(number, 2)
FROM table_1;

Квадратный корень

SELECT number,
       SQRT(ABS(number))
FROM table_1;

Основные агрегирующие функции в SQL:

• SUM(поле) возвращает сумму значений в поле;
• AVG(поле) находит среднее арифметическое для значений в поле;
• MIN(поле) возвращает минимальное значение в поле;
• МАХ(поле) возвращает максимальное значение в поле;
• COUNT(*) выводит количество записей в таблице, а COUNT(поле) — количество записей в поле.

SELECT SUM(total),
       MIN(total),
       MAX(total)
FROM invoice;

SELECT ROUND(AVG(total), 2)
FROM invoice;

-- так считают записи в таблице
SELECT COUNT(*)
FROM invoice;

-- так считают записи в поле
SELECT COUNT(billing_postal_code)
FROM invoice;

 -- уникальные значения без дубликатов
SELECT DISTINCT billing_country 
FROM invoice
LIMIT 5;

 -- а так уникальные сочетания значений полей
SELECT DISTINCT customer_id,
                billing_country
FROM invoice
LIMIT 10;

 -- DISTINCT можно сочетать с агрегирующими функциями
SELECT COUNT(DISTINCT billing_country)
FROM invoice;

Примеры использования агрегирующих функций:

SELECT customer_id,
       CAST(invoice_date AS date),
       total
FROM invoice
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS timestamp)) = 9
      AND customer_id IN (11, 13, 44, 36, 48, 52, 54, 56);

SELECT MIN(total),
       MAX(total),
       FLOOR(AVG(total)),
       COUNT(customer_id),
       SUM(total)
FROM invoice
WHERE EXTRACT(MONTH FROM CAST(invoice_date AS timestamp)) = 9
  AND customer_id in (11, 13, 44, 36, 48, 52, 54, 56);

SELECT COUNT(*)
FROM client
WHERE fax IS NULL;

-- средняя стоимость заказов оформленная в понедельник
SELECT AVG(total)
FROM invoice
WHERE EXTRACT(DOW FROM CAST(invoice_date AS timestamp))=1;

Группировка данных

-- нужно указать поля и необходимые вычисления
SELECT billing_city,
       ROUND(AVG(total))
FROM invoice
GROUP BY billing_city -- здесь указывают поле, по которому группируют данные
LIMIT 5;

 -- выгрузит все уникальные значения в поле. Похоже на работу DISTINCT
SELECT billing_city
FROM invoice
GROUP BY billing_city;

SELECT SUM(total)
FROM invoice
WHERE billing_country = 'USA';

SELECT billing_city,
       SUM(total),
       COUNT(*),
       AVG(total)
FROM invoice
WHERE billing_country = 'USA'
GROUP BY billing_city;

SELECT DATE_TRUNC('week', CAST(invoice_date AS timestamp)),
       SUM(total),
	   COUNT(DISTINCT customer_id),
	   SUM(total)/COUNT(DISTINCT customer_id)
FROM invoice
WHERE billing_country = 'USA'
GROUP BY DATE_TRUNC('week', CAST(invoice_date AS timestamp));

SELECT support_rep_id,
       COUNT(customer_id)
FROM client
WHERE email LIKE '%yahoo%' OR email LIKE '%gmail%'
GROUP BY support_rep_id;

SELECT CASE
            WHEN total<1 THEN 'low cost'
            ELSE 'high cost'
       END,
       SUM(total)
FROM invoice
WHERE billing_postal_code IS NOT NULL
GROUP BY CASE
            WHEN total<1 THEN 'low cost'
            ELSE 'high cost'
         END;

 -- сортировка
SELECT billing_city,
       AVG(total)
FROM invoice
WHERE billing_country = 'USA'
GROUP BY billing_city
ORDER BY AVG(total) DESC -- или ASC
LIMIT 5;

SELECT customer_id,
       COUNT(customer_id)
FROM invoice
WHERE CAST(invoice_date as date) BETWEEN '2011-05-25' AND '2011-09-25'
      AND billing_country = 'USA'
GROUP BY customer_id
ORDER BY COUNT(customer_id) DESC, customer_id
LIMIT 5;

SELECT EXTRACT(YEAR FROM CAST(invoice_date as date)),
       MIN (total),
       MAX (total),
       SUM (total),
       COUNT (invoice_id),
       ROUND(SUM(total)/COUNT(DISTINCT customer_id))
FROM invoice
WHERE billing_country IN ('USA', 'United Kingdom', 'Germany')
GROUP BY EXTRACT(YEAR FROM CAST(invoice_date as date))
ORDER BY EXTRACT(YEAR FROM CAST(invoice_date as date)) DESC;

Группировка по нескольким полям сразу

SELECT CAST(invoice_date AS date),
       billing_country,
       COUNT(total)
FROM invoice
GROUP BY CAST(invoice_date AS date),
         billing_country 
ORDER BY CAST(invoice_date AS date) DESC
LIMIT 10;

При сортировке важен порядок полей, которые указывают после оператора ORDER BY

SELECT billing_country,
       customer_id,
       total
FROM invoice
WHERE billing_country = 'India' or billing_country = 'Portugal'
ORDER BY billing_country,  -- и наконец по стране
         customer_id,  -- вторым шагом по customer_id
         total DESC; -- сначала сортируем по total

Оператор HAVING

Оператор WHERE работает только с изначальной таблицей, но не с результатом агрегирующих функций. Если нужно получить срез данных после группировки, используют оператор HAVING. HAVING похож на оператор WHERE, но с отличием: HAVING всегда идёт после GROUP BY. HAVING можно сочетать с агрегирующими функциями

SELECT customer_id,
       SUM(total)
FROM invoice
GROUP BY customer_id
HAVING SUM(total) > 41
ORDER BY SUM(total) DESC;

SELECT customer_id,
       SUM(total)
FROM invoice
GROUP BY customer_id
HAVING SUM(total) > 41 AND AVG(total) > 7
ORDER BY SUM(total) DESC;

SELECT billing_country,
       COUNT(*)
FROM invoice
WHERE billing_postal_code IS NULL
      AND billing_address NOT LIKE '%Street%'
           AND billing_address NOT LIKE '%Way%'
           AND billing_address NOT LIKE '%Road%'
           AND billing_address NOT LIKE '%Drive%'
GROUP BY billing_country
HAVING COUNT(*)>10;