hivesql求一段时间内每周_Hive SQL 经典优化案例

声明 l 本文内容及图片来源于互联网，版权归原作者所有，仅供学习参考之用，禁止用于商业用途，侵删

HiveSQL经典优化案例一：

1.1 将要执行的查询(执行了 1个多小时才出结果)：

SELECT dt as DATA_DATE,STRATEGY,AB_GROUP,SOURCE,    count(distinct case when lower(event) not like '%push%' and event!='corner_mark_show' then udid else null end) as DAU,    count(case when event='client_show' then 1 else null end) as TOTAL_VSHOW,    count(distinct case when event='client_show' then vid else null end) as TOTAL_VIDEO_VSHOW,    count(case when event='video_play' then 1 else null end) as TOTAL_VV_VP,    count(distinct case when event='video_play' then udid else null end) as TOTAL_USERS_VP,    count(case when event='effective_play' then 1 else null end) as TOTAL_VV_EP,    count(distinct case when event='effective_play' then udid else null end) as TOTAL_USERS_EP,    sum(case when event='video_over' then duration else 0 end) as TOTAL_DURATION,    count(case when event='video_over' then 1 else null end) as TOTAL_VOVER,    sum(case when event='video_over' then play_cnts else 0 end) as TOTAL_VOVER_PCNTS,    count(case when event='push_video_clk' then 1 else null end) as TOTAL_PUSH_VC,    count(distinct case when event='app_start' and body_source = 'push' then udid else null end) as TOTAL_PUSH_START,    count(case when event='post_comment' then 1 else null end) as TOTAL_REPLY,    count(distinct case when event='post_comment' then udid else null end) as TOTAL_USERS_REPLY    FROM dwb_v8sp_tmp.base_report_bystrategy_byab_source_column_zklgroup by dt,strategy,ab_group,source;

1.2 查询语句涉及到的表有 7.7亿+ 数据。(查询如下)