HiveSQL 进阶指南

最新推荐文章于 2022-11-20 17:39:35 发布

路在脚下扬帆远航

最新推荐文章于 2022-11-20 17:39:35 发布

阅读量518

点赞数

分类专栏：数据仓库 hive 文章标签： hive sql

本文链接：https://blog.csdn.net/yangws2004/article/details/70763434

版权

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

摘要由CSDN通过智能技术生成

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'