Комментарии 9
Интересный подход!
На самом деле, помогла бы оконка с range between, если бы в CH такое было реализовано (для 2 задачи), но а так получается интересный подход в ее отсутствие
Да, в Exasol, к примеру, задача решалась бы как:
SUM(gmv) OVER (PARTITION BY car_id ORDER BY order_created_at DESC RANGE BETWEEN INTERVAL '24' HOUR PRECEDING AND CURRENT ROW) AS NEXT_24_HOURS_GMV_SUM
Было бы интересно замерить разницу в производительности между этими тремя методами) Но а так да, в условиях отсутствия функционала БД пришлось изобретать решение)
Что-то выглядит сложно и медленно.
Так-то если по уму - находим в телеметрии случаи превышения и соединяем их с машинами. Оно и в лоб будет неплохо работать, а если чуть постараться - вообще моментально.
select from car c where c.id in(select c_id from telemetry t where t.telemetry_created_at_dttm>now()-make_interval(weeks:=3) and t.speed>140)
В каршеринге яндекса 17 тыс. машин; при пятиминутных интервалах за три недели - около 5 млн строк, ни о чем.
Какая-то антиреклама, "все должно делаться медленно и неправильно"
Да, где-то тут self join совершенно непонятно.
В нашем кейсе мы хотим не просто “найти машины, по котором были превышения”, а привязать каждое превышение к конкретной аренде, чтобы определить нарушителя. А для этого уже нужно сопоставить временные интервалы телеметрии и заказов — вот тут и начинаются сложности. Простого джоина по ключу недостаточно, приходится искать пересечения по времени.
Про производительность — в конце статьи есть таблица с замерами. Можно проверить на своих или тестовых данных, что работает лучше :)
Подход замечательный, но тут, как водится есть много нюансов. И с массивами их прямо много.
Для того чтобы это работало быстро, нужны следующие, не указанные в статье условия:
1. Данных должно быть немного, либо вам не нужно делать по ним поиск.
Почему - для списков кликхаус не строит файл засечек, и не имеет встроенных механизмов поддержания упорядоченности и алгоритма бинарного поиска по массиву. Переход на массивы поднимает сложность поиска до линейной (перебор массива в лоб). При малых выборках это не страшно, и может работать эффективнее, но если у вас в массиве хотя бы десятки тысяч записей, это будет проблемой (засечки работают обычно от 8к).
2. У вас должна быть возможность хранить такой массив в MergeTree или ReplacingMergeTree, т.е. вы записываете его один раз и не дополняете, либо перезаписываете целиком, и при этом его сортируете (руками). Дело в том, что данные хранятся на диске сжато, а сортированные данные сжимаются гораздо лучше. Поэтому плоские таблицы без массивов всегда упорядочены и хорошо жмуться. Если ваш массив будет неупорядочен, он будет занимать существенно больше места на диске. А чтение с диска самая медленная операция. Что вместе с предыдущим (нет засечек, и массив всегда читается весь) может оказать драматически негативный эффект на производительность. Т.е. эффективно будет работать, только если вы этот массив руками записываете - если использовать AggregatingMergeTree, то в нем нет simple аггрегации массивов с сортировкой и/или дедупликацией. И там варианты либо не сортированный массив(который плохо жмется), либо аггрегационный тип хранилища, который еще больше занимает места и требует дообработки (и будет еще медленнее). Могут еще и дубликаты пробегать.
3. Данных в массиве не больше 1млн штук. Это вроде бы по документации лимит массива. Для большинства задач этого достаточно, но стоит всегда об этом помнить, чтобы внезапно не упереться, и не обнаружить что массивы совсем не подходят и все надо переделывать. В статье об этом сказано, но не указано число.
При этом, если вы ни под одно из этих ограничений не попадаете, можно действительно получить существенный прирост к производительности (я вроде бы до x2 выжимал) и потреблению ресурсов (CPU и память). Но если они у вас есть - эффект будет противоположный.
Спасибо, очень ценный комментарий! Более сжато, но в "подводных камнях" подсветил и предложил решение по проблемам 1 и 3: разбивать данные на партии (например, грузить по месяцам или более короткий промежуток, искать баланс производительности).
В целом, как вы правильно сказали — всё зависит от масштаба, характера данных и конкретной задачи. Если аккуратно управлять размерами и понимать компромиссы, можно получить прирост в x2 и более, а также сэкономить память. Но если просто “включить массивы везде” — можно легко сделать только хуже.
А тут точно не опечатка?
argMax(speed, telemetry_created_at_dttm) AS max_speed, -- максимальная скорость за 5-минутный интервализ документации
argMax(arg, val):Эта функция возвращает значение
argдля максимального значенияval.
Т.е. в данном случае мы получаем последнее значение speed в конце интервала. Максимальное значение мы получили бы просто через max(speed)
«Посчитай сумму только за следующие 24 часа.»
Обычный подход — это self-join:
Соединяем каждое событие со всеми возможными интервалами аренды;
Проверяем условия через WHERE или ON с использованием BETWEEN.
select field1, ..., fieldN from some_table where timestamp between some_time + toHour(24)
Зачем здесь соединять каждое событие со всеми возможными интервалами аренды?
Массивы вместо self-join: как писать быстрые запросы в ClickHouse