lagpartitionclickhouse

function same as lag partition by in clickouse

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: enter image description here

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.

Solution

  • 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 │                ᴺᵁᴸᴸ │
└─────────┴─────────────────────┴─────────────────────┘
*/