Академия: Конспект второй недели курса Learn SQL

Продолжаем изучение SQL.
Как я писал ранее - на очереди агрегирующие функции.
Агрегирующие функции являются "обёртками" при выборке полей из таблиц. Мы разберём каждую из них.

Подсчёт количества строк

Предположим, что у вас в таблице энное количество строк. Вы можете извлечь их все и потом пробежавшись по ним посчитать их количество. Но, так-же, можно отдать эту задачу на исполнение самой базе данных, это будет работать быстрее и кода вы напишите меньше.
Сделаем обычный запрос:
SELECT * FROM fake_apps;

Как вы видите - строк очень много
Оператором COUNT можно посчитать количество строк в выборке:
SELECT COUNT(*) FROM fake_apps;

Такой запрос сразу выдаст нам число 200 - количество строк в таблице fake_apps

Если обратить внимание на первый скриншот - то видно, что у каждого приложения есть цена. У кого-то она равно нулю. Подсчитать количество бесплатных приложений можно тем-же самым запросом, добавив в него условие.
SELECT COUNT(*) FROM fake_apps WHERE price = 0;

Группировка

Группировка как правило всегда используется с агрегирующими функциями.
Продолжим экспериментировать с ценами и количеством. Давайте посчитаем сколько приложений для каждой цены.
Если вы не знаете о группировке - то можно было бы извлечь все уникальные цены и для каждой из них сделать свой запрос. Но проще всё это сделать в одном запросе.
Для группировки используется ключевое слово GROUP BY, после которого перечисляются поля(или одно поле), по которым происходит группировка.
SELECT price, COUNT(*) FROM fake_apps GROUP BY price;

В таком запросе наряду с COUNT()* нужно извлечь и поле price, чтобы было понятно к какой цене относится количество приложений.

Мы легко можем усложнить данный запрос, к примеру сделав такую выборку только для тех приложений, которые скачали более 20 тысяч раз.
SELECT price, COUNT(*) FROM fake_apps WHERE downloads > 20000 GROUP BY price;

Нахождение суммы

Для суммирования значений ячеек строк используется оператор SUM.
Просто оборачиваем им нужную ячейку и её значения будут суммированы.
Найдём общее количество скачиваний всех приложений
SELECT SUM(downloads) FROM fake_apps;

У каждого приложения есть свой раздел, разобьем общее количество на категории.
Для этого вновь будем использовать ключевое слово GROUP BY следующим образом.
SELECT category, SUM(downloads) FROM fake_apps GROUP BY category;
В этом запросе будет извлечена колонка category и для каждой уникальной category(потому-что по ней сгруппировали) будет складываться значение ячейки downloads.

Максимальные и минимальные значения

Для нахождения максимального и минимального значений ячейки используются операторы MAX и MIN.
Оборачиваем одним из них нужную ячейку и получаем искомое значение
Самое популярное приложение:
SELECT MAX(downloads) FROM fake_apps;

Как вы видите, это просто ячейка с количеством скачиваний, без указания на само приложение.
Теперь найдём самое скачиваемое приложение в каждом разделе:
SELECT name, category, MAX(downloads) FROM fake_apps GROUP BY category;
В этом запросе мы условно делим выборку на группы по полю category, затем внутри каждой группы находим строку с максимальным значением downloads и выводим name и category этой строки.

Соответственно вместо MAX можно написать MIN.
SELECT MIN(downloads) FROM fake_apps;
SELECT name, category, MIN(downloads) FROM fake_apps GROUP BY category;

Усреднённое значение

Для вычисления среднего значения ячейки используется оператор AVG (от average).
Подсчитать среднее количество скачиваний для всей таблицы очень легко. Делается это так:
SELECT AVG(downloads) FROM fake_apps;

Среднее количество скачиваний для каждой ценовой категории можно вычислить сгруппировав данные по полю price
SELECT price, AVG(downloads) FROM fake_apps GROUP BY price;

В результатах запроса видно, что среднее значение имеет множество знаков после запятой. Избавиться от них можно при помощи оператора ROUND.
В него нужно передать два аргумента - округляемое число и количество знаков после запятой.
SELECT price, ROUND(AVG(downloads), 2) FROM fake_apps GROUP BY price;
Теперь AVG(downloads) будет округлено до сотых.

Округление будет математическим. То есть 12.238 округлится до 12.24, а не просто обрежется до 12.23.

Если опустить второй аргумент
SELECT price, ROUND(AVG(downloads)) FROM fake_apps GROUP BY price;
то ROUND округлит значение до целых.

Запрос сразу к нескольким таблицам

Как бы банально это не звучало, но можно просто выполнить два запроса:) Запросы отделяются друг от друга точкой с запятой.

Так-же выборка данных из нескольких таблиц может быть и в одном запросе, и чтобы понимать из какой таблицы извлекать колонку(тем более они могут иметь одинаковые имена) перед именем колонки указывается имя таблицы.
Вот такой запрос будет корректно работать:
SELECT albums.name, albums.year, artists.name FROM albums, artists;

Присоединение таблиц

В таблице albums у нас есть поле artist_id, в котором хранится числовой идентификатор артиста из таблицы artists. Давайте попробуем связать обе таблицы по этому полю.
Для такой связки используется оператор JOIN.
Сам запрос будет таким:

SELECT *
FROM albums
JOIN artists 
ON albums.artist_id = artists.id;

Запрос работает так: извлекаются все ячейки из всех таблиц. Вначале извлекаются все строки из таблицы albums. Затем из каждой строки таблицы albums берётся значение поля artist_id и ищется строка в таблице artists, у которой значение ячейки id совпадает с ним. Если такая строка не будет найдена - то строка исключается из выборки.

Как видите - значение колонок artist_id и id равно.

Если вам не нужно терять данные, для которых нет сопутствующих данных в “приджоиниваемой” таблице - то используется присоединение слева LEFT JOIN.
В LEFT JOIN нужно знать и понимать одну вещь. Если этот джоин не возвращает данные - то он вернёт NULL для каждого поля “приджойненной” таблицы и вы не потеряете строку из основной таблицы.

Для альбома 1989 нет исполнителя, поэтому его id и name пусты.

Использование псевдонимов

Имена ячеек не всегда могут удобны, они могут иметь неоднозначное имя и пересекаться им с другими участвующими в запросе таблицами.
Например у нас и в таблице albums, и в artists есть колонка name. В первом случае это имя альбома, а во втором имя артиста. Если вы работаете с одной таблицей - то всё в порядке, а с двумя возникает коллизия.
Решается она использованием псевдонимов при выборке полей. Псевдоним указывается после имени ячейки. Между именем ячейки и псевдонимом стоит ключевое слово AS, которое необязательно(с ним просто запрос легче воспринимается на глаз).
Давайте сделаем выборку альбомов, выпущенных после 1980 года с указанием автора альбома(его артиста).

SELECT
  albums.name AS 'Album',
  albums.year,
  artists.name AS 'Artist'
FROM
  albums
JOIN artists ON
  albums.artist_id = artists.id
WHERE
  albums.year > 1980;

Собственно это основы работы с реляционными базами данных. Всё это будет работать и для других типов баз, будут лишь мелкие поправки на синтаксис.

Схематично работу с несколькими таблицами можно представить так.

Что для меня было наиболее интересным и впечатляющим в данной неделе курса?

Очень понравилось, что объяснено как можно подсчитать статистические данные непосредственно в базе данных. Обработка массива данных, для которого, навскидку, можно было бы написать небольшой скрипт реализована в одном запросе. А ведь краткость - сестра таланта.