sql交并差运算

-- 取并集
select count(distinct user_id) from
(
select user_id from hive_table where {some condition}
union
select user_id from hive_table where {some condition}
) t1 


-- 取交集
select count(distinct t1.user_id) from  
(select user_id from hive_table where {some condition} ) t1 
inner join
(select user_id from hive_table where {some condition} ) t2
on t1.user_id = t2.user_id


-- 取差集  
select count(distinct t1.user_id) from  
(select user_id from hive_table where {some condition} ) t1 
left join
(select user_id from hive_table where {some condition} ) t2
on t1.user_id = t2.user_id 
where t2.user_id is null

 

 

clickhouse localQuery用法

select user_id from localQuery(app_hdd, db.table_local, `
select user_id from db.table_local where dt = 'xxx'
`)

select sum(cnt) from localQuery(app_hdd, db.table_local, `
select count(1) as cnt from db.table_local where dt = 'xxx'
`)

 

clickhouse json string 解析 {"M": 50, "F": 10}
arraySum( arrayMap(x->toFloat64(replaceAll(tupleElement(x,2),'"','')), arrayFilter(x-> has(['M'], tupleElement(x,1)), JSONExtractKeysAndValuesRaw(coalesce(gender_dist,''))))) >= 1