Hive sql 每天场景题39-40

39、动销率定义为品类商品中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数）。
滞销率定义为品类商品中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品 / 已上架总商品数）。
只要当天任一店铺有任何商品的销量就输出该天的结果
从订单明细表（order_detail）和商品信息表（sku_info）表中求出国庆7天每天每个品类的商品的动销率和滞销率

结果如下（截取部分）：

category_id <string> （品类id）	first_sale_rate <decimal(16,2)> （动销)	first_unsale_rage <decimal(16,2)> （滞销)	second_sale_rate <decimal(16,2)> （动销)	second_unsale_rate <decimal(16,2)> （滞销)
1	1.00	0.00	0.50	0.50
2	0.75	0.25	0.75	0.25
3	0.25	0.75	0.75	0.25

需要用到的表：
订单明细表：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  
distinct 
sku_id
,category_id
,count(sku_id) over(partition by category_id ) as sum_allcategory
from sku_info
)
,t3 as (
select 
-- 品类 日期 动销率 滞销率
category_id
,create_date
,cast(day_allsale /sum_allcategory  as decimal(10,2))as sale_rate
,cast(1 - day_allsale /sum_allcategory  as decimal(10,2))as unsale_rate
from (
select 
-- 品类 品类总上架数 日期  该日期及品类中有销售记录的商品数
distinct
t1.category_id
,t1.sum_allcategory
,t2.create_date
,count(distinct t2.sku_id) over(partition by t1.category_id,t2.create_date) as day_allsale
from t1 
left join order_detail t2 on t1.sku_id = t2.sku_id 
where t2.create_date BETWEEN '2021-10-01' and '2021-10-07'
)a
)

select 
category_id
,sum(case when create_date='2021-10-01' then sale_rate else 0 end ) as first_sale_rate
,sum(case when create_date='2021-10-01' then unsale_rate else 0 end ) as first_unsale_rate
,sum(case when create_date='2021-10-02' then sale_rate else 0 end ) as second_sale_rate
,sum(case when create_date='2021-10-02' then unsale_rate else 0 end ) as second_unsale_rate
,sum(case when create_date='2021-10-03' then sale_rate else 0 end ) as third_sale_rate
,sum(case when create_date='2021-10-03' then unsale_rate else 0 end ) as third_unsale_rate
,sum(case when create_date='2021-10-04' then sale_rate else 0 end ) as fourth_sale_rate
,sum(case when create_date='2021-10-04' then unsale_rate else 0 end ) as fourth_unsale_rate
,sum(case when create_date='2021-10-05' then sale_rate else 0 end ) as fifth_sale_rate
,sum(case when create_date='2021-10-05' then unsale_rate else 0 end ) as fifth_unsale_rate
,sum(case when create_date='2021-10-06' then sale_rate else 0 end ) as sixth_sale_rate
,sum(case when create_date='2021-10-06' then unsale_rate else 0 end ) as sixth_unsale_rate
,sum(case when create_date='2021-10-07' then sale_rate else 0 end ) as seventh_sale_rate
,sum(case when create_date='2021-10-07' then unsale_rate else 0 end ) as seventh_unsale_rate
from t3
group by
category_id

40、根据用户登录明细表（user_login_detail），求出平台同时在线最多的人数。

结果如下：

cn <bigint>

7

需要用到的表：

用户登录明细表：user_login_detail

user_id(用户id)	ip_address(ip地址)	login_ts(登录时间)	logout_ts(登出时间)
101	180.149.130.161	2021-09-21 08:00:00	2021-09-27 08:30:00
102	120.245.11.2	2021-09-22 09:00:00	2021-09-27 09:30:00
103	27.184.97.3	2021-09-23 10:00:00	2021-09-27 10:30:00

代码

with t1 as (
-- 时间  动作（登录+1，登出-1）
select
login_ts as check_time
,1 as cnt
from user_login_detail
union all
select
logout_ts as check_time
,-1 as cnt
from user_login_detail
)

select 
-- 秒级求最大共同人数
max(cn) as cn
from (
select 
-- 时间  累加人数 （开窗sum()只要over()中没有partition by 就能实现累加）
check_time
,sum(cnt) over(order by check_time) as cn
from t1
)a