Исключение строк в оконных функциях!

Иногда при расчётах нужно не учитывать текущую строку или соседние — например, чтобы не искажать среднее значение. Для этого в SQL есть оператор EXCLUDE - задаёт, какие строки исключать из окна.

Сначала исключим текущую строку:

SELECT id, value,
       AVG(value) OVER (
           ORDER BY id
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
           EXCLUDE CURRENT ROW
       ) AS avg_without_current
FROM data;

Исключаем все строки с одинаковым значением сортировки:

SELECT id, category, value,
       SUM(value) OVER (
           ORDER BY category
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
           EXCLUDE GROUP
       ) AS sum_without_group
FROM data;

Исключаем только строки с тем же значением ORDER BY (ties):

SELECT id, score,
       COUNT(*) OVER (
           ORDER BY score
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
           EXCLUDE TIES
       ) AS cnt_without_ties
FROM data;

🔥 Так EXCLUDE помогает гибко управлять рамкой окна — исключая нужные строки из расчётов без изменения общей логики запроса.

➡️ SQL Ready | #практика

☕️ Neon — интерактивный учебник по базам данных!

Хочешь научиться работать с базами данных - от простых запросов до глубоких фич PostgreSQL? На Neon ты найдёшь полное руководство: как начать, как писать запросы, работать с транзакциями и продвинутыми темами вроде CTE, индексов и триггеров.

📌 Оставляю ссылочку: neon.com

➡️ SQL Ready | #ресурс

🖥 Знаете, как посчитать средний интервал между событиями в SQL?

Если нужно посчитать время между событиями (например, между заказами, логинами или платежами). Есть простой и эффективный способ — функция LAG().

Сначала берём дату текущего и предыдущего события для каждого пользователя:

LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date)

Считаем разницу между ними:

order_date - LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date)

Чтобы получить средний промежуток:

SELECT user_id,
       AVG(order_date - LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date)) AS avg_gap
FROM orders;

🔥 Так вы узнаете, среднюю частоту заказов по каждому пользователю, без подзапросов, без циклов.

➡️ SQL Ready | #совет