Hive SQL 每日SQL

1、查询订单明细表（order_detail）中销量（下单件数）排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回。

需要用到的表：

订单明细表：order_detail

代码：

select 
sku_id
from 
(
select 
sku_id
,sale_num
,dense_rank() over (order by sale_num desc ) as drp 
from 
(
select 
sku_id
,sum(sku_num) as sale_num 
from  order_detail
group by sku_id
)a
)b 
where drp = 2

结果：

2、查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下

 

订单信息表：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

代码

select 
distinct user_id
from 
(
select 
user_id
,date1
,case when (datediff(date2,date1)=1 and datediff(date3,date2)=1 and datediff(date3,date1)=2) then 1 else 0 end  diff
from 
(
select 
distinct user_id
,create_date as date1
,lead(create_date) over (partition by user_id order by create_date) as date2
,lead(create_date,2) over (partition by user_id order by create_date) as date3
from 
(select 
 distinct user_id,create_date from order_info
)a
)b
)c where diff =1

结果

3、从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品，

期望结果如下：

category_id <string>	category_name <string>	sku_id <string>	name <string>	order_num <bigint>	sku_cnt <bigint>
1	数码	2	手机壳	302	4
2	厨卫	8	微波炉	253	4
3	户外	12	遮阳伞	349	4

需要用到的表

订单明细表：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

商品分类信息表：category_info

category_id (分类id)	category_name (分类名称)
1	数码
2	厨卫
3	户外

代码：

with t1 as (
select 
a.category_id
,b.category_name
,count(sku_id) as sku_cnt
from sku_info a 
left join category_info b on a.category_id =b.category_id
group by 
a.category_id
,b.category_name)
,
t2 as (
select * 
from 
(
select  
category_id
,sku_id
,name
,order_num
,rank() over(partition by category_id order by order_num desc) rk
from (
select 
b.category_id 
,a.sku_id
,b.name
,sum(a.sku_num) as order_num
from order_detail  a 
left join sku_info b on a.sku_id=b.sku_id
group by 
b.category_id 
,a.sku_id
,b.name
)a
)b
where rk='1'
)

select  
t2.category_id
,t1.category_name
,t2.sku_id
,t2.name
,t2.order_num
,t1.sku_cnt
from t2 
left join t1 on t2.category_id = t1.category_id

结果：

4、从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额，以及每个用户在其每个下单日期的VIP等级。

用户vip等级根据累积消费金额计算，计算规则如下：
设累积消费总额为X，
若0=<X<10000,则vip等级为普通会员
若10000<=X<30000,则vip等级为青铜会员
若30000<=X<50000,则vip等级为白银会员
若50000<=X<80000,则vip为黄金会员
若80000<=X<100000,则vip等级为白金会员
若X>=100000,则vip等级为钻石会员

期望结果如下：

user_id <string> (用户id)	create_date <string> (下单日期)	sum_so_far <decimal(16,2)> (截至每个下单日期的累计下单金额)	vip_level <string> (每个下单日期的VIP等级)
101	2021-09-27	29000.00	青铜会员
101	2021-09-28	99500.00	白金会员
101	2021-09-29	142800.00	钻石会员
101	2021-09-30	143660.00	钻石会员
102	2021-10-01	171680.00	钻石会员
102	2021-10-02	177850.00	钻石会员
103	2021-10-02	69980.00	黄金会员
103	2021-10-03	75890.00	黄金会员
104	2021-10-03	89880.00	白金会员
105	2021-10-04	120100.00	钻石会员
106	2021-10-04	9390.00	普通会员
106	2021-10-05	119150.00	钻石会员
107	2021-10-05	69850.00	黄金会员
107	2021-10-06	124150.00	钻石会员
108	2021-10-06	101070.00	钻石会员
108	2021-10-07	155770.00	钻石会员
109	2020-10-08	24020.00	青铜会员
109	2021-10-07	153500.00	钻石会员
1010	2020-10-08	51950.00	黄金会员

需要用到的表：

订单信息表：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

代码

select  
*
,case when (sum_so_far >=0 and sum_so_far <10000) then '普通会员'
	  when (sum_so_far >=10000 and sum_so_far <30000) then '青铜会员'
    when (sum_so_far >=30000 and sum_so_far <50000) then '白银会员'
    when (sum_so_far >=50000 and sum_so_far <80000) then '黄金会员'
    when (sum_so_far >=80000 and sum_so_far <100000) then '白金会员'
    else '钻石会员' end vip_level
from (
select   
user_id
,create_date
,sum(sum_so_far) over(partition by user_id order by create_date rows BETWEEN unbounded preceding and current row  ) as sum_so_far
from  
(
select  
user_id
,create_date
,sum(total_amount) as sum_so_far 
from order_info 
group by 
user_id
,create_date
)a
)b

5、从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例，结果保留一位小数，使用百分数显示

期望结果如下：

percentage <string>

70.0%

需要用到的表：

订单信息表：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

代码

with t as (
select
user_id
,create_date as date1
,lag(create_date,1,'null') over(partition by user_id order by create_date ) as date2
,lead(create_date) over(partition by user_id order by create_date ) as date3
from (select distinct user_id,create_date from order_info)a
)


select 
concat(round(avg(if(datediff(date3,date1)=1,1,0))*100,1),'%') as percentage 
from t 
where date2='null'