Hive SQL优化

最新推荐文章于 2024-12-09 09:18:04 发布

程默子弹

最新推荐文章于 2024-12-09 09:18:04 发布

阅读量1.3k

点赞数 2

分类专栏： hive sql

本文链接：https://blog.csdn.net/li_19186535821/article/details/119138601

版权

Hive SQL优化

案例一：

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(