如何用ClickHouse uniqTheta系列函数改写Druid Theta Sketch交集查询？

根据Druid文档，DS_THETA函数可处理sketches。现需使用ClickHouse的uniqTheta系列函数改写以下Druid查询：

SELECT THETA_SKETCH_ESTIMATE(
         THETA_SKETCH_INTERSECT(
           DS_THETA(theta_uid) FILTER(WHERE "show" = 'Bridgerton' AND "episode" = 'S1E1'),
           DS_THETA(theta_uid) FILTER(WHERE "show" = 'Bridgerton' AND "episode" = 'S1E2')
         )
       ) AS users
FROM ts_tutorial

我尝试用物化视图方案解决，创建了如下表和数据：

CREATE TABLE ts_tutorial
(
   date_id Date,
   uid String,
   show String,
   episode String
)
ENGINE = MergeTree()
ORDER BY (date_id, show, episode, uid);

CREATE TABLE tutorial_tbl
(
   date_id Date,
   show String,
   episode String,
   theta_uid AggregateFunction(uniqTheta, String)
)
ENGINE = AggregatingMergeTree()
ORDER BY (date_id, show, episode);

CREATE MATERIALIZED VIEW IF NOT EXISTS tutorial_mv TO tutorial_tbl
AS
    SELECT
        date_id,
        show,
        episode,
        uniqThetaState(uid) as theta_uid

    FROM ts_tutorial
    GROUP BY date_id, show, episode
;

INSERT INTO ts_tutorial VALUES ('2022-05-19','alice','Game of Thrones','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-19','alice','Game of Thrones','S1E2');
INSERT INTO ts_tutorial VALUES ('2022-05-19','alice','Game of Thrones','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-19','bob','Bridgerton','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-20','alice','Game of Thrones','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-20','carol','Bridgerton','S1E2');
INSERT INTO ts_tutorial VALUES ('2022-05-20','dan','Bridgerton','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-21','alice','Game of Thrones','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-21','carol','Bridgerton','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-21','erin','Game of Thrones','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-21','alice','Bridgerton','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-22','bob','Game of Thrones','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-22','bob','Bridgerton','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-22','carol','Bridgerton','S1E2');
INSERT INTO ts_tutorial VALUES ('2022-05-22','bob','Bridgerton','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-22','erin','Game of Thrones','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-22','erin','Bridgerton','S1E2');
INSERT INTO ts_tutorial VALUES ('2022-05-23','erin','Game of Thrones','S1E1');
INSERT INTO ts_tutorial VALUES ('2022-05-23','alice','Game of Thrones','S1E1');

为查询“有多少用户同时观看了《Bridgerton》的两集”，执行了以下语句：

SELECT finalizeAggregation(
         uniqThetaIntersect(
           uniqThetaStateIf(theta_uid, show = 'Bridgerton' AND episode = 'S1E1'),
           uniqThetaStateIf(theta_uid, show = 'Bridgerton' AND episode = 'S1E2')
         )
       ) AS users
FROM tutorial_mv

但查询返回0，正确结果应为1（用户carol同时观看了两集），求解决办法。

原因分析 #

当前查询逻辑错误：uniqThetaStateIf用于聚合过程中按条件筛选数据生成sketch，但tutorial_mv中的theta_uid已经是按date_id, show, episode聚合完成的sketch。此时全局扫描表并使用uniqThetaStateIf，会因大部分行不满足条件生成空sketch，最终导致交集结果为0。

正确方案 #

方案1：直接查询原表（无需物化视图） #

若数据量不大，可直接在原表上计算交集，无需预聚合：

SELECT uniqThetaIntersect(
    uniqThetaStateIf(uid, show = 'Bridgerton' AND episode = 'S1E1'),
    uniqThetaStateIf(uid, show = 'Bridgerton' AND episode = 'S1E2')
) AS users
FROM ts_tutorial

方案2：基于现有物化视图的正确查询 #

要利用预聚合的物化视图，需先分别提取两个目标分组的sketch，再计算交集：

WITH
    (SELECT theta_uid FROM tutorial_tbl WHERE show = 'Bridgerton' AND episode = 'S1E1') AS sketch_e1,
    (SELECT theta_uid FROM tutorial_tbl WHERE show = 'Bridgerton' AND episode = 'S1E2') AS sketch_e2
SELECT uniqThetaFinalize(uniqThetaIntersect(sketch_e1, sketch_e2)) AS users

方案3：优化物化视图结构（支持灵活多维度交集） #

若需频繁查询不同剧集的交集，可调整物化视图按用户聚合观看记录，再统计符合条件的用户数：

-- 创建按用户聚合的物化视图
CREATE TABLE user_show_episode
(
    uid String,
    episodes AggregateFunction(groupUniqArray, String)
)
ENGINE = AggregatingMergeTree()
ORDER BY uid;

CREATE MATERIALIZED VIEW IF NOT EXISTS user_show_episode_mv TO user_show_episode
AS
    SELECT
        uid,
        groupUniqArrayState(concat(show, '-', episode)) AS episodes
    FROM ts_tutorial
    GROUP BY uid;

-- 查询同时观看Bridgerton S1E1和S1E2的用户数
SELECT count(*) AS users
FROM user_show_episode
WHERE
    has(groupUniqArrayMerge(episodes), 'Bridgerton-S1E1')
    AND has(groupUniqArrayMerge(episodes), 'Bridgerton-S1E2')

验证结果 #

以上三种方案均能返回正确结果1，对应用户carol。

内容的提问来源于stack exchange，提问作者stackoverflow2019