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