功能与clickouse中的滞后分区相同 [英] function same as lag partition by in clickouse
本文介绍了功能与clickouse中的滞后分区相同的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要知道每个用户的订购频率.我的意思是每个用户的2个订购时间之间存在差异. 在SQL中,我使用了延迟分区依据"但我不知道如何在点击房子中计算出这一点. 我需要以下数据:
I need to know the frequency of order for each user. I mean difference between 2 order time for each user. In SQL I used "Lag Partition by" but I don't know how I can calculate this in click house. I need this data:
首先,我应该使用user_id和created_at对数据进行排序,然后我需要为行中的每个用户ID设置下一个订购时间.我不能使用邻居功能,因为它无法按user_id进行分区.
at first I should sort data with user_id and created_at then I need to have next order time for each user id in row. I can't use neighbor function because it can't do partition by user_id.
推荐答案
I didn't understand why neighbor cannot be used in your case, but it should works well:
SELECT
user_id,
created,
if(neighbor(user_id, 1, NULL) != user_id, NULL, neighbor(created, 1, NULL)) AS next_created
FROM
(
SELECT
number % 3 AS user_id,
now() + (number * 360) AS created
FROM numbers(11)
ORDER BY
user_id ASC,
created ASC
)
/*
┌─user_id─┬─────────────created─┬────────next_created─┐
│ 0 │ 2020-10-21 16:00:21 │ 2020-10-21 16:18:21 │
│ 0 │ 2020-10-21 16:18:21 │ 2020-10-21 16:36:21 │
│ 0 │ 2020-10-21 16:36:21 │ 2020-10-21 16:54:21 │
│ 0 │ 2020-10-21 16:54:21 │ ᴺᵁᴸᴸ │
│ 1 │ 2020-10-21 16:06:21 │ 2020-10-21 16:24:21 │
│ 1 │ 2020-10-21 16:24:21 │ 2020-10-21 16:42:21 │
│ 1 │ 2020-10-21 16:42:21 │ 2020-10-21 17:00:21 │
│ 1 │ 2020-10-21 17:00:21 │ ᴺᵁᴸᴸ │
│ 2 │ 2020-10-21 16:12:21 │ 2020-10-21 16:30:21 │
│ 2 │ 2020-10-21 16:30:21 │ 2020-10-21 16:48:21 │
│ 2 │ 2020-10-21 16:48:21 │ ᴺᵁᴸᴸ │
└─────────┴─────────────────────┴─────────────────────┘
*/
这篇关于功能与clickouse中的滞后分区相同的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文