Самостоятельное объединение (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
*/
Да, безусловно этот запрос получился заметно больше, нежели написание двух оконных функций. Однако, такой подробный запрос имеет два главных преимущества: читабельность и масштабируемость.
Запросы могут быть чудовищно длинные. Например в компании Meta*, мне встречался запрос, содержащий в себе 1000 строк и вызывал сразу 25 таблиц. Этот запрос был бы совершенно нечитаемым без применения оператора WITH, который разграничивает отдельные участки кода и присваивает им псевдонимы.
Работая с большим объемом данных, мы не имеем таких роскошных возможностей как последовательное выполнение подзапросов, сохранение данных в CSV формат, затем объединение полученных данных и анализа их с помощью Python. Все взаимодействия с базой данных должны происходить за один раз.
Давайте разберем еще один вариант. Предположим, в нашей школе уволили директора, и система оценок претерпела изменения. У нас появился такой показатель как средневзвешенный балл, который вычисляется как оценка (grades) умноженная на коэффициент сложности (weight). Теперь для сдачи экзамена необходимо иметь: средневзвешенный балл по всем предметам не ниже 85% или этот же балл не ниже 70% по собственному проекту. Объединение данной логики в один блок CASE WHEN достаточно сложно, но, если мы разделим запрос с помощью WITH всё станет гораздо проще.
Давайте начнем с того, что выявим учеников, у которых средневзвешенный балл больше 85%.
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
*/
Прекрасно. Теперь узнаем у кого из учеников больше 70% по персональному проекту.
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?
И в итоге, как нам понять, что один запрос более эффективен, чем другой?
Postgres может нам об этом рассказать. Ключевое слово 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
*/
Например в результате выше можно увидеть, что Postgres последовательно сканирует (Seq Scan) таблицы grades и students, потому что они не индексированы. Иначе говоря, Postgres не знает что строка внизу таблицы имеет id меньше или больше чем строка наверху таблицы. В нашем случае, это не имеет особого значения ввиду размеров нашей базы данных, но если бы мы работали с миллионами строк данных, то нам определенно потребовалось бы найти и устранить такие узкие места в производительности.
Данная статья была обзором тех навыков в SQL, которые вам точно понадобятся сразу после получения базовых знаний. Мы начали с установки Postgres и pgAdmin, тем самым получив возможность экспериментировать с базой данных на своём компьютере.
Затем мы узнали про полезные синтаксические конструкции, которые позволили нам составлять более сложные запросы. Мы начали с фильтрации данных и узнали, чем отличается 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 символов, так как не предполагаем, что будут значения длиннее. Но на самом ли деле мы экономим таким образом место на диске?
С технической точки зрения, в Postgres не имеет значения, какую длину строки мы определили (10, 100 или 500 символов). Указание длины строки – это скорее хороший способ коммуникации между разработчиками, потому что таким образом вы указываете, что вы ожидаете получить в этом поле.
Но, например, в MySQL длина строк имеет значение: временные таблицы и таблицы MEMORY будут хранить строки одинаковой длины и дополнять их до максимального значения. Это означает, что, если вы указали тип поля VARCHAR(1000), то все данные в этой колонке будут дополняться до указанной длины, даже если строка состоит из 100 символов.
4. Оператор If
Если вам интересно, ниже пример того, как в Postgres выглядит синтаксис с оператором 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 сам выбрал наиболее быстрый путь.
Комментарии