Подсчет во временном окне для каждого текущего дня в Clickhouse
У меня есть таблица с журналами пользователей, которые использовали определенный сервис. Что-то вроде таблицы ниже, каждая строка представляет собой временную метку активности и идентификатор пользователя.
Я хочу рассчитать количество уникальных пользователей за каждый день, но не только в этот день, но и за неделю до этого. По сути, уникальный счетчик движущегося окна: для дня «x» счетчик должен быть в окне «x-7 дней»: «x».
Как я вижу в документах,
Синтаксис INTERVAL для кадра DateTime RANGE OFFSET: не поддерживается, вместо этого укажите количество секунд (RANGE работает с любым числовым типом).
Простой способ использования интервала с передачей чего-то вроде RANGE INTERVAL 7 day PRECEDING не поддерживается, и они предлагают использовать диапазон с проходящими секундами, но у меня действительно нет опыта работы с диапазоном в sql, поэтому я действительно не понимаю, как вы там передаете секунды . Мой текущий код:
with cleaned_table as (
select
user_id,
date_trunc('day', timestamp) as day
from
table
)
SELECT
day,
uniqExact(user_id) OVER (
PARTITION by day ORDER BY day range ???
)
FROM
cleaned_table
Кроме того, в идеале у меня есть ощущение, что я должен где-то добавить group by, так как мне нужна только одна строка в каждый день, а не строка для каждой строки в исходной таблице, и без группировки я делаю пересчет(?) для каждой строки вместо расчет за каждый день один раз.
Ответы 2
Я отмечу это как ответ, но я был бы рад, если бы кто-нибудь знал, как оптимизировать это решение, включив group by или другие методы, чтобы не пересчитывать оконную функцию для каждой строки и вычислять ее только один раз за каждый день.
Во всяком случае, RANGE BETWEEN 6 PRECEDING and current row это то, что я искал, сработало просто отлично. Также добавлен ::date для преобразования временной метки в тип даты, а DISTINCT day позволяет напрямую выбирать только одну строку для каждого дня вместо запуска group by с any еще раз.
with cleaned_table as (
select
user_id,
date_trunc('day', timestamp)::date as day
from
table
)
SELECT
DISTINCT day,
uniqExact(user_id) OVER (
ORDER BY
day ASC RANGE BETWEEN 6 PRECEDING
and current row
) as users
FROM
cleaned_table
create table t(user_id Int64, timestamp DateTime) Engine = Memory as select * from values((831, '2022-06-22 04:37:10'), (789,'2022-06-22 12:38:57'), (831,'2022-06-22 16:40:10'), (1,'2022-06-21 12:38:57'), (2,'2022-06-20 16:40:10'));
SELECT
day,
finalizeAggregation(u) AS uniqByDay,
uniqMerge(u) OVER (ORDER BY day ASC RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS uniqBy6Days
FROM
(
SELECT
toDate(timestamp) AS day,
uniqState(user_id) AS u
FROM t
GROUP BY day
)
ORDER BY day ASC
┌────────day─┬─uniqByDay─┬─uniqBy6Days─┐
│ 2022-06-20 │ 1 │ 1 │
│ 2022-06-21 │ 1 │ 2 │
│ 2022-06-22 │ 2 │ 4 │
└────────────┴───────────┴─────────────┘
См. Как получить p95 дня и p95 последних 7 дней этого дня из Clickhouse через SQL-запрос?