Множественные комбинации: перекрёстное соединение в SQL

Мы ещё не затрагивали тему соединений, которая является очень важной и обширной. И начну я, пожалуй, с одного из наиболее редких соединений. Это CROSS JOIN. Несмотря на его не частое использование, важно понимать как он работает.

По своей сути cross join — это декартово произведение. Каждая строка таблицы A объединяется с каждой строкой таблицы B. Число строк набора результатов будет равно произведению количества строк таблиц, которые будут объединены. Это отличный инструмент для создания всевозможных комбинаций.

Синтаксис:
SELECT id_a, id_b
FROM table_a
CROSS JOIN table_b;

Аналогично сработает:
SELECT id_a, id_b
FROM table_a, table_b

Давайте рассмотрим как будет выглядеть результат перекрёстного объединения двух таблиц:
table_a
1
3
NULL

table_b
1
2
NULL

Результат:
1 1
1 2
1 NULL
3 1
3 2
3 NULL
NULL 1
NULL 2
NULL NULL

Обратите внимание на то, как произошло объединение по NULL — это полноценное значение поля и забывать о нём не нужно.

При использовании cross join на больших таблицах не забывайте, что это дорогостоящая операция, возвращающая огромные наборы данных (ведь мы умножаем одну таблицу на другую).

#sql #join

DROP, DELETE, TRUNCATE — погружение в мир удаления данных

В SQL есть несколько способов избавиться от данных и хорошо бы в них не путаться. Так что давайте вспоминать азы.

DROP — когда нужно грохнуть всё и сразу
Эта DDL-операция используется для удаления объектов базы данных (таблицы, представления, индексы и т.д., включая саму базу). Важно понимать, что удаляются не только данные, но и сама структура, определяющая их хранение.

DROP TABLE table_name;

DELETE — выборочное удаление
DML-операция DELETE удаляет записи из таблиц, при этом есть возможность удаления данных по условию. Если условие не указано, то удаляются все данные (но не структура объекта). При этом при удалении DELETE возвращает количество удалённых строк. Также стоит помнить, что DELETE сохраняет значения авто-инкрементального ID, т.е. если вы удалили последнюю запись в таблице и у неё был ID 37, новая строка добавится с ID 38.
Транзакцию с DELETE можно откатить. Удаление "построчное", поэтому медленное.

DELETE FROM table_name WHERE column_name=value;

TRUNCATE — быстрая очистка
DDL-операция TRUNCATE позволяет быстро очистить таблицу ото всех записей, так как в лог-транзакций не записывается построчное удаление (быстрота особенно ощущается на больших таблицах, но не экспериментируйте на проде). При удалении TRUNCATE сбрасывает все авто-инкрементальные счётчики и статистику.

TRUNCATE TABLE table_name;

Удаляйте осознано и, главное, с пониманием что и где вы делаете 😄

#sql

Сказка о потерянном времени или проблемы с датами при репликации

На хабре вышла полезная статья про то, почему стоит отказаться от timestamp в PostgreSQL. Рекомендую к прочтению, так как на самом деле тема актуальна не только для постгри.

При построении хранилища очень важно понимать какие даты лежат в источниках, какой часовой пояс на сервере и что именно приходит в нужные нам поля таблиц. Иначе мы можем получить несогласованность в данных: если в источник данные приходят без указания часового пояса, но "по умолчанию" считается, что это Москва, а само хранилище работает в UTC, при репликации даты могут сдвинуться на несколько часов (и, например, перейти в следующий день), что приведёт к недопониманию временных интервалов и ошибкам при анализе временных периодов. Это может повлиять на оценку эффективности стратегий или бизнес-процессов.

В финансовой отчетности критически важна точность дат. Несогласованные даты при расчетах прибыли и убытков могут привести к ошибкам в финансовых планах и стратегиях.

Что же делать?

— Необходим анализ всех полей с датами в источниках. Важно понимать, что неразбериха с часовыми поясами может быть даже в рамках одной таблицы, например, даты в тех.полях могут поступать в UTC, а в самих фактах в Московском времени.

— Нужна явная обработка часовых поясов при репликации, чтобы избежать несогласованных данных.

— Использование UTC в общем случае упростит согласование дат и предотвратит временную путаницу.

— Чаще всего лучше использовать тип timestamptz (with time zone), если данный тип поддерживается.

— Проверке времён нужно уделить особо внимание при тестировании и приёмке данных.

