6、从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额。
期望结果如下：
sku_id
(商品id)
year
(销售首年年份)
order_num
(首年销量)
order_amount
(首年销售金额)
2020
4000.00
2020
260.00
2020
5000.00
2021
318000.00
2021
242
121000.00
2020
12000.00
2020
3500.00
2020
35400.00
2021
194
194000.00
2020
9400.00
2020
4750.00
2020
1660.00
需要用到的表：
订单明细表：order_detail
order_detail_id(订单明细id)
order_id(订单id)
sku_id(商品id)
create_date(下单日期)
price(商品单价)
sku_num(商品件数)
2021-09-30
2000.00
2021-09-30
5000.00
2020-10-02
6000.00
2020-10-02
500.00
2020-10-02
2000.00
with t as (
select
sku_id
,substr(create_date,1,4) as year
,sum(sku_num) as order_num
,sum(price*sku_num) as order_amount
,rank() over (partition by sku_id order by substr(create_date,1,4) ) as rk
from order_detail
group by
sku_id
,substr(create_date,1,4)
)
select
sku_id
,year
,order_num
,order_amount
from t where rk =1
7、从订单明细表(order_detail)中筛选出去年总销量小于100的商品及其销量，假设今天的日期是2022-01-10，不考虑上架时间小于一个月的商品
期望结果如下：
sku_id
（商品id）
name
（商品名称）
order_num
（销量）
xiaomi 10
apple 12
xiaomi 13
洗碗机
需要用到的表：
商品信息表：sku_info
sku_id(商品id)
name(商品名称)
category_id(分类id)
from_date(上架日期)
price(商品价格)
xiaomi 10
2020-01-01
2000
洗碗机
2020-02-01
2000
自行车
2020-01-01
1000
订单明细表：order_detail
order_detail_id(订单明细id)
order_id(订单id)
sku_id(商品id)
create_date(下单日期)
price(商品单价)
sku_num(商品件数)
2021-09-30
2000.00
2021-09-30
5000.00
2020-10-02
6000.00
2020-10-02
500.00
2020-10-02
2000
with t as (
select
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
where substr(a.create_date ,1,4)='2021' and b.from_date < date_add('2022-01-10',-30)
group by
a.sku_id
,b.name)
select * from t where order_num < 100
8、从用户登录明细表（user_login_detail）中查询每天的新增用户数，若一个用户在某天登录了，且在这一天之前没登录过，则任务该用户为这一天的新增用户。
期望结果如下：
login_date_first
（日期）
user_count
（新增用户数）
2021-09-21
2021-09-22
2021-09-23
2021-09-24
2021-09-25
2021-09-26
2021-09-27
2021-10-04
2021-10-06
需要用到的表：
用户登录明细表：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 t as (
select
user_id
,login_ts
,lag(login_ts,1,'null') over(partition by user_id order by login_ts ) as last_date
from
(select distinct user_id,substr(login_ts,1,10)as login_ts from user_login_detail)a)
select
login_ts as login_date_first
,sum(if(last_date='null',1,0)) as user_count
from t where last_date ='null'
group by
login_ts
9、从订单明细表（order_detail）中统计出每种商品销售件数最多的日期及当日销量，如果有同一商品多日销量并列的情况，取其中的最小日期
期望结果如下：
sku_id
（商品id）
create_date
（销量最高的日期）
sum_num
（销量）
2021-09-30
2021-10-02
5800
2021-10-05
2021-10-07
2021-10-03
2021-10-03
2021-10-05
2020-10-08
2021-10-01
2020-10-08
2020-10-08
2021-10-03
20400
需要用到的表：
订单明细表：order_detail
order_detail_id(订单明细id)
order_id(订单id)
sku_id(商品id)
create_date(下单日期)
price(商品单价)
sku_num(商品件数)
2021-09-30
2000.00
2021-09-30
5000.00
2020-10-02
6000.00
2020-10-02
500.00
2020-10-02
2000.00
with t as (
select
sku_id
,create_date
,sum(sku_num) as sum_num
from order_detail
group by
sku_id
,create_date)
select
sku_id
,create_date
,sum_num
from (
select *
,row_number() over (partition by sku_id order by sum_num desc ) as rk
from t)a
where rk=1
10、从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品
期望结果如下：
sku_id
name
sum_num
cate_avg_num
手机壳
6044
1546
破壁机
242
194
热水壶
252
194
微波炉
253
194
遮阳伞
20682
5373
需要用到的表：
商品信息表：sku_info
sku_id(商品id)
name(商品名称)
category_id(分类id)
from_date(上架日期)
price(商品价格)
xiaomi 10
2020-01-01
2000
洗碗机
2020-02-01
2000
自行车
2020-01-01
1000
订单明细表：order_detail
order_detail_id(订单明细id)
order_id(订单id)
sku_id(商品id)
create_date(下单日期)
price(商品单价)
sku_num(商品件数)
2021-09-30
2000.00
2021-09-30
5000.00
2020-10-02
6000.00
2020-10-02
500.00
2020-10-02
2000.00
with t1 as (
select
a.sku_id
,b.name
,b.category_id
,sum(sku_num) as sum_num
from order_detail a
left join sku_info b on a.sku_id=b.sku_id
group by
a.sku_id
,b.name
,b.category_id
),
t2 as (
select
distinct b.category_id
,cast (sum(a.sku_num) over (partition by b.category_id )/count(distinct a.sku_id) over (partition by b.category_id ) as int ) as cate_avg_num
from order_detail a
left join sku_info b on a.sku_id=b.sku_id
)
select
t1.sku_id
,t1.name
,t1.sum_num
,t2.cate_avg_num
from t1
left join t2 on t1.category_id=t2.category_id
where t1.sum_num > t2.cate_avg_num
11、从用户登录明细表（user_login_detail）和订单信息表（order_info）中查询每个用户的注册日期（首次登录日期）、总登录次数以及其在2021年的登录次数、订单数和订单总额。
期望结果如下：
user_id
(用户id)
register_date
(注册日期)
total_login_count
(累积登录次数)
login_count_2021
(2021年登录次数)
order_count_2021
(2021年下单次数)
order_amount_2021
(2021年订单金额)
101
2021-09-21
143660.00
102
2021-09-22
177850.00
103
2021-09-23
75890.00
104
2021-09-24
89880.00
105
2021-10-04
120100.00
106
2021-10-04
119150.00
107
2021-09-25
124150.00
108
2021-10-06
155770.00
109
2021-09-26
129480.00
需要用到的表：
用户登录明细表：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
订单信息表：order_info
order_id (订单id)
user_id (用户id)
create_date (下单日期)
total_amount (订单金额)
101
2021-09-30
29000.00
103
2020-10-02
28000.00
with t1 as (
select
distinct user_id
,min(substr(login_ts,1,10)) over(partition by user_id rows between unbounded preceding and unbounded following ) as register_date
,count(login_ts) over(partition by user_id rows between unbounded preceding and unbounded following ) as total_login_count
,sum(if(substr(login_ts,1,4)='2021',1,0)) over(partition by user_id rows between unbounded preceding and unbounded following ) as login_count_2021
from user_login_detail
)
,t2 as (
select
user_id
,count(order_id ) as order_count_2021
,sum(total_amount ) as order_amount_2021
from order_info
where substr(create_date,1,4)='2021'
group by
user_id
)
select
t1.user_id
,t1.register_date
,t1.total_login_count
,nvl(t1.login_count_2021,0) as login_count_2021
,nvl(t2.order_count_2021,0) as order_count_2021
,nvl(t2.order_amount_2021,0) as order_amount_2021
from t1 left join t2 on t1.user_id=t2.user_id
order by t1.user_id
Original: https://blog.csdn.net/weixin_43859562/article/details/128566221
Author: 吃再多糖也不长胖
Title: Hive SQL 每日场景题
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/818358/
转载文章受原作者版权保护。转载请注明原作者出处！