HiveSQL 进阶指南

最新推荐文章于 2025-09-05 08:00:00 发布

原创

最新推荐文章于 2025-09-05 08:00:00 发布 · 587 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#hive #sql

本文是关于HiveSQL的进阶指南，涵盖了CASE WHEN的使用，包括与IF()NVL()的比较和多种常见用法。还深入讨论了窗口函数如LEAD/LAG、first_value/last_value，以及各种聚合和分析函数。此外，文章还介绍了如何进行列拆行、行合列操作，以及数据立方体rollup/cube/grouping sets的运用。

SQL进阶指南

数据准备

环境：
hadoop 2.4.1 +
hive 1.2.1+

create table tmp.yws_example as 
select 1 as showing_id,'A' as broker_id ,'c001' as cust_id,'h001' as housedel_id ,1 as biz_type ,20160101 as start_time   union all 
select 2 as showing_id,'A' as broker_id ,'c002' as cust_id,'h001' as housedel_id ,1 as biz_type ,20160105 as start_time   union all 
select 3 as showing_id,'A' as broker_id ,'c003' as cust_id,'h002' as housedel_id ,2 as biz_type ,20160108 as start_time   union all 
select 4 as showing_id,'A' as broker_id ,'c005' as cust_id,'h002' as housedel_id ,2 as biz_type ,20160209 as start_time   union all 
select 5 as showing_id,'A' as broker_id ,'c001' as cust_id,'h003' as housedel_id ,1 as biz_type ,20160218 as start_time   union all 
select 6 as showing_id,'A' as broker_id ,'c004' as cust_id,'h004' as housedel_id ,2 as biz_type ,20160214 as start_time   union all 
select 7 as showing_id,'A' as broker_id ,'c008' as cust_id,'h004' as housedel_id ,2 as biz_type ,20160307 as start_time   union all 
select 8 as showing_id,'A' as broker_id ,'c007' as cust_id,'h001' as housedel_id ,1 as biz_type ,20160306 as start_time   union all 
select 9 as showing_id,'A'