SQL Query (ClickHouse): group by where timediff between values less then X

Question

I need a little help with sql-query. I'm using clickhouse, but maybe standard SQL syntax is enough for this task.

I've got the following table:

event_time; Text; ID
2021-03-16 09:00:48; Example_1; 1
2021-03-16 09:00:49; Example_2; 1
2021-03-16 09:00:50; Example_3; 1
2021-03-16 09:15:48; Example_1_1; 1
2021-03-16 09:15:49; Example_2_2; 1
2021-03-16 09:15:50; Example_3_3; 1

What I want to have at the end for this example - 2 rows:

Example_1Example2Example_3
Example_1_1Example2_2Example_3_3

Concatenation of Text field based on ID. The problem that this ID is not unique during some time interval. It's unique only for a minute as an example. So I want to concatenate only strings where the difference between first and last row is less than a minute.

Right now I've got a query like:

SELECT arrayStringConcat(groupArray(Text))
FROM (SELECT event_time, Text, ID
      FROM Test_Table
      ORDER by event_time asc)
GROUP BY ID;

What kind of condition should I add here?

score 0 · Answer 1 · answered Mar 17 '21 at 03:21

Here is an example

create table X(event_time DateTime, Text String, ID Int64) Engine=Memory;
insert into X values ('2021-03-16 09:00:48','Example_1', 1), ('2021-03-16 09:00:49','Example_2', 1), ('2021-03-16 09:00:50','Example_3', 1), ('2021-03-16 09:01:48','Example_4', 1), ('2021-03-16 09:01:49','Example_5', 1), ('2021-03-16 09:15:48','Example_1_1', 1), ('2021-03-16 09:15:49','Example_2_2', 1),('2021-03-16 09:15:50','Example_3_3', 1);

SELECT * FROM X

┌──────────event_time─┬─Text────────┬─ID─┐
│ 2021-03-16 09:00:48 │ Example_1   │  1 │
│ 2021-03-16 09:00:49 │ Example_2   │  1 │
│ 2021-03-16 09:00:50 │ Example_3   │  1 │
│ 2021-03-16 09:01:48 │ Example_4   │  1 │
│ 2021-03-16 09:01:49 │ Example_5   │  1 │
│ 2021-03-16 09:15:48 │ Example_1_1 │  1 │
│ 2021-03-16 09:15:49 │ Example_2_2 │  1 │
│ 2021-03-16 09:15:50 │ Example_3_3 │  1 │
└─────────────────────┴─────────────┴────┘

What result is expected in this case?

CH 21.3

set allow_experimental_window_functions = 1;

SELECT
    ID,
    y,
    groupArray(event_time),
    groupArray(Text)
FROM
(
    SELECT
        ID,
        event_time,
        Text,
        max(event_time) OVER (PARTITION BY ID ORDER BY event_time ASC RANGE BETWEEN CURRENT ROW AND 60 FOLLOWING) AS y
    FROM X
)
GROUP BY
    ID,
    y
ORDER BY
    ID ASC,
    y ASC

Query id: 9219a1f2-8c96-425f-9301-745fa7b88b40

┌─ID─┬───────────────────y─┬─groupArray(event_time)────────────────────────────────────────────────────────────────────┬─groupArray(Text)──────────────────────────────────┐
│  1 │ 2021-03-16 09:01:48 │ ['2021-03-16 09:00:48']                                                                   │ ['Example_1']                                     │
│  1 │ 2021-03-16 09:01:49 │ ['2021-03-16 09:00:49','2021-03-16 09:00:50','2021-03-16 09:01:48','2021-03-16 09:01:49'] │ ['Example_2','Example_3','Example_4','Example_5'] │
│  1 │ 2021-03-16 09:15:50 │ ['2021-03-16 09:15:48','2021-03-16 09:15:49','2021-03-16 09:15:50']                       │ ['Example_1_1','Example_2_2','Example_3_3']       │
└────┴─────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────┴───────────────────────────────────────────────────┘

SQL Query (ClickHouse): group by where timediff between values less then X

1 Answers1