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

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

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

id	name	population	area
1	Россия	147 000 000	17 100 000
2	Франция	67 000 000	551 695
…	…	…	…

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

id	name	country_id	population	rating
1	Москва	1	13 274 285	5
2	Париж	2	2 102 650	3
…	…	…	…

---

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

Оператор 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 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 не удаляет повторяющиеся строки.

Пересечение

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

Разница

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