Как добавить строки в таблицу по условию в ClickHouse?

У меня есть таблица в ClickHouse с событиями подключения и отключения от системы. Запрос select timestamp, username, event from table дает следующий результат.

отметка времени имя пользователя событие 20 декабря 2022 г., 18:24 1 Соединять 20 декабря 2022 г., 18:30 1 Отключить 20 декабря 2022 г., 18:34 1 Соединять 21 декабря 2022 г., 12:07 1 Отключить 20 декабря 2022 г., 12:15 2 Соединять 20 декабря 2022 г., 12:47 2 Отключить

Сеанс должен отображаться в таблице как завершенный к концу дня. Если пользователь был подключен к системе 20 декабря и после этого в тот же день не было «Отключения», я должен добавить такое событие «Отключение» в таблицу с некоторым запросом. А также я должен добавить строку с событием «Подключение» в таблицу в 00:00 следующего дня. Например, в примере таблицы видно, что пользователь №1 не завершил сеанс 20 декабря, поэтому я хочу получить следующий результат:

отметка времени имя пользователя событие 20 декабря 2022 г., 18:24 1 Соединять 20 декабря 2022 г., 18:30 1 Отключить 20 декабря 2022 г., 18:34 1 Соединять 20 декабря 2022 г., 23:59 1 Отключить 21 декабря 2022 г., 00:00 1 Соединять 21 декабря 2022 г., 12:07 1 Отключить 20 декабря 2022 г., 12:15 2 Соединять 20 декабря 2022 г., 12:47 2 Отключить

Есть ли способ изменить запрос, чтобы он работал так, как я описал выше? ClickHouse не так распространен, как Posrgres или SQL Server, насколько мне известно, поэтому код на диалекте Postgres будет в порядке, я узнаю, как сделать то же самое с ClickHouse.

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
0
106
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Сначала определите эти события 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-рабочий пример демо

благодарить! Я сейчас пытаюсь сделать то же самое в ClickHouse. Есть ли шанс не использовать «латеральное» ключевое слово? В Clickhouse такого нет.

Daniel G 21.12.2022 14:57

Я не мог понять, как это сделать без lateral, но, возможно, эта ТАК нить может пролить больше света. Или, может быть, UDF может помочь. Или, возможно, используя процедурный язык, если ClickHouse его поддерживает.

Stefanov.sm 21.12.2022 15:08

Это оказалось намного проще и понятнее без lateral. Ответ обновлен.

Stefanov.sm 22.12.2022 15:44
Ответ принят как подходящий

Вам не нужно 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 не имеет такой гибкости с массивами, насколько я знаю.

Daniel G 23.12.2022 09:26

Я сейчас пытаюсь переписать его для Postgres, но сложность заключается в ARRAY JOIN, так как Postgres не имеет этого (я не могу найти такую ​​​​функцию в документации)

Daniel G 23.12.2022 10:05

PostgreSQL имеет ARRAY JOIN эквивалент под названием UNNEST: postgresql.org/docs/15/functions-array.html

JustMe 23.12.2022 10:31

Другие вопросы по теме