SQL查询(ClickHouse):根据小于X的值之间的timediff进行分组

Question

我需要一些关于sql查询的帮助。我使用的是clickhouse，但也许标准的SQL语法就足够完成这项任务了。

我有下表：

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

我想在这个例子的末尾有什么——2行：

Example_1Example2Example_3
Example_1_1Example2_2Example_3_3

基于ID的文本字段的连接。此ID在某个时间间隔内不是唯一的问题。作为一个例子，它只有一分钟的独特性。所以我只想连接第一行和最后一行之间的差小于一分钟的字符串。

现在我有一个类似的查询：

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

我应该在这里添加什么样的条件？

Answer 1

下面是一个示例

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 │
└─────────────────────┴─────────────┴────┘

在这种情况下，预计会有什么结果？

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']       │
└────┴─────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────┴───────────────────────────────────────────────────┘