宽表通用圈选洞察引擎： 任意维度的集合交并差计算 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
;
