Шпаргалка по SQL. Часть #2

SQL (Structured Query Language) — это язык для работы с базами данных. Позволяет выбирать данные и строить сложные отчеты. Сегодня SQL стал универсальным языком данных.

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

Таблица «страна»

Таблица «город»

Агрегация и группировка данных

Оператор GROUP BY группирует записи с одинаковыми значениями в заданных столбцах, вычисляя агрегатные показатели для каждой группы.

Простейшие агрегатные функции

• avg(выражение) − среднее значение для строк в группе данных
• count(выражение) − количество строк в группе данных
• max(выражение) − максимальное значение в группе данных
• min(выражение) − минимальное значение в группе данных
• sum(выражение) − сумма значений в группе данных

-- количество городов
SELECT COUNT(*)
  FROM city;

-- количество городов с заполненным рейтингом
SELECT COUNT(rating)
  FROM city;

-- количество уникальных стран через города
SELECT COUNT(DISTINCT country_id)
  FROM city;

-- наименьшее и наибольше количество населения
SELECT MIN(population), 
       MAX(population)
  FROM country;

-- расчет общего населения городов по странам
  SELECT country_id, 
         SUM(population)
    FROM city
GROUP BY country_id;

-- расчет среднего рейтинга городов по странам (с фильтром по среднему)
  SELECT country_id, 
         AVG(rating)
    FROM city
GROUP BY country_id
  HAVING AVG(rating) > 3.0;

-- количество городов сгруппированных по описанию рейтинга
  SELECT 
         CASE 
           WHEN rating IN (4, 5) THEN 'высокий рейтинг'
           WHEN rating IN (2, 3) THEN 'средний рейтинг'
           ELSE 'низкий рейтинг'
         END AS rating_word,
         COUNT(*)
    FROM city
GROUP BY rating_word;

-- количество городов в стране с высоким рейтингом
  SELECT country_id,
         COUNT(CASE WHEN rating >= 4 THEN 1 ELSE 0 END) AS high_rating
    FROM city
GROUP BY country_id;

Расширения оператора GROUP BY

Оператор GROUPING SETS позволяет указать несколько наборов столбцов для группировки в одном запросе.

   SELECT region, 
          product, 
          COUNT(order_id)
     FROM sales
 GROUP BY 
     GROUPING SETS ((region, product), ());

Оператор CUBE создает группировки для всех возможных подмножеств столбцов, указанных в GROUP BY.

   SELECT region, 
          product, 
          COUNT(order_id)
     FROM sales
 GROUP BY 
     CUBE (region, product);

Оператор ROLLUP добавляет новые уровни группировки для промежуточных итогов и общего результата.

   SELECT region, 
          product, 
          COUNT(order_id)
     FROM sales
 GROUP BY 
     ROLLUP (region, product);

Подзапросы

Подзапрос — это запрос, вложенный внутри другого запроса или внутри другого подзапроса. Существуют различные типы подзапросов.

Подзапрос с одиночным значением

Самый простой подзапрос возвращает ровно один столбец и ровно одну строку. Такой подзапрос можно использовать с операторами сравнения =, !=, <, <=, > или >=

-- запрос находит города с рейтингом, как у Москвы
SELECT name
  FROM city
 WHERE rating = (SELECT rating
                   FROM city
                  WHERE name = 'Москва');

Подзапрос со множеством значений

Подзапрос также может возвращать несколько столбцов или несколько строк. Такие подзапросы можно использовать с операторами IN, EXISTS, ALL или ANY.

-- запрос находит города в странах с населением свыше 20 миллионов
SELECT name
  FROM city
 WHERE country_id IN (SELECT country_id
                        FROM country
                       WHERE population > 20000000);

Коррелированный подзапрос

Коррелированный подзапрос обращается к таблицам из внешнего запроса. Такой подзапрос зависит от внешнего запроса и не может быть выполнен отдельно от него.

/* 
  возвращает города, население которых превышает 
  среднее население по стране
*/
SELECT *
  FROM city AS main
 WHERE population > (SELECT AVG(population)
                       FROM city AS avg_city
                      WHERE avg_city.country_id = main.country_id);

-- запрос находит страны, в которых есть хотя бы один город:
SELECT name
  FROM country
 WHERE EXISTS (SELECT *
                 FROM city
                WHERE country_id = country.id);

Множества

Операции над множествами используются для объединения результатов двух или более запросов в единый результат. Объединяемые запросы должны возвращать одинаковое количество столбцов и совместимые типы данных. Названия соответствующих столбцов могут отличаться.

Объединение

Оператор UNION объединяет результаты двух наборов данных и удаляет дубликаты. Оператор UNION ALL не удаляет повторяющиеся строки.

/*
  запрос выводит российских хоккеистов вместе 
  с французскими футболистами.
*/
SELECT name
  FROM hockey
 WHERE country = 'Россия'
UNION / UNION ALL
SELECT name
  FROM football
 WHERE country = 'Франция';

Пересечение

Оператор INTERSECT возвращает только строки, которые присутствуют в обоих наборах данных

/*
  запрос выводит российских хоккеистов,
  которые одновременно являются и
  футболистами.
*/
SELECT name
  FROM hockey
 WHERE country = 'Россия'
INTERSECT
SELECT name
  FROM football
 WHERE country = 'Россия';

Разница

Оператор EXCEPT возвращает только строки, которые присутствуют в первом наборе результатов, но отсутствуют во втором.

/*
  запрос выводит российских хоккеистов, за 
  исключением тех, кто одновременно является 
  футболистом.
*/
SELECT name
  FROM hockey
 WHERE country = 'Россия'
EXCEPT / MINUS
SELECT name
  FROM football
 WHERE country = 'Россия';