Блог

Я часто хочу получить значение смежной строки, когда я выполняю MIN MAX оператор or. Например, в следующем утверждении:

 WITH people AS (
    select 'Greg' as name, 20 as age union
    select 'Tom' as name, 17 as age
) SELECT MAX(age) FROM people;

# MAX(age)
20
 

MAX Функция выполняет эквивалент: MAX(eval_expression=age, return_expression=age) , где она всегда имеет одинаковое вычисление и возвращаемое значение (неявно). Тем не менее, я хотел бы найти name человека с максимальным возрастом. Итак, концептуальный синтаксис будет следующим: MAX(eval_expression=age, return_expression=name) . Это шаблон, который я использую довольно часто, и обычно в конечном итоге взламываю что-то вроде:

 WITH people AS (
    select 'Greg' as name, 20 as age union
    select 'Tom' as name, 17 as age
) SELECT name FROM people NATURAL JOIN (SELECT name, MAX(age) age FROM people) _;

# name
'Greg'
 

Есть ли общий способ сделать MAX(expr, return) то, что я пытаюсь выполнить?

Обновление: чтобы привести пример, где требуется агрегирование:

 with sales as (
    select DATE '2014-01-01' as date, 100 as sales, 'Fish' as product union
    select DATE '2014-01-01' as date, 105 as sales, 'Potatoes' as product union
    select DATE '2014-01-02' as date, 84 as sales, 'Salsa' as product
)  select date, max(sales) from sales group by date

# date, max(sales)
2014-01-01, 105
2014-01-02, 84
 

И как получить эквивалент: MAX(expr=sales, return=product) ? Что-то вроде:

 WITH sales AS (
    select DATE '2014-01-01' as d, 100 as revenue, 'Fish' as product union
    select DATE '2014-01-01' as d, 105 as revenue, 'Potatoes' as product union
    select DATE '2014-01-02' as d, 84 as revenue, 'Salsa' as product
) SELECT d AS date, product FROM sales NATURAL JOIN (SELECT d, MAX(revenue) AS revenue FROM sales GROUP BY d) _;

# date, product
2014-01-01, Potatoes
2014-01-02, Salsa
 

Если я здесь чего-то не упускаю — используйте limit с order by :

 WITH people AS (
    select 'Greg' as name, 20 as age union
    select 'Tom' as name, 17 as age
)
SELECT name
FROM people 
ORDER BY age DESC
LIMIT 1;

# name
'Greg'
 

Если вы хотите использовать first_value() , я бы рекомендовал:

 select distinct date, 
    first_value(product) over(partition by date order by sales desc) top_product
from sales
 

Здесь нет необходимости ни в агрегировании, ни в спецификации фрейма в функции window. Функция window обходит набор данных, начиная со строки с наибольшими продажами, поэтому всем строкам в разделе присваивается одинаковое top_product значение. Затем distinct сохраняется только одна строка на раздел.

Но в основном это заканчивается как проблема с наибольшим числом пользователей на группу, когда вам нужна строка с наибольшей продажей на каждую дату. first_value() Решение плохо масштабируется, если вы хотите использовать более одного столбца в этой строке. Типичным решением является ранжирование записей в подзапросе, а затем фильтрация. Опять же, агрегация не требуется, это только логика фильтрации:

 select *
from (
    select s.*
        row_number() over(partition by date order bys ales desc) rn
    from sales
) t
where rn = 1
 

Одним из решений было бы использовать неограниченную оконную функцию, такую как FIRST_VALUE , где вы можете сортировать раздел даты по продажам. Вот пример:

 ;WITH sales AS (
    select DATE '2014-01-01' as date, 100 as sales, 'Fish' as product union
    select DATE '2014-01-01' as date, 105 as sales, 'Potatoes' as product union
    select DATE '2014-01-01' as date, 103 as sales, 'Lettuce' as product union
    select DATE '2014-01-02' as date, 84 as sales, 'Salsa' as product
)  

SELECT DISTINCT date, LAST_VALUE(product) OVER (
    partition by date
    order by sales
    -- Default: https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html
    -- rows between unbounded preceding and current row
    rows between unbounded preceding and unbounded following
) top_product
 FROM sales group by date;

# date, top_product
'2014-01-01', 'Potatoes'
'2014-01-02', 'Salsa'
 

Я думаю subselect , что это может быть проще для чтения (по крайней мере, для меня), но это другой вариант. Вам нужно было бы проверить производительность двух, но я думаю, что аналитическая функция (без неиндексируемого соединения) будет намного быстрее.