SQL HowTo: рейтинг-за-интервал

[hmpd]

А не о кубах не думали? Пусть каждую ночь куб обновляется, а там уж можно анализировать и смотреть цифры по периодам, например YoY, в сравнении с предыдущим периодом, текущий период и т. д.

[Kilor]

Вот как раз «каждую ночь обновляется» — это зло в наших условиях: независимых клиентов много (миллионы), ждать «пока куб обновится» в Мск-ночи они не особо готовы во Владивостоке.

[hmpd]

Нет, ждать-то не надо — куб в это время остается доступен для запросов. У меня есть таблица с 40 млн транзакций в месяц, и вот по ней такие adhoc-запросы, что вы показали, делать за приемлемое время невозможно

[Kilor]

Я имел в виду не «не иметь доступа к статистике», а «не иметь актуальной статистики» в период пересчета.

[uaggster]

Я так понимаю, в MSSQL проблема решается просто построением колоночного индекса по таблице.

[Kilor]

Если я правильно понял, и речь про columnstore, то это может существенно ускорить само вычисление агрегатов, но чтобы найти «топ» их все равно придется вычислить и отсортировать — это все-таки проигрывает линейному чтению индекса.

[Drunik]

Мне кажется вы изобрели обычные материализованные представления, но где-то посередине всё сильно усложнили. Это же просто суммарные обороты по месяцам — а потом с этими оборотами можно делать что угодно — брать сумму за последние 12 месяцев, полгода, 3 года.
Не знаю как это реализовано в PostgreSQL, но в MSSQL и Oracle это нормально получается. С десятками миллионов проводок за год всё работает быстро и данные всегда гарантированно актуальные.

[Kilor]

В PG примерно это решается REFRESH MATERIALIZED VIEW CONCURRENTLY, но таки они не учитывают «текущие» изменения и требуют солидных ресурсов в моменте перегенерации.

[maxzhurkin]

Для решения проблемы вагона валенков лучше бы подошла оконная функция.
Ограничение интервала — частный случай оконной функции, но довольно топорный.
Не путать с оконными функциями SQL

[devopg]

Господи наконец-то статья о рейтингах. Ты мой герой!
Я тут уже год всем спрашиваю как устроены рейтинги по типу hot / trend и т.д. никто нихрена не знает… не формул ничего, сидят 10 лет на хабре читают, ничего не знаю, ничего подсказать не могут…
Подписался лайк, жду новый статей в этом направлении

[BugM]

А можно просто взять Кликхаус

SELECT CounterID, count(*) as last_years_hits
FROM hits_100m_obfuscated
WHERE EventDate > today() - INTERVAL 20 year
GROUP BY CounterID
ORDER BY last_years_hits DESC
limit 20

В песочнице на 100 миллионах записей не тормозит. Интервал любой по вкусу.
https://play.clickhouse.tech

[Kilor]

Технически, там используется MergeTree, что в модели статьи аналогично «суммировать от хранимых помесячных агрегатов»:

CREATE TABLE datasets.hits_100m_obfuscated (`WatchID` UInt64, `JavaEnable` UInt8, `Title` String, `GoodEvent` Int16,
  ... `CLID` UInt32) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID), EventTime)
  SAMPLE BY intHash32(UserID) SETTINGS index_granularity_bytes = 1048576, index_granularity = 8192

Конечно, много быстрее, чем по сырым данным, но медленнее простого Index Scan.
Почему-то EXPLAIN SELECT в песочнице не работает, чтобы окончательно убедиться.

[BugM]

Технически, там используется MergeTree, что в модели статьи аналогично «суммировать от хранимых помесячных агрегатов»:

Вы не пробовали документацию открывать? MergeTree не про «суммировать от хранимых помесячных агрегатов», вообще ничего общего.
Это просто сырые данные без аггрегирования.

[Kilor]

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

[BugM]

Не совсем. Партиции дают возможность сразу исключить и не обратывать те которые не попали в фильтр. Все попавшие честно сканируются по индексу и считаются.

Никаких предрассчитанных аггрегатов или чего-то подобного нет.

[Kilor]

Но индекс-то — колоночный. Грубо, там будет записано «дальше в столбце записано 100500 раз значение CounterID=123» (или как там реализовано RLE в деталях), что позволяет прочитать только такую заголовочную запись и уже иметь готовый агрегат.

[BugM]

Не попали. Индекс Кликхауса говорит что в следующих N блоках нет нужных данных.

Те блоки в которых нужные данные есть надо читать и считать аггрегат.
Поколоночно, естесвенно. База колоночная.

[Kilor]

Индекс Кликхауса говорит что в следующих N блоках нет нужных данных.

А вроде как раз «где есть»:
clickhouse.tech/docs/ru/engines/table-engines/mergetree-family/mergetree/#primary-keys-and-indexes-in-queries

[BugM]

Чуть ниже надо читать https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-data_skipping-indexes

These indices aggregate some information about the specified expression on blocks, which consist of granularity_value granules (the size of the granule is specified using the index_granularity setting in the table engine). Then these aggregates are used in SELECT queries for reducing the amount of data to read from the disk by skipping big blocks of data where the where query cannot be satisfied.

Название «Data Skipping Indexes» пусть не смущает. Все индексы в Кликхаусе такие, других нет.

[devopg]

в песочнице на 4х кластерной системе kafka ваш запрос в KSQL обрабатывается почти моментально (данных около 2 тб)

[BugM]

ClickHouse Playground gives the experience of m2.small Managed Service for ClickHouse instance (4 vCPU, 32 GB RAM) hosted in Yandex.Cloud.
От меня: vCPU — это гипертрединг ядра. 2 реальных ядра.

Согласитесь, разница принципиальная? По деньгам уходящим на железо, ественно.

[Kilor]

Это все удобно, когда требуется вычислить абстрактные неизвестные заранее агрегаты. Но если они определены заранее, то чтение топа может занять всего несколько килобайт данных, для чего хватит существенно более слабой машины, даже если дашборд смотрят сотни раз в час.

[BugM]

Приходит к вам однажды аналитик.
А потом важный клиент.
А потом продакт с новой идеей.

Писать кастомное решение под каждого гораздо сложнее и дороже чем просто обычный запрос. Аналитик так и вообще сам себе запрос написать может.

[Kilor]

Это ключевое отличие между заказной и массово-тиражной разработкой.

Если уж мы предлагаем использовать отчет в контексте готового бизнес-процесса, под который заточен и интерфейс приложения, то и работать он должен предельно быстро, создавая минимальную нагрузку. А если хочется «покрутить куб» в свое удовольствие, то там и подождать пользователь может.

[BugM]

Но ведь можно и то и другое и с маслом тоже можно.

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

Гибкие отчеты показывающие то что хочется пользователю за адекватное время на адекватном железе, вместо прибитых гвоздями отчетов показывающих то что вы считаете нужным это полезная фича.
Без нее вы обречены или отставать от соседа или тратить невообразимо дорогое время программистов на постоянные доделки.
Пресеты для типичных сценариев конечно нужны, но не только они.

[Kilor]

Все правильно. Только не «вместо», но «вместе».

Оперативный отчет на основном пути работы — предельно быстр и оптимизирован, со сложными пользовательскими фильтрами/группировками — в балансе ожиданий пользователя по времени работы, создаваемой нагрузки и стоимости разработки.