hive 实例

1.得出2015年每个用户前2次订单,并得出每个订单购买的商品号列表 

select *from (select cust_id,parent_id,concat_ws('#', collect_set(product_id)),row_number() over (partition by cust_id order by order_creation_date) as paimingfrom dwfact.order_send_detail orderswhere orders.data_date >= '2015-01-01' and data_date < '2016-01-01'and cust_id = '9680935'group by cust_id,parent_id,order_creation_date) bwhere paiming < 3 ;

2.得到2015年6月1日的前30天、前60天、前90天的金额汇总

selectdata_date, sum(moneys) over(order by data_date rows between 30 preceding and CURRENT ROW)from (select data_date,sum(bargin_price*allot_quantity) as moneysfrom dwfact.order_send_detail orderswhere data_date >= '2015-01-01' and data_date < '2015-05-01'group by data_date) a;如果需求是每一天的前60天这个还可以。建议使用下面的：select sum(case when datediff('2015-06-01',data_date) <= 30 then  bargin_price*allot_quantity else 0 end) as 30_moneys,sum(case when datediff('2015-06-01',data_date) <= 60 then  bargin_price*allot_quantity else 0 end) as 60_moneys,sum(case when datediff('2015-06-01',data_date) <= 90 then  bargin_price*allot_quantity else 0 end) as 90_moneysfrom dwfact.order_send_detail orderswhere data_date >= '2015-01-01' and data_date <= '2015-06-01';

3.获得2014年、2015年销售最高和最低的月份及销售额

select substr(data_date,0,4) as year, substr(data_date,0,7) as month,sum(bargin_price*allot_quantity) as moneys,row_number() over (partition by substr(data_date,0,4) order by sum(bargin_price*allot_quantity) desc) as number,row_number() over (partition by substr(data_date,0,4) order by sum(bargin_price*allot_quantity)) as number2from dwfact.order_send_detailwhere data_date >= '2014-01-01' and data_date < '2016-01-01'group by substr(data_date,0,4),substr(data_date,0,7)；

4.一个字段的值为123456789_a232323_b3434343_s343434_r43434
规则为一串数字与其他首字符为字母其余为数字的单元用_连接，请问怎么获得第一串数字，和s开头的后面的数字，首字母s的数字串位置和数量都不固定

select regexp_extract('123456789_a232323_b3434343_23456_s343434_r43434','^(\\d+)',1),regexp_extract('123456789_a232323_b3434343_23456_s343434_s43434','_([s])(\\d+)',2) from ubd_tmp.pcp;