在Clickhouse中实现具有正确小计的重叠GROUP BY组的方法

Question

假设以下模式：

CREATE TABLE test
(
date Date, 
user_id UInt32,
user_answer UInt8,
user_multi_choice_answer Array(UInt8),
events UInt32
)
ENGINE = MergeTree() ORDER BY date;

和内容：

INSERT INTO test VALUES
('2020-01-01', 1, 5, [2, 3], 15),
('2020-01-01', 2, 6, [1, 2], 7);

假设我想进行查询"给我 # 个用户和按日期和user_answer分组的 # 个事件，并带有小计"。这很简单：

select date, user_answer, count(distinct user_id), sum(events) from test group by date, user_answer with rollup;
┌───────date─┬─user_answer─┬─uniqExact(user_id)─┬─sum(events)─┐
│ 2020-01-01 │           5 │                  1 │          15 │
│ 2020-01-01 │           6 │                  1 │           7 │
│ 2020-01-01 │           0 │                  2 │          22 │
│ 0000-00-00 │           0 │                  2 │          22 │
└────────────┴─────────────┴────────────────────┴─────────────┘

我不能轻易做的是使用重叠的组进行查询，例如按多项选择题的个性化选项分组时。例如：

#

• 用户数和 # 个事件(按日期和user_multi_choice_answer分组(，并包含小计
• # 个用户及其事件按任意手写分组条件分组，例如"将 user_answer=5 和 has(user_multi_choice_answer， 1( 的用户与使用 has(user_multi_choice_answer， 2( 的用户进行比较">

例如，对于第一个查询，我希望看到以下内容：

┌───────date─┬─user_multi_choice_answer─┬─uniqExact(user_id)─┬─sum(events)─┐
│ 2020-01-01 │                        1 │                  1 │          15 │
│ 2020-01-01 │                        2 │                  2 │          22 │
│ 2020-01-01 │                        3 │                  1 │           7 │
│ 2020-01-01 │                        0 │                  2 │          22 │
│ 0000-00-00 │                        0 │                  2 │          22 │
└────────────┴──────────────────────────┴────────────────────┴─────────────┘

第二个：

┌─my_grouping_id─┬─uniqExact(user_id)─┬─sum(events)─┐
│              1 │                  1 │          15 │ # users fulfilling arbitrary condition #1
│              2 │                  2 │          22 │ # users fulfilling arbitrary condition #2
│              0 │                  2 │          22 │ # subtotal
└────────────────┴────────────────────┴─────────────┘

我能得到的最接近的是使用arrayJoin()：

select date, arrayJoin(user_multi_choice_answer) as multi_answer, count(distinct user_id), sum(events)
from test group by date, multi_answer with rollup;
select arrayJoin(
arrayConcat(
if(user_answer=5 and has(user_multi_choice_answer, 3), [1], []),
if(has(user_multi_choice_answer, 2), [2], [])
)
) as my_grouping_id, count(distinct user_id), sum(events)
from test group by my_grouping_id with rollup;

但这不是一个好的解决方案，原因有两个：

• 虽然它计算正确的分组结果，但小计sum(events)的结果不正确(因为重复的行计数多次(
• 这似乎效率不高，因为它会产生大量数据重复(而我只想将同一行聚合到几个组中(

因此，我正在寻找一种方法，使我可以轻松地对多项选择题的答案进行分组，并在某些列上按任意条件进行摸索。我可以更改模式以实现这一目标，但我主要希望 Clickhouse 有一种内置的方式来实现这一目标。

Answer 1

虽然它计算了正确的分组结果，但 sum(events( 的结果对于小计不正确(因为重复的行计数多次(

您可以手动创建my_grouping_id = 0而不使用汇总。例如

select arrayJoin(
arrayConcat(
[0],
if(user_answer=5 and has(user_multi_choice_answer, 3), [1], []),
if(has(user_multi_choice_answer, 2), [2], [])
)
) as my_grouping_id, count(distinct user_id), sum(events)
from test group by my_grouping_id

这似乎效率不高，因为它会产生大量数据重复(而我只想将同一行聚合到几个组中(

目前不可能。但我看到了可能性。我会尝试制作一个GROUP BY ARRAY的 POC.这似乎是一个有效的用例。