hive业务场景

梦里梦外; 2022-04-18 01:51 510阅读 0赞

表一：orders（订单表）

create table orders(  
order\_id string,  
user\_id string,  
eval\_set string,  
order\_number string,  
order\_dow string,  
order\_hour\_of\_day string,  
days\_since\_prior\_order string  
)  
row format delimited fields terminated by ',' lines terminated by '\\n';![20181106201847726.png][]

表二：prior（商品表）

create table priors(  
order\_id string,  
product\_id string,  
add\_to\_cart\_order string,  
reordered string  
)  
row format delimited fields terminated by ',' lines terminated by '\\n';

![20181106201913527.png][]

业务1：

查询每个用户有多少个订单？

select user\_id,count(1) as order\_cnt   
from orders   
group by user\_id   
order by order\_cnt desc   
limit 10;

业务2：

每个用户平均每个订单平均是多少商品 ？

思路，某个用户平均每个订单平均的商品数量=某个用户一共购买的商品数量/一共购买的订单数量

1.先在商品表算出一个订单有多少个商品

select order\_id,count(1) as pri\_cnt  
from priors   
group by order\_id

2.将商品表的order\_id和订单表的order\_id关联起来，把订单中的产品数量信息带到用户信息里

select odr.user\_id,pri.order\_id,pri\_cnt  
from orders odr  
join  
(  
select order\_id,count(1) as pri\_cnt  
from priors   
group by order\_id) pri  
on odr.order\_id=pri.order\_id  
limit 10;

3.求和，算出每个用户总共购买多少商品

select user\_id,sum(pri\_cnt)  
from(  
select odr.user\_id,pri.order\_id,pri\_cnt  
from orders odr  
join  
(  
select order\_id,count(1) as pri\_cnt  
from priors   
group by order\_id) pri  
on odr.order\_id=pri.order\_id  
)t  
group by user\_id  
limit 10;

4.求和，算出某个用户一共购买多少订单

每个用户有多少个订单：  
  select user\_id,count(1)  
  from orders   
  group by user\_id;

5.求平均

select user\_id,  
  sum(prod\_cnt)/count(1) as sc\_prod,  
  avg(prod\_cnt) as avg\_prod   
  from (select \* from orders where eval\_set='prior')od --如果不是prior统计为0  
  join (  
  select order\_id,count(1) as prod\_cnt   
  from priors   
  group by order\_id  
  limit 10000)pro  
  on od.order\_id=pro.order\_id  
  group by user\_id  
  limit 10;

业务3：每个用户在一周中的购买订单的分布(列转行)

user\_id,dow0,dow1,dow2,dow3,dow4...dow6  
    1       0    0    1    2    2      0  
  select   
  user\_id,  
  sum(case order\_dow when '0' then 1 else 0 end) as dow0,  
  sum(case order\_dow when '1' then 1 else 0 end) as dow1,  
  sum(case order\_dow when '2' then 1 else 0 end) as dow2,  
  sum(case order\_dow when '3' then 1 else 0 end) as dow3,  
  sum(case order\_dow when '4' then 1 else 0 end) as dow4,  
  sum(case order\_dow when '5' then 1 else 0 end) as dow5,  
  sum(case order\_dow when '6' then 1 else 0 end) as dow6  
  from orders  
  group by user\_id  
  limit 10;

业务4：

每个用户平均每个购买天中，购买的商品数量是多少？

比如有一个user ，有两天是购买商品的 ：  
day 1 ：两个订单，1订单：10个product，2订单：15个product ， 共25个  
day 2：一个订单， 1订单：12个product 共12个  
day 3： 没有购买

这个user 平均每个购买天的商品数量：  
(10+15+12)/2 ,这里是2天有购买行为，不是三天，也不是3个订单。

select ord.user\_id,sum(pri.products\_cnt)/ count(distinct ord.days\_since\_prior\_order) as avg\_prod    
\--date: 2离第一个商品10天,3离第二个10天   
from   
(select order\_id,  
user\_id,   
if(days\_since\_prior\_order='','-1',days\_since\_prior\_order) as days\_since\_prior\_order   
from orders where eval\_set='prior')ord   
join   
(select order\_id,count(1) as products\_cnt from priors group by order\_id)pri   
on ord.order\_id=pri.order\_id  
group by ord.user\_id  
limit 10;

业务5：每个用户最喜爱购买的三个product是什么，最终表结构可以是三个列，也可以是一个字符串/数组

select user\_id,   
collect\_list(concat\_ws('\_',product\_id,cast(row\_num as string))) as pro\_top3,  
size(collect\_list(product\_id)) as top\_size  
from  
(  
select user\_id,  
product\_id,   
row\_number() over(partition by user\_id order by top\_cnt desc) as row\_num  
from  
(  
\-- user\_id，product\_id,count(1)这个用户其中一个商品购买了几次  
select   
ord.user\_id as user\_id,  
pri.product\_id as product\_id,  
count(1) over(partition by user\_id,product\_id) as top\_cnt  
from   
(select \* from orders where eval\_set='prior')ord  
join   
(select \* from priors limit 10000)pri   
on ord.order\_id=pri.order\_id  
\-- group by ord.user\_id,pri.product\_id  
)t   
)t1  where row\_num <=3 group by user\_id limit 10;

[20181106201847726.png]: /images/20220418/3fc3fc8963b04942ae95d5437500523d.png
[20181106201913527.png]: /images/20220418/a3ac142043fc40329ed8031305344ffb.png