|
|
Задавайте вопросы, мы ответим
Вы не зашли.
Всем привет!
Давайте немного поговорим о группировке запросов с использованием window functions.
(Кстати, не знаю, как их лучше называть по-русски; на ум приходит «блочные или интервальные функции». Как считаете?)
Примером послужит вот такая довольно распространенная задача: есть цены на товары от поставщиков (по несколько на каждый товар), из них нужно выбрать наименьшую вместе с идентификатором поставщика:
+------------+-------------+------------+ | product_id | best_source | best_price | +------------+-------------+------------+ | 1 | A | 1000 | | 2 | C | 2300 | | 3 | B | 5000 | +------------+-------------+------------+
Давайте его разберем подробнее.
Как известно, DISTINCT - это форма GROUP BY, поэтому рассмотрим сначала, как запрос выглядит и работает без него.
Запросы с window functions отличаются от запросов с GROUP BY тем, что строки в них не склеиваются в одну, а остаются в неизменном количестве, просто к колонкам добавляется результат некоторой групповой операции, одинаковый для каждой строки в группе:
+------------+--------+-------+-------------+------------+ | product_id | source | price | best_source | best_price | +------------+--------+-------+-------------+------------+ | 1 | A | 1000 | A | 1000 | | 1 | B | 1100 | A | 1000 | | 1 | C | 1200 | A | 1000 | | 2 | C | 2300 | C | 2300 | | 2 | B | 2500 | C | 2300 | | 2 | A | 2700 | C | 2300 | | 3 | B | 5000 | B | 5000 | | 3 | C | 5000 | B | 5000 | | 3 | A | 5200 | B | 5000 | +------------+--------+-------+-------------+------------+
Алгоритмически подобные запросы работают так: сначала выбираются все нужные данные, после чего по ним делается дополнительный пробег, вычисляются групповые операции блока и добавляются к результату. Назовём это группировкой № 1.
Нам, однако, нужны не все строки с их данными, а только поставщик с лучшей ценой и значение этой цены. То есть, требуется еще одна группировка по product_id.
Загвоздка в том, что GROUP BY (наряду с WHERE и HAVING) выполняется перед применением window function, а не совместно с ней. Если, например, написать вот такой запрос:
+------------+-------------+------------+ | product_id | best_source | best_price | +------------+-------------+------------+ | 1 | A | 1000 | | 2 | A | 2700 | | 3 | A | 5200 | +------------+-------------+------------+
Поэтому нужно запрос обернуть в группировку:
Отредактированно LazY (13.04.2022 19:12:28)
Неактивен
LazY, спасибо за интересную тему. Запрос с ANY_VALUE у меня не сработал из-за only_full_group_by, потребовалось часть ORDER BY заменить на ORDER BY ANY_VALUE(price)
Неактивен
С оконными функциями избавиться от повторной группировки не получится. Это связано с заданным порядком исполнения: сначала запрос, потом оконные функции. Может быть, возможна оптимизация таких ситуаций на уровне плана исполнения запроса, но это потребует разработки нового способа исполнения запросов.
Думаю, что русский вариант названия window functions сохранится в виде "оконные функции". Ключевое слово "WINDOW" будет сложно представлять себе иначе, как "окно".
Неактивен
rgbeast написал:
Запрос с ANY_VALUE у меня не сработал из-за only_full_group_by, потребовалось часть ORDER BY заменить на ORDER BY ANY_VALUE(price)
Да, действительно. Спасибо, исправил в сообщении.
Также поменял порядок вызова функций в SELECT - FIRST_VALUE(ANY_VALUE(...)), а не наоборот, как было. На мой взгляд, такая запись лучше отражает порядок действий - сначала GROUP BY, для которой нужна ANY_VALUE, а потом window-функция, для которой FIRST_VALUE(). Хотя на результат это не влияет.
rgbeast написал:
Думаю, что русский вариант названия window functions сохранится в виде "оконные функции". Ключевое слово "WINDOW" будет сложно представлять себе иначе, как "окно".
Да, я тоже себя поймал на этом. Видимо, так и приживется, хотя по-русски не говорят "окно результатов" и т.п.
Неактивен
