Академия: Конспект второй недели курса 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;
Собственно это основы работы с реляционными базами данных. Всё это будет работать и для других типов баз, будут лишь мелкие поправки на синтаксис.
Схематично работу с несколькими таблицами можно представить так.
Что для меня было наиболее интересным и впечатляющим в данной неделе курса?
Очень понравилось, что объяснено как можно подсчитать статистические данные непосредственно в базе данных. Обработка массива данных, для которого, навскидку, можно было бы написать небольшой скрипт реализована в одном запросе. А ведь краткость - сестра таланта.