SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 12.04.2022 22:33:54

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 844

Window functions + GROUP BY

Всем привет!

Давайте немного поговорим о группировке запросов с использованием window functions.

(Кстати, не знаю, как их лучше называть по-русски; на ум приходит «блочные или интервальные функции». Как считаете?)

Примером послужит вот такая довольно распространенная задача: есть цены на товары от поставщиков (по несколько на каждый товар), из них нужно выбрать наименьшую вместе с идентификатором поставщика:


CREATE TABLE test (
    product_id INT,
    source VARCHAR(32),
    price DECIMAL(6),
    UNIQUE KEY (product_id, source)
);

INSERT INTO test VALUES
    ( 1, 'A', 1000 ),
    ( 1, 'B', 1100 ),
    ( 1, 'C', 1200 ),
    ( 2, 'A', 2700 ),
    ( 2, 'B', 2500 ),
    ( 2, 'C', 2300 ),
    ( 3, 'A', 5200 ),
    ( 3, 'B', 5000 ),
    ( 3, 'C', 5000 )
    ;


Требуемый запрос будет выглядеть следующим образом:

SELECT DISTINCT
    product_id,
    FIRST_VALUE(source) OVER(w) AS best_source,
    FIRST_VALUE(price)  OVER(w) AS best_price
FROM test
WINDOW w AS (PARTITION BY product_id ORDER BY price)


Код:

+------------+-------------+------------+
| product_id | best_source | best_price |
+------------+-------------+------------+
|          1 | A           |       1000 |
|          2 | C           |       2300 |
|          3 | B           |       5000 |
+------------+-------------+------------+

Давайте его разберем подробнее.

Как известно, DISTINCT - это форма GROUP BY, поэтому рассмотрим сначала, как запрос выглядит и работает без него.

Запросы с window functions отличаются от запросов с GROUP BY тем, что строки в них не склеиваются в одну, а остаются в неизменном количестве, просто к колонкам добавляется результат некоторой групповой операции, одинаковый для каждой строки в группе:


SELECT
    test.*,
    FIRST_VALUE(source) OVER(w) AS best_source,
    FIRST_VALUE(price)  OVER(w) AS best_price
FROM test
WINDOW w AS (PARTITION BY product_id ORDER BY price)
 


Код:

+------------+--------+-------+-------------+------------+
| 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, а не совместно с ней. Если, например, написать вот такой запрос:

SELECT
    product_id,
    FIRST_VALUE(ANY_VALUE(source)) OVER(w) AS best_source,
    FIRST_VALUE(ANY_VALUE(price)) OVER(w) AS best_price
FROM test
GROUP BY product_id
WINDOW w AS (PARTITION BY product_id ORDER BY ANY_VALUE(price))


(ANY_VALUE() - это функция-заглушка для строгого режима работы сервера ONLY_FULL_GROUP_BY, при котором запрещается использовать колонки без агрегирующих функций. Если этот режим выключен, то ANY_VALUE(expr) - это все равно, что просто expr.)

То он сначала сгруппирует строки и только потом пробежится по ним и разобьет на блоки. Там и разбивать будет нечего, т.к. каждый блок после группировки и так будет состоять из одной строки. Результат получится не тот, что нужно:

Код:

+------------+-------------+------------+
| product_id | best_source | best_price |
+------------+-------------+------------+
|          1 | A           |       1000 |
|          2 | A           |       2700 |
|          3 | A           |       5200 |
+------------+-------------+------------+

Поэтому нужно запрос обернуть в группировку:

SELECT
    product_id,
    ANY_VALUE(best_source),
    ANY_VALUE(best_price)
FROM (
    SELECT product_id,
    FIRST_VALUE(source) OVER(w) AS best_source,
    FIRST_VALUE(price) OVER(w) AS best_price
    FROM test
    WINDOW w AS (PARTITION BY product_id ORDER BY price)
) AS t
GROUP BY product_id


Это будет группировка № 2. Если запрос переписать в форме DISTINCT, то он приобретет вид, приведенный в начале текста.

Алгоритмически получается такая картина: сначала СУБД пробегает по данным, разбивая их на блоки по product_id (группировка № 1), а потом пробегает по результату еще раз, чтобы склеить строки в традиционном GROUP BY (группировка № 1) по тому же самому product_id.

Внимание, вопрос: нельзя ли вместо двух пробегов делать один, учитывая, что группировка в обоих случаях идёт по одной и той же колонке?

Отредактированно LazY (13.04.2022 19:12:28)

Неактивен

 

#2 13.04.2022 16:56:21

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3872

Re: Window functions + GROUP BY

LazY, спасибо за интересную тему. Запрос с ANY_VALUE у меня не сработал из-за only_full_group_by, потребовалось часть ORDER BY заменить на ORDER BY ANY_VALUE(price)

Неактивен

 

#3 13.04.2022 17:34:16

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3872

Re: Window functions + GROUP BY

С оконными функциями избавиться от повторной группировки не получится. Это связано с заданным порядком исполнения: сначала запрос, потом оконные функции. Может быть, возможна оптимизация таких ситуаций на уровне плана исполнения запроса, но это потребует разработки нового способа исполнения запросов.

Думаю, что русский вариант названия window functions сохранится в виде "оконные функции". Ключевое слово "WINDOW" будет сложно представлять себе иначе, как "окно".

Неактивен

 

#4 13.04.2022 19:14:33

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 844

Re: Window functions + GROUP BY

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" будет сложно представлять себе иначе, как "окно".

Да, я тоже себя поймал на этом. Видимо, так и приживется, хотя по-русски не говорят "окно результатов" и т.п.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson