У меня есть таблица в ClickHouse с событиями подключения и отключения от системы.
Запрос select timestamp, username, event from table
дает следующий результат.
Сеанс должен отображаться в таблице как завершенный к концу дня. Если пользователь был подключен к системе 20 декабря и после этого в тот же день не было «Отключения», я должен добавить такое событие «Отключение» в таблицу с некоторым запросом. А также я должен добавить строку с событием «Подключение» в таблицу в 00:00 следующего дня. Например, в примере таблицы видно, что пользователь №1 не завершил сеанс 20 декабря, поэтому я хочу получить следующий результат:
Есть ли способ изменить запрос, чтобы он работал так, как я описал выше? ClickHouse не так распространен, как Posrgres или SQL Server, насколько мне известно, поэтому код на диалекте Postgres будет в порядке, я узнаю, как сделать то же самое с ClickHouse.
Сначала определите эти события Disconnect, которым должна предшествовать пара Disconnect/Connect. Это t
CTE, overnight
атрибут. Затем вставьте пару Disconnect/Connect в the_table
для каждой записи t
с overnight
true.
with t as
(
select *,
"timestamp"::date > lag("timestamp") over (partition by username order by "timestamp")::date overnight
from the_table
where "event" = 'Disconnect'
)
insert into the_table ("timestamp", "username", "event")
select date_trunc('day', "timestamp") - interval '1 second', "username", 'Disconnect'
from t where overnight
union all
select date_trunc('day', "timestamp"), "username", 'Connect'
from t where overnight;
DB-рабочий пример демо
Я не мог понять, как это сделать без lateral
, но, возможно, эта ТАК нить может пролить больше света. Или, может быть, UDF может помочь. Или, возможно, используя процедурный язык, если ClickHouse его поддерживает.
Это оказалось намного проще и понятнее без lateral
. Ответ обновлен.
Вам не нужно lateral
соединение для достижения желаемого результата в Clickhouse (JOINs
в Clickhouse всегда выполняются тяжелые вычисления, поскольку это хранилище столбцов. ClickHouse берет нужную таблицу и создает для нее хеш-таблицу в ОЗУ).
Вы можете использовать UNION ALL
и ARRAY JOIN
особым образом для создания отсутствующих строк:
CREATE TABLE connections
(
`timestamp` DateTime,
`username` LowCardinality(String),
`event` enum('Connect', 'Disconnect')
)
ENGINE = Memory;
INSERT INTO connections VALUES
('2022-12-20 18:24:00'::DateTime, '1', 'Connect')
('2022-12-20 18:30:00'::DateTime, '1', 'Disconnect')
('2022-12-20 18:34:00'::DateTime, '1', 'Connect')
('2022-12-21 12:07:00'::DateTime, '1', 'Disconnect')
('2022-12-20 12:15:00'::DateTime, '2', 'Connect')
('2022-12-20 12:47:00'::DateTime, '2', 'Disconnect');
SELECT * FROM
(
SELECT
timestamp, username, event
FROM
connections
UNION ALL
SELECT
timestamp, username, event
FROM
(
SELECT
[toStartOfDay(timestamp) + INTERVAL '1 DAY' - INTERVAL '1 SECOND',
toStartOfDay(timestamp) + INTERVAL '1 DAY' ] timestamps,
username,
[ 'Disconnect', 'Connect' ] :: Array(Enum('Connect', 'Disconnect')) events
FROM
connections
GROUP BY
toStartOfDay(timestamp), username
HAVING
anyLast(event) = 'Connect'
) ARRAY JOIN
timestamps AS timestamp,
events AS event
)
ORDER BY
username, timestamp
Вот результат:
┌───────────timestamp─┬─username─┬─event──────┐
│ 2022-12-20 18:24:00 │ 1 │ Connect │
│ 2022-12-20 18:30:00 │ 1 │ Disconnect │
│ 2022-12-20 18:34:00 │ 1 │ Connect │
│ 2022-12-20 23:59:59 │ 1 │ Disconnect │
│ 2022-12-21 00:00:00 │ 1 │ Connect │
│ 2022-12-21 12:07:00 │ 1 │ Disconnect │
│ 2022-12-20 12:15:00 │ 2 │ Connect │
│ 2022-12-20 12:47:00 │ 2 │ Disconnect │
└─────────────────────┴──────────┴────────────┘
8 rows in set. Elapsed: 0.011 sec.
Спасибо, сэр! это отлично работает и не требует предложения insert into
, которое я не могу реализовать из-за режима только для чтения. Еще один вопрос - знаете ли вы, что мне нужно изменить в этом запросе, чтобы он работал на Postges или MSSQL? У меня очень похожая задача в другой базе данных. MSSQL не имеет такой гибкости с массивами, насколько я знаю.
Я сейчас пытаюсь переписать его для Postgres, но сложность заключается в ARRAY JOIN, так как Postgres не имеет этого (я не могу найти такую функцию в документации)
PostgreSQL имеет ARRAY JOIN
эквивалент под названием UNNEST
: postgresql.org/docs/15/functions-array.html
благодарить! Я сейчас пытаюсь сделать то же самое в ClickHouse. Есть ли шанс не использовать «латеральное» ключевое слово? В Clickhouse такого нет.