clickhouse - 从事件中提取会话
问题描述
在 Clickhouse 中,我有一个表格列出了带有用户 ID 和标签的事件。我的目标是从这些数据中提取会话。
一个会话是一组时间相近的事件。举个例子,如果一个事件在前一个事件之后半小时以上,它就在另一个会话中。但是,如果每 1500 万个事件发生一次,则会话可能长达 12 小时。
我查看了 timeslot 函数的文档,它描述了一个与我类似的用例,但我不知道如何编写查询。(https://clickhouse.yandex/docs/en/query_language/functions/date_time_functions/#timeslot)
例如:
事件:
date | user | tag
2018-12-21 00:00:00 │ user1 │ tag1
2018-12-21 00:00:00 │ user2 │ tag1
2018-12-21 00:15:00 │ user1 │ tag1
2018-12-21 00:15:00 │ user2 │ tag2
2018-12-21 00:30:00 │ user1 │ tag1
2018-12-21 00:45:00 │ user1 │ tag1
2018-12-21 01:45:00 │ user1 │ tag1
结果会话:
date | date_end | user | tag | count
2018-12-21 00:00:00 | 2018-12-21 00:45:00 | user1 | tag1 | 4
2018-12-21 00:00:00 | 2018-12-21 00:00:00 | user2 | tag1 | 1
2018-12-21 00:15:00 | 2018-12-21 00:15:00 | user2 | tag2 | 1
2018-12-21 01:45:00 | 2018-12-21 01:45:00 | user1 | tag1 | 1
解决方案
此查询依赖于默认的timeSlot -函数将日期向下舍入到半小时:
SELECT user, tag, eventCount, length(sessionStartDateArray) sessionCount, sessionStartDateArray
FROM
(
SELECT
user,
tag,
-- a count of events with rounded date (remove DISTINCT-clause from nested query to get a real count of events).
count() as eventCount,
-- an array of rounded dates
groupArray(roundedDate) AS roundedDateArray,
-- an array of rounded dates shifted to 30 minutes (where 30 min taken from timeSlot-function)
arrayMap(i -> (i + 1800), roundedDateArray) AS shiftedRoundedDateArray,
-- to intersect two arrays to find the dates when sessions start
arrayFilter(x -> (has(shiftedRoundedDateArray, x) = 0), roundedDateArray) AS sessionStartDateArray
FROM
(
SELECT DISTINCT
user,
tag,
-- rounds down the time to the half hour.
timeSlot(date) AS roundedDate
FROM test01
)
GROUP BY user, tag
)
ORDER BY user, tag;
推荐阅读
- haskell - Haskell RIO monad 在持久化池中
- node.js - Socket.io - 会话管理的每个套接字事件的身份验证
- javascript - 如何在粘贴后(而不是在粘贴之前)添加对状态生效的EventListener?
- javascript - NodeJS Bcrypt 哈希和比较平均需要 2-3 秒。如何优化它
- substrate - 元数据生成失败,退出代码无
- javascript - 从 localStorage 中删除记录时刷新变量
- c# - ASP.NET Core - 使用 [HttpPost] 发布请求不适用于对象
- flutter - 如何使用颤振视频播放器跳转到帧?
- javascript - 如何在页面重新加载时将 HTML 弹出窗口保持在 chrome 扩展程序的前面?
- python - 如何在空间中找到 3 个正交向量的旋转矩阵。我当前的方法将矢量旋转到错误的方向