宽表通用圈选洞察引擎： 任意维度的集合交并差计算 SQL 逻辑

select count(UserID)
from visits_v1
where Age > 20
  and StartTime > '2014-03-17 04:36:11';

select count(distinct UserID)
from visits_v1
where Age > 20
  and StartTime > '2014-03-17 04:36:11';

select count(VisitID)
from visits_v1;

select count(distinct VisitID)
from visits_v1;



select count(UserID)
from hits_v1
where Age > 20
  and EventTime > '2014-03-17 04:36:11';

select count(distinct UserID)
from hits_v1
where Age > 20
  and EventTime > '2014-03-17 04:36:11';


select count(distinct UserID) as user_cnt, count(1) as total
from tutorial.hits_v1;


select count(distinct (VisitID)) as user_cnt, count(1) as total
from tutorial.visits_v1;

select uniq(VisitID) as cnt, Age as fv
from tutorial.visits_v1
where Age > 0
group by Age
order by cnt desc;


select count(WatchID)
from hits_v1;


select count(VisitID)
from visits_v1;

drop table tutorial.hit_event;

-- create table tutorial.hit_event
-- (
--     date  Date,
--     value AggregateFunction(sum, UInt32)
-- ) ENGINE = MergeTree
--       PARTITION BY date
--       ORDER BY date
-- ;

alter
table
tutorial.hit_event
delete
where 1 = 1;

drop table tutorial.hit_event;

CREATE MATERIALIZED VIEW tutorial.hit_event
            ENGINE = AggregatingMergeTree()
                PARTITION BY EventDate
                ORDER BY (CounterID, EventDate)
            POPULATE
AS
SELECT CounterID,
       EventDate,
       sumState(RequestNum) AS RequestNums, --使用 sumState 函数写入时聚合函数类型字段值
       uniqState(UserID)    AS Users        --使用 uniqState 函数写入时聚合函数类型字段值
FROM tutorial.hits_v1
GROUP BY CounterID, EventDate;


select count(1)
from tutorial.hit_event;


SELECT EventDate, sumMerge(RequestNums) AS RequestNums, uniqMerge(Users) AS Users
FROM tutorial.hit_event
GROUP BY EventDate
ORDER BY EventDate;


SELECT EventDate, sum(RequestNum) AS RequestNums, uniq(UserID) AS Users
FROM tutorial.hits_v1
GROUP BY EventDate;


alter
table
tutorial.hit_event
delete
where 1 = 1;

select count(1)
from tutorial.hit_event;

-- select sum(request_num) as cnt
-- from tutorial.hit_event
-- order by cnt desc;

-- select sumMerge(value) as cnt
-- from tutorial.hit_event
-- order by cnt desc;
--
--
-- select sumMerge(state)
-- from (select sumState(request_num) as state from tutorial.hit_event);
--
--


--交集
select [1,2] as a, [2,3] as b, arrayIntersect(a, b);

--并集
select [1,2] as a, [2,3] as b, arrayDistinct(arrayConcat(a, b));

--差集
select [1,2] as a,
       [2,3] as b,
       arrayFilter(x->x is not null, arrayMap(x -> case when x not in arrayIntersect(a, b) then x end, a));


select [1,2] as a,
       [2,3] as b,
       arrayFilter(x->x is not null, arrayMap(x -> case when x not in arrayIntersect(a, b) then x end, a));

select a.i
from (select arrayJoin([1,2]) i) a INTERSECT
select b.i
from (select arrayJoin([2, 3]) i) b;

SET union_default_mode = 'ALL';
select distinct(t.i)
from (select a.i from (select arrayJoin([1,2]) i) a UNION select b.i from (select arrayJoin([2,3]) i) b ) t;

select a.i
from (select arrayJoin([1,2]) i) a EXCEPT
select b.i
from (select arrayJoin([2, 3]) i) b;


-- SET union_default_mode = 'DISTINCT';
-- SET union_default_mode = 'ALL';


select array(1, 2, 2) as a,
       array(2, 2, 3) as b,
       arrayIntersect(a, b);

SELECT arrayIntersect([1, 2, 3, 3], [4, 5, 6])          AS noIntersect,
       arrayIntersect([1, 2, 3, 3], [2, 2, 3, 4, 5, 6]) AS hasIntersect;


--160 ms (execution: 144 ms, fetching: 16 ms)
select arrayIntersect((select groupUniqArray(UserID) from hits_v1 where RequestNum > 10),
                      (select groupUniqArray(UserID) from hits_v1 where RequestNum < 10));

--211 ms (execution: 194 ms, fetching: 17 ms)
select arrayIntersect(
                   (select groupUniqArray(UserID) from hits_v1 where RequestNum = 15),
                   (select arrayIntersect((select groupUniqArray(UserID) from hits_v1 where RequestNum > 10),
                                          (select groupUniqArray(UserID) from hits_v1 where RequestNum < 20))));



select t.a1, t.a2
from (select [1,2] as a1, [2,3] as a2) t;


select arrayIntersect(
                   (select groupUniqArray(UserID) from hits_v1 where RequestNum = 15),
                   (select arrayIntersect((select groupUniqArray(UserID) from hits_v1 where RequestNum > 10),
                                          (select groupUniqArray(UserID) from hits_v1 where RequestNum < 20))));

--arrayIntersect(t[3],arrayIntersect(t[1], t[2])), 数组下标从 1 开始

select t.res
from (
         select arrayIntersect(t[3], arrayIntersect(t[1], t[2])) as res,
                array(
                            (select groupUniqArray(UserID) from hits_v1 where RequestNum = 15),
                            (select groupUniqArray(UserID) from hits_v1 where RequestNum > 10),
                            (select groupUniqArray(UserID) from hits_v1 where RequestNum < 20)
                    )                                               t
         ) t;



select length(t.res)
from (
         select arrayIntersect(t[3], arrayIntersect(t[1], t[2])) as res,
                array(
                            (select groupUniqArray(UserID) from hits_v1 where Sex = 1),
                            (select groupUniqArray(UserID) from hits_v1 where Age > 18),
                            (select groupUniqArray(UserID) from hits_v1 where RequestNum > 0)
                    )                                               t
         ) t;



select uniq(hits_v1.UserID)
from hits_v1
where Sex = 1
  and Age > 18
  and RequestNum > 0;

select 1 in [1,23];


select uniq(hits_v1.UserID) * (1 / 0.1) as cnt, EventDate
from hits_v1 SAMPLE 0.1
where UserID in (
    select arrayJoin(t.res)
    from (
             select arrayIntersect(t[3], arrayIntersect(t[1], t[2])) as res,
                    array(
                                (select groupUniqArray(UserID) from hits_v1 where Sex = 1),
                                (select groupUniqArray(UserID) from hits_v1 where Age > 18),
                                (select groupUniqArray(UserID) from hits_v1 where RequestNum > 0)
                        )                                               t
             ) t
)
group by EventDate
order by cnt desc
;


select sum(hits_v1.RequestNum) as cnt, EventDate
from hits_v1
where UserID in (
    select arrayJoin(t.res)
    from (
             select arrayIntersect(t[3], arrayIntersect(t[1], t[2])) as res,
                    array(
                                (select groupUniqArray(UserID) from hits_v1 where Sex = 1),
                                (select groupUniqArray(UserID) from hits_v1 where Age > 18),
                                (select groupUniqArray(UserID) from hits_v1 where RequestNum > 0)
                        )                                               t
             ) t
)
group by EventDate
order by cnt desc
;