Крайнее значение: argMax без окна
Чему научишься
- отвечать на вопрос «что было в момент максимума» одним агрегатом:
argMaxиargMin - возвращать сразу пару значений:
argMax((a, b), t) - показывать, какую дополнительную работу при том же ответе добавляет оконная форма, и находить её в плане
- различать
argMax,any,anyLastиtopKи знать, какой из них не обещает конкретного значения
Вопрос у окна выдачи
К окну подходит посетитель с карточкой № 1: «Я был у вас на прошлой неделе, открывал что-то по навигации, — что именно?» В журнале зала есть все открытия карточек за март. Нужно найти последнее открытие этого посетителя.
Первое решение напрашивается само: отсортировать записи посетителя по времени и взять верхнюю. В зале это можно сделать оконной функцией — пронумеровать записи внутри каждого посетителя и оставить первую. Ответ будет верным.
Но цена у такого решения есть. В зале 23 565 записей и 791 посетитель с открытиями. Чтобы найти по одной последней записи, движку придётся отсортировать по времени всё прочитанное внутри каждой группы.
Значит, задача здесь не только в том, чтобы получить правильный ответ. Нужно проверить, можно ли вернуть ту же крайнюю запись без полной сортировки.
Для этого в ClickHouse есть обычный .
argMax, справа — через оконную функцию. Оба способа ищут последнюю запись каждого посетителя; первая строка отвечает посетителю у окна. Последняя колонка показывает, у скольких из 791 посетителя результаты разошлись.Значение в момент максимума
argMax(что_вернуть, по_чему_искать_максимум) отвечает не на вопрос «каков максимум?», а на вопрос «что лежало рядом с максимумом?». Второй аргумент здесь будем называть спутником. Движок находит его наибольшее значение и возвращает первый аргумент из той же строки. argMin устроен так же, но ищет наименьшее значение спутника.
Для задачи у окна это именно то, что нужно: время определяет крайнюю запись, а материал из этой записи становится ответом. Никакой сортировки всей группы не требуется — достаточно одного состояния на группу.
Первым аргументом можно передать пару или тройку. Такой набор значений в круглых скобках называется кортежем; вернёт его целиком из выбранной строки.
В ячейке колонка «материал и зал» показывает обе величины ИЗ ОДНОЙ И ТОЙ ЖЕ крайней записи. Это важная деталь: два отдельных argMax по разным колонкам при ничьей могут выбрать разные строки.
Ничья по спутнику
Теперь нужно разобрать пограничный случай. Что вернёт argMax, если максимальное значение второго аргумента встретилось дважды?
Любое из подходящих значений. Какое именно, ClickHouse не обещает. В зале ничьих нет: у всех 791 посетителя последнее открытие единственное, поэтому колонка расхождений показывает 0.
Если ничьи возможны, правило выбора нужно задать явно. argMax(x, (t, id)) сравнивает пару, и при равном времени побеждает больший id — ответ становится определённым.
Три соседа, которых легко перепутать
Рядом с argMax есть функции, которые выглядят подходящими, но отвечают на другие вопросы.
any(x)— «любое значение из группы». Не первое и не последнее: движок читает куски параллельно и возвращает то, которое попалось раньше. На тех же данных два прогона могут дать разные ответы, и это не дефект — функция именно это и обещает.anyLast(x)— «последнее ПРОЧИТАННОЕ», а не последнее по времени. Порядок чтения никак не связан с твоей колонкой времени.topK(n)(x)— n самых частых значений, а не самых поздних. Это ответ на совсем другой вопрос.
Из этих четырёх только argMax связывает возвращаемое значение с явно заданным спутником. Поэтому для вопроса «что было в момент максимума?» выбирать нужно его.
Отдельно: argMax уже встречался как приём ДЕДУПЛИКАЦИИ — «оставить последнюю версию строки» в главе про колоночное хранилище. Здесь задача другая: не убрать дубли, а ответить на вопрос о крайнем значении. Дублей в зале нет, а последнее открытие спрашивают каждый день.
КВЕРИ: Оконная форма отсортировала весь март ради одной строки. Ответ верный. Теперь загляни в план и посчитай, сколько стоила эта аккуратность.
argMax агрегирует, а оконная форма добавляет сортировку. Поэтому здесь цену нужно сравнивать уже по плану. Строка чтения таблицы из плана убрана: в ней есть имя базы, а оно у каждого исполнения своё.argMax одно состояние на группу и четыре шага плана, а оконная форма добавляет сортировку — всего получается шесть шагов.Вопрос с собеседования
Как это спрашивают на собеседовании
Частая формулировка: «последнее значение по каждому пользователю — как напишете?» Ответ через с нумерацией примут. Но следом обычно спрашивают, можно ли решить задачу без неё, — и ждут argMax.
Здесь важно знать оба способа. Оконная функция отвечает правильно, но требует дополнительной работы после чтения. argMax решает тот же вопрос обычной агрегацией.
Второй вопрос: «чем argMax отличается от any?» Ответ короткий: any не обещает, какое именно значение вернёт; он гарантирует лишь, что это будет значение из этой группы. Строить на нём отчёт, который потом будут сверять, нельзя.
Третий вопрос проверяет уже крайний случай: «как сделать ответ определённым при равных временных отметках?» Нужно сравнивать кортеж, добавив в него уникальный столбец.
- Самый дорогой купленный товар каждого пользователяEASY
- Самый дешёвый купленный товар каждого пользователяEASY
Главное из урока
| вопрос | приём | цена |
|---|---|---|
| Что посетитель открывал последним? | argMax(title_id, event_ts) | 23 565 строк, 4 шага плана, без сортировки |
| То же самое | с нумерацией | 23 565 строк, 6 шагов плана, одна сортировка |
На чтении разницы нет: оба способа проходят те же 23 565 строк. Разница начинается ПОСЛЕ чтения. Оконной форме нужно ещё отсортировать записи по порядку, а argMax держит крайнее значение прямо во время агрегации.
Отсюда правило, которое пригодится в главе про раскладку: прочитанное — первая мера цены, но не единственная. Если число прочитанных строк совпало, смотри в план и проверяй, что движок делает дальше.
И ответ посетителю у окна нашёлся: материал 117, северный зал, 25 марта.