Hive sql 每天场景题31-34

31、从订单明细表（order_detail）中列出每个商品每个年度的购买总额

结果如下（截取部分）：

sku_id <string> （商品id）	year_date <bigint> （年份）	sku_sum <decimal(16,2)> （销售总额）
1	2021	102000.00
10	2021	29900.00
11	2021	16000.00
12	2021	413640.00
2	2021	60440.00
3	2021	180000.00
4	2021	318000.00
5	2021	121000.00
6	2021	64000.00
7	2021	25200.00
8	2021	151800.00
9	2021	194000.00

需要用到的表：

订单明细表：order_detail

order_detail_id(订单明细id)	order_id(订单id)	sku_id(商品id)	create_date(下单日期)	price(商品单价)	sku_num(商品件数)
1	1	1	2021-09-30	2000.00	2
2	1	3	2021-09-30	5000.00	5
22	10	4	2020-10-02	6000.00	1
23	10	5	2020-10-02	500.00	24
24	10	6	2020-10-02	2000.00	5

select 
sku_id
,substr(create_date,1,4) as year_date
,sum(price*sku_num) as sku_sum
from order_detail
group by 
sku_id
,substr(create_date,1,4) 

32、从订单详情表（order_detail）中查询2021年9月27号-2021年10月3号这一周所有商品每天销售情况

结果如下：

sku_id <string>	monday <bigint>	tuesday <bigint>	wednesday <bigint>	thursday <bigint>	friday <bigint>	saturday <bigint>	sunday <bigint>
1	0	0	9	8	0	4	2
10	0	0	0	0	48	69	0
11	0	0	0	0	15	61	0
12	0	0	43	0	31	20400	0
2	0	0	0	18	5800	0	0
3	0	0	0	6	0	1	5
4	9	0	0	8	1	5	0
5	33	0	0	0	24	47	0
6	0	0	0	1	5	8	0
7	0	37	0	17	0	20	0
8	0	46	0	48	39	0	0
9	0	12	0	45	0	0	0

需要用到的表：

订单明细表：order_detail

order_detail_id(订单明细id)	order_id(订单id)	sku_id(商品id)	create_date(下单日期)	price(商品单价)	sku_num(商品件数)
1	1	1	2021-09-30	2000.00	2
2	1	3	2021-09-30	5000.00	5
22	10	4	2020-10-02	6000.00	1
23	10	5	2020-10-02	500.00	24
24	10	6	2020-10-02	2000.00	5

代码

with t1 as (
-- 商品 数量 日期 周几 
select 
sku_id
,create_date
,pmod(datediff(create_date,"1920-01-01")-3,7) as week
,sum(sku_num) as sku_num
from order_detail
group by 
sku_id
,create_date
,pmod(datediff(create_date,"1920-01-01")-3,7)
)

select 
distinct 
sku_id
,sum(if(week=1,sku_num,0)) over(partition by sku_id rows between unbounded preceding and unbounded following ) as monday
,sum(if(week=2,sku_num,0)) over(partition by sku_id rows between unbounded preceding and unbounded following ) as tuesday
,sum(if(week=3,sku_num,0)) over(partition by sku_id rows between unbounded preceding and unbounded following ) as wednesday
,sum(if(week=4,sku_num,0)) over(partition by sku_id rows between unbounded preceding and unbounded following ) as thursday
,sum(if(week=5,sku_num,0)) over(partition by sku_id rows between unbounded preceding and unbounded following ) as friday
,sum(if(week=6,sku_num,0)) over(partition by sku_id rows between unbounded preceding and unbounded following ) as saturday
,sum(if(week=0,sku_num,0)) over(partition by sku_id rows between unbounded preceding and unbounded following ) as sunday
from t1
where create_date between '2021-09-27' and '2021-10-03'

33、从商品价格变更明细表（sku_price_modify_detail），得到最近一次价格的涨幅情况，并按照涨幅升序排序。

从商品价格变更明细表（sku_price_modify_detail），得到最近一次价格的涨幅情况，并按照涨幅升序排序。

结果如下：

sku_id <string> （商品id）	price_change <decimal(16,2)> （涨幅）
8	-200.00
9	-100.00
2	-70.00
11	-16.00
12	-15.00
3	1.00
5	10.00
10	10.00
7	12.00
6	12.00
1	100.00
4	400.00

需要用到的表：

商品价格变更明细表：sku_price_modify_detail

sku_id(商品id)	new_price(本次变更之后的价格)	change_date(变更日期)
1	1900.00	2021-09-25
1	2000.00	2021-09-26
2	80.00	2021-09-29
2	10.00	2021-09-30

代码

with t1 as (
-- 商品 价格  日期  顺序 
select  
*
,row_number() over (partition by sku_id order by change_date desc ) as rn 
from sku_price_modify_detail
)
,t2 as (
-- 商品 现在价格 上一次价格 
select  
sku_id
,sum(now_price) as now_price
,sum(old_price) as old_price
from (
select 
sku_id
,if(rn=1,new_price,0) as now_price
,if(rn=2,new_price,0) as old_price
from t1 where rn<=2)a
group by sku_id
)

-- 商品 涨幅 
select 
sku_id
,now_price - old_price as price_change
from t2
order by now_price - old_price 

34、通过商品信息表（sku_info）订单信息表（order_info）订单明细表（order_detail）分析如果有一个用户成功下单两个及两个以上的购买成功的手机订单（购买商品为xiaomi 10，apple 12，小米13）那么输出这个用户的id及第一次成功购买手机的日期和第二次成功购买手机的日期，以及购买手机成功的次数。

结果如下：

user_id <string> （用户id）	first_date <string> （首次时间）	last_date <string> （末次时间）	cn <bigint> （购买次数）
101	2021-09-27	2021-09-28	3
1010	2021-10-08	2021-10-08	2
102	2021-10-01	2021-10-01	3
103	2021-09-30	2021-10-02	2
104	2021-10-03	2021-10-03	3
105	2021-10-04	2021-10-04	2
106	2021-10-04	2021-10-05	3
107	2021-10-05	2021-10-05	3
108	2021-10-06	2021-10-06	3
109	2021-10-07	2021-10-07	3

需要用到的表：

订单信息表：order_info

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

订单明细表：order_detail

order_detail_id(订单明细id)	order_id(订单id)	sku_id(商品id)	create_date(下单日期)	price(商品单价)	sku_num(商品件数)
1	1	1	2021-09-30	2000.00	2
2	1	3	2021-09-30	5000.00	5
22	10	4	2020-10-02	6000.00	1
23	10	5	2020-10-02	500.00	24
24	10	6	2020-10-02	2000.00	5

商品信息表：sku_info

sku_id(商品id)	name(商品名称)	category_id(分类id)	from_date(上架日期)	price(商品价格)
1	xiaomi 10	1	2020-01-01	2000
6	洗碗机	2	2020-02-01	2000
9	自行车	3	2020-01-01	1000

代码

with t1 as (
-- 商品 日期  日期排序
select 
a.user_id
,a.create_date 
,row_number() over(partition by a.user_id order by a.create_date ) as rn
from order_info a
left join order_detail b  on  b.order_id = a.order_id 
left join sku_info c on c.sku_id = b.sku_id
where c.name in ('xiaomi 10','apple 12','小米13','xiaomi 13')
)

select 
distinct 
user_id
,first_date
,last_date
,cn
from 
(
select 
user_id
,min(create_date) over(partition by user_id) as first_date
,max(create_date) over(partition by user_id) as last_date
,max(rn) over(partition by user_id) as max_rn
,count(*) over(partition by user_id) as cn
from t1
)a
where max_rn>=2