Управление часовыми поясами при репликации — ключевой аспект поддержания целостности данных в хранилище. Системный подход к этому вопросу поможет избежать временной путаницы и обеспечить точность аналитических отчётов.

#sql

EXCEPT в 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

Магия 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 можно легко объединить студентов и занятия, на которые они записаны.

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

Explicit is better than implicit: не используйте SELECT *

Один из моих любимых принципов в The Zen of Python (PEP-20) — это "Explicit is better than implicit" (явное — лучше неявного). Он актуален не только для питона, но и для любого кода, документации и в целом жизни. Сегодня я хочу поговорить о примере использования этого принципа.

Часто для сокращения sql-кода используется конструкция SELECT *, которая выгружает информацию сразу из всех колонок. Согласитесь, если у вас таблица с 200 столбцами, то вместо того, чтобы писать колбасу из перечислений, куда проще просто поставить *. Ведь мы на 100% уверены, что все столбцы нужны.

Почему так делать не нужно?

1. Структура данных: ничто не вечно
Даже если кажется, что исходная таблица никогда не изменится, через Х времени это произойдёт. Столбцы могут быть добавлены или удалены, и отлаженный процесс будет сломан. Но из-за отсутствия явного указания, найти и исправить ошибку будет не так просто.

2. Снижение производительности
SELECT * в запросах извлекает все столбцы из указанных объектов, включая те, которые не требуются. Это может значительно увеличить объем получаемых данных и снизить скорость выполнения запроса и его производительность (это особенно актуально для колоночного хранения).

3. Сложность в понимании логики запроса
Используя неявное указание, мы усложняем дальнейшую поддержку запроса другими людьми. Явное перечисление — это самодокументирующийся код с четким пониманием смыслов.

4. Непреднамеренное раскрытие данных
SELECT * может случайно раскрыть конфиденциальную информацию, которая не предназначалась для текущего контекста или анализа. К примеру, это может случиться после того как в исходную таблицу будут добавлены новые столбцы о которых заранее никто не предполагал.

Для четкой понимании логики запроса, оптимизации запросов и упрощения рефакторинга всегда указывайте столбцы явно (разве что речь не идёт об ad-hoc запросах).

#sql

Группировка NULL-значений

Мы уже рассмотрели множество особенностей NULL, но это ещё не всё.
На очереди группировка данных, включающих NULL-значения.

Как думаете, будут ли NULL сгруппированы или каждый будет считаться самостоятельной единицей?

В этот раз, если вы читали мои предыдущие статьи, ответ может вас удивить. При использовании GROUP BY, строки с NULL в группирующем столбце объединяются в одну группу. Казалось бы NULL обозначает "неизвестное" значение и как мы можем его группировать? Но факт остаётся фактом, при агрегации все NULL считаются равными между собой и формируют единую группу.

Чтобы сделать это поведение более очевидным при аналитике, мы можем использовать функции COALESCE или CASE, чтобы заменить NULL на значение, которое ясно указывает на отсутствие данных, например, на 'Неизвестно'.

#sql #null

Используете USING? Тогда мы идём к вам

Ранее я писала про то, почему не стоит использовать 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

Немного об оконных функциях

Рассказывать о простом 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 #оконные_функции

Противостояние CTE и подзапросов

В области анализа данных есть два классных инструмента — общие табличные выражения (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

Как lag и lead помогают заглянуть в прошлое и будущее ваших данных

Привет!
Сегодня хочу рассказать про оконные функции 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 #оконные_функции

UNION и UNION ALL. Так ли всё просто?

Маленькая заметка-напоминалка.

Операторы UNION и UNION ALL в SQL отвечают за объединение результатов нескольких запросов. При этом просто UNION выводит только уникальные строки в запросах, то с ALL выведет абсолютно все строки, включая возможные дубли.

Как операторы объединения работают с NULL?
UNION — объединит похожие строки, содержащие NULL в 1 (считая, что это дубли), а UNION ALL оставит все строки.

Ещё несколько особенностей:
1. Набор полей у всех объединяемых запросов должен быть одинаков.
2. Важно! При использовании UNION снижается производительность, так как приходится сканировать результат на наличие дублей. В случае, если в результатах объединения предсказуемо нет дублирующихся полей, предпочтительнее использовать UNION ALL.

#sql #null

Прокачиваем 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

Обожаю Snowflake за множество классных функций, делающих sql-код более читабельным. При этом специально "переучиваться" для работы в снежке не нужно, так как он поддерживает всем знакомый (ну я надеюсь) стандарт ANSI 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