Hive sql 每天场景题29-30

最新推荐文章于 2024-03-16 16:13:22 发布

吃再多糖也不长胖

最新推荐文章于 2024-03-16 16:13:22 发布

阅读量607

点赞数

分类专栏： Hive场景题训练文章标签： hive sql 数据库

本文链接：https://blog.csdn.net/weixin_43859562/article/details/128674257

版权

Hive场景题训练专栏收录该内容

21 篇文章 19 订阅

订阅专栏

该文提供了两个SQL查询示例。第一个查询从订单详情表(order_detail)中计算商品连续售卖的时间区间，包括sku_id、起始时间(start_date)和结束时间(end_date)。第二个查询结合用户登录明细表(user_login_detail)和配送信息表(delivery_info)，统计用户登录次数和交易次数。

摘要由CSDN通过智能技术生成

29、从订单详情表（order_detail）中，求出商品连续售卖的时间区间

结果如下（截取部分）：

sku_id <string> （商品id）	start_date <string> （起始时间）	end_date <string> （结束时间）
1	2021-09-27	2021-09-27
1	2021-09-30	2021-10-01
1	2021-10-03	2021-10-08
10	2021-10-02	2021-10-03
10	2021-10-05	2021-10-08
11	2021-10-02	2021-10-08
12	2021-09-30	2021-09-30
12	2021-10-02	2021-10-06
12	2021-10-08	2021-10-08

需要用到的表：

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

代码

with t as (
select  
-- 商品 日期-序号 最小日期 最大日期
sku_id
,ds
,min(create_date) as start_date
,max(create_date) as end_date
from(
	select
	-- 商品 日期 日期-序号
    sku_id 
    ,create_date
    ,date_sub(create_date,rn) as ds
from
(
  -- 商品 日期  序号  
select 
sku_id
,create_date
,row_number() over(partition by sku_id order by create_date) as rn
from (select distinct sku_id,create_date from order_detail)a
)a
)b
group by 
sku_id
,ds
)
select  sku_id,start_date,end_date from t

30、分别从登陆明细表（user_login_detail）和配送信息表中用户登录时间和下单时间统计登陆次数和交易次数

结果如下（截取部分）：

user_id <string> （用户id）	login_date <string> （登录时间）	login_count <bigint> （登陆次数）	order_count <bigint> （交易次数）
101	2021-09-21	1	0
101	2021-09-27	1	1
101	2021-09-28	1	1
101	2021-09-29	1	1
101	2021-09-30	1	1
1010	2021-09-27	1	0
1010	2021-10-09	1	0
102	2021-09-22	1	0
102	2021-10-01	2	3

需要用到的表：

用户登录明细表：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

配送信息表：delivery_info

delivery_id （运单 id ）	order_id （订单id）	user_id （用户 id ）	order_date （下单日期）	custom_date （期望配送日期）
1	1	101	2021-09-27	2021-09-29
2	2	101	2021-09-28	2021-09-28
3	3	101	2021-09-29	2021-09-30

代码

select 
a.user_id
,a.login_ts as login_date
,a.login_count
,if(b.order_count is null ,0,b.order_count) as order_count
from (select 
      -- 用户 登录日期 登录次数
      user_id,substr(login_ts,1,10) as login_ts 
      ,count(1) as login_count
      from user_login_detail
      group by  user_id,substr(login_ts,1,10)
      ) a
left join (select user_id, order_date, count(1) as order_count from delivery_info group by  user_id, order_date) b on a.user_id = b.user_id and a.login_ts = b.order_date