Как не ронять базу лишний раз. Используем FILTER для оконок.
Обычно для фильтрации в SQL используют WHERE. Как мы знаем из другой заметки, логический путь выполнения запроса такой: сначала берем данные, потом фильтруем, затем агрегируем.
Но что, если нам нужно взять все данные (то есть не использовать WHERE) и при этом сделать расчет только по определенным строкам? Нужно сочетать несочетаемое: видеть все строки, но считать не по всем.
Допустим, мы анализируем продажи на мебельной фабрике. Мы берем все данные по диванам, столам и стульям. Нам нужно вывести все продажи и отдельным столбцом — количество проданных экземпляров, но только для стульев из лимитированной коллекции.
Если мы просто сделаем COUNT(*) OVER(...), база будет считать всё подряд. Мы можем добавить PARTITION BY и посчитать количество продаж каждого вида, но:
- Нам не нужна лишняя информация по другим категориям.
- Что если лимитированных стульев 100 штук, а остальных товаров 2 миллиона? Посчитать 100 штук явно проще и быстрее, чем 2 млн и 100 штук.
Когда мы используем FILTER, база будет считать только там, где нужно:
COUNT(*) FILTER (WHERE furniture = 'chairs') OVER (ORDER BY date)
То есть сначала фильтруем, а потом считаем. В строках со стульями мы получим количество проданных экземпляров, а в остальных категориях — просто null. Это чище и понятнее, чем городить CASE WHEN внутри оконки.
Важно: работает не со всеми оконными функциями, а только с агрегацией. То есть row_number() так не сработает.
Синтаксис: aggregate_function() FILTER (WHERE conditions) OVER (PARTITION BY ... ORDER BY ...)
В некоторых СУБД, например в ClickHouse, есть альтернативы: sumIf(amount, furniture = 'chairs').
Связи: 📌 В другой заметке рассказывал, как использовать алиасы для оконок, чтобы сделать код чище: Псевдоним для окна (WINDOW) 📌 А здесь про то, в каком порядке база на самом деле читает запрос: Порядок выполнения запроса в SQL
В этом посте были ссылки, но мы их удалили по правилам Сетки
