hive-sql高能技巧

1. get_json_object

示例：获取json中的sale_price字段
get_json_object(detail_json,'$.sale_price')

2. sum(case when...then...else end)

示例：获取第7天的总销售额
sum(case when by_day=7 then pay_amt else 0 end)

3.count(case when...then...else end)

示例：获取第7天的下单用户数
count(distinct case when by_day=7 then user_id end) as day_7,

4.min(case when...then...else end)

示例：获取vip用户下的第1单
min(case when is_vip=1 then order_dt end) ,

5.rank() over([partition by col1] order by col2)

示例：获取订单中每个订单是用户下的第几单
row_number() over (partition by user_id order by order_time asc) as order_cnt

除Row_number外还有rank,dense_rank 

以下是语法： 
rank() over([partition by col1] order by col2) 
dense_rank() over([partition by col1] order by col2) 
row_number() over([partition by col1] order by col2)

未完待续。。

row_number() 图自：https://www.cnblogs.com/ianunspace/p/5057333.html rank() 图自：https://www.cnblogs.com/ianunspace/p/5057333.html dense_rank() 图自：https://www.cnblogs.com/ianunspace/p/5057333.html