EXCEPT в SQL: ищем уникальные значения
Оператор разности EXCEPT служит для сравнения двух наборов данных. Он извлекает уникальные значения из первого, отсутствующие во втором наборе.
EXCEPT — инструмент для фильтрации данных и поиска различий между таблицами. Он позволяет выполнять сравнение в одном или нескольких столбцах, в этом случае сравнение идёт кортежами.
Важно помнить, что при выполнении операции EXCEPT автоматически устраняются дубликаты строк. Если строка существует во втором наборе данных, она не появится в окончательном результате, даже если в первом наборе присутствует несколько раз.
Для использования оператора необходимо, чтобы в обоих наборах данных было указано одинаковое количество столбцов, а их порядок и типы данных должны быть сопоставимыми.
Синтаксис:
Если результирующий набор данных нужно отсортировать, то после второго набора данных указывается
Также EXCEPT можно заменить на JOIN (и чаще всего это может дать прирост производительности):
Однако использование EXCEPT делает запрос более читабельным. Что касается эффективности — её лучше тестировать (и по возможности смотреть планы запросов) на реальных данных с учётом индексов и прочих особенностей в конкретных СУБД.
Но не только EXCEPT и LEFT JOIN помогают выявлять уникальные значения в данных. В одном из следующих постов вспомним с вами о таких методах вычитания как NOT IN и NOT EXISTS.
#sql
Оператор разности EXCEPT служит для сравнения двух наборов данных. Он извлекает уникальные значения из первого, отсутствующие во втором наборе.
EXCEPT — инструмент для фильтрации данных и поиска различий между таблицами. Он позволяет выполнять сравнение в одном или нескольких столбцах, в этом случае сравнение идёт кортежами.
Важно помнить, что при выполнении операции EXCEPT автоматически устраняются дубликаты строк. Если строка существует во втором наборе данных, она не появится в окончательном результате, даже если в первом наборе присутствует несколько раз.
Для использования оператора необходимо, чтобы в обоих наборах данных было указано одинаковое количество столбцов, а их порядок и типы данных должны быть сопоставимыми.
Синтаксис:
select column_1
from table_name_1
[where condition]
except
select column_1
from table_name_2
[where condition]
order by column_1
Если результирующий набор данных нужно отсортировать, то после второго набора данных указывается
order by
.Также EXCEPT можно заменить на JOIN (и чаще всего это может дать прирост производительности):
select t1.column_1
from table_name_1 t1
left join table_name_2 t2
on t1.column_1 = t2.column_1
where t2.column_1 is NULL
order by t1.column_1
Однако использование EXCEPT делает запрос более читабельным. Что касается эффективности — её лучше тестировать (и по возможности смотреть планы запросов) на реальных данных с учётом индексов и прочих особенностей в конкретных СУБД.
Но не только EXCEPT и LEFT JOIN помогают выявлять уникальные значения в данных. В одном из следующих постов вспомним с вами о таких методах вычитания как NOT IN и NOT EXISTS.
#sql
❤1
Магия SQL Joins: как собрать нужные данные при соединений таблиц
JOIN — фундаментальная функция в SQL, которая позволяет объединять информацию из разных таблиц. С её помощью можно объединять строки из двух или более таблиц на основе связанного между ними столбца. Основными ключевыми словами для JOIN являются INNER JOIN, LEFT JOIN (или LEFT OUTER JOIN), RIGHT JOIN (или RIGHT OUTER JOIN) и FULL JOIN (или FULL OUTER JOIN).
Понимание принципов работы с JOIN поможет эффективно объединять данные и избежать множества ошибок.
Ниже приведу несколько примеров использования JOIN, а в следующий раз поговорим о типичных ошибках при использовании, об улучшении производительности запросов и других особенностях при объединении таблиц.
INNER JOIN
Представим, что у нас есть база данных университета. С помощью INNER JOIN можно легко объединить студентов и занятия, на которые они записаны.
LEFT JOIN
Теперь перейдём к электронной коммерции. Воспользуемся LEFT JOIN (INCLUSIVE), чтобы получить список всех клиентов, включая тех, кто еще не совершил покупку.
Если же нам нужно найти только тех покупателей, которые не совершали заказ, то вносим небольшие правки в EXCLUSIVE-запрос:
RIGHT JOIN
Теперь представим, что перед нами база библиотеки. Воспользуемся RIGHT JOIN (INCLUSIVE), чтобы получить список всех книг, даже тех, которые не выданы ни одному читателю.
Если же нужно найти только книги, которые выданы читателям, добавляем EXCLUSIVE условие:
FULL JOIN
Перейдём к управлению проектами. С помощью FULL JOIN (INCLUSIVE) выведем всех сотрудников и все проекты, независимо есть ли между ними связь.
Если же нам нужно вывести всех сотрудников без проектов и все проекты, без связанных сотрудников, модифицируем запрос до EXCLUSIVE:
Хочу отметить, что прежде чем приступать к использованию JOIN, необходимо хорошо понимать модель базы данных и саму цель запроса. Примеры выше подчеркивают универсальность SQL JOIN.От правильного выбора зависит вся работа нашего запроса и запутаться очень легко. В первое время в качестве помощи можно использовать шпаргалку, до тех пор пока знания не дойдут до автоматизма.
#sql #join
JOIN — фундаментальная функция в SQL, которая позволяет объединять информацию из разных таблиц. С её помощью можно объединять строки из двух или более таблиц на основе связанного между ними столбца. Основными ключевыми словами для JOIN являются INNER JOIN, LEFT JOIN (или LEFT OUTER JOIN), RIGHT JOIN (или RIGHT OUTER JOIN) и FULL JOIN (или FULL OUTER JOIN).
Понимание принципов работы с JOIN поможет эффективно объединять данные и избежать множества ошибок.
Ниже приведу несколько примеров использования JOIN, а в следующий раз поговорим о типичных ошибках при использовании, об улучшении производительности запросов и других особенностях при объединении таблиц.
INNER JOIN
Представим, что у нас есть база данных университета. С помощью INNER JOIN можно легко объединить студентов и занятия, на которые они записаны.
SELECT
students.student_id,
students.student_name,
students.grade,
courses.course_name
FROM students
INNER JOIN
courses ON students.student_id = courses.student_id;
LEFT JOIN
Теперь перейдём к электронной коммерции. Воспользуемся LEFT JOIN (INCLUSIVE), чтобы получить список всех клиентов, включая тех, кто еще не совершил покупку.
SELECT
customers.customer_id,
orders.order_id
FROM customers
LEFT JOIN
orders ON customers.customer_id = orders.customer_id;
Если же нам нужно найти только тех покупателей, которые не совершали заказ, то вносим небольшие правки в EXCLUSIVE-запрос:
SELECT
customers.customer_id,
orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.customer_id IS NULL;
RIGHT JOIN
Теперь представим, что перед нами база библиотеки. Воспользуемся RIGHT JOIN (INCLUSIVE), чтобы получить список всех книг, даже тех, которые не выданы ни одному читателю.
SELECT
books.book_id,
borrowers.borrower_name,
books.title,
books.author,
books.genre
FROM
books
RIGHT JOIN
borrowers ON books.borrower_id = borrowers.borrower_id;
Если же нужно найти только книги, которые выданы читателям, добавляем EXCLUSIVE условие:
SELECT
books.book_id,
borrowers.borrower_name,
books.title,
books.author,
books.genre
FROM
books
RIGHT JOIN
borrowers ON books.borrower_id = borrowers.borrower_id
WHERE books.borrower_id IS NULL;
FULL JOIN
Перейдём к управлению проектами. С помощью FULL JOIN (INCLUSIVE) выведем всех сотрудников и все проекты, независимо есть ли между ними связь.
SELECT
e.employee_id,
e.employee_name,
pr.project_id,
pr.project_name
FROM
employees e
FULL JOIN
projects pr ON e.employee_id = pr.employee_id;
Если же нам нужно вывести всех сотрудников без проектов и все проекты, без связанных сотрудников, модифицируем запрос до EXCLUSIVE:
SELECT
e.employee_id,
e.employee_name,
pr.project_id,
pr.project_name
FROM
employees e
FULL JOIN
projects pr ON e.employee_id = pr.employee_id
WHERE
pr.employee_id IS NULL OR pr.project_id IS NULL;
Хочу отметить, что прежде чем приступать к использованию JOIN, необходимо хорошо понимать модель базы данных и саму цель запроса. Примеры выше подчеркивают универсальность SQL JOIN.От правильного выбора зависит вся работа нашего запроса и запутаться очень легко. В первое время в качестве помощи можно использовать шпаргалку, до тех пор пока знания не дойдут до автоматизма.
#sql #join
❤1
Разгадка тайн соединения NULL-значений в SQL
Продолжим серию статей про особенности работы с #null🙂 да, они ещё не закончились.
Одна из часто встречающихся проблем — непонимание как происходит JOIN таблиц с NULL-значениями. Давайте посмотрим на примерах.
Допустим у нас есть две таблицы:
user_names
user_roles
INNER JOIN
Строки, для которых нет совпадения в обеих таблицах, исключаются из результирующего набора. В том числе, если в столбце, по которому происходит соединение, встречаются значения с NULL, эти строки будут исключены из результата, так как два NULL нельзя сравнить между собой.
Результат:
LEFT JOIN
Возвращает все записи из левой таблицы (users_name) и соответствующие записи из правой таблицы (users_role).
Результат:
Здесь стоит отметить, что появившиеся Null в полях r.ud_id — не являются Null-значениями из таблицы users_role.
FULL JOIN
Возвращает все записи, включая совпадения в левой или правой таблице. Строки в любой из таблиц будут содержать NULL значения в столбцах из другой таблицы, в случае отсутствия совпадения по ключу.
Результат:
Обратим внимание, что строки с NULL NULL повторяются дважды, так как они не равны друг другу и их нельзя объединить.
#sql #null
Продолжим серию статей про особенности работы с #null
Одна из часто встречающихся проблем — непонимание как происходит JOIN таблиц с NULL-значениями. Давайте посмотрим на примерах.
Допустим у нас есть две таблицы:
user_names
us_id name
1 Илья
2 Ольга
3 Null
Null Null
user_roles
us_id role
1 admin
2 user
3 user
Null guest
Null Null
INNER JOIN
Строки, для которых нет совпадения в обеих таблицах, исключаются из результирующего набора. В том числе, если в столбце, по которому происходит соединение, встречаются значения с NULL, эти строки будут исключены из результата, так как два NULL нельзя сравнить между собой.
SELECT u.us_id, r.us_id, name, role
FROM user_names u
INNER JOIN user_roles r ON u.us_id = r.us_id;
Результат:
u.us_id r.ud_id name role
1 1 Илья admin
2 2 Ольга user
3 3 Null user
LEFT JOIN
Возвращает все записи из левой таблицы (users_name) и соответствующие записи из правой таблицы (users_role).
SELECT u.us_id, r.us_id, name, role
FROM user_names u
LEFT JOIN user_roles r ON u.us_id = r.us_id;
Результат:
u.us_id r.ud_id name role
1 1 Илья admin
2 2 Ольга user
3 3 Null user
Null Null Null Null
Здесь стоит отметить, что появившиеся Null в полях r.ud_id — не являются Null-значениями из таблицы users_role.
FULL JOIN
Возвращает все записи, включая совпадения в левой или правой таблице. Строки в любой из таблиц будут содержать NULL значения в столбцах из другой таблицы, в случае отсутствия совпадения по ключу.
SELECT u.us_id, r.us_id, name, role
FROM user_names u
FULL JOIN user_roles r ON u.us_id = r.us_id;
Результат:
u.us_id r.ud_id name role
1 1 Илья admin
2 2 Ольга user
3 3 Null user
Null Null Null Null
Null Null Null guest
Null Null Null Null
Обратим внимание, что строки с NULL NULL повторяются дважды, так как они не равны друг другу и их нельзя объединить.
#sql #null
Please open Telegram to view this post
VIEW IN TELEGRAM
❤1
Explicit is better than implicit: не используйте SELECT *
Один из моих любимых принципов в The Zen of Python (PEP-20) — это "Explicit is better than implicit" (явное — лучше неявного). Он актуален не только для питона, но и для любого кода, документации и в целом жизни. Сегодня я хочу поговорить о примере использования этого принципа.
Часто для сокращения sql-кода используется конструкция
Почему так делать не нужно?
1. Структура данных: ничто не вечно
Даже если кажется, что исходная таблица никогда не изменится, через Х времени это произойдёт. Столбцы могут быть добавлены или удалены, и отлаженный процесс будет сломан. Но из-за отсутствия явного указания, найти и исправить ошибку будет не так просто.
2. Снижение производительности
3. Сложность в понимании логики запроса
Используя неявное указание, мы усложняем дальнейшую поддержку запроса другими людьми. Явное перечисление — это самодокументирующийся код с четким пониманием смыслов.
4. Непреднамеренное раскрытие данных
Для четкой понимании логики запроса, оптимизации запросов и упрощения рефакторинга всегда указывайте столбцы явно (разве что речь не идёт об ad-hoc запросах).
#sql
Один из моих любимых принципов в The Zen of Python (PEP-20) — это "Explicit is better than implicit" (явное — лучше неявного). Он актуален не только для питона, но и для любого кода, документации и в целом жизни. Сегодня я хочу поговорить о примере использования этого принципа.
Часто для сокращения sql-кода используется конструкция
SELECT *
, которая выгружает информацию сразу из всех колонок. Согласитесь, если у вас таблица с 200 столбцами, то вместо того, чтобы писать колбасу из перечислений, куда проще просто поставить *. Ведь мы на 100% уверены, что все столбцы нужны.Почему так делать не нужно?
1. Структура данных: ничто не вечно
Даже если кажется, что исходная таблица никогда не изменится, через Х времени это произойдёт. Столбцы могут быть добавлены или удалены, и отлаженный процесс будет сломан. Но из-за отсутствия явного указания, найти и исправить ошибку будет не так просто.
2. Снижение производительности
SELECT *
в запросах извлекает все столбцы из указанных объектов, включая те, которые не требуются. Это может значительно увеличить объем получаемых данных и снизить скорость выполнения запроса и его производительность (это особенно актуально для колоночного хранения).3. Сложность в понимании логики запроса
Используя неявное указание, мы усложняем дальнейшую поддержку запроса другими людьми. Явное перечисление — это самодокументирующийся код с четким пониманием смыслов.
4. Непреднамеренное раскрытие данных
SELECT *
может случайно раскрыть конфиденциальную информацию, которая не предназначалась для текущего контекста или анализа. К примеру, это может случиться после того как в исходную таблицу будут добавлены новые столбцы о которых заранее никто не предполагал. Для четкой понимании логики запроса, оптимизации запросов и упрощения рефакторинга всегда указывайте столбцы явно (разве что речь не идёт об ad-hoc запросах).
#sql
🔥2❤1
Группировка NULL-значений
Мы уже рассмотрели множество особенностей NULL, но это ещё не всё.
На очереди группировка данных, включающих NULL-значения.
Как думаете, будут ли NULL сгруппированы или каждый будет считаться самостоятельной единицей?
В этот раз, если вы читали мои предыдущие статьи, ответ может вас удивить. При использовании GROUP BY, строки с NULL в группирующем столбце объединяются в одну группу. Казалось бы NULL обозначает "неизвестное" значение и как мы можем его группировать? Но факт остаётся фактом, при агрегации все NULL считаются равными между собой и формируют единую группу.
Чтобы сделать это поведение более очевидным при аналитике, мы можем использовать функции COALESCE или CASE, чтобы заменить NULL на значение, которое ясно указывает на отсутствие данных, например, на 'Неизвестно'.
#sql #null
Мы уже рассмотрели множество особенностей NULL, но это ещё не всё.
На очереди группировка данных, включающих NULL-значения.
Как думаете, будут ли NULL сгруппированы или каждый будет считаться самостоятельной единицей?
В этот раз, если вы читали мои предыдущие статьи, ответ может вас удивить. При использовании GROUP BY, строки с NULL в группирующем столбце объединяются в одну группу. Казалось бы NULL обозначает "неизвестное" значение и как мы можем его группировать? Но факт остаётся фактом, при агрегации все NULL считаются равными между собой и формируют единую группу.
Чтобы сделать это поведение более очевидным при аналитике, мы можем использовать функции COALESCE или CASE, чтобы заменить NULL на значение, которое ясно указывает на отсутствие данных, например, на 'Неизвестно'.
#sql #null
❤1
Используете USING? Тогда мы идём к вам
Ранее я писала про то, почему не стоит использовать
Например, вместо:
Можно написать:
В этом случае
Почему так делать не нужно?
1. USING хоть и добавляет компактности, но убирает ясность в коде (это особенно заметно при множественном объединении таблиц), а следовательно снижает его поддерживаемость.
2. При использовании USING, столбец, по которому происходит объединение, возвращается в результирующем наборе только один раз. Какой из нужных? Звучит неоднозначно, особенно при использовании не только INNER JOIN. Это может создать сложности при необходимости разделения значений из каждой таблицы в дальнейшем анализе.
3. Изменения неизбежны в любой базе данных. Столбцы могут быть переименованы, добавлены или удалены. И при явном указании столбцов объединения, мы легко найдём ошибку. В случае же использования USING, могут возникнуть ситуации, когда будет переименовано несколько столбцов в разных таблицах и логика объединения нарушится, превратив результаты запроса в мусор.
4. Использование USING иногда может ограничить возможности оптимизатора выбирать наиболее эффективный план выполнения.
5. Особенности отдельны СУБД. Если JOIN ON работает предсказуемо, то использование USING может привнести сюрпризы.
Например, в Snowflake при определённых условиях множественные USING по одинаковым столбцам могут вообще игнорироваться. Совет: всегда изучайте документацию.
USING — это синтаксический сахар и его использование далеко не всегда оправдано.
Как я уже говорила ранее, помните, что явное всегда лучше неявного. Точные условия объединения с использованием ON делают зависимости между таблицами понятными, тем самым повышая стабильность, эффективность и надежность запросов, улучшая их масштабируемость.
#sql
Ранее я писала про то, почему не стоит использовать
SELECT *
, а сегодня хочу поговорить об USING
.USING
— ключевое слово, которое используется в SQL-запросах для упрощения синтаксиса объединения таблиц по одноимённым столбцам.Например, вместо:
SELECT o.order_id, o.amount, c.customer_surname, c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
Можно написать:
SELECT o.order_id, o.amount, c.customer_surname, c.customer_name
FROM orders o
JOIN customers c
USING (customer_id)
В этом случае
USING
объединяет таблицы по столбцам с одинаковыми именами, указанными в условии.Почему так делать не нужно?
1. USING хоть и добавляет компактности, но убирает ясность в коде (это особенно заметно при множественном объединении таблиц), а следовательно снижает его поддерживаемость.
2. При использовании USING, столбец, по которому происходит объединение, возвращается в результирующем наборе только один раз. Какой из нужных? Звучит неоднозначно, особенно при использовании не только INNER JOIN. Это может создать сложности при необходимости разделения значений из каждой таблицы в дальнейшем анализе.
3. Изменения неизбежны в любой базе данных. Столбцы могут быть переименованы, добавлены или удалены. И при явном указании столбцов объединения, мы легко найдём ошибку. В случае же использования USING, могут возникнуть ситуации, когда будет переименовано несколько столбцов в разных таблицах и логика объединения нарушится, превратив результаты запроса в мусор.
4. Использование USING иногда может ограничить возможности оптимизатора выбирать наиболее эффективный план выполнения.
5. Особенности отдельны СУБД. Если JOIN ON работает предсказуемо, то использование USING может привнести сюрпризы.
Например, в Snowflake при определённых условиях множественные USING по одинаковым столбцам могут вообще игнорироваться. Совет: всегда изучайте документацию.
USING — это синтаксический сахар и его использование далеко не всегда оправдано.
Как я уже говорила ранее, помните, что явное всегда лучше неявного. Точные условия объединения с использованием ON делают зависимости между таблицами понятными, тем самым повышая стабильность, эффективность и надежность запросов, улучшая их масштабируемость.
#sql
❤1
Немного об оконных функциях
Рассказывать о простом SQL можно много и долго, но давайте перейдем к более глубоким темам. Например, поговорим об оконных функциях. И перед тем как перейти к деталям, выясним, что же такое окошки?
Это инструмент, позволяющий углубить анализ и обработку данных. Если в обычном запросе мы работаем со всеми данными или с определенной группой, то оконные функции позволяют проводить вычисления, не прибегая к фактическому схлопыванию данных. То есть они позволяют сохранить информацию о каждой строке.
Оконные функции обрабатывают данные в рамках окна, которое задается с помощью параметров:
1. Разделение на группы —
2. Упорядочивание данных внутри каждой группы —
3. Определение диапазона строк для группы, над которыми будет производиться вычисление (
Ниже показан расчет средней зарплаты по отделам. Каждая строка результата будет содержать данные о сотруднике, его отделе, зарплате и средней зарплате по его отделу.
Оконные функции предоставляют возможности для решения большого количества различных аналитических задач, например таких как:
— Анализ трендов и изменений
— Вычисление всевозможных рейтингов (ранжирование)
— Расчет скользящего среднего
— Сравнение со смещением
— Всевозможные агрегации
— Расчет кумулятивных сумм
— Определение первого/последнего значения в группе
— ...
Окошки незаменимы для аналитиков данных, и крайне важно понимать принцип их работы. А если вы умеете использовать не только стандартные агрегаты и ранжирование, а также владеете фреймами, то это в разы повысит вашу ценность как специалиста.
Следите за каналом) в следующих статьях рассмотрим применение конкретных оконных функций под различные задачи.
#sql #оконные_функции
Рассказывать о простом SQL можно много и долго, но давайте перейдем к более глубоким темам. Например, поговорим об оконных функциях. И перед тем как перейти к деталям, выясним, что же такое окошки?
Это инструмент, позволяющий углубить анализ и обработку данных. Если в обычном запросе мы работаем со всеми данными или с определенной группой, то оконные функции позволяют проводить вычисления, не прибегая к фактическому схлопыванию данных. То есть они позволяют сохранить информацию о каждой строке.
Окно — набор строк, который указанным образом связан с текущей строкой.
Оконные функции обрабатывают данные в рамках окна, которое задается с помощью параметров:
1. Разделение на группы —
PARTITION BY
.2. Упорядочивание данных внутри каждой группы —
ORDER BY
.3. Определение диапазона строк для группы, над которыми будет производиться вычисление (
ROWS BETWEEN
) — поддерживается не всеми оконками.Ниже показан расчет средней зарплаты по отделам. Каждая строка результата будет содержать данные о сотруднике, его отделе, зарплате и средней зарплате по его отделу.
SELECT
employee_id,
department,
salary,
AVG(salary) OVER(PARTITION BY department) as department_salary_avg
FROM employees;
Оконные функции предоставляют возможности для решения большого количества различных аналитических задач, например таких как:
— Анализ трендов и изменений
— Вычисление всевозможных рейтингов (ранжирование)
— Расчет скользящего среднего
— Сравнение со смещением
— Всевозможные агрегации
— Расчет кумулятивных сумм
— Определение первого/последнего значения в группе
— ...
Окошки незаменимы для аналитиков данных, и крайне важно понимать принцип их работы. А если вы умеете использовать не только стандартные агрегаты и ранжирование, а также владеете фреймами, то это в разы повысит вашу ценность как специалиста.
Следите за каналом) в следующих статьях рассмотрим применение конкретных оконных функций под различные задачи.
#sql #оконные_функции
❤1❤🔥1
Противостояние CTE и подзапросов
В области анализа данных есть два классных инструмента — общие табличные выражения (CTE) и подзапросы, играющие важную роль в структурировании и обработке информации. Их правильное применение может значительно повысить эффективность ваших запросов.
CTE — это временные именованные наборы результатов, к которым можно обращаться внутри операторов SELECT, INSERT, UPDATE или DELETE. Они обеспечивают структурированный подход и делают код более читаемым и легким для анализа.
Плюсы:
— Повышают читаемость и структурированность кода.
— Их можно многократно использовать в одном запросе, сокращая повторения и облегчая отладку.
— Незаменимы для рекурсивных запросов и работы с иерархическими данными.
Минусы:
— Могут быть менее эффективны при неоптимальном использовании, особенно в больших базах данных из-за временного хранения результатов.
Пример:
В этом примере в CTE category_sales вычисляется общее количество продаж товаров для каждой категории. Затем основной запрос использует эту информацию для вывода количества продаж по каждому продукту вместе с общим количеством продаж по его категории. Таким образом, мы можем увидеть как общие продажи по категориям, так и детальные продажи по каждому продукту. Это облегчает анализ эффективности продаж в разрезе категорий. Этот пример также можно реализовать с помощью оконных функций, но об этом в другой раз.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.
Подзапросы – это запросы, вложенные в другой запрос. Они предоставляют набор результатов для использования в основном запросе. Звучит сложно? На самом деле всё просто, сначала делается выборка по подзапросу, а результаты этой выборки используются для дальнейших вычислений.
Плюсы:
— Простота использования и понимания.
— Идеально подходят для извлечения данных в рамках ограниченного контекста.
Минусы:
— Ограниченные возможности повторного использования и рекурсии по сравнению с CTE.
— Могут привести к ухудшению читаемости кода при сложных вложениях.
Пример:
В этом примере подзапрос в WHERE-части вычисляет среднюю цену по категориям. Затем основной запрос сравнивает цену товара и выводит только ту, чья цена выше средней.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.
В боевых решениях я рекомендую использовать CTE, так как это упрощает чтение и отладку кода. Но в общем случае ориентироваться нужно в первую очередь, конечно же, на саму задачу.
#sql
В области анализа данных есть два классных инструмента — общие табличные выражения (CTE) и подзапросы, играющие важную роль в структурировании и обработке информации. Их правильное применение может значительно повысить эффективность ваших запросов.
CTE — это временные именованные наборы результатов, к которым можно обращаться внутри операторов SELECT, INSERT, UPDATE или DELETE. Они обеспечивают структурированный подход и делают код более читаемым и легким для анализа.
Плюсы:
— Повышают читаемость и структурированность кода.
— Их можно многократно использовать в одном запросе, сокращая повторения и облегчая отладку.
— Незаменимы для рекурсивных запросов и работы с иерархическими данными.
Минусы:
— Могут быть менее эффективны при неоптимальном использовании, особенно в больших базах данных из-за временного хранения результатов.
Пример:
WITH category_sales AS (
SELECT p.category,
SUM(s.quantity) AS total_quantity
FROM sales s
JOIN products p
ON s.product_id = p.product_id
GROUP BY p.category
)
SELECT p.product_name,
p.category,
SUM(s.quantity) AS product_quantity,
cs.total_quantity AS category_total_quantity
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN category_sales cs ON p.category = cs.category
GROUP BY p.product_name, p.category, cs.total_quantity;
В этом примере в CTE category_sales вычисляется общее количество продаж товаров для каждой категории. Затем основной запрос использует эту информацию для вывода количества продаж по каждому продукту вместе с общим количеством продаж по его категории. Таким образом, мы можем увидеть как общие продажи по категориям, так и детальные продажи по каждому продукту. Это облегчает анализ эффективности продаж в разрезе категорий. Этот пример также можно реализовать с помощью оконных функций, но об этом в другой раз.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.
Подзапросы – это запросы, вложенные в другой запрос. Они предоставляют набор результатов для использования в основном запросе. Звучит сложно? На самом деле всё просто, сначала делается выборка по подзапросу, а результаты этой выборки используются для дальнейших вычислений.
Плюсы:
— Простота использования и понимания.
— Идеально подходят для извлечения данных в рамках ограниченного контекста.
Минусы:
— Ограниченные возможности повторного использования и рекурсии по сравнению с CTE.
— Могут привести к ухудшению читаемости кода при сложных вложениях.
Пример:
SELECT p.product_name, p.price
FROM products p
WHERE p.price > (
SELECT AVG(price) -- Вычисление средней цены по категории
FROM products
WHERE category = p.category
);
В этом примере подзапрос в WHERE-части вычисляет среднюю цену по категориям. Затем основной запрос сравнивает цену товара и выводит только ту, чья цена выше средней.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.
В боевых решениях я рекомендую использовать CTE, так как это упрощает чтение и отладку кода. Но в общем случае ориентироваться нужно в первую очередь, конечно же, на саму задачу.
#sql
Sqliteonline
SQL Online AiDE - Next gen SQL Editor
SQL Online AiDE: Effortless SQL development. AI error analysis, WebRTC collaboration for SQLite, supports MySQL, Postgres & MS SQL. 10+ years of superior SLA, Performance & UX
Как lag и lead помогают заглянуть в прошлое и будущее ваших данных
Привет!
Сегодня хочу рассказать про оконные функции lag и lead в SQL. Если кратко: они помогают сравнивать значения между строками.
Например, у вас есть таблица с продажами по дням и вы хотите сравнить сегодняшние продажи со вчерашними. Или увидеть сколько продаж было на следующий день. Здесь и пригодятся функции lag и lead.
Lag смотрит назад, то есть в своём запросе вы можете увидеть то, что было в предыдущей строке. Lead, наоборот, смотрит вперед. С ним вы узнаете, что будет в следующей строке.
Посмотрим на примерах:
Этот запрос покажет вам продажи за текущий день, вчерашний день и следующий день. Всё в одной витрине, красиво и удобно.
Также можно использовать эти функции для расчета разницы между значениями:
Так можно увидеть насколько выросли или упали продажи по сравнению с предыдущим днем.
Что круто, lag и lead можно применять не только к соседним строкам, а можно заглянуть на несколько дней назад или вперед:
Этот запрос покажет продажи за сегодня и за неделю до этого.
Кроме того, lag и lead часто используют для заполнения пропусков в данных. Если у вас нет данных за какой-то день, вы можете взять значение из предыдущего дня (обратите внимание, что пример ниже не бизнесовый, а только для отражения возможностей использования функции):
Теперь вы знаете, как использовать lag и lead. Эти функции помогут лучше понимать ваши данные и находить интересные закономерности.
#sql #оконные_функции
Привет!
Сегодня хочу рассказать про оконные функции lag и lead в SQL. Если кратко: они помогают сравнивать значения между строками.
Например, у вас есть таблица с продажами по дням и вы хотите сравнить сегодняшние продажи со вчерашними. Или увидеть сколько продаж было на следующий день. Здесь и пригодятся функции lag и lead.
Lag смотрит назад, то есть в своём запросе вы можете увидеть то, что было в предыдущей строке. Lead, наоборот, смотрит вперед. С ним вы узнаете, что будет в следующей строке.
Посмотрим на примерах:
SELECT
sales_dt,
sales_amount,
LAG(sales_amount) OVER (ORDER BY sales_dt) AS sales_previous_day,
LEAD(sales_amount) OVER (ORDER BY sales_dt) AS sales_next_day
FROM sales_daily;
Этот запрос покажет вам продажи за текущий день, вчерашний день и следующий день. Всё в одной витрине, красиво и удобно.
Также можно использовать эти функции для расчета разницы между значениями:
SELECT
sales_dt,
sales_amount,
sales_amount - LAG(sales) OVER (ORDER BY sales_dt) AS sales_change_daily
FROM sales_daily;
Так можно увидеть насколько выросли или упали продажи по сравнению с предыдущим днем.
Что круто, lag и lead можно применять не только к соседним строкам, а можно заглянуть на несколько дней назад или вперед:
SELECT
sales_dt,
sales_amount,
LAG(sales_amount, 7) OVER (ORDER BY sales_dt) AS sales_week_ago
FROM sales_daily;
Этот запрос покажет продажи за сегодня и за неделю до этого.
Кроме того, lag и lead часто используют для заполнения пропусков в данных. Если у вас нет данных за какой-то день, вы можете взять значение из предыдущего дня (обратите внимание, что пример ниже не бизнесовый, а только для отражения возможностей использования функции):
SELECT
sales_dt,
COALESCE(sales_amount, LAG(sales) OVER (ORDER BY sales_dt)) AS sales_filled
FROM sales_daily;
Теперь вы знаете, как использовать lag и lead. Эти функции помогут лучше понимать ваши данные и находить интересные закономерности.
#sql #оконные_функции
❤1✍1
Ранжирующие функции в SQL: как создавать рейтинги и топы
Привет! Сегодня поговорим о ранжирующих оконных функциях в SQL. С ними вы легко сможете находить лучшие продукты, оценивать эффективность сотрудников или составлять списки топовых клиентов.
Ранжирующие функции — это особый вид оконок. Они присваивают каждой строке таблицы номер (ранг) в рамках группы данных, определенной оператором OVER(). Этот номер может быть уникальным или учитывать равенство значений в строках.
В SQL есть три основные ранжирующие функции:
- ROW_NUMBER() или простая нумерация — присваивает уникальный номер каждой строке. Даже если значения в строках одинаковы, номера будут различаться.
- RANK() или ранжирование с пропусками — присваивает одинаковый ранг строкам с одинаковыми значениями. Следующая строка получает номер с пропуском на количество одинаковых значений (т.е., например, 1 1 1 4). Можно использовать, когда важно показать, сколько объектов находится выше по рейтингу.
- DENSE_RANK() или ранжирование без пропусков — похожа на RANK(), но не пропускает номера. Если несколько строк имеют одинаковый ранг, следующая строка получит номер, идущий непосредственно за ними (1 1 1 2). Пригодится для создания категорий или групп на основе значений.
Пример ранжирования с пропусками:
Результат:
Если нужна нумерация внутри групп, необходимо скомбинировать ранжирующие функции с
Функция присваивает ранг каждой строке в пределах группы (категории). Если две строки имеют одинаковое значение sales_amount, они получат одинаковый ранг, а следующая строка пропустит номер и возьмёт следующий. Не понятно?) Посмотрим на примере вывода:
Ранжирующие функции полезны, если нужно создавать рейтинги или анализировать данные с учетом их позиции в наборе. Например, если нужно найти первую строчку в группе, определить топ-продавцов, сравнить позиции или ранжировать сотрудников по их результатам. Эти функции помогают решать задачи быстрее и проще, чем с использованием сложных подзапросов.
В следующих статьях мы разберем каждую функцию подробнее и посмотрим на более сложные примеры их применения. А пока попробуйте применить их к своим данным😉
#sql #оконные_функции
Привет! Сегодня поговорим о ранжирующих оконных функциях в SQL. С ними вы легко сможете находить лучшие продукты, оценивать эффективность сотрудников или составлять списки топовых клиентов.
Ранжирующие функции — это особый вид оконок. Они присваивают каждой строке таблицы номер (ранг) в рамках группы данных, определенной оператором OVER(). Этот номер может быть уникальным или учитывать равенство значений в строках.
В SQL есть три основные ранжирующие функции:
- ROW_NUMBER() или простая нумерация — присваивает уникальный номер каждой строке. Даже если значения в строках одинаковы, номера будут различаться.
- RANK() или ранжирование с пропусками — присваивает одинаковый ранг строкам с одинаковыми значениями. Следующая строка получает номер с пропуском на количество одинаковых значений (т.е., например, 1 1 1 4). Можно использовать, когда важно показать, сколько объектов находится выше по рейтингу.
- DENSE_RANK() или ранжирование без пропусков — похожа на RANK(), но не пропускает номера. Если несколько строк имеют одинаковый ранг, следующая строка получит номер, идущий непосредственно за ними (1 1 1 2). Пригодится для создания категорий или групп на основе значений.
Пример ранжирования с пропусками:
SELECT
product_name,
sales_amount,
DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM product_sales;
Результат:
| product_name | sales_amount | sales_rank |
|--------------|--------------|------------|
| iPhone | 100000 | 1 |
| MacBook | 100000 | 1 |
| AirPods | 80000 | 2 |
| iPad | 60000 | 3 |
Если нужна нумерация внутри групп, необходимо скомбинировать ранжирующие функции с
PARTITION BY
. Например, разобъём данные на группы по категориям:
SELECT
category,
product_name,
sales_amount,
RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS category_rank
FROM product_sales;
Функция присваивает ранг каждой строке в пределах группы (категории). Если две строки имеют одинаковое значение sales_amount, они получат одинаковый ранг, а следующая строка пропустит номер и возьмёт следующий. Не понятно?) Посмотрим на примере вывода:
| category | product_name | sales_amount | category_rank |
|----------|----------------|--------------|---------------|
| Phones | iPhone 13 | 150000 | 1 |
| Phones | Galaxy S21 | 130000 | 2 |
| Phones | Pixel 6 | 130000 | 2 |
| Phones | OnePlus 9 | 90000 | 4 |
| Laptops | MacBook Pro | 200000 | 1 |
| Laptops | Dell XPS | 180000 | 2 |
| Laptops | ThinkPad X1 | 150000 | 3 |
| Laptops | MateBook 14 | 150000 | 3 |
Ранжирующие функции полезны, если нужно создавать рейтинги или анализировать данные с учетом их позиции в наборе. Например, если нужно найти первую строчку в группе, определить топ-продавцов, сравнить позиции или ранжировать сотрудников по их результатам. Эти функции помогают решать задачи быстрее и проще, чем с использованием сложных подзапросов.
В следующих статьях мы разберем каждую функцию подробнее и посмотрим на более сложные примеры их применения. А пока попробуйте применить их к своим данным
#sql #оконные_функции
Please open Telegram to view this post
VIEW IN TELEGRAM
👨💻2❤1✍1 1
ANY_VALUE: функция для упрощения GROUP BY запросов
Привет! Сегодня расскажу про функцию ANY_VALUE в SQL. Она помогает упростить GROUP BY запросы, особенно когда вы работаете с большими наборами данных.
Если вы работали с агрегатными функциями и группировками GROUP BY, то, вероятно, сталкивались с ограничениями при выборе столбцов.
Представьте, у вас есть не очень нормализированная витрина с заказами (всё также рекомендую смотреть таблички в десктоп версии или развернуть телефон горизонтально🥲 ):
И перед вами стоит задача получить общую сумму заказов для каждого клиента:
Но что если мы захотим добавить в результат customer name (cust_nm)? Получим ошибку, потому что cust_nm не входит в GROUP BY и не используется в агрегатной функции. Вот здесь и приходит на помощь ANY_VALUE:
Этот запрос выполнится без ошибок. ANY_VALUE говорит базе данных: "Возьми любое значение cust_nm для каждой группы cust_id".
Важно понимать, что ANY_VALUE не гарантирует, какое именно значение будет выбрано. Оно может меняться от запуска к запуску. Поэтому используйте эту функцию, только когда вам не важно, какое именно значение будет возвращено, или если вы уверены, что внутри группы значения одинаковы.
ANY_VALUE помогает оптимизировать запросы. В некоторых СУБД она дает понять оптимизатору, что порядок выбора значений не важен, что может привести к более эффективному плану выполнения, чем при использовании min-max на группе.
Однако, не все СУБД поддерживают ANY_VALUE. В PostgreSQL, например, как раз таки придётся использовать min или max:
ANY_VALUE — полезная функция для упрощения агрегатных запросов, когда точное значение не имеет значения. Главное — использовать его осознанно и понимать, когда его применение оправдано.
#sql
Привет! Сегодня расскажу про функцию ANY_VALUE в SQL. Она помогает упростить GROUP BY запросы, особенно когда вы работаете с большими наборами данных.
Если вы работали с агрегатными функциями и группировками GROUP BY, то, вероятно, сталкивались с ограничениями при выборе столбцов.
Представьте, у вас есть не очень нормализированная витрина с заказами (всё также рекомендую смотреть таблички в десктоп версии или развернуть телефон горизонтально
| ord_id | cust_id | cust_nm | product | qty | price |
|--------|---------|---------|------------|-----|-------|
| 101 | 1 | Иван | Ноутбук | 2 | 1500 |
| 102 | 2 | Ольга | Смартфон | 1 | 800 |
| 103 | 1 | Иван | Планшет | 1 | 600 |
| 104 | 3 | Анна | Наушники | 3 | 150 |
| 105 | 2 | Ольга | Умные часы | 2 | 400 |
И перед вами стоит задача получить общую сумму заказов для каждого клиента:
SELECT cust_id, SUM(qty * price) as total_amount
FROM orders
GROUP BY cust_id
Но что если мы захотим добавить в результат customer name (cust_nm)? Получим ошибку, потому что cust_nm не входит в GROUP BY и не используется в агрегатной функции. Вот здесь и приходит на помощь ANY_VALUE:
SELECT
cust_id,
ANY_VALUE(cust_nm) as customer_name,
SUM(qty * price) as total_amount
FROM orders
GROUP BY cust_id
Этот запрос выполнится без ошибок. ANY_VALUE говорит базе данных: "Возьми любое значение cust_nm для каждой группы cust_id".
Важно понимать, что ANY_VALUE не гарантирует, какое именно значение будет выбрано. Оно может меняться от запуска к запуску. Поэтому используйте эту функцию, только когда вам не важно, какое именно значение будет возвращено, или если вы уверены, что внутри группы значения одинаковы.
ANY_VALUE помогает оптимизировать запросы. В некоторых СУБД она дает понять оптимизатору, что порядок выбора значений не важен, что может привести к более эффективному плану выполнения, чем при использовании min-max на группе.
Однако, не все СУБД поддерживают ANY_VALUE. В PostgreSQL, например, как раз таки придётся использовать min или max:
SELECT
cust_id,
MIN(cust_nm) AS customer_name,
SUM(qty * price) AS total_amount
FROM orders
GROUP BY cust_id;
ANY_VALUE — полезная функция для упрощения агрегатных запросов, когда точное значение не имеет значения. Главное — использовать его осознанно и понимать, когда его применение оправдано.
#sql
Please open Telegram to view this post
VIEW IN TELEGRAM
👍5❤4
UNION и UNION ALL. Так ли всё просто?
Маленькая заметка-напоминалка.
Операторы UNION и UNION ALL в SQL отвечают за объединение результатов нескольких запросов. При этом просто UNION выводит только уникальные строки в запросах, то с ALL выведет абсолютно все строки, включая возможные дубли.
Как операторы объединения работают с NULL?
UNION — объединит похожие строки, содержащие NULL в 1 (считая, что это дубли), а UNION ALL оставит все строки.
Ещё несколько особенностей:
1. Набор полей у всех объединяемых запросов должен быть одинаков.
2. Важно! При использовании UNION снижается производительность, так как приходится сканировать результат на наличие дублей. В случае, если в результатах объединения предсказуемо нет дублирующихся полей, предпочтительнее использовать UNION ALL.
#sql #null
Маленькая заметка-напоминалка.
Операторы UNION и UNION ALL в SQL отвечают за объединение результатов нескольких запросов. При этом просто UNION выводит только уникальные строки в запросах, то с ALL выведет абсолютно все строки, включая возможные дубли.
Как операторы объединения работают с NULL?
UNION — объединит похожие строки, содержащие NULL в 1 (считая, что это дубли), а UNION ALL оставит все строки.
Ещё несколько особенностей:
1. Набор полей у всех объединяемых запросов должен быть одинаков.
2. Важно! При использовании UNION снижается производительность, так как приходится сканировать результат на наличие дублей. В случае, если в результатах объединения предсказуемо нет дублирующихся полей, предпочтительнее использовать UNION ALL.
#sql #null
👍5
QUALIFY: фильтруем результаты оконных функций
QUALIFY — SQL-конструкция, которая позволяет отфильтровать результаты после применения оконных функций😍
Она работает аналогично WHERE, но с той разницей, что QUALIFY применяется после оконных функций, а WHERE — до них.
Напомню порядок выполнения запроса:
1. From
2. Where
3. Group by
4. Having
5. Window
6. QUALIFY
7. Distinct
8. Order by
9. Limit
Представим, что у нас есть таблица продаж, и мы хотим выбрать топ-5 продаж по каждой категории товаров. С помощью QUALIFY это можно сделать просто и эффективно:
В этом запросе сначала нумеруем продажи в каждой категории по убыванию суммы продаж с помощью ROW_NUMBER(). Затем с помощью QUALIFY оставляем только первые пять записей в каждой категории. Легко и понятно.
Почему нельзя использовать WHERE вместо QUALIFY? Потому что WHERE фильтрует данные до выполнения оконных функций, а нам нужно отфильтровать данные после. Если попробовать использовать WHERE rn <= 5, SQL не поймёт, что такое rn, потому что на этапе выполнения WHERE эта колонка ещё не создана.
Конечно, можно использовать QUALIFY и с другими оконными функциями, например, RANK():
Этот запрос выберет сотрудников с наивысшей зарплатой в каждом отделе.
Стоит отметить, что QUALIFY поддерживается не во всех СУБД. Например, в Snowflake и Teradata эта функция есть, а в PostgreSQL или MySQL её нет. В таких случаях приходится использовать подзапросы или CTE (Common Table Expressions).
Например так:
Согласитесь, что использование QUALIFY делает код более кратким и читаемым. Используйте его, когда это возможно😎
#sql
QUALIFY — SQL-конструкция, которая позволяет отфильтровать результаты после применения оконных функций
Она работает аналогично WHERE, но с той разницей, что QUALIFY применяется после оконных функций, а WHERE — до них.
Напомню порядок выполнения запроса:
1. From
2. Where
3. Group by
4. Having
5. Window
6. QUALIFY
7. Distinct
8. Order by
9. Limit
Представим, что у нас есть таблица продаж, и мы хотим выбрать топ-5 продаж по каждой категории товаров. С помощью QUALIFY это можно сделать просто и эффективно:
SELECT
category,
product,
sales_amount,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rn
FROM sales
QUALIFY rn <= 5;
В этом запросе сначала нумеруем продажи в каждой категории по убыванию суммы продаж с помощью ROW_NUMBER(). Затем с помощью QUALIFY оставляем только первые пять записей в каждой категории. Легко и понятно.
Почему нельзя использовать WHERE вместо QUALIFY? Потому что WHERE фильтрует данные до выполнения оконных функций, а нам нужно отфильтровать данные после. Если попробовать использовать WHERE rn <= 5, SQL не поймёт, что такое rn, потому что на этапе выполнения WHERE эта колонка ещё не создана.
Конечно, можно использовать QUALIFY и с другими оконными функциями, например, RANK():
SELECT
employee_id,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC)
AS salary_rank
FROM employees
QUALIFY salary_rank = 1;
Этот запрос выберет сотрудников с наивысшей зарплатой в каждом отделе.
Стоит отметить, что QUALIFY поддерживается не во всех СУБД. Например, в Snowflake и Teradata эта функция есть, а в PostgreSQL или MySQL её нет. В таких случаях приходится использовать подзапросы или CTE (Common Table Expressions).
Например так:
WITH ranked_sales AS (
SELECT
category,
product,
sales_amount,
RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rn
FROM sales
)
SELECT *
FROM ranked_sales
WHERE rn <= 5;
Согласитесь, что использование QUALIFY делает код более кратким и читаемым. Используйте его, когда это возможно
#sql
Please open Telegram to view this post
VIEW IN TELEGRAM
❤5🔥4
Прокачиваем SQL-запросы с фишками оконных функций
Многие аналитики активно используют базовые оконные функции, но не всегда знают, как применять такие инструменты, как фрейм окна (window frame). А ведь это сделает запросы ещё более читабельными и эффективными. Давайте разбираться вместе.
В этой статье рассмотрим два способа определить, какие строки включать в окно для расчетов: ROWS и RANGE.
Начнем с ROWS BETWEEN. Эта конструкция работает с физическими строками и помогает анализировать конкретное количество записей до и после текущей строки. Чтобы стало понятнее, рассмотрим пример: мы хотим рассчитать скользящую сумму продаж за последние 3 дня, включая текущий день. Для этого подсчёта нам необходимо от каждой строки отсчитать две строки назад и суммировать значения продаж за эти дни.
Результат:
Выражением ROWS BETWEEN 2 PRECEDING AND CURRENT ROW мы буквально говорим, что нужно от каждой строки отсчитывать две строки назад и суммировать значения продаж за эти дни (включая значение в текущей). Таким образом, каждая строка будет содержать сумму продаж за текущий день и два предыдущих. Важно! В нашей исходной витрине данные уже сгруппированы по дням, поэтому в данном случае корректно говорить Х дней назад, но по сути мы говорим про строки.
RANGE BETWEEN работает иначе. Он группирует строки по значению, а не по их физическому положению. Это бывает полезно, когда нужно учитывать все строки с определённым диапазоном значений относительно текущей строки. Не понятно? Перейдём к примеру.
Нужно рассчитать среднюю цену за последние 3 дня от каждой даты:
Результат:
При использовании RANGE все строки с одинаковым значением столбца, указанного в ORDER BY, обрабатываются вместе. А RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND CURRENT ROW говорит о том, что нужно посчитать значения в фрейме с интервалом 3 дня до текущей строки.
Обратите внимание, что даты 2024-01-03 и 2024-01-05 отсутствуют в таблице. Когда мы используем указанный RANGE BETWEEN, SQL ищет все строки, где price_date находится в диапазоне от текущей даты минус календарных 3 дня, то есть учитывает только имеющиеся даты в этом диапазоне.
Поняли разницу? RANGE учитывает все записи в указанном временном интервале, даже если их много. А ROWS всегда отсчитывает фиксированное количество строк.
Естественно обе функции можно использовать не только при расчётах связанными с датами. Давайте в одной из следующих статей рассмотрим другие примеры. Фреймы кажутся той темой, которой стоит уделить особое внимание.
#sql
Многие аналитики активно используют базовые оконные функции, но не всегда знают, как применять такие инструменты, как фрейм окна (window frame). А ведь это сделает запросы ещё более читабельными и эффективными. Давайте разбираться вместе.
Фрейм определяет набор строк для вычислений оконной функции относительно текущей строки.
В этой статье рассмотрим два способа определить, какие строки включать в окно для расчетов: ROWS и RANGE.
Начнем с ROWS BETWEEN. Эта конструкция работает с физическими строками и помогает анализировать конкретное количество записей до и после текущей строки. Чтобы стало понятнее, рассмотрим пример: мы хотим рассчитать скользящую сумму продаж за последние 3 дня, включая текущий день. Для этого подсчёта нам необходимо от каждой строки отсчитать две строки назад и суммировать значения продаж за эти дни.
| sales_date | sales_amount |
|------------|--------------|
| 2024-01-01 | 100 |
| 2024-01-02 | 150 |
| 2024-01-03 | 200 |
| 2024-01-04 | 250 |
SELECT
sales_date,
sales_amount,
SUM(sales_amount) OVER (
ORDER BY sales_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_sum
FROM sales_daily;
Результат:
| sales_date | sales_amount | rolling_sum |
|------------|--------------|-------------|
| 2024-01-01 | 100 | 100 |
| 2024-01-02 | 150 | 250 |
| 2024-01-03 | 200 | 450 |
| 2024-01-04 | 250 | 600 |
Выражением ROWS BETWEEN 2 PRECEDING AND CURRENT ROW мы буквально говорим, что нужно от каждой строки отсчитывать две строки назад и суммировать значения продаж за эти дни (включая значение в текущей). Таким образом, каждая строка будет содержать сумму продаж за текущий день и два предыдущих. Важно! В нашей исходной витрине данные уже сгруппированы по дням, поэтому в данном случае корректно говорить Х дней назад, но по сути мы говорим про строки.
RANGE BETWEEN работает иначе. Он группирует строки по значению, а не по их физическому положению. Это бывает полезно, когда нужно учитывать все строки с определённым диапазоном значений относительно текущей строки. Не понятно? Перейдём к примеру.
Нужно рассчитать среднюю цену за последние 3 дня от каждой даты:
| price_date | stock_price |
|-------------|-------------|
| 2024-01-01 | 100 |
| 2024-01-01 | 102 |
| 2024-01-02 | 105 |
| 2024-01-04 | 103 |
| 2024-01-04 | 106 |
| 2024-01-06 | 110 |
SELECT
price_date,
stock_price,
AVG(stock_price) OVER (
ORDER BY price_date
RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND CURRENT ROW
) AS avg_price_3days
FROM stock_prices;
Результат:
| price_date | stock_price | avg_price_3days |
|------------|-------------|-----------------|
| 2024-01-01 | 100 | 101.00 |
| 2024-01-01 | 102 | 101.00 |
| 2024-01-02 | 105 | 102.33 |
| 2024-01-04 | 103 | 103.20 |
| 2024-01-04 | 106 | 103.20 |
| 2024-01-06 | 110 | 106.33 |
При использовании RANGE все строки с одинаковым значением столбца, указанного в ORDER BY, обрабатываются вместе. А RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND CURRENT ROW говорит о том, что нужно посчитать значения в фрейме с интервалом 3 дня до текущей строки.
Обратите внимание, что даты 2024-01-03 и 2024-01-05 отсутствуют в таблице. Когда мы используем указанный RANGE BETWEEN, SQL ищет все строки, где price_date находится в диапазоне от текущей даты минус календарных 3 дня, то есть учитывает только имеющиеся даты в этом диапазоне.
Поняли разницу? RANGE учитывает все записи в указанном временном интервале, даже если их много. А ROWS всегда отсчитывает фиксированное количество строк.
Естественно обе функции можно использовать не только при расчётах связанными с датами. Давайте в одной из следующих статей рассмотрим другие примеры. Фреймы кажутся той темой, которой стоит уделить особое внимание.
#sql
👍4
Обожаю Snowflake за множество классных функций, делающих sql-код более читабельным. При этом специально "переучиваться" для работы в снежке не нужно, так как он поддерживает всем знакомый (ну я надеюсь) стандарт ANSI SQL. Просто каждый раз в работе (или листая документацию) ты находишь прикольные фишки, которые хочется использовать.
Например, как бы вы посчитали, сколько задач разработчиков завершено до дедлайна, а сколько — нет? Обычно пришлось бы писать что-то вроде:
В Snowflake этот подсчёт выглядит куда лаконичнее:
Кажется, что это незначительная мелочь? Но насколько приятнее и понятнее становится разбирать тысячи строк какого-нибудь сложного аналитического запроса. Краткость — сила!
#snowflake #sql
Например, как бы вы посчитали, сколько задач разработчиков завершено до дедлайна, а сколько — нет? Обычно пришлось бы писать что-то вроде:
SELECT
developer,
SUM(CASE WHEN completion_date <= deadline THEN 1 ELSE 0 END) AS on_time_tasks,
SUM(CASE WHEN completion_date > deadline THEN 1 ELSE 0 END) AS late_tasks
FROM tasks
GROUP BY developer;
В Snowflake этот подсчёт выглядит куда лаконичнее:
SELECT
developer,
COUNT_IF(completion_date <= deadline) AS on_time_tasks,
COUNT_IF(completion_date > deadline) AS late_tasks
FROM tasks
GROUP BY developer;
Кажется, что это незначительная мелочь? Но насколько приятнее и понятнее становится разбирать тысячи строк какого-нибудь сложного аналитического запроса. Краткость — сила!
#snowflake #sql
👍2🔥1
GROUPING SETS для упрощения агрегации
Мы группируем данные десятки раз в день: по датам, категориям, клиентам или нескольким полям сразу. Но что, если нужно получить несколько уровней агрегации в одном результате? Объединять три разных запроса через UNION ALL? Писать вложенные подзапросы? Такой сценарий превращает простую задачу в головоломку с кучей повторяющегося кода🔄
Теперь представьте: один запрос возвращает и детализацию, и промежуточные итоги, и общую сумму. И всё это без дублирования логики и потери производительности. Это не магия — это
Синтаксис:
Итак, у нас есть таблица с заказами, и нужно вывести витрину продаж: по дням, категориям, дням и категориям, а также общие продажи.
Запрос:
Результат:
🔵 Полные строки (order_dt и category заполнены) — детализированные данные.
🔵 Строки с order_dt и NULL показывают суммы по каждому дню.
🔵 Строки с category и NULL показывают суммы по каждой категории.
🔵 Строка с двумя NULL — общая сумма.
Если нужно определить, какие строки являются результатом группировки, используйте функцию
Пример:
Почему GROUPING SETS лучше UNION ALL?
➕ один запрос вместо нескольких
➕ оптимизация выполнения — СУБД сканирует таблицу один раз и для каждой строки вычисляет все группировки параллельно
➕ читабельность кода
➖ поддерживаются не все диалекты SQL (но основные — PostgreSQL, Oracle, SQL Server, Snowflake, BigQuery — да)
🟢 удобно: меньше кода, меньше ошибок
🟢 быстро: один проход по данным
🟢 гибко: возможны любые комбинации группировок
#sql
Мы группируем данные десятки раз в день: по датам, категориям, клиентам или нескольким полям сразу. Но что, если нужно получить несколько уровней агрегации в одном результате? Объединять три разных запроса через UNION ALL? Писать вложенные подзапросы? Такой сценарий превращает простую задачу в головоломку с кучей повторяющегося кода
Теперь представьте: один запрос возвращает и детализацию, и промежуточные итоги, и общую сумму. И всё это без дублирования логики и потери производительности. Это не магия — это
GROUP BY GROUPING SETS
. Спойлер: после него вы вряд ли захотите возвращаться к старому подходу.Синтаксис:
SELECT column1, column2, AGG_FUNC(column3) AS aggregate_result
FROM table_name
GROUP BY GROUPING SETS
(
(column1),
(column2),
(column1, column2),
() -- итоговая строка для всех данных
);
Итак, у нас есть таблица с заказами, и нужно вывести витрину продаж: по дням, категориям, дням и категориям, а также общие продажи.
| order_id | order_dt | category | price |
|----------|------------|-------------|-------|
| 1 | 2025-02-01 | Книги | 100 |
| 2 | 2025-02-01 | Книги | 200 |
| 3 | 2025-02-01 | Электроника | 700 |
| 4 | 2025-02-02 | Книги | 150 |
| 5 | 2025-02-02 | Электроника | 250 |
| 6 | 2025-02-02 | Электроника | 550 |
Запрос:
SELECT
order_dt,
category,
SUM(price) AS total_sum
FROM orders
GROUP BY GROUPING SETS
(
(order_dt, category), -- Группировка по дням и категориям
(order_dt), -- по дням
(category), -- по категориям
() -- Итоговая строка
);
Результат:
| order_dt | category | total_sum |
|------------|-------------|-----------|
| 2024-01-01 | Книги | 300 |
| 2024-01-01 | Электроника | 700 |
| 2024-01-02 | Книги | 150 |
| 2024-01-02 | Электроника | 800 |
| 2024-01-01 | NULL | 1000 |
| 2024-01-02 | NULL | 950 |
| NULL | NULL | 1950 |
| NULL | Книги | 450 |
| NULL | Электроника | 1500 |
Если нужно определить, какие строки являются результатом группировки, используйте функцию
GROUPING()
. Она возвращает 1 там, где значение агрегировано.Пример:
SELECT
order_dt,
category,
SUM(price) AS total_sales,
GROUPING(order_dt) AS is_dt_agg,
GROUPING(category) AS is_cat_agg
FROM orders
GROUP BY GROUPING SETS
(
(order_dt), -- Группировка по дням
(category), -- Группировка по категориям
() -- Итоговая строка
);
| order_dt | category | total_sales | is_dt_agg | is_cat_agg |
|------------|------------|-------------|-----------|------------|
| 2024-01-01 | NULL | 1000 | 0 | 1 |
| 2024-01-02 | NULL | 950 | 0 | 1 |
| NULL | NULL | 1950 | 1 | 1 |
| NULL | Книги | 450 | 1 | 0 |
| NULL | Электроника| 1500 | 1 | 0 |
Почему GROUPING SETS лучше UNION ALL?
➕ один запрос вместо нескольких
➕ оптимизация выполнения — СУБД сканирует таблицу один раз и для каждой строки вычисляет все группировки параллельно
➕ читабельность кода
➖ поддерживаются не все диалекты SQL (но основные — PostgreSQL, Oracle, SQL Server, Snowflake, BigQuery — да)
GROUP BY GROUPING SETS
полезен для отчетности и аналитических анализов, где нужны сводные данные разной детализации. Это инструмент работает:#sql
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥5👍3
SQL под капотом: как выполняются запросы
Знаете ли вы, что происходит под капотом СУБД, когда вы выполняете SQL-запрос? База данных запускает целый процесс, шаг за шагом превращая код в набор данных. Каждая команда проходит проверку, оптимизацию, выполнение, обработку и вывод результата. Давайте посмотрим на каждый из этапов:
1. Query Parsing (разбор запроса)
Сначала сервер проверяет, правильно ли написан запрос. Проводит так называемый синтаксический анализ. Ошиблись в запятой или перепутали порядок ключевых слов? Получите ошибку.
После синтаксического анализа начинается семантический разбор: существуют ли таблицы и колонки, есть ли у вас права на запрос? Если все ок, база строит parse tree.
Parse Tree — это иерархическое представление запроса, где каждый узел — отдельная операция (например, фильтр, join, сортировка). Это облегчает работу оптимизатора и позволяет строить разные планы выполнения.
2. Query Optimization (оптимизация запроса)
На этом этапе в работу вступает умный планировщик. Он оценивает различные стратегии выполнения запроса, чтобы определить наиболее эффективную и менее ресурсоёмкую. Оптимизаторы сильно отличаются от СУБД к СУБД, но, к примеру, в Snowflake он, действительно, умный и даже плохо написанный запрос в большинстве случаев "переписывает" оптимально самостоятельно (это, конечно, не значит что стоит писать запросы как попало👿 ).
Оптимизатор, в зависимости от СУБД может проверять:
Как соединять таблицы — Nested Loop, Hash Join, Merge Join?
Как фильтровать и сортировать данные?
Использовать индексы или нет?
Оптимизатор анализирует статистику таблиц: сколько строк, какие значения чаще встречаются, какие индексы есть. Он перебирает варианты и выбирает наилучший.
3. Query Execution (выполнение запроса)
После этого база данных начинает пошагово выполнять запрос, согласно выбранному плану.
Запросы могут выполняться через:
🔵 Table Scan — полный перебор строк в таблице (долго).
🔵 Index Seek — точечный поиск через индекс (быстро, но требует индекса).
4. Извлечение или изменение данных
Если наш запрос извлекает данные (SELECT - Data Query Language), база выбирает нужные строки из таблиц и формирует результат. Если же запрос изменяет данные (INSERT, MERGE, UPDATE или DELETE - Data Manipulation Language), информация в таблице обновляется, удаляется или дополняется.
5. Формирование результата
Когда SQL-движок собрал нужные строки, он финально формирует итоговый результат: сортирует, группирует, выполняет агрегатные вычисления. Однако часть агрегаций, особенно в запросах с GROUP BY, может выполняться еще на этапе извлечения данных, если движок решит, что это эффективнее. То есть это зависит от плана выполнения запроса и используемого метода обработки.
6. Результат
Когда всё готово, результат возвращается в клиентское приложение, которое уже отображает его пользователю.
Для SELECT-запросов, если данных много, они передаются частями, чтобы не перегружать память.
Некоторые базы поддерживают Lazy Execution — строки выгружаются только при необходимости.
Как видите, написанный запрос запускает целые механизмы внутри СУБД. Каждый этап играет свою роль: разбор проверяет синтаксис на ошибки, оптимизатор выбирает самый быстрый путь, выполнение шаг за шагом приводит к нужному результату, а передача данных гарантирует, что вы получите ответ в удобной форме, ничего не потеряв.
Не всегда имеет смысл знать, что происходит под капотом, но хотя бы верхнеуровневое понимание помогает нам самим работать эффективнее. Если что-то идет не так, вы будете знать, где искать проблему и как ее решить. Понимание происходящего — ключ к написанию быстрых и оптимизированных запросов.
#sql
Знаете ли вы, что происходит под капотом СУБД, когда вы выполняете SQL-запрос? База данных запускает целый процесс, шаг за шагом превращая код в набор данных. Каждая команда проходит проверку, оптимизацию, выполнение, обработку и вывод результата. Давайте посмотрим на каждый из этапов:
1. Query Parsing (разбор запроса)
Сначала сервер проверяет, правильно ли написан запрос. Проводит так называемый синтаксический анализ. Ошиблись в запятой или перепутали порядок ключевых слов? Получите ошибку.
После синтаксического анализа начинается семантический разбор: существуют ли таблицы и колонки, есть ли у вас права на запрос? Если все ок, база строит parse tree.
Parse Tree — это иерархическое представление запроса, где каждый узел — отдельная операция (например, фильтр, join, сортировка). Это облегчает работу оптимизатора и позволяет строить разные планы выполнения.
2. Query Optimization (оптимизация запроса)
На этом этапе в работу вступает умный планировщик. Он оценивает различные стратегии выполнения запроса, чтобы определить наиболее эффективную и менее ресурсоёмкую. Оптимизаторы сильно отличаются от СУБД к СУБД, но, к примеру, в Snowflake он, действительно, умный и даже плохо написанный запрос в большинстве случаев "переписывает" оптимально самостоятельно (это, конечно, не значит что стоит писать запросы как попало
Оптимизатор, в зависимости от СУБД может проверять:
Как соединять таблицы — Nested Loop, Hash Join, Merge Join?
Как фильтровать и сортировать данные?
Использовать индексы или нет?
Оптимизатор анализирует статистику таблиц: сколько строк, какие значения чаще встречаются, какие индексы есть. Он перебирает варианты и выбирает наилучший.
3. Query Execution (выполнение запроса)
После этого база данных начинает пошагово выполнять запрос, согласно выбранному плану.
Запросы могут выполняться через:
4. Извлечение или изменение данных
Если наш запрос извлекает данные (SELECT - Data Query Language), база выбирает нужные строки из таблиц и формирует результат. Если же запрос изменяет данные (INSERT, MERGE, UPDATE или DELETE - Data Manipulation Language), информация в таблице обновляется, удаляется или дополняется.
5. Формирование результата
Когда SQL-движок собрал нужные строки, он финально формирует итоговый результат: сортирует, группирует, выполняет агрегатные вычисления. Однако часть агрегаций, особенно в запросах с GROUP BY, может выполняться еще на этапе извлечения данных, если движок решит, что это эффективнее. То есть это зависит от плана выполнения запроса и используемого метода обработки.
6. Результат
Когда всё готово, результат возвращается в клиентское приложение, которое уже отображает его пользователю.
Для SELECT-запросов, если данных много, они передаются частями, чтобы не перегружать память.
Некоторые базы поддерживают Lazy Execution — строки выгружаются только при необходимости.
Как видите, написанный запрос запускает целые механизмы внутри СУБД. Каждый этап играет свою роль: разбор проверяет синтаксис на ошибки, оптимизатор выбирает самый быстрый путь, выполнение шаг за шагом приводит к нужному результату, а передача данных гарантирует, что вы получите ответ в удобной форме, ничего не потеряв.
Не всегда имеет смысл знать, что происходит под капотом, но хотя бы верхнеуровневое понимание помогает нам самим работать эффективнее. Если что-то идет не так, вы будете знать, где искать проблему и как ее решить. Понимание происходящего — ключ к написанию быстрых и оптимизированных запросов.
#sql
Please open Telegram to view this post
VIEW IN TELEGRAM
👍6👎2❤1🤡1
Разбираемся с дублями
Если вашу выборку нужно почистить от дублей, вы можете сделать это очень просто:
В результате получим в выводе только уникальные строки (вместо *, конечно же, указываем корректный список полей).
🙃
Недостаток: пока что работает не во всех СУБД🥲
Если СУБД не поддерживает оператор QUALIFY, можем чистить так:
P.S. Про сам
#sql
Если вашу выборку нужно почистить от дублей, вы можете сделать это очень просто:
SELECT *
FROM your_table
QUALIFY ROW_NUMBER() OVER(PARTITION BY column_id ORDER BY column_dt DESC) = 1;
В результате получим в выводе только уникальные строки (вместо *, конечно же, указываем корректный список полей).
QUALIFY + ROW_NUMBER()
= никаких лишних подзапросов Недостаток: пока что работает не во всех СУБД
Если СУБД не поддерживает оператор QUALIFY, можем чистить так:
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY column_id ORDER BY column_dt DESC) AS rn
FROM your_table
)
SELECT *
FROM cte
WHERE rn = 1;
P.S. Про сам
QUALIFY
я уже писала здесь.#sql
Please open Telegram to view this post
VIEW IN TELEGRAM
👍4❤3🔥2
Иногда приходится разбирать чужие sql-запросы и периодически сталкиваюсь с различными ошибками. Сегодня хочу рассказать о трёх наиболее распространённых.
Некорректная работа с NULL
Я уже много раз писала, NULL — не просто пустота, это неизвестность. Поэтому нельзя сравнивать с NULL в лоб. Запрос вам ошибку не выдаст, но отработает некорректно.
Также при подсчёте количества строк
Больше про #null я писала в постах с соответствующим тегом) на собесах часто про это спрашивают, но уделить внимание теме, конечно же, стоит не только поэтому.
Неправильное использование оператора BETWEEN
Ещё часто вижу, как забывают об особеннстях BETWEEN, забывая, что он включает и верхнюю, и нижнюю границы диапазона. Это может привести к дублированию данных или их пропуску при последовательной выборке.
В этом примере заказы, созданные ровно в полночь 2 марта (2024-03-02 00:00:00), будут включены в обе выборки! Лучше использовать явные полуинтервалы:
Но если сильно хочется BETWEEN, то:
Да, про миллисекунды забывать не нужно, а то можно что-то потерять. И всё-таки проще использовать полуинтервалы)
Ошибки в логических операторах
Ещё часто забывают про приоритеты при использовании AND и OR в одном условии. В SQL сначала выполняются все AND, а затем уже OR.
Например, нужно найти все транзакции на сумму больше 100.000, которые имеют статус "completed" и при этом либо от премиум-пользователя, либо оплачены кредитной картой.
По правилам SQL операторы AND приоритетнее. Поэтому запрос интерпретируется так:
То есть мы получим все завершённые транзакции премиум-пользователей с суммой больше 100000, плюс абсолютно все транзакции с кредитных карт (даже незавершённые и с маленькими суммами).
Так мы получим именно то, что хотели:
В целом, проще лишний раз указать скобки, чем запутаться и получить ошибочный результат.
Кому-то кажется очевидным, но такие вещи, действительно, встречаются. А с какими ошибками в sql вы часто сталкиваетесь?
#sql
Некорректная работа с NULL
Я уже много раз писала, NULL — не просто пустота, это неизвестность. Поэтому нельзя сравнивать с NULL в лоб. Запрос вам ошибку не выдаст, но отработает некорректно.
-- неправильно:
SELECT * FROM users WHERE age = NULL;
SELECT * FROM users WHERE age != NULL;
-- правильно:
SELECT * FROM users WHERE age IS NULL;
SELECT * FROM users WHERE age IS NOT NULL;
Также при подсчёте количества строк
COUNT(column_name)
пропустит все NULL-значения. Поэтому если нужно посчитать прям вообще всё используйте COUNT(*).
-- считает количество заполненных номеров:
SELECT COUNT(phone) FROM users;
-- считает все строки, в том числе с NULL:
SELECT COUNT(*) FROM users;
Больше про #null я писала в постах с соответствующим тегом) на собесах часто про это спрашивают, но уделить внимание теме, конечно же, стоит не только поэтому.
Неправильное использование оператора BETWEEN
Ещё часто вижу, как забывают об особеннстях BETWEEN, забывая, что он включает и верхнюю, и нижнюю границы диапазона. Это может привести к дублированию данных или их пропуску при последовательной выборке.
-- пример кода с ошибкой:
-- выборка за 1 марта о полю типа дата-время
SELECT * FROM orders WHERE order_dttm BETWEEN '2024-03-01' AND '2024-03-02';
-- Выборка за 2 марта
SELECT * FROM orders WHERE order_dttm BETWEEN '2024-03-02' AND '2024-03-03';
В этом примере заказы, созданные ровно в полночь 2 марта (2024-03-02 00:00:00), будут включены в обе выборки! Лучше использовать явные полуинтервалы:
-- правильно:
-- выборка за 1 марта
SELECT * FROM orders WHERE order_dttm >= '2024-03-01' AND order_dttm < '2024-03-02';
-- выборка за 2 марта
SELECT * FROM orders WHERE order_dttm >= '2024-03-02' AND order_dttm < '2024-03-03';
Но если сильно хочется BETWEEN, то:
-- выборка за 1 марта
SELECT * FROM orders WHERE order_dttm BETWEEN '2024-03-01 00:00:00' AND '2024-03-01 23:59:59';
-- выборка за 2 марта
SELECT * FROM orders WHERE order_dttm BETWEEN '2024-03-01 00:00:00.000' AND '2024-03-01 23:59:59.999';
Да, про миллисекунды забывать не нужно, а то можно что-то потерять. И всё-таки проще использовать полуинтервалы)
Ошибки в логических операторах
Ещё часто забывают про приоритеты при использовании AND и OR в одном условии. В SQL сначала выполняются все AND, а затем уже OR.
Например, нужно найти все транзакции на сумму больше 100.000, которые имеют статус "completed" и при этом либо от премиум-пользователя, либо оплачены кредитной картой.
-- неправильно:
SELECT * FROM transactions
WHERE amount > 100000
AND status = 'completed'
AND user_type = 'premium' OR payment_method = 'credit_card'
По правилам SQL операторы AND приоритетнее. Поэтому запрос интерпретируется так:
SELECT * FROM transactions
WHERE (status = 'completed' AND amount > 100000 AND user_type = 'premium')
OR (payment_method = 'credit_card')
То есть мы получим все завершённые транзакции премиум-пользователей с суммой больше 100000, плюс абсолютно все транзакции с кредитных карт (даже незавершённые и с маленькими суммами).
Так мы получим именно то, что хотели:
-- правильно:
SELECT * FROM transactions
WHERE status = 'completed'
AND amount > 100000
AND (user_type = 'premium' OR payment_method = 'credit_card')
В целом, проще лишний раз указать скобки, чем запутаться и получить ошибочный результат.
Кому-то кажется очевидным, но такие вещи, действительно, встречаются. А с какими ошибками в sql вы часто сталкиваетесь?
#sql
👍6🔥5
Занималась тут оптимизацией чужого запроса. И вот вроде бы знаешь базу и хочешь её применить, но оптимизатор всегда оказывается хитрее 🙂
Среди прочего, пыталась применить одно из главных правил оптимизации — predicate pushdown. Это когда мы поднимаем условия фильтрации как можно выше, чтобы заранее уменьшить объем данных. Так вот, вынесла в cte фильтрацию одной таблички (~2GB), а в другом cte уже шла работа с отфильтрованными данными — джойны и тп. Смотрю в план запроса и вижуфигу, что снежок (snowflake) всё равно сначала сканирует таблицу целиком, затем джойнит, и только после этого фильтрует 😵 причём аналогичный сценарий на другой, но бОльшей таблице (~в 8GB) отрабатывает как надо 🥲 Видимо, размер данных или внутренняя статистика влияют на решения cost-based оптимизатора.
Никаких инсайтов в этой заметке вам не дам, но в очередной раз убеждаюсь: важно уметь читать (и понимать) планы запросов и анализировать query profile. Не всегда логичные на первый взгляд шаги оптимизации работают как ожидается. И не только от СУБД к СУБД поведение может разительно отличаться, но и даже в рамках таблиц в одном хранилище. Экспериментируйте и тестируйте на реальных данных🤖
P.S. Тем, кто хочет использовать для анализа планов гпт, всё же советую сначала самостоятельно научиться их читать, т.к. LLM всё ещё склонны к галлюцинациям. Как говорится: "на ИИ надейся, да сам не плошай".
#sql #snowflake
Среди прочего, пыталась применить одно из главных правил оптимизации — predicate pushdown. Это когда мы поднимаем условия фильтрации как можно выше, чтобы заранее уменьшить объем данных. Так вот, вынесла в cte фильтрацию одной таблички (~2GB), а в другом cte уже шла работа с отфильтрованными данными — джойны и тп. Смотрю в план запроса и вижу
Никаких инсайтов в этой заметке вам не дам, но в очередной раз убеждаюсь: важно уметь читать (и понимать) планы запросов и анализировать query profile. Не всегда логичные на первый взгляд шаги оптимизации работают как ожидается. И не только от СУБД к СУБД поведение может разительно отличаться, но и даже в рамках таблиц в одном хранилище. Экспериментируйте и тестируйте на реальных данных
P.S. Тем, кто хочет использовать для анализа планов гпт, всё же советую сначала самостоятельно научиться их читать, т.к. LLM всё ещё склонны к галлюцинациям. Как говорится: "на ИИ надейся, да сам не плошай".
#sql #snowflake
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥12