uniqTheta* variations don't seem to work on theta sketches within MV

[githubdev2018]

I'm trying to re-wirte a Druid query using uniqTheta* & MV combination in ClickHouse to achieve a similar outcome. But I'm getting 0 result as opposed to 1 with the below example.

According to Druid doc, the DS_THETA function can work on sketches.

How could the below Druid query be rewritten in ClickHouse using the uniqTheta* variations?

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

Here's an attempt to solve it using MV

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');

And to answer: How many users watched both episodes of 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

The above query would return 0 instead of 1 for user carol. Which doesn't seem to work.

[den-crane]

you store States in the mv, so instead of uniqThetaState you need to use uniqThetaMergeState otherwise you count number of states. It's hard to explain.

And better to query tutorial_tbl instead of tutorial_mv (it's more performant)

SELECT finalizeAggregation(
                       uniqThetaIntersect(
                         uniqThetaMergeStateIf(theta_uid, show = 'Bridgerton' AND episode = 'S1E1'),
                         uniqThetaMergeStateIf(theta_uid, show = 'Bridgerton' AND episode = 'S1E2')
                       )
                     ) AS users
              FROM tutorial_tbl
              ;
┌─users─┐
│     1 │
└───────┘

[den-crane]

I'll try to explain by example with uniq function.

Merged/finalized uniq

select uniq(u) from (select  number u from numbers(10));
┌─uniq(u)─┐
│      10 │
└─────────┘

Intermediate state

select uniqState(u) from (select  number u from numbers(10));
┌─uniqState(u)──────────┐
│
u�,��4�ƣ������e���	��h�uUL�sE|� │
└───────────────────────┘

Number of uniq Intermediate states (has no sense uniq from 1 single binary garbage) -- That's why you got 0!!!

select uniq(us) from (select uniqState(u) us from (select  number u from numbers(10)));
┌─uniq(us)─┐
│        1 │
└──────────┘

But if you use uniqMergeState, you get the State back, and uniqMergeState is aggregating function

select uniqMergeState(us) from (select uniqState(u) us from (select  number u from numbers(10)));
┌─uniqMergeState(us)────┐
│
u�,��4�ƣ������e���	��h�uUL�sE|� │
└───────────────────────┘

Then you can finalize it using Merge (aggregating function)

select uniqMerge(us1)  from (
select uniqMergeState(us) us1 from (select uniqState(u) us from (select  number u from numbers(10))));
┌─uniqMerge(us1)─┐
│             10 │
└────────────────┘

Or you can finalize it using finalizeAggregation ( not aggregating function)

select finalizeAggregation(us1)  from (
select uniqMergeState(us) us1 from (select uniqState(u) us from (select  number u from numbers(10))));
┌─finalizeAggregation(us1)─┐
│                       10 │
└──────────────────────────┘

[githubdev2018]

Wow :)
@den-crane I appreciate your prompt help!

[githubdev2018]

@den-crane I'm curious to know which one do you recommend the *Merge or finalizeAggregation in the above example? And why?

[den-crane]

In this case finalizeAggregation is fine.

You will understand by experience when you need which one, basically you will get an error if you use incorrect one.

for example

SELECT uniqThetaMerge(
                       uniqThetaIntersect(
                         uniqThetaMergeStateIf(theta_uid, show = 'Bridgerton' AND episode = 'S1E1'),
                         uniqThetaMergeStateIf(theta_uid, show = 'Bridgerton' AND episode = 'S1E2')
                       )
                     ) AS users
              FROM tutorial_tbl
              ;
DB::Exception: Aggregate function uniqThetaMergeStateIf(theta_uid, and(equals(show, 'Bridgerton'), equals(episode, 'S1E1'))) is found inside another aggregate function in query: While processing uniqThetaMergeStateIf(theta_uid, (show = 'Bridgerton') AND (episode = 'S1E1')). (ILLEGAL_AGGREGATION)

SELECT
    show,
    finalizeAggregation(users)
FROM
(
    SELECT
        show,
        uniqThetaIntersect(uniqThetaMergeStateIf(theta_uid, episode = 'S1E1'), uniqThetaMergeStateIf(theta_uid, episode = 'S1E2')) AS users
    FROM tutorial_tbl
    GROUP BY show
)
GROUP BY show

: 215. DB::Exception: Received from localhost:9000. DB::Exception: Column `users` is not under aggregate function and not in GROUP BY. Have columns: ['show']: While processing show, finalizeAggregation(users). (NOT_AN_AGGREGATE)