mysql - Clickhouse 按顺序时间和特定类型查找事件对

Question

我events在 clickhouse 有桌子。当某个用户（由 user_id 定义）进入房间（由 object_id 定义）或离开房间时，应打开由钥匙卡打开的电子锁，因此对于与锁的每次交互，events表中都有一条记录，相应event_type设置为“来”或“左”。

CREATE TABLE default.events
(
 `user_id` UInt32,         -- user of event
 `object_id` UInt32,       -- object of event
 `event_type` String,      -- type of event, can be "came" or "left"
 `event_moment_date` Date, -- date of event
 `event_moment` DateTime   -- datetime of event 
)
ENGINE = MergeTree(
    event_moment_date,
    (
        object_id,
        user_id,
        event_moment
    ),
8192)

我需要以以下形式输出数据：

user_id,     -- user 
object_id,   -- object
came_moment, -- moment then user entered the room
left_moment  -- moment then user leaved the room

因此，对于每个“来”事件，我需要找到匹配的“左”事件，该事件具有相同的 user_id 和 object_id，并且相对于“来” event_moment 在最近可能的将来具有 event_datetime。

我可以在 MySQL/Postgrsql 中使用这样的子查询轻松地做到这一点：

SELECT
    s1.object_id, 
    s1.user_id, 
    s1.action_moment as "came_moment", 
    (
        select s2.action_moment from source as s2 
        where 
            s1.user_id = s2.user_id 
        and 
            s1.object_id = s2.object_id
        and 
            s1.action_moment < s2.action_moment
        and
            s2.action_type = 'left'
        order by s2.action_moment asc
        limit 1
    ) as "left_moment"
FROM
source as s1
where s1.action_type = 'came'

但是 Clickhouse 不允许在子查询（依赖子查询）中使用外部查询字段。

我也尝试过使用 JOIN，但 Clickhouse 不允许在 JOIN 的 ON 语句中使用“<”或“>”。此外，我尝试使用neighbourClickhouse 的功能，按 event_moment 排序，但通过排序数据和选择下一个相邻行，我只选择了具有特定came_moment和对应的单行left_moment，似乎我无法加载此的完整列表种行。

我开始认为我的任务在 ClickHouse 中是不可能的，我需要某种脚本（php/python/whatever）来扫描数据库并添加一些“session_id”字段，该字段应该每两行具有相同的 id 号分别对应“来”和“离开”。

标签： mysqlsqlclickhouse