🐘 Руководство по SQL для начинающих. Часть 3: усложняем запросы, именуем вложенные запросы, анализируем скорость запроса
В последней части цикла статей мы составим сложные запросы, поработаем с вложенными запросами, поговорим о сохранности данных и немного о необходимости индексов. Первая часть 🐘 Руководство по SQL для начинающих. Часть 1: создание базы данных, таблиц и установка связей между таблицами Вторая часть 🐘 Руководство по SQL для начинающих. Часть 2: фильтрация данных, запрос внутри запроса, работа с массивами Иногда возникает ситуация, когда для получения необходимых данных нам понадобится объединить таблицу саму с собой. Наглядный пример вы можете посмотреть по этой ссылке. Суть в том, что, если строки в таблице содержат данные, которые могут быть связаны с данными из других строк этой же таблицы (например Давайте для начала добавим поле с идентификатором лучшего друга Итак, в таблице мы храним Оконные функции очень похожи на агрегатные функции ( Давайте вычислим средний балл для каждого ученика. На строках с четвертой по шестую в запросе ниже мы добавили Для агрегирующих операторов Возможность ранжирования данных более полезный случай. Попробуем сделать это с оценками наших учеников. Чтобы ранжировать оценки всех учеников мы будем использовать оператор Добавим всего одну строку в наш запрос, а именно У оконных функций есть еще несколько интересных возможностей. Например, функции распределения Давайте познакомимся еще с одним инструментом – именование вложенных запросов. Оператор Например, мы хотим сравнить оценку ученика (колонка Это было просто. Но как же нам сравнить каждую оценку со средним баллом? Взгляните на примеры запросов ниже, все они выдадут ошибку. Мы можем дважды использовать оконные функции, но это выглядит как минимум не очень читабельно. Чтобы наш запрос был читабельным и масштабируемым, будем использовать Да, безусловно этот запрос получился заметно больше, нежели написание двух оконных функций. Однако, такой подробный запрос имеет два главных преимущества: читабельность и масштабируемость. Запросы могут быть чудовищно длинные. Например в компании Meta (Facebook), мне встречался запрос, содержащий в себе 1000 строк и вызывал сразу 25 таблиц. Этот запрос был бы совершенно нечитаемым без применения оператора Работая с большим объемом данных, мы не имеем таких роскошных возможностей как последовательное выполнение подзапросов, сохранение данных в CSV формат, затем объединение полученных данных и анализа их с помощью Python. Все взаимодействия с базой данных должны происходить за один раз. Давайте разберем еще один вариант. Предположим, в нашей школе уволили директора, и система оценок претерпела изменения. У нас появился такой показатель как средневзвешенный балл, который вычисляется как оценка ( Давайте начнем с того, что выявим учеников, у которых средневзвешенный балл больше 85%. Прекрасно. Теперь узнаем у кого из учеников больше 70% по персональному проекту. Но, как мы помним, нам нужно, чтобы наш запрос выдал всех учеников, которые удовлетворяют либо первому, либо второму условию. Это очень просто с использованием В завершении данной статьи, давайте разберем еще одну важную тему. Чем больше мы изучаем И в итоге, как нам понять, что один запрос более эффективен, чем другой? Postgres может нам об этом рассказать. Ключевое слово Можно пойти еще дальше и использовать оператор Например в результате выше можно увидеть, что Postgres последовательно сканирует ( *** Данная статья была обзором тех навыков в SQL, которые вам точно понадобятся сразу после получения базовых знаний. Мы начали с установки Postgres и pgAdmin, тем самым получив возможность экспериментировать с базой данных на своём компьютере. Затем мы узнали про полезные синтаксические конструкции, которые позволили нам составлять более сложные запросы. Мы начали с фильтрации данных и узнали, чем отличается В финальной части мы разобрали, как составлять еще более сложные запросы, такие как объединение таблицы с самой собой, оконные функции для сравнения данных, и оператор И конечно же это далеко не всё. Есть еще много функций, про которые стоит знать, например Что же на самом деле происходит когда мы используем Давайте предположим, что мы удаляем таблицу Теперь заново создадим таблицу Так произошло, потому что И еще одно замечание по поводу Систематизирование схемы базы данных и создание скриптов – это лучшая практика с инженерной точки зрения, но при работе с реальными данными очень часто используются резервные копии. Существует много подходов, иногда используются полные копии баз данных, которые занимают большое количество памяти, а иногда фиксируют только конкретные изменения. В идеале, такие копии должны храниться на географически удаленном сервере, чтобы снизить вариант возможного влияния стихии на сохранность ваших данных. Мы установили, что тип данных в колонке С технической точки зрения, в Postgres не имеет значения, какую длину строки мы определили (10, 100 или 500 символов). Указание длины строки – это скорее хороший способ коммуникации между разработчиками, потому что таким образом вы указываете, что вы ожидаете получить в этом поле. Но, например, в MySQL длина строк имеет значение: временные таблицы и таблицы Если вам интересно, ниже пример того, как в Postgres выглядит синтаксис с оператором При увеличении базы данных, установка индексов становится критически важной. Для примера, давайте создадим индекс на поле с оценками в таблице Тем не менее, если сейчас мы применим оператор *** 
Самостоятельное объединение (self join)
id), мы может сделать join таблицы к ней самой.best_friend_id в таблицу учеников и вставим в него некоторые данные.
ALTER TABLE students ADD best_friend_id INT; UPDATE students SET best_friend_id = 5 WHERE id = 1; UPDATE students SET best_friend_id = 4 WHERE id = 2; UPDATE students SET best_friend_id = 2 WHERE id = 3; UPDATE students SET best_friend_id = 2 WHERE id = 4; UPDATE students SET best_friend_id = 1 WHERE id = 5; SELECT * FROM students; /* id | name | classroom_id | best_friend_id --- | -------- | ------------ | -------------- 1 | Adam | 1 | 5 2 | Betty | 1 | 4 3 | Caroline | 2 | 2 4 | Dina | [null] | 2 5 | Evan | [null] | 1 */ id лучшего друга для каждого ученика. Это эффективно, но не очень читабельно. Чтобы определить, кто же является лучшим другом, нам придётся соединить таблицу саму с собой. Возьмем таблицу students и сделаем соединение (join) снова с таблицей students, в качестве ключей для связи у нас будет id студента и id лучшего друга (best_friend_id). Определим псевдонимы таблиц x и y для улучшения понимания кода.
SELECT x.name, y.name AS best_friend FROM students AS x INNER JOIN students AS y ON y.id = x.best_friend_id; /* name | best_friend -------- | ----------- Adam | Evan Betty | Dina Caroline | Betty Dina | Betty Evan | Adam */ Оконные функции (Window functions)
group by) тем, что они применяются для вычислений сгруппированных наборов данных. Но в отличие от агрегатных функций, оконные не уменьшают количество строк в результатах запроса.OVER и PARTITION BY, чтобы из агрегатной функции сделать оконную.
SELECT s.name, g.score, AVG(g.score) OVER ( PARTITION BY s.name ) FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id; /* name | score | avg ------| ----- | ---------- Adam | 82 | 80.8000... Adam | 82 | 80.8000... Adam | 80 | 80.8000... Adam | 75 | 80.8000... Adam | 85 | 80.8000... Betty | 74 | 70.4000... Betty | 75 | 70.4000... ... | ... | ... */ AVG, MIN, или MAX, каждая строка в группировке PARTITION BY будет отображать одинаковое значение. Возможно, для определенных видов анализа это и понадобится, но на самом деле, сила оконных функций в другом.RANK() OVER, в который мы передадим нужный нам столбец.
SELECT s.name, g.score, RANK() OVER ( ORDER BY g.score ) FROM grades AS g INNER JOIN students AS s ON s.id = g.student_id; /* name | score | rank ----- | ----- | ---- Betty | 64 | 1 Dina | 64 | 1 Evan | 67 | 3 ... | ... | ... */ PARTITION BY s.name к оператору OVER. В итоге мы получили ранжирование в рамках одного студента.
SELECT s.name, g.score, RANK() OVER ( PARTITION BY s.name -- ranks by student ORDER BY g.score ) FROM grades AS g INNER JOIN students AS s ON s.id = g.student_id; /* name | score | rank -------- | ----- | ---- Adam | 75 | 1 Adam | 80 | 2 Adam | 82 | 3 Adam | 82 | 3 Adam | 85 | 5 Betty | 64 | 1 Betty | 69 | 2 Betty | 70 | 3 Betty | 74 | 4 Betty | 75 | 5 Caroline | 90 | 1 Caroline | 92 | 2 ... | ... | ... */ cumulative distribution, функции dense и percent ranks. dense_rank – функция возвращает ранг каждой строки, но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий. С функциями lag и lead вы можете более подробно ознакомиться по ссылке. Оператор WITH
WITH позволяет нам присваивать имя нашему подзапросу, а это значит, что мы сможем обращаться к его результатам в других местах.grades) с его средним баллом. Сделать это в одном запросе вроде бы несложно, нам сначала нужно вычислить средний балл с помощью оператора GROUP BY, а затем написать что-то вроде g.score > avg. Начнем с агрегации GROUP BY.
SELECT s.name, ROUND(AVG(g.score),1) AS avg FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id GROUP BY s.name; /* name | avg -------- | ---- Dina | 79.6 Evan | 83.4 Betty | 70.4 Caroline | 94.6 Adam | 80.8 */
SELECT s.name, ROUND(AVG(g.score),1) AS avg, g.score > avg ... -- ERROR: column "avg" does not exist SELECT s.name, ROUND(AVG(g.score),1) AS avg, g.score > ROUND(AVG(g.score),1) ... -- ERROR: column "g.score" must appear in the GROUP BY -- clause or be used in an aggregate function
SELECT s.name, AVG(g.score) OVER (PARTITION BY s.name), g.score > AVG(g.score) OVER (PARTITION BY s.name) ... WITH. Разделим наш запрос на два подзапроса: первый будет считать средний балл, а второй добавит записи с индивидуальными оценками grades.
WITH averages AS ( SELECT s.id, ROUND(AVG(g.score),1) AS avg_score FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id GROUP BY s.id ) SELECT s.name, g.score, a.avg_score, g.score > a.avg_score AS above_avg FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN averages AS a ON a.id = s.id; /* name | score | avg_score | above_avg ----- | ----- | --------- | --------- Adam | 82 | 80.8 | true Adam | 82 | 80.8 | true Adam | 80 | 80.8 | false Adam | 75 | 80.8 | false Adam | 85 | 80.8 | true Betty | 74 | 70.4 | true Betty | 75 | 70.4 | true */ WITH, который разграничивает отдельные участки кода и присваивает им псевдонимы.grades) умноженная на коэффициент сложности (weight). Теперь для сдачи экзамена необходимо иметь: средневзвешенный балл по всем предметам не ниже 85% или этот же балл не ниже 70% по собственному проекту. Объединение данной логики в один блок CASE WHEN достаточно сложно, но, если мы разделим запрос с помощью WITH всё станет гораздо проще.
SELECT s.name FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN assignments AS a ON a.id = g.assignment_id GROUP BY s.name HAVING SUM(g.score * a.weight) > 85; /* name -------- Caroline */
SELECT s.name FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN assignments AS a ON a.id = g.assignment_id WHERE a.name = 'biography' AND g.score > 70 /* name -------- Adam Caroline Evan */
SELECT DISTINCT name FROM students WHERE name IN <people_who_passed_final> OR name IN <people_who_passed_project>; WITH. Мы присвоим псевдонимы нашим запросам weighted_pass и project_pass, а потом объединим их.
WITH weighted_pass AS ( SELECT s.name FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN assignments AS a ON a.id = g.assignment_id GROUP BY s.name HAVING SUM(g.score * a.weight) > 85 ), project_pass AS ( SELECT s.name FROM students AS s INNER JOIN grades AS g ON s.id = g.student_id INNER JOIN assignments AS a ON a.id = g.assignment_id WHERE a.name = 'biography' AND g.score > 70 ) SELECT DISTINCT name FROM students WHERE name IN (SELECT name FROM weighted_pass) OR name IN (SELECT name FROM project_pass); /* name -------- Evan Caroline Adam */ Смотрим вглубь – EXPLAIN
SQL, тем больше способов построения сложных запросов нам известно. Когда лучше использовать EXCEPT, а когда NOT IN? Нужно ли нам использовать несколько JOIN для объединения таблиц, либо лучше применить WITH и UNION ALL?explain предоставляет нам план выполнения, который подробно описывает как выполняется ваш запрос. Вернемся к запросу из начала статьи и увидим, что Postgres выполняет запрос совершенно не в том порядке, в каком мы написали.
EXPLAIN SELECT s.id AS student_id, g.score FROM students AS s LEFT JOIN grades AS g ON s.id = g.student_id WHERE g.score > 90 ORDER BY g.score DESC; /* QUERY PLAN ---------- Sort (cost=80.34..81.88 rows=617 width=8) [...] Sort Key: g.score DESC [...] -> Hash Join (cost=16.98..51.74 rows=617 width=8) [...] Hash Cond: (g.student_id = s.id) [...] -> Seq Scan on grades g (cost=0.00..33.13 rows=617 width=8) [...] Filter: (score > 90) [...] -> Hash (cost=13.10..13.10 rows=310 width=4) [...] -> Seq Scan on students s (cost=0.00..13.20 rows=320 width=4) */ EXPLAIN ANALYZE, который отобразит еще более детальную информацию (например, время выполнения каждой части запроса и используемую память).
EXPLAIN ANALYZE SELECT s.id AS student_id, g.score FROM students AS s LEFT JOIN grades AS g ON s.id = g.student_id WHERE g.score > 90 ORDER BY g.score DESC; /* QUERY PLAN ---------- Sort (cost=80.34..81.88 rows=617 width=8) (actual tiem=0.169..0.171 rows=6 loops=1) [...] Sort Key: g.score DESC [...] Sort Method: quicksort Memory: 25kB [...] -> Hash Join (cost=16.98..51.74 rows=617 width=8) (actual time=0.115..0.145 rows=6 loops=1) [...] Hash Cond: (g.student_id = s.id) [...] -> Seq Scan on grades g (cost=0.00..33.13 rows=617 width=8) (actual time=0.045..0.052 rows=6 loops=1) [...] Filter: (score > 90) Rows removed by Filter: 19 [...] -> Hash (cost=13.10..13.10 rows=310 width=4) (actual time=0.059..0.060 rows=5 loops=1) [...] Buckets: 1024 Batches: 1 Memory Usage: 9kB [...] -> Seq Scan on students s (cost=0.00..13.10 rows=310 width=4) (actual time=0.022..0.027 rows=5 loops=1) Planning Time: 0.379 ms Execution Time: 0.227 ms */ Seq Scan) таблицы grades и students, потому что они не индексированы. Иначе говоря, Postgres не знает что строка внизу таблицы имеет id меньше или больше чем строка наверху таблицы. В нашем случае, это не имеет особого значения ввиду размеров нашей базы данных, но если бы мы работали с миллионами строк данных, то нам определенно потребовалось бы найти и устранить такие узкие места в производительности.WHERE от HAVING. Далее мы познакомились с условными конструкциями, узнали как сегментировать данные с помощью CASE WHEN и обрабатывать null, используя COALESCE. Мы перешли от горизонтального к вертикальному объединению таблиц с помощью операций над множествами, разобрав как UNION, UNION ALL, INTERSECT, и EXCEPT работают с повторяющимися строками в таблицах. И в конце второй части статьи узнали как создавать массивы и работать с ними.WITH для именования вложенных запросов. И в конце мы узнали, что с помощью EXPLAIN и EXPLAIN ANALYZE можно оценить производительность наших запросов и узнать в каких местах их можно оптимизировать.CAST (для приведения одного типа данных к другому, например float к integer), или функции, определяемые пользователем (user-defined functions), которые можно использовать для дальнейшего упрощения кода. Всё это безусловно очень полезно, но я бы рекомендовал всегда думать об оптимизации запросов. Даже в FAANG компаниях с практически неограниченными вычислительными ресурсами, запросы могут завершаться с ошибкой в том случае, если они используют больше памяти, чем может предоставить сервер. Выбор правильного подхода к написанию запроса, упрощает работу с потоком данных, что снижает шанс получить гневный звонок в полночь.Дополнение
1. CASCADE
CASCADE при удалении таблицы? classrooms и не затрагиваем остальные. Данные в таблице students не затронуты, в результате запроса мы по прежнему видим id класса.
SELECT s.name, s.classroom_id, c.teacher FROM students AS s LEFT JOIN classrooms AS c ON c.id = s.classroom_id; /* name | classroom_id | teacher -------- | ------------ | ------- Adam | 1 | Mary Betty | 1 | Mary Caroline | 2 | Jonah Dina | [null] | [null] Evan | [null] | [null] */ DROP TABLE classrooms CASCADE; /* DROP TABLE Query returned successfully in 71 msec. */ SELECT * FROM students; /* id | name | classroom_id | best_friend_id -- | -------- | ------------ | -------------- 1 | Adam | 1 | 5 2 | Betty | 1 | 4 3 | Caroline | 2 | 2 4 | Dina | [null] | 2 5 | Evan | [null] | 1 */ classrooms и добавим в нее новых учителей, которых не было в оригинальной таблице. Связь между таблицами students и classrooms уже не точная, поскольку имена учителей изменили в сравнении с оригинальной таблицей.
CREATE TABLE classrooms ( id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, teacher VARCHAR(100) ); /* CREATE TABLE Query returned successfully in 139 msec. */ INSERT INTO classrooms (teacher) VALUES ('Dr. Random'), ('Alien Banana'); /* INSERT 0 2 Query returned successfully in 99 msec. */ SELECT s.name, s.classroom_id, c.teacher FROM students AS s LEFT JOIN classrooms AS c ON c.id = s.classroom_id; /* name | classroom_id | teacher -------- | ------------ | ----------- Adam | 1 | Dr. Random Betty | 1 | Dr. Random Caroline | 2 | Alien Banana Dina | [null] | [null] Evan | [null] | [null] */ CASCADE удалил внешний ключ в таблице students. Мы можем это проверить. Изменим значение classroom_id в таблице students, поскольку оно больше не является внешним ключом и не связано с таблицей classrooms, запрос выполнится успешно. Но если мы попробуем сделать то же самое с полем student_id в таблице grades, мы получим ошибку, потому что student_id – это внешний ключ.
UPDATE students SET classroom_id = 10 WHERE id = 1; /* UPDATE 1 Query returned successfully in 37 msec. */ UPDATE grades SET student_id = 10 WHERE id = 1; /* ERROR: insert or update on table "grades" violates foreign key constraint "fk_students" DETAIL: Key (student_id)=(10) is not present in table "students". SQL state: 23503 */ CASCADE. Если мы используем ON DELETE CASCADE при создании внешнего ключа в таблице students, а потом удаляем запись в classrooms, у нас также исчезнут связанные строки в таблице students. Такой способ может быть полезен при работе с личными данными пользователей, например мы хотим удалить все данные о клиенте или о сотруднике.
DROP TABLE IF EXISTS students CASCADE; CREATE TABLE students ( id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name VARCHAR(100), classroom_id INT, CONSTRAINT fk_classrooms FOREIGN KEY(classroom_id) REFERENCES classrooms(id) ON DELETE CASCADE ); INSERT INTO students (name, classroom_id) VALUES ('Adam', 1), ('Betty', 1), ('Caroline', 2); SELECT * FROM students; /* id | name | classroom_id -- | -------- | ------------ 1 | Adam | 1 2 | Betty | 1 3 | Caroline | 2 */ DELETE FROM classrooms WHERE id = 1; SELECT * FROM students; /* id | name | classroom_id -- | -------- | ------------ 3 | Caroline | 2 */ 2. Сохранность данных
3. Длина строк
учитель – это строка с максимальной длиной в 100 символов, так как не предполагаем, что будут значения длиннее. Но на самом ли деле мы экономим таким образом место на диске?MEMORY будут хранить строки одинаковой длины и дополнять их до максимального значения. Это означает, что, если вы указали тип поля VARCHAR(1000), то все данные в этой колонке будут дополняться до указанной длины, даже если строка состоит из 100 символов.4. Оператор If
if.
DO $$ BEGIN IF (SELECT COUNT(*) FROM grades) > (SELECT COUNT(*) FROM students) THEN RAISE NOTICE 'More grades than students.'; ELSE RAISE NOTICE 'Equal or more students than grades.'; END IF; END $$; /* NOTICE: More grades than students. */ 5. Индексы
grades:
CREATE INDEX score_index ON grades(score); EXPLAIN ANALYZE, мы увидим, что Postgres по-прежнему выполняет последовательное сканирование. Так происходит потому, что Postgres уже достаточно неплохо оптимизирован. Если количество записей в таблице невелико, быстрее выполнить последовательное сканирование, нежели использовать индексы. То есть Postgres сам выбрал наиболее быстрый путь.Материалы по теме
- 8 views
- 0 Comment