2021年3月3日加入智联，进入全新的行业很有挑战性，所以最近两个月没有写过博客。今天周末，过来加班＋学习，记录下吧。
这篇博客主要讲下hive中groupby和distinct区别以及性能比较，用distinct去重，很容易数据量大的时候的数据倾斜。
都会在map阶段count，但reduce阶段，distinct只有一个， group by 可以有多个进行并行聚合，所以group by会快
-- 搜索单投android_iOS
SELECT dt, count(distinct user_id) application_count_by_day
FROM dwd.dwd_evt_c_jd_deliver_hour
WHERE dt BETWEEN ${date1} AND ${date2} AND deliver_source_page_type_cd='5019' -- 5019 search;5020 recommend
AND deliver_type_cd= 2 -- 2：单个投递3：批量投递
AND (deliver_termn_cd='210' or deliver_termn_cd='220') -- 210:IOS; 220:Android
GROUP BY dt
ORDER BY dt
-- 8min40s-- 搜索单投android_iOS
SELECT dt, count(table1.user_id) application_count_by_day
FROM
(SELECT dt, user_id FROM dwd.dwd_evt_c_jd_deliver_hour
WHERE dt BETWEEN ${date1} AND ${date2} AND deliver_source_page_type_cd='5019' -- 5019 search;5020 recommend
AND deliver_type_cd= 2 -- 2：单个投递3：批量投递
AND (deliver_termn_cd='210' or deliver_termn_cd='220')
GROUP BY dt, user_id
) table1 -- 210:IOS; 220:Android
GROUP BY dt
ORDER BY dt
-- 4min58s查询时间基本上为原来的一半儿！要知道今天是周末，工作日这个差距更明显些。
