hive 实例

最新推荐文章于 2021-10-16 17:05:29 发布

原创最新推荐文章于 2021-10-16 17:05:29 发布 · 655 阅读

0 ·

CC 4.0 BY-SA版权

hive-实践专栏收录该内容

6 篇文章

订阅专栏

该博客通过Hive查询展示了如何获取2015年每个用户最初的两次订单详情，包括每个订单中购买的商品编号列表，提供了数据处理的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 paiming
from dwfact.order_send_detail orders
where 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
) b
where paiming < 3 ;

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

select
data_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 moneys
from 
dwfact.order_send_detail orders
where 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_moneys
from dwfact.order_send_detail orders
where 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 number2
from dwfact.order_send_detail
where 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;