Kilor 19 янв 2022 в 16:11

SQL HowTo: считаем «уников» на интервале

4 мин

9.7K

Блог компании ТензорPostgreSQL*SQL*Администрирование баз данных*CRM-системы*

+10

Комментарии 27

hungry_forester 19 янв 2022 в 17:14

"пользователи, обращавшиеся на горячую линию на прошлой неделе", "контрагенты, оплатившие за последние 30 дней" или "потенциальные клиенты, с кем был контакт в этом квартале".

Буквально написано - списки, а не количества :)

count(DISTINCT clientid)

Вот кстати, это такое бест практис или просто "а че DISTINCT, есть такая опция, значит, она хорошая?" Нету базы с 1.5М фактов чтобы потестить. Больше есть :)

В целом, так загоняться ради 0.7 сек... Такое. С другой стороны, по-моему, оно и считать должно на такой таблице уники побыстрее... Без извращений, только с грамотной индексацией.

Kilor 19 янв 2022 в 17:32

Собственно, списки мы тоже имеем этим способом.

"а че DISTINCT, есть такая опция, значит, она хорошая?"

Ну как бы именно count(DISTINCT column) дает количество уникальных значений столбца, а count(column). даст просто колчичество неNULLовых.

так загоняться ради 0.7 сек... Такое.

Теперь предположим, что где-то в интерфейсе на дашборде у каждого из 5K сотрудников висит "метер", который считает уников по первому наивному способу...

baldr 19 янв 2022 в 23:01

Теперь предположим, что где-то в интерфейсе на дашборде у каждого из 5K сотрудников висит "метер", который считает уников по первому наивному способу...

Статья хорошая, но вот с этим вашим комментарием - не согласен категорически. Плохой пример. Если у 5К сотрудников висит "метер", который из базы периодически тянет этот показатель GET-запросом, то тут не исправить оптимизацией запроса. Тут надо либо аггрегировать предварительно для всех и рассылать push-запросом, либо где-нибудь в Redis вести отдельный счетчик, либо как-то еще решать это архитектурно.

На мой взгляд операции с базой (удаленной) - слишком дорогие чтобы такими запросами баловаться. Если база обслуживает 5К юзеров, то у нее уже наверняка есть более важные и тяжелые операции.

Я понимаю что вы придумали этот пример "на бегу" и наверняка сами понимаете его условность, но объяснить должен кто-то :)

Kilor 19 янв 2022 в 23:25

Понятно, что давать этим 5K юзеров дергать из базы один и тот же набор данных - сильно неоптимально. Но и данные у них могут быть свои у каждого (по своим сделкам), и решения с выносом операций обсчета "наружу" - в Redis, отдельный воркер, который только тем и занимается, что считает да рассылает - не всегда будет архитектурно оптимально. Баланс тут очень тонок и зависит от дополнительных факторов за рамками статьи.

MrGrey13 19 янв 2022 в 23:20

Кейс в статье вырван из контекста, конечно. Обычно это как раз преднастроенные дашборды на витринах с известной структурой, типичный хранилищный кейс. Колоночная база - и вашему сеньору не придется делать сравнение полдюжины способов расчета "количества уникальных" чтобы выиграть какие-то миллисекунды. Вместо этого он может пойти порешать действительно интересные и важные таски.

Kilor 19 янв 2022 в 23:27

Например, такие таски как написание синхронизирующих эти две базы воркеров, устранение рассогласований в них или внедрение протоколов двухфазного коммита...

Hashbash 20 янв 2022 в 00:34

Мне кажется, вы слишком усложняете себе задачу, а потом ее зачем-то решаете...

...
CREATE INDEX tbl_fact_idx ON tbl_fact (clientid);
vacuum analyze  tbl_fact;

-- 10 s 72 ms
public> select count(distinct clientid) from tbl_fact
[2022-01-20 00:32:34] 1 row retrieved starting from 1 in 10 s 72 ms (execution: 10 s 31 ms, fetching: 41 ms)


--3 s 712 ms
public> select count(*) from (select clientid from tbl_fact group by clientid) q
[2022-01-20 00:33:45] 1 row retrieved starting from 1 in 3 s 712 ms (execution: 3 s 672 ms, fetching: 40 ms)

Kilor 20 янв 2022 в 09:26

Бессмысленно обсуждать производительность запросов без их планов. А в них надо обязательно смотреть buffers - а то вполне можем попасть на ситуацию описанную в "наивном" варианте - когда приходится читать 180MB, если не повезет - с диска.

Hashbash 20 янв 2022 в 12:38

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

Kilor 20 янв 2022 в 12:48

вариант, в котором нет необходимости в сортировке значений

Который из них? Оба приведенных варианта запроса не учитывают задаваемый пользователем интервал. А если его просто добавить в запрос - перестанет нормально помогать индекс (clientid).

Hashbash 20 янв 2022 в 13:50

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

Kilor 20 янв 2022 в 13:58

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

Aggregate (actual time=4633.054..4633.056 rows=1 loops=1)
  Buffers: shared hit=10783998 read=30552
  ->  Group (actual time=0.076..4370.153 rows=4889090 loops=1)
        Group Key: tbl_fact.clientid
        Buffers: shared hit=10783998 read=30552
        ->  Index Only Scan using tbl_fact_idx on tbl_fact (actual time=0.072..3056.843 rows=19036668 loops=1)
              Heap Fetches: 0
              Buffers: shared hit=10783998 read=30552
Planning:
  Buffers: shared hit=17 read=1
Planning Time: 0.960 ms
Execution Time: 4633.278 ms

Но наличие даты в условии сразу все ломает.

Hashbash 20 янв 2022 в 14:23

Не ломает

create index tbl_fact_idx2 on tbl_fact(clientid, dt);
explain analyze
select count(*) from (select clientid from tbl_fact
where dt between '2021-01-01' and '2021-12-01' group by clientid) q;

Aggregate  (cost=641207.12..641207.13 rows=1 width=8) (actual time=5522.464..5522.466 rows=1 loops=1)
  ->  Group  (cost=0.44..594183.54 rows=3761887 width=4) (actual time=55.265..5263.736 rows=4817602 loops=1)
        Group Key: tbl_fact.clientid
        ->  Index Only Scan using tbl_fact_idx2 on tbl_fact  (cost=0.44..552706.71 rows=16590731 width=4) (actual time=0.095..3838.740 rows=16545711 loops=1)
              Index Cond: ((dt >= '2021-01-01'::date) AND (dt <= '2021-12-01'::date))
              Heap Fetches: 0

Kilor 20 янв 2022 в 14:42

Без buffers ненаглядно, но - стало медленнее, 5 сек вместо 3 сек?

Это вполне соотносится с "плохим" использованием индекса "без начала": https://habr.com/ru/company/tensor/blog/488104/

Hashbash 20 янв 2022 в 14:58

Нет, выполнение те же 3 секунды

public> select count(*) from (select clientid from tbl_fact
            where dt between '2021-01-01' and '2021-12-01' group by clientid) q
[2022-01-20 14:57:33] 1 row retrieved starting from 1 in 3 s 322 ms (execution: 3 s 302 ms, fetching: 20 ms)

Kilor 20 янв 2022 в 15:16

В приведенном выше плане общее время 5522.466 - и вот как раз так может играть чтение из кэша/с диска.

vasyakolobok77 20 янв 2022 в 12:58

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

Hashbash 20 янв 2022 в 13:53

Тогда бы и первый стал бы работать быстрее при повторных запусках, а этого не происходит. Ну плюс план разный, первый seq скан, второй index scan only. + сортировка перед каунтом.

miksoft 20 янв 2022 в 00:37

А если построить предагрегат для всех возможных диапазонов дат?
Это не так уж много — за 10 лет всего 6,66 млн. записей.
Тогда всего одну запись дернуть по индексу — может оказаться меньше миллисекунды, если индекс в кэше.

Kilor 20 янв 2022 в 09:24

Вариант вполне реализуемый, но каждая такая запись будет хранить массив примерно от 100K (в день) до 5M (за все время) идентификаторов.

Если прикинуть просто по среднему, получится 6.66M (записей) x 2.5M (идентификаторов) x 4 (байта) ~= 66.6TB, даже без учета индексов.

miksoft 23 янв 2022 в 16:09

Зачем хранить массив идентификаторов?
Мы исследуем count(DISTINCT clientid). И в других вариантах получаем его же. Вот его и давайте хранить.

Kilor 23 янв 2022 в 18:14

Если не хранить массив, то непонятно, как эти числа складывать.

В первый день 2 уника: {1,2}, во второй - тоже 2 уника: {2,3}. А за оба дня вместе? 2 + 2 = ... 3: {1,2,3}.

miksoft 27 янв 2022 в 23:04

Складывать на основе сырых данных, они никуда не денутся.
В день N посчитать count(DISTINCT clientid) для всех интервалов 1..N — N и записать в таблицу с предагрегатом.

Ivan22 20 янв 2022 в 16:33

Неплохо, но в нашей реаьности у юзеров перед глазами дашборды в PowerBI и они само собой не в DirectQuery работают а в своем кеше загруженном и там считает DAX дистинкт каунты, а не sql постгресовый

mrShadow 29 янв 2022 в 13:52

Спасибо, заодно узнал про GROUP by <номер> и ORDER BY <номер>
https://stackoverflow.com/questions/7392730/what-does-sql-clause-group-by-1-mean

akhkmed 11 апр 2022 в 14:09

Для расчёта медианы, например, есть приближённые подходы к вычислению.

А кто знает приближённые подходы к подсчёту count(distinct user_id) ?

Ivan22 23 авг 2022 в 17:51

знать среднее количество операций у каждого юзера - и делить общий count(user_id) на это среднее. (Но будет весьма приближенно)

Зарегистрируйтесь на Хабре, чтобы оставить комментарий