尚硅谷SQL刷题练习
目录
- SQL 一：查询累积销量排名第二的商品
- SQL 二：查询至少连续三天下单的用户
- SQL 三：查询各品类销售商品的种类数及销量最高的商品
- SQL 四：查询用户的累计消费金额及VIP等级
- SQL 五：查询首次下单后第二天连续下单的用户比率
- SQL 六：每个商品销售首年的年份、销售数量和销售金额
- SQL 七：筛选去年总销量小于100的商品
- SQL 八：查询每日新用户数
- SQL 九：统计每个商品的销量最高的日期
- SQL 十：查询销售件数高于品类平均数的商品
- SQL 十一：用户注册、登录、下单综合统计
- SQL 十二：查询指定日期的全部商品价格
- SQL 十三：即时订单比例
- SQL 十四：向用户推荐朋友收藏的商品
- SQL 十五：查询所有用户的连续登录两天及以上的日期区间
- SQL 十六：男性和女性每日的购物总金额统计
- SQL 十七：订单金额趋势分析
- SQL 十八：购买过商品1和商品2但是没有购买商品3的顾客
- SQL 十九：统计每日商品1和商品2销量的差值
- SQL 二十：询出每个用户的最近三笔订单
- SQL 二十一：查询每个用户登录日期的最大空档期
- SQL二十二：查询相同时刻多地登陆的用户（不会）
- SQL 二十三：销售额完成任务指标的商品
- SQL 二十四：更多操作根据商品销售情况进行商品分类
- SQL 二十五：各品类销量前三的所有商品
- SQL 二十六：各品类中商品价格的中位数
- SQL 二十七：找出销售额连续3天超过100的商品
- SQL 二十八：查询有新注册用户的当天的新用户数量、新用户的第一天留存率
- SQL 二十九：求出商品连续售卖的时间区间
- SQL 三十：登录次数及交易次数统计
- SQL 三十一： 按年度列出每个商品销售总额
- SQL 三十二：某周内每件商品每天销售情况
- SQL 三十三：查看每件商品的售价涨幅情况
- SQL 三十四：销售订单首购和次购分析
- SQL 三十五：同期商品售卖分析表
- SQL 三十六：国庆期间每个品类的商品的收藏量和购买量
- SQL 三十七：统计活跃间隔对用户分级结果（难）
- SQL 三十八：连续签到领金币数
- SQL 三十九：国庆期间的7日动销率和滞销率（难）
- SQL 四十：同时在线最多的人数(学到了)
- SQL 四十一：同时在线人数问题
- SQL 四十二：会话划分问题（没做出来）
- SQL 四十三：间断连续登录用户问题（🥬，难）
- SQL 四十四：日期交叉问题（不会，思想值得学习）
- SQL 四十五：复购率问题
- SQL 四十六：出勤率问题
- SQL 四十七：打车问题
- SQL 四十八：排列问题
- SQL 四十九：视频热度问题
- SQL 五十：员工在职人数问题
SQL 一：查询累积销量排名第二的商品
题目需求
查询订单明细表（order_detail）中销量（下单件数）排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回。
期望结果如下：
| sku_id |
|---|
| 11 |
需要用到的表：
订单明细表：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
nvl(sku_id,null) as sku_id
from (select
sku_id,
rank() over (order by sum(sku_num) desc) rk
from order_detail
group by sku_id
) t1
where rk = 2;
SQL 二：查询至少连续三天下单的用户
题目需求
查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下：
| user_id |
|---|
| 101 |
订单信息表：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
user_id
from (
SELECT
user_id,
create_date,
lead(create_date,2,null) over(partition by user_id order by create_date asc) lead_2_date, -- 取后两行数据，如果没有就返回null
date_add(create_date,2) add_2_date -- 将当前时间 +2 相当于2天后
from (
select user_id, create_date from order_info group by user_id,create_date
) t1
) t2
where
lead_2_date = add_2_date -- 比较两天后的时间是否登录后两行数据
group by
user_id;
思路：按user_id分组和create_date升序 进行开窗，通过lead函数，取当前日期后两行的数据得到lead_2_date，通过date_add函数，对当前日期加2天得到add_2_date，两者相比较，如果相对即为连续3天下单
tip：为了避免统一用户当天多次消费，用 group by user_id,create_date 进行了去重操作
SQL 三：查询各品类销售商品的种类数及销量最高的商品
题目需求
从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品，
期望结果如下：
| category_id |
category_name |
sku_id |
name |
order_num |
sku_cnt |
|---|---|---|---|---|---|
| 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 | 户外 |
题解：
select
category_id,
category_name,
sku_id,
name,
order_num,
sku_cnt
from (
SELECT
category_id,
category_name,
sku_id,
name,
order_num,
sku_cnt,
rank() over(partition by category_id
order by order_num desc) as rk
from (
select
od.sku_id,
t1.category_id,
t1.category_name,
si.name,
sum(od.sku_num) order_num,
t1.sku_cnt
from (
select
ci.category_id,
ci.category_name,
count(*) as sku_cnt
from
sku_info si
join category_info ci on si.category_id=ci.category_id
group by
ci.category_id,
ci.category_name
) t1
join sku_info si on t1.category_id = si.category_id
join order_detail od on si.sku_id = od.sku_id
group by
od.sku_id,
t1.category_id,
t1.category_name,
si.name,
t1.sku_cnt
) t2
) t3
where rk = 1;
注意点：在hql中 group by中出现的字段，在select中要出现，要么是要查询的，要么用聚合函数包裹住
SQL 四：查询用户的累计消费金额及VIP等级
题目需求：
从订单信息表(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 |
create_date |
sum_so_far <decimal(16,2)> (截至每个下单日期的累计下单金额) | vip_level |
|---|---|---|---|
| 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
t1.*,
case
when sum_so_far>=100000 then '钻石会员'
when sum_so_far>=80000 then '白金会员'
when sum_so_far>=50000 then '黄金会员'
when sum_so_far>=30000 then '白银会员'
when sum_so_far>=10000 then '青铜会员'
when sum_so_far>=0 then '普通会员'
end vip_level
from (
select
t0.user_id,
t0.create_date,
sum(t0.total_amount) over(partition by t0.user_id order by t0.create_date) sum_so_far
from (select user_id, create_date, sum(total_amount) as total_amount from order_info group by user_id,create_date)t0
)t1 order by t1.user_id,t1.create_date;
要考虑一个用户可能同一天多次消费
SQL 五：查询首次下单后第二天连续下单的用户比率
题目需求：
从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例，结果保留一位小数，使用百分数显示，
期望结果如下：
| percentage |
|---|
| 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 |
题解：
select
concat(round(count(distinct t4.user_id) / count(distinct oi.user_id),1) * 100,'%') as percentage
from order_info oi,
(select
user_id
from (
select
user_id,
create_date,
datediff(create_date,first_date) as diff
from (
select
user_id,
create_date,
first_value(create_date) over(partition by user_id
order by create_date) as first_date
from (
select
distinct user_id,create_date
from order_info
) t1
) t2
)t3
where t3.diff=1)t4;
先把每个订单中的用户首次交易日期找出来，然后通过2值计算，统计相差1的人数，最后计算百分比
SQL 六：每个商品销售首年的年份、销售数量和销售金额
题目需求：
从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额。
期望结果如下：
| sku_id |
year |
order_num |
order_amount <decimal(16,2)> (首年销售金额) |
|---|---|---|---|
| 1 | 2020 | 2 | 4000.00 |
| 2 | 2020 | 26 | 260.00 |
| 3 | 2020 | 1 | 5000.00 |
| 4 | 2021 | 53 | 318000.00 |
| 5 | 2021 | 242 | 121000.00 |
| 6 | 2020 | 6 | 12000.00 |
| 7 | 2020 | 35 | 3500.00 |
| 8 | 2020 | 59 | 35400.00 |
| 9 | 2021 | 194 | 194000.00 |
| 10 | 2020 | 94 | 9400.00 |
| 11 | 2020 | 95 | 4750.00 |
| 12 | 2020 | 83 | 1660.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 |
题解：
select
distinct first_value(sku_id) over(partition by sku_id order by year) sku_id, -- 取首先数据
first_value(year) over(partition by sku_id order by year) year,
first_value(order_num) over(partition by sku_id order by year) order_num,
first_value(order_amount) over(partition by sku_id order by year) order_amount
from (
select
sku_id,
year(create_date) as year,
sum(sku_num) as order_num,
sum(price*sku_num) as order_amount
from order_detail
group by sku_id,year(create_date) -- 统计每年的总和
)t1
思考：SQL优化，不要用distinct
SQL 七：筛选去年总销量小于100的商品
题目需求：
从订单明细表(order_detail)中筛选出去年总销量小于100的商品及其销量，假设今天的日期是2022-01-10，不考虑上架时间小于一个月的商品
期望结果如下：
| sku_id |
name |
order_num |
|---|---|---|
| 1 | xiaomi 10 | 49 |
| 3 | apple 12 | 35 |
| 4 | xiaomi 13 | 53 |
| 6 | 洗碗机 | 26 |
需要用到的表：
商品信息表：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 |
订单明细表：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
t1.sku_id,
si.name,
t1.order_num
from (
select
sku_id,
sum(sku_num) as order_num
from order_detail
where datediff('2022-01-10',create_date)>30 and -- 不考虑小于一个月的数据
year(create_date)='2021'
group by sku_id
having order_num<100 -- 对统计后的数据进行筛选
) t1
join sku_info si on t1.sku_id = si.sku_id;
思考：SQL优化 -> 据说Hive中要少用Having函数
SQL 八：查询每日新用户数
题目需求
从用户登录明细表（user_login_detail）中查询每天的新增用户数，若一个用户在某天登录了，且在这一天之前没登录过，则任务该用户为这一天的新增用户。
期望结果如下：
| login_date_first |
user_count |
|---|---|
| 2021-09-21 | 1 |
| 2021-09-22 | 1 |
| 2021-09-23 | 1 |
| 2021-09-24 | 1 |
| 2021-09-25 | 1 |
| 2021-09-26 | 1 |
| 2021-09-27 | 1 |
| 2021-10-04 | 2 |
| 2021-10-06 | 1 |
需要用到的表：
用户登录明细表：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 |
题解：
select
login_date_first,
count(user_id) as user_count
from (
select
date_format(login_date_first,'yyyy-MM-dd') as login_date_first,
user_id
from (
select
distinct user_id,
first_value(login_ts) over(partition by user_id order by login_ts) login_date_first -- 统计首次登录用户，即每条新增用户
from user_login_detail
)t1
)t2
group by login_date_first;
思考：SQL优化 -> 不要使用distinct
SQL 九：统计每个商品的销量最高的日期
题目需求
从订单明细表（order_detail）中统计出每种商品销售件数最多的日期及当日销量，如果有同一商品多日销量并列的情况，取其中的最小日期。
期望结果如下：
| sku_id |
create_date |
sum_num |
|---|---|---|
| 1 | 2021-09-30 | 9 |
| 2 | 2021-10-02 | 5800 |
| 3 | 2021-10-05 | 9 |
| 4 | 2021-10-07 | 10 |
| 5 | 2021-10-03 | 47 |
| 6 | 2021-10-03 | 8 |
| 7 | 2021-10-05 | 58 |
| 8 | 2020-10-08 | 59 |
| 9 | 2021-10-01 | 45 |
| 10 | 2020-10-08 | 94 |
| 11 | 2020-10-08 | 95 |
| 12 | 2021-10-03 | 20400 |
需要用到的表：
订单明细表：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
distinct sku_id,
first_value(create_date) over(partition by sku_id order by sum_num desc,create_date asc) as create_date,
first_value(sum_num) over(partition by sku_id order by sum_num desc,create_date asc) as sum_num -- 按要求开窗，取第一个值
from (
select
sku_id,
create_date,
sum(sku_num) as sum_num -- 统计每个商品一天的销量
from order_detail
group by sku_id,create_date
) t1;
SQL优化
select
sku_id,
create_date,
sum_num
from(
select
sku_id,
create_date,
sum_num,
row_number() over(partition by sku_id order by sum_num desc,create_date asc) as rn
from (
select
sku_id,
create_date,
sum(sku_num) as sum_num -- 统计每个商品一天的销量
from order_detail
group by sku_id,create_date
) t1
)t2
where rn = 1;
SQL 十：查询销售件数高于品类平均数的商品
题目需求：
从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品
期望结果如下：
| sku_id |
name |
sum_num |
cate_avg_num |
|---|---|---|---|
| 2 | 手机壳 | 6044 | 1546 |
| 5 | 破壁机 | 242 | 194 |
| 7 | 热水壶 | 252 | 194 |
| 8 | 微波炉 | 253 | 194 |
| 12 | 遮阳伞 | 20682 | 5373 |
需要用到的表：
商品信息表：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 |
订单明细表：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 |
题解：
select
sku_id,
name,
sum_num,
floor(cate_avg_num) as cate_avg_num -- 4.floor()向下取整
from(
select
*,
avg(sum_num) over(partition by category_id
rows between unbounded preceding and unbounded following) as cate_avg_num -- 按品类开一个全窗口，统计平均值
from (
select
sku_id,
name,
category_id,
sum(sku_num) as sum_num -- 2.统计该品类下的对应sku_id的销售件数
from (
select
od.sku_id,
si.name,
si.category_id,
od.sku_num
from order_detail od
left join sku_info si on od.sku_id = si.sku_id -- 1.进行表关联，获取对应品类信息
) t1
group by sku_id,name,category_id
)t2
)t3
where sum_num > cate_avg_num; -- 3.筛选大于对应平均值的数据
SQL 十一：用户注册、登录、下单综合统计
题目需求
从用户登录明细表（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年订单金额) <decimal(16,2)> |
|---|---|---|---|---|---|
| 101 | 2021-09-21 | 5 | 5 | 4 | 143660.00 |
| 102 | 2021-09-22 | 4 | 4 | 4 | 177850.00 |
| 103 | 2021-09-23 | 2 | 2 | 4 | 75890.00 |
| 104 | 2021-09-24 | 4 | 4 | 4 | 89880.00 |
| 105 | 2021-10-04 | 1 | 1 | 4 | 120100.00 |
| 106 | 2021-10-04 | 2 | 2 | 4 | 119150.00 |
| 107 | 2021-09-25 | 4 | 4 | 4 | 124150.00 |
| 108 | 2021-10-06 | 2 | 2 | 4 | 155770.00 |
| 109 | 2021-09-26 | 3 | 3 | 2 | 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 (订单金额) |
|---|---|---|---|
| 1 | 101 | 2021-09-30 | 29000.00 |
| 10 | 103 | 2020-10-02 | 28000.00 |
题解：
select DISTINCT
t1.user_id,
t1.register_date,
t1.total_login_count,
t2.login_count_2021,
t3.order_count_2021,
t3.order_amount_2021
from
(
select
user_id,
login_ts,
date_format (
first_value (login_ts) over (
partition by
user_id
order by
login_ts
),
'yyyy-MM-dd'
) as register_date,
count() over (
PARTITION by
user_id
) as total_login_count
from
user_login_detail
) t1
inner join (
select
user_id,
count(user_id) as login_count_2021
from
user_login_detail
where
year (login_ts) = '2021'
group by
user_id
) t2 on t1.user_id = t2.user_id
inner join (
select
user_id,
count(user_id) as order_count_2021,
sum(total_amount) as order_amount_2021
from
order_info
where
year (create_date) = '2021'
group by
user_id
) t3 on t2.user_id = t3.user_id;
-------------------------------------------------------
select login.user_id,
register_date,
total_login_count,
login_count_2021,
order_count_2021,
order_amount_2021
from (
select user_id,
-- 首次登陆（注册日期）
min(date_format(login_ts, 'yyyy-MM-dd')) register_date,
-- 累计登录次数
count(1) total_login_count,
-- 2021年登录次数
count(if(year(login_ts) = '2021', 1, null)) login_count_2021
from user_login_detail
group by user_id
) login
join
(
select user_id,
-- 下单次数
count(distinct(order_id)) order_count_2021,
-- 订单金额
sum(total_amount) order_amount_2021
from order_info
where year(create_date) = '2021'
group by user_id
) oi
on login.user_id = oi.user_id
SQL 十二：查询指定日期的全部商品价格
题目需求
查询所有商品（sku_info表）截至到2021年10月01号的最新商品价格（需要结合价格修改表进行分析）
期望结果如下：
| sku_id |
price <decimal(16,2)> （商品价格） |
|---|---|
| 1 | 2000.00 |
| 2 | 10.00 |
| 3 | 5000.00 |
| 4 | 6000.00 |
| 5 | 500.00 |
| 6 | 2000.00 |
| 7 | 100.00 |
| 8 | 600.00 |
| 9 | 1000.00 |
| 10 | 90.00 |
| 11 | 66.00 |
| 12 | 20.00 |
需要用到的表：
商品信息表：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 |
商品价格变更明细表：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 |
题解：
select
si.sku_id,
cast(if((si.from_date)>(t1.change_date),si.price,t1.new_price) as decimal(16,2)) as price -- 比较改动时间
from
sku_info si
left join (
select
sku_id,
new_price,
change_date,
rank() over(partition by sku_id order by change_date desc) rk -- 为每行数据进行排序（采用row_number函数可能会好点）
from sku_price_modify_detail
where change_date<='2021-10-01' -- 筛选截至指定时间之前的数据
)t1
on t1.rk = 1 and si.sku_id = t1.sku_id; -- 筛选最新改动数据，并且进行连接操作
SQL 十三：即时订单比例
题目需求：
订单配送中，如果期望配送日期和下单日期相同，称为即时订单，如果期望配送日期和下单日期不同，称为计划订单。
请从配送信息表（delivery_info）中求出每个用户的首单（用户的第一个订单）中即时订单的比例，保留两位小数，以小数形式显示。
期望结果如下：
| percentage <decimal(16,2)> |
|---|
| 0.50 |
需要用到的表：
配送信息表：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
cast(sum(order_status)/count(order_status) as decimal(16,2)) as percentage -- 统计计算比例
from (
select
if(order_date=custom_date,1,0) order_status -- 进行首单数据判断
from (
select
user_id,
order_date,
custom_date,
row_number() over(partition by user_id order by order_date) rn -- 按order_date排序，为每行数据赋值行号
from
delivery_info
)t1
where rn=1 -- 筛选出行号为1的数据就是用户首单数据了
) t2
SQL 十四：向用户推荐朋友收藏的商品
题目需求
现需要请向所有用户推荐其朋友收藏但是用户自己未收藏的商品，请从好友关系表（friendship_info）和收藏表（favor_info）中查询出应向哪位用户推荐哪些商品。
期望结果如下：
| user_id |
sku_id |
|---|---|
| 101 | 2 |
| 101 | 4 |
| 101 | 7 |
| 101 | 9 |
| 101 | 8 |
| 101 | 11 |
| 101 | 1 |
需要用到的表：
好友关系表：friendship_info
| user1_id（用户1 id） | user2_id（用户2 id） |
|---|---|
| 101 | 1010 |
| 101 | 108 |
| 101 | 106 |
收藏表：favor_info
| user_id(用户id) | sku_id(商品id) | create_date(收藏日期) |
|---|---|---|
| 101 | 3 | 2021-09-23 |
| 101 | 12 | 2021-09-23 |
| 101 | 6 | 2021-09-25 |
题解：
SELECT
t1.user1_id user_id,t2.sku_id
FROM
friendship_info t1
JOIN favor_info t2
ON t1.user2_id=t2.user_id --直接获得好友收藏记录
WHERE
concat(t1.user1_id,t2.sku_id) --将好友收藏的sku_id与自己关联
NOT IN ( -- 筛选出好友收藏了，自己没收藏的记录
SELECT concat(user_id,sku_id) --查找的是自己的收藏记录
FROM favor_info
)
GROUP BY t1.user1_id,t2.sku_id;
有点绕
SQL 十五：查询所有用户的连续登录两天及以上的日期区间
题目需求
从登录明细表（user_login_detail）中查询出，所有用户的连续登录两天及以上的日期区间，以登录时间（login_ts）为准。
期望结果如下：
| user_id |
start_date |
end_date |
|---|---|---|
| 101 | 2021-09-27 | 2021-09-30 |
| 102 | 2021-10-01 | 2021-10-02 |
| 106 | 2021-10-04 | 2021-10-05 |
| 107 | 2021-10-05 | 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 |
题解：
select
user_id,
min(login_ts) as start_date, -- 取最小日期数据
max(date_next) as end_date -- 取最大日期数据
from (
select
user_id,
login_ts,
date_next,
datediff(date_next,login_ts) as diff_date_next
from (
select
user_id,
login_ts,
lead(login_ts,1,login_ts) over(partition by user_id order by login_ts)as date_next
from (
select
distinct user_id,
date_format(login_ts,'yyyy-MM-dd') as login_ts
from user_login_detail
)t1 -- 通过日期转换函数和distinct，对数据进行初步的过滤清洗
)t2 -- 通过lead()取后一行的数据，不存在就当前行数据=》相邻日期
)t3 -- 通过datediff()计算相邻日期与当前日期的差值
where diff_date_next = 1 -- 筛选出相邻日期差值为1 的数据
group by user_id -- 按id分组
或者
select
distinct user_id,
start_date,
end_date
from(
select
user_id,
first_value(login_date)over(partition by user_id ,flag rows between unbounded preceding and unbounded following) start_date,
last_value(login_date)over(partition by user_id ,flag rows between unbounded preceding and unbounded following) end_date,
count(*)over(partition by user_id ,flag) flag2 -- 按user_id和flag分组，统计条数=》为连续登录的天数
from(
select
user_id,
login_date,
date_sub(login_date,rank()over(partition by user_id order by login_date asc)) flag -- 当前日期减去行号=》如果为连续登录，那么值应该是相同的
from(
select
distinct user_id,
date_format(login_ts,"yyyy-MM-dd") login_date
from user_login_detail )t1
)t2
)t3
where flag2 >= 2; -- 筛选出连续登录大于2的数据
SQL 十六：男性和女性每日的购物总金额统计
题目需求
从订单信息表（order_info）和用户信息表（user_info）中，分别统计每天男性和女性用户的订单总金额，如果当天男性或者女性没有购物，则统计结果为0。
期望结果如下：
| create_date |
total_amount_male <decimal(16,2)> （男性用户总金额） | total_amount_female <decimal(16,2)> （女性用户总金额） |
|---|---|---|
| 2020-10-08 | 51950.00 | 24020.00 |
| 2021-09-27 | 29000.00 | 0.00 |
| 2021-09-28 | 70500.00 | 0.00 |
| 2021-09-29 | 43300.00 | 0.00 |
| 2021-09-30 | 860.00 | 0.00 |
| 2021-10-01 | 0.00 | 171680.00 |
| 2021-10-02 | 0.00 | 76150.00 |
| 2021-10-03 | 89880.00 | 5910.00 |
| 2021-10-04 | 9390.00 | 120100.00 |
| 2021-10-05 | 109760.00 | 69850.00 |
| 2021-10-06 | 101070.00 | 54300.00 |
| 2021-10-07 | 54700.00 | 129480.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 |
用户信息表：user_info
| user_id(用户id) | gender(性别) | birthday(生日) |
|---|---|---|
| 101 | 男 | 1990-01-01 |
| 102 | 女 | 1991-02-01 |
| 103 | 女 | 1992-03-01 |
| 104 | 男 | 1993-04-01 |
题解：
SELECT
create_date,
sum(IF (t2.gender = '男', t1.total_amount, 0.0)) total_amount_male,
sum(IF (t2.gender = '女', t1.total_amount, 0.0)) total_amount_female
FROM
order_info t1
JOIN user_info t2 ON t1.user_id = t2.user_id
GROUP BY
create_date
SQL 十七：订单金额趋势分析
题目需求
查询截止每天的最近3天内的订单金额总和以及订单金额日平均值，保留两位小数，四舍五入。
期望结果如下：
| create_date |
total_3d <decimal(16,2)> (最近3日订单金额总和) | avg_3d <decimal(16,2)> (最近3日订单金额日平均值) |
|---|---|---|
| 2020-10-08 | 75970.00 | 75970.00 |
| 2021-09-27 | 104970.00 | 52485.00 |
| 2021-09-28 | 175470.00 | 58490.00 |
| 2021-09-29 | 142800.00 | 47600.00 |
| 2021-09-30 | 114660.00 | 38220.00 |
| 2021-10-01 | 215840.00 | 71946.67 |
| 2021-10-02 | 248690.00 | 82896.67 |
| 2021-10-03 | 343620.00 | 114540.00 |
| 2021-10-04 | 301430.00 | 100476.67 |
| 2021-10-05 | 404890.00 | 134963.33 |
| 2021-10-06 | 464470.00 | 154823.33 |
| 2021-10-07 | 519160.00 | 173053.33 |
需要用到的表：
订单信息表：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 t1 as (
select
create_date,
sum(total_amount) as total_amount
from
order_info
group by create_date
) -- 创建一个统计好每天订单总金额的视图
select
create_date,
sum(total_amount) as total_3d, -- 直接聚合统计
cast(avg(total_amount) as decimal(16,2)) as avg_3d
from (
select
t1.create_date,
t2.total_amount
from
t1 inner join t1 t2
on t2.create_date<=t1.create_date and t1.create_date <= date_add(t2.create_date,2) -- 通过该条件筛选出了截至每天的最近3填内的订单金额
-- t2.create_date<=t1.create_date确保了是过去数据
-- t1.create_date <= date_add(t2.create_date,2)确保了是最近3天的数据
)t3
group by create_date;
SQL 十八：购买过商品1和商品2但是没有购买商品3的顾客
题目需求
从订单明细表(order_detail)中查询出所有购买过商品1和商品2，但是没有购买过商品3的用户，
期望结果如下：
| user_id |
|---|
| 103 |
| 105 |
需要用到的表：
订单信息表：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 |
题解：
select
user_id
from (
select
oi.user_id,
collect_set(sku_id) as sku_id_set -- 通过集合封装
from
order_info oi left join order_detail od
on oi.order_id = od.order_id
group by oi.user_id
) t1 -- 统计每个user_id的购买sku_id记录
where array_contains(sku_id_set,'1')
and array_contains(sku_id_set,'2')
and not array_contains(sku_id_set,'3') -- array_contains()函数是用来判断集合是否存在某个元素的，返回的是boolean类型
SQL 十九：统计每日商品1和商品2销量的差值
题目需求
从订单明细表（order_detail）中统计每天商品1和商品2销量（件数）的差值（商品1销量-商品2销量）
期望结果如下：
| create_date |
diff |
|---|---|
| 2020-10-08 | -24 |
| 2021-09-27 | 2 |
| 2021-09-30 | 9 |
| 2021-10-01 | -10 |
| 2021-10-02 | -5800 |
| 2021-10-03 | 4 |
| 2021-10-04 | -55 |
| 2021-10-05 | -30 |
| 2021-10-06 | -49 |
| 2021-10-07 | -40 |
需要用到的表：
订单明细表：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
create_date,
sku1_num - sku2_num as diff
from (
select
create_date,
sum(if(sku_id=1,sku_num,0)) as sku1_num, -- 统计sku_id为1的下单件数
sum(if(sku_id=2,sku_num,0)) as sku2_num -- 统计sku_id为2的下单件数
from
order_detail
where sku_id = 1 or sku_id = 2 -- 筛选出只有sku_id为1和2的信息
group by create_date
order by create_date
) t1
SQL 二十：询出每个用户的最近三笔订单
题目需求
从订单信息表（order_info）中查询出每个用户的最近三个下单日期的所有订单
期望结果如下：
| user_id |
order_id |
create_date |
|---|---|---|
| 101 | 2 | 2021-09-28 |
| 101 | 3 | 2021-09-29 |
| 101 | 4 | 2021-09-30 |
| 102 | 5 | 2021-10-01 |
| 102 | 6 | 2021-10-01 |
| 102 | 8 | 2021-10-02 |
| 103 | 9 | 2021-10-02 |
| 103 | 10 | 2021-10-02 |
| 103 | 12 | 2021-10-03 |
| 104 | 13 | 2021-10-03 |
| 104 | 14 | 2021-10-03 |
| 104 | 15 | 2021-10-03 |
| 105 | 17 | 2021-10-04 |
| 105 | 18 | 2021-10-04 |
| 105 | 19 | 2021-10-04 |
| 106 | 22 | 2021-10-05 |
| 106 | 23 | 2021-10-05 |
| 106 | 24 | 2021-10-05 |
| 107 | 25 | 2021-10-05 |
| 107 | 27 | 2021-10-06 |
| 107 | 28 | 2021-10-06 |
| 108 | 29 | 2021-10-06 |
| 108 | 31 | 2021-10-07 |
| 108 | 32 | 2021-10-07 |
| 109 | 33 | 2021-10-07 |
| 109 | 35 | 2021-10-08 |
| 109 | 36 | 2021-10-08 |
| 1010 | 37 | 2021-10-08 |
| 1010 | 38 | 2021-10-08 |
| 1010 | 39 | 2020-10-08 |
需要用到的表：
订单信息表：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
user_id,
order_id,
create_date
from (
select
user_id,
order_id,
create_date,
dense_rank() over(partition by user_id order by create_date desc) as dr
from
order_info
)t1
where dr<=3
order by user_id,create_date;
SQL 二十一：查询每个用户登录日期的最大空档期
题目需求
从登录明细表（user_login_detail）中查询每个用户两个登录日期（以login_ts为准）之间的最大的空档期。统计最大空档期时，用户最后一次登录至今的空档也要考虑在内，假设今天为2021-10-10。
期望结果如下：
| user_id |
max_diff |
|---|---|
| 101 | 10 |
| 102 | 9 |
| 103 | 10 |
| 104 | 9 |
| 105 | 6 |
| 106 | 5 |
| 107 | 10 |
| 108 | 4 |
| 109 | 10 |
| 1010 | 12 |
需要用到的表：
用户登录明细表：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 |
题解：
select
user_id,
max(next_login_diff) as max_diff
from (
select
user_id,
datediff(next_login_ts, login_ts) as next_login_diff
from (
select
user_id,
date_format(login_ts,'yyyy-MM-dd') as login_ts,
date_format(lead(login_ts,1,'2021-10-10') over(partition by user_id order by login_ts),'yyyy-MM-dd') as next_login_ts -- 因为有截至日期，获取后一天的数据会方便点
from
user_login_detail
) t1
)t2
group by user_id;
SQL二十二：查询相同时刻多地登陆的用户（不会）
题目需求
从登录明细表（user_login_detail）中查询在相同时刻，多地登陆（ip_address不同）的用户
期望结果如下：
| user_id |
|---|
| 101 |
| 102 |
| 104 |
| 107 |
需要用到的表：
用户登录明细表：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 |
题解：
SELECT
user_id
from(
SELECT *,
lead(login_ts,1,'9999-12-31')over(partition by user_id order by login_ts) lead_login, -- 获取上一次登录时间，没有就置为无限大
lead(ip_address,1)over(partition by user_id order by login_ts) lead_ip -- 获取上一次登录的ip地址
from user_login_detail
)t1
where lead_login<logout_ts -- 比较
and ip_address != lead_ip;
我题目没理解到？时刻指的是一天内还是登录时间段？不用考虑登出时间？正常一个账号也是不能多方登录的？
SQL 二十三：销售额完成任务指标的商品
题目需求
商家要求每个商品每个月需要售卖出一定的销售总额
假设1号商品销售总额大于21000，2号商品销售总额大于10000，其余商品没有要求
请写出SQL从订单详情表中（order_detail）查询连续两个月销售总额大于等于任务总额的商品
结果如下：
| sku_id |
|---|
| 1 |
需要用到的表：
订单明细表：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
from (
select
sku_id,
amount_month,
create_date,
add_months(create_date,1) as add_1_month,
lead(create_date,1,null) over(partition by sku_id order by create_date) lead_1_date
from (
select
sku_id,
create_date,
amount_month
from (
select
t1.sku_id,
t1.create_date,
sum(t1.amount) as amount_month,
if (sku_id=1,21000,10000) as target_amount -- 插入任务指标
from (
select
sku_id,
date_format(create_date,'yyyy-MM-01') as create_date, -- 将日期转换成月初
price * sku_num as amount
from order_detail
) t1
where t1.sku_id in (1,2)
group by t1.sku_id,t1.create_date
) t2
where amount_month>target_amount -- 筛选完成任务指标的数据
)t3
) t4
where add_1_month = lead_1_date -- 取后一行日期数据等于当前日期加1个月时间的数据
group by sku_id;
其他思路：
SELECT
sku_id
from (
SELECT
*,
month+year*12 sum_month, --把年份转成月份值
row_number()over(partition by sku_id order by year,month) rn
from(
SELECT
*,
if(sku_id=1,21000,10000) ask
from(
SELECT
sku_id,
year(create_date) year,
month(create_date) month,
sum(price*sku_num) sum_num
from
order_detail
where
sku_id in (1,2)
GROUP by
sku_id,year(create_date),month(create_date)
)t1
)t2
where sum_num > ask -- 筛选完成任务指标的数据
)t3
group by sku_id,sum_month-rn -- sum_month-rn把连续月份放在一个聚合里
HAVING count(*)>1 -- 筛选两天以上的
SQL 二十四：更多操作根据商品销售情况进行商品分类
题目需求
从订单详情表中（order_detail）对销售件数对商品进行分类，0-5000为冷门商品，5001-19999位一般商品，20000往上为热门商品，并求出不同类别商品的数量
结果如下：
| category |
cn |
|---|---|
| 一般商品 | 1 |
| 冷门商品 | 10 |
| 热门商品 | 1 |
需要用到的表：
订单明细表：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 |
题解：
select
category,
count(category) as cn
from (
select
case
when sku_num>=20000 then "热门商品"
when sku_num>=5001 then "一般商品"
when sku_num>=0 then "冷门商品"
end as category
from (
select
sku_id,
sum(sku_num) sku_num
from
order_detail
group by
sku_id
) t1
)t2
group by category;
SQL 二十五：各品类销量前三的所有商品
题目需求
从订单详情表中（order_detail）和商品（sku_info）中查询各个品类销售数量前三的商品。如果该品类小于三个商品，则输出所有的商品销量。
结果如下：
| sku_id |
category_id |
|---|---|
| 2 | 1 |
| 4 | 1 |
| 1 | 1 |
| 8 | 2 |
| 7 | 2 |
| 5 | 2 |
| 12 | 3 |
| 11 | 3 |
| 10 | 3 |
需要用到的表：
订单明细表：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 |
题解：
select
sku_id,
category_id
from (
select
*,
row_number() over(partition by category_id order by amount_sku_num desc) as rn
from (
select
od.sku_id,
si.category_id,
sum(od.sku_num) as amount_sku_num
from
order_detail od left join sku_info si on od.sku_id = si.sku_id
group by od.sku_id,si.category_id
) t1
) t2
where rn <=3;
SQL 二十六：各品类中商品价格的中位数
题目需求
从商品信息表（sku_info）求出各分类商品价格的中位数，如果一个分类下的商品个数为偶数则输出中间两个值的平均值，如果是奇数，则输出中间数即可。
结果如下：
| category_id |
medprice <decimal(16,2)> （中位数） |
|---|---|
| 1 | 3500.00 |
| 2 | 550.00 |
| 3 | 75.00 |
需要用到的表：
商品信息表：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 |
题解：
select
category_id,
cast(avg(price) as decimal(16,2)) as medprice
from (
select
category_id,
sku_id,
price,
row_number() over(partition by category_id order by price) as rn,
count(*) over(partition by category_id) as cnt
from
sku_info
) t1
where rn = ceil(cnt/2) or rn = ceil((cnt+1)/2)
group by category_id;
通过对行号计算，筛选出中间的两个值
SQL 二十七：找出销售额连续3天超过100的商品
题目需求
从订单详情表（order_detail）中找出销售额连续3天超过100的商品
结果如下：
| sku_id |
|---|
| 1 |
| 10 |
| 11 |
| 12 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
需要用到的表：
订单明细表：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
from (
select
sku_id,
create_date,
date_add(create_date,3) as add_3_day,
lead(create_date,3) over(partition by sku_id order by create_date) as lead_3_day
from (
select
sku_id,
create_date
from
order_detail
where
(sku_num * price)>100
)t1
)t2
where add_3_day = lead_3_day
group by sku_id;
这题可以用求《连续登录3天的用户》思想来解决，只不过要先过滤掉销售额小于100的信息
SQL 二十八：查询有新注册用户的当天的新用户数量、新用户的第一天留存率
题目需求
从用户登录明细表（user_login_detail）中首次登录算作当天新增，第二天也登录了算作一日留存
结果如下：
| first_login |
register |
retention <decimal(16,2)> （留存率） |
|---|---|---|
| 2021-09-21 | 1 | 0.00 |
| 2021-09-22 | 1 | 0.00 |
| 2021-09-23 | 1 | 0.00 |
| 2021-09-24 | 1 | 0.00 |
| 2021-09-25 | 1 | 0.00 |
| 2021-09-26 | 1 | 0.00 |
| 2021-09-27 | 1 | 0.00 |
| 2021-10-04 | 2 | 0.50 |
| 2021-10-06 | 1 | 0.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 |
题解：
select
login_date first_login,
count(*) register,-- 获取每日注册的用户数
cast(sum(if(datediff(lead_date,login_date)=1,1,0))/count(*) as decimal(16,2)) retention -- 首次登录与第二次登录做计算
from(
select
user_id,
login_date,
lead(login_date,1)over(partition by user_id order by login_date) lead_date, -- 获取用户下一次登录时间
rank()over(partition by user_id order by login_date) rk -- 为每个用户对时间排名
from(
select -- 查找出每个用户的每天登录情况
user_id,
date_format(login_ts,'yyyy-MM-dd') login_date
from
user_login_detail
group by
user_id,date_format(login_ts,'yyyy-MM-dd')
)t1
)t2
where rk=1 -- 取第一条数据，很好的筛选出了首次登录数据
group by login_date -- 按登录时间分组，将同一天注册的用户分到一组
SQL 二十九：求出商品连续售卖的时间区间
题目需求
从订单详情表（order_detail）中，求出商品连续售卖的时间区间
结果如下（截取部分）：
| sku_id |
start_date |
end_date |
|---|---|---|
| 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 | 5 |
题解：
select
sku_id,
min(create_date) as start_date, -- 取区间值
max(create_date) as end_date
from (
select
sku_id,
create_date,
date_add(create_date,-rn) as continuous_start_time -- 计算出连续登录的首天日期
from (
select
sku_id,
create_date,
row_number() over(partition by sku_id order by create_date) as rn -- 取行号
from order_detail
group by
sku_id,create_date -- 先清洗出一天多余的售卖记录
)t1
) t2
group by sku_id,continuous_start_time
（转）一样思路的跨行取值函数解法：
SELECT DISTINCT
sku_id
,FIRST_VALUE(create_date) OVER (PARTITION BY sku_id, rr ORDER BY create_date) AS start_date
,FIRST_VALUE(create_date) OVER (PARTITION BY sku_id, rr ORDER BY create_date DESC) AS end_date
FROM (
SELECT sku_id
,create_date
,DATE_SUB(create_date, DENSE_RANK() OVER (PARTITION BY sku_id ORDER BY create_date)) AS rr
FROM order_detail
) AS temp
SQL 三十：登录次数及交易次数统计
题目需求
分别从登陆明细表（user_login_detail）和配送信息表中用户登录时间和下单时间统计登陆次数和交易次数
结果如下（截取部分）：
| user_id |
login_date |
login_count |
order_count |
|---|---|---|---|
| 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
t2.user_id,
t2.login_date,
t2.login_count,
nvl(t3.order_count,0) as order_count
from (
select -- 先求出登录次数
user_id,
login_ts as login_date,
count(*) as login_count
from(
select
user_id,
date_format(login_ts,'yyyy-MM-dd') as login_ts
from user_login_detail
)t1
group by user_id,login_ts
)t2
left join( -- left join链接（先登录才能下单）
select -- 再求出下单次数
user_id,
order_date,
count(*) as order_count
from delivery_info
group by user_id,order_date
) t3
on t2.user_id = t3.user_id and t2.login_date = t3.order_date
要考虑出现的情况：一天登录n次，下单0次；一天登录1次，下单n次
SQL 三十一： 按年度列出每个商品销售总额
题目需求
从订单明细表（order_detail）中列出每个商品每个年度的购买总额
结果如下（截取部分）：
| sku_id |
year_date |
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,
year_date,
sum(amount) as sku_sum
from (
select
sku_id,
year(create_date) as year_date,
(price * sku_num) as amount
from order_detail
) t1
group by sku_id,year_date
简化？：
SELECT
sku_id,year(create_date) as year_date,
sum(price * sku_num) as sku_sum
from order_detail
group by sku_id , year(create_date)
SQL 三十二：某周内每件商品每天销售情况
题目需求
从订单详情表（order_detail）中查询2021年9月27号-2021年10月3号这一周所有商品每天销售情况。
结果如下：
| sku_id |
monday |
tuesday |
wednesday |
thursday |
friday |
saturday |
sunday |
|---|---|---|---|---|---|---|---|
| 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 |
题解：
select
sku_id,
sum(if(day_of_week == 2,sku_num,0)) as monday, -- 注意:国外认为一周的第一天为星期天
sum(if(day_of_week == 3,sku_num,0)) as tuesday,
sum(if(day_of_week == 4,sku_num,0)) as wednesday,
sum(if(day_of_week == 5,sku_num,0)) as thursday,
sum(if(day_of_week == 6,sku_num,0)) as friday,
sum(if(day_of_week == 7,sku_num,0)) as saturday,
sum(if(day_of_week == 1,sku_num,0)) as sunday
from(
select
sku_id,
sum(sku_num) as sku_num,
dayofweek(create_date) as day_of_week -- dayofweek函数可以计算出当天为一周的第几天
from order_detail
where create_date between '2021-09-27' and '2021-10-03'
group by sku_id,create_date
)t1
group by sku_id;
SQL 三十三：查看每件商品的售价涨幅情况
题目需求
从商品价格变更明细表（sku_price_modify_detail），得到最近一次价格的涨幅情况，并按照涨幅升序排序。
结果如下：
| sku_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 |
题解：
select
sku_id,
sum(new_price) as price_change
from (
select
sku_id,
if(rn==1,new_price,-new_price) as new_price -- 最近一次为正数
from (
select
sku_id,
new_price,
change_date,
row_number() over(partition by sku_id order by change_date desc) rn
from sku_price_modify_detail
)t1
where rn<=2 -- 筛选出最近2次的变更记录
) t2
group by sku_id;
思路二：行转列......
SQL 三十四：销售订单首购和次购分析
题目需求
通过商品信息表（sku_info）订单信息表（order_info）订单明细表（order_detail）分析如果有一个用户成功下单两个及两个以上的购买成功的手机订单（购买商品为xiaomi 10，apple 12，小米13）那么输出这个用户的id及第一次成功购买手机的日期和第二次成功购买手机的日期，以及购买手机成功的次数。
结果如下：
| user_id |
first_date |
last_date |
cn |
|---|---|---|---|
| 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 |
题解：
select
user_id,
min(create_date) as first_date,
max(create_date) as last_date,
count(*) as cn
from (
select
oi.user_id,
od.create_date
from
order_detail od left join order_info oi on od.order_id = oi.order_id
left join sku_info si on od.sku_id = si.sku_id
where si.name in ('xiaomi 10','xiaomi 13','apple 12')
)t1
group by user_id
这题与我的题目理解有出入（原本筛选条件：where si.category_id=1 and od.sku_num>=2）
优化：这题可谓词下推（略）
SQL 三十五：同期商品售卖分析表
题目需求
从订单明细表（order_detail）中。
求出同一个商品在2021年和2022年中同一个月的售卖情况对比。
结果如下（截取部分）：
| sku_id |
month |
2020_skusum |
2021_skusum |
|---|---|---|---|
| 1 | 9 | 0 | 11 |
| 1 | 10 | 2 | 38 |
| 10 | 10 | 94 | 205 |
| 11 | 10 | 95 | 225 |
| 12 | 9 | 0 | 43 |
| 12 | 10 | 83 | 20556 |
| 2 | 10 | 26 | 6018 |
| 3 | 9 | 0 | 5 |
| 3 | 10 | 1 | 30 |
| 4 | 9 | 0 | 9 |
需要用到的表：
订单明细表：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,
`month`,
sum(if (`year`=2020,sku_num,0)) as 2020_skusum, -- 行转列
sum(if (`year`=2021,sku_num,0)) as 2021_skusum
from (
select
sku_id,
month(create_date) as `month`,
year(create_date) as `year`,
sku_num
from
order_detail
) t1
group by sku_id,`month`
浓缩：
SELECT sku_id,
month(create_date) `month`,
sum(if(year(create_date) = '2020',sku_num,0)) 2020_skusum,
sum(if(year(create_date) = '2021',sku_num,0)) 2021_skusum
FROM order_detail
GROUP BY sku_id,month(create_date)
SQL 三十六：国庆期间每个品类的商品的收藏量和购买量
题目需求
从订单明细表（order_detail）和收藏信息表（favor_info）统计2021国庆期间，每个商品总收藏量和购买量
结果如下：
| sku_id |
sku_sum |
favor_cn |
|---|---|---|
| 1 | 38 | 1 |
| 10 | 205 | 2 |
| 11 | 225 | 2 |
| 12 | 20556 | 0 |
| 2 | 6018 | 1 |
| 3 | 30 | 0 |
| 4 | 44 | 2 |
| 5 | 209 | 1 |
| 6 | 26 | 1 |
| 7 | 180 | 1 |
| 8 | 148 | 0 |
| 9 | 182 | 1 |
需要用到的表：
订单明细表：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 |
收藏信息表：favor_info
| user_id(用户id) | sku_id(商品id) | create_date(收藏日期) |
|---|---|---|
| 101 | 3 | 2021-09-23 |
| 101 | 12 | 2021-09-23 |
| 101 | 6 | 2021-09-25 |
题解：
select
si.sku_id,
nvl(si.sku_sum,0) as sku_sum,
nvl(fi.favor_cn,0) as favor_cn
from (
select
sku_id,
sum(sku_num) as sku_sum
from order_detail
where create_date between '2021-10-01' and '2021-10-07'
group by sku_id
) as si
full join (
select
sku_id,
count(*) as favor_cn
from favor_info
where create_date between '2021-10-01' and '2021-10-07'
group by sku_id
) as fi
on si.sku_id = fi.sku_id
要考虑购买了但没收藏，收藏了但没购买的情况，因此用 full join 和 nvl 进行操作好一点。
SQL 三十七：统计活跃间隔对用户分级结果（难）
题目需求
用户等级：
忠实用户：近7天活跃且非新用户
新晋用户：近7天新增
沉睡用户：近7天未活跃但是在7天前活跃
流失用户：近30天未活跃但是在30天前活跃
假设今天是数据中所有日期的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数
结果如下：
| level |
cn |
|---|---|
| 忠实用户 | 6 |
| 新增用户 | 3 |
| 沉睡用户 | 1 |
需要用到的表：
用户登录明细表：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 |
题解：
select
level,
count(distinct user_id) as cn
from (
select
user_id,
case
when datediff(today,first_login_ts)<=7 then '新增用户'
when datediff(today,last_login_ts)<=7 then '忠实用户'
when datediff(today,last_login_ts)>30 then '流式用户'
when datediff(today,last_login_ts)>7 then '沉睡用户'
else '未知用户'
end as level
from (
select
user_id,
max(logout_ts) over() as today,
min(login_ts) over(partition by user_id) as first_login_ts,
max(login_ts) over(partition by user_id) as last_login_ts
from (
select
user_id,
date_format(login_ts,'yyyy-MM-dd') as login_ts,
date_format(logout_ts,'yyyy-MM-dd') as logout_ts
from user_login_detail
) t1
)t2
) t3
group by level
题目理解错了，以为活跃用户指连续登录
SQL 三十八：连续签到领金币数
题目需求
用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。
每连续签到7天重新累积签到天数。
从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序
结果如下：
| user_id |
sum_coin_cn |
|---|---|
| 101 | 7 |
| 109 | 3 |
| 107 | 3 |
| 102 | 3 |
| 106 | 2 |
| 104 | 2 |
| 103 | 2 |
| 1010 | 2 |
| 108 | 1 |
| 105 | 1 |
需要用到的表：
用户登录明细表：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 |
题解：
select
user_id,
sum(
case -- 取模判断
when (continue_ts%7)=3 then 3
when (continue_ts%7)=0 then 7
else 1
end
) as sum_coin_cn
from (
select
user_id,
row_number() over (partition by user_id,diff_ts) as continue_ts -- 获取连续登录的天数
from (
select
user_id,
date_add(login_ts,-rn) as diff_ts -- 减去行号，取得连续登录的起始天数
from (
select
user_id,
login_ts,
row_number() over(partition by user_id order by login_ts) as rn -- 对日期取行号，便于后面求连续天数
from (
select -- 先格式化日期，转换成yyyy-MM-dd的格式
user_id,
date_format(login_ts,'yyyy-MM-dd') as login_ts
from user_login_detail
group by user_id,date_format(login_ts,'yyyy-MM-dd')
) t1
) t2
) t3
) t4
group by user_id
order by sum_coin_cn desc
SQL 三十九：国庆期间的7日动销率和滞销率（难）
题目需求
动销率定义为品类商品中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数）。
滞销率定义为品类商品中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品 / 已上架总商品数）。
只要当天任一店铺有任何商品的销量就输出该天的结果
从订单明细表（order_detail）和商品信息表（sku_info）表中求出国庆7天每天每个品类的商品的动销率和滞销率
结果如下（截取部分）：
| category_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 |
题解：
select
category_id,
sum(`if`(flag = 1, sale_rate, 0)) first_sale_rate,
sum(`if`(flag = 1, unsale_rate, 0)) first_unsale_rate,
sum(`if`(flag = 2, sale_rate, 0)) second_sale_rate,
sum(`if`(flag = 2, unsale_rate, 0)) second_unsale_rate,
sum(`if`(flag = 3, sale_rate, 0)) third_sale_rate,
sum(`if`(flag = 3, unsale_rate, 0)) third_unsale_rate,
sum(`if`(flag = 4, sale_rate, 0)) fourth_sale_rate,
sum(`if`(flag = 4, unsale_rate, 0)) fourth_unsale_rate,
sum(`if`(flag = 5, sale_rate, 0)) fifth_sale_rate,
sum(`if`(flag = 5, unsale_rate, 0)) fifth_unsale_rate,
sum(`if`(flag = 6, sale_rate, 0)) sixth_sale_rate,
sum(`if`(flag = 6, unsale_rate, 0)) sixth_unsale_rate,
sum(`if`(flag = 7, sale_rate, 0)) seventh_sale_rate,
sum(`if`(flag = 7, unsale_rate, 0)) seventh_unsale_rate
from (
select
category_id,
substr(create_date, 10, 1) flag, -- 获取天数
cast(sale / cn as DECIMAL(16, 2)) sale_rate,
cast((1 - sale / cn) as DECIMAL(16, 2)) unsale_rate
from (
select
category_id,
create_date,
count(distinct od.sku_id) sale, -- 统计不同商品类型每日销售了多少个sku_id
cn
from order_detail od
join ( -- 全连接
select
sku_id,
category_id,
count(1) over(partition by category_id) cn-- 统计一个商品类型下有多少个sku_id
from sku_info
)sku on sku.sku_id = od.sku_id -- 按sku_id进行连接
where create_date between '2021-10-01' and '2021-10-07' -- 筛选国庆期间的数据。不用考虑往年数据？？？
group by category_id, create_date, cn -- 此处cn分组只是为了后面能获取数据，因为cn在之前是按category_id统计的，因此是独一无二的
)t
)t1 group by category_id;
不用考虑国庆期间突然上架了一件商品，然后没人消费的情况？...
SQL 四十：同时在线最多的人数(学到了)
题目需求
根据用户登录明细表（user_login_detail），求出平台同时在线最多的人数。
结果如下：
| cn |
|---|
| 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 |
题解：
-- 如果这是实时流数据你会怎么操作？
-- 是不是来一条用户登录数据就 +1 ，用户登出就 -1。
-- 按照这个思路做：
select
max(count_people) as cn -- 筛选出最大同时在线人数
from (
select
sum(mark) over (order by `time`) as count_people -- 开窗统计在线人数
from (
select
login_ts as `time`,
1 as mark -- 登录操作记为1
from user_login_detail
union all -- 进行表合并
select
logout_ts as `time`,
-1 as mark -- 登出的操作记为-1
from user_login_detail
) t1
) t2
SQL 四十一：同时在线人数问题
现有各直播间的用户访问记录表（live_events）如下，表中每行数据表达的信息为，一个用户何时进入了一个直播间，又在何时离开了该直播间。
| user_id (用户id) | live_id (直播间id) | in_datetime (进入直播间的时间) | out_datetime (离开直播间的时间) |
|---|---|---|---|
| 100 | 1 | 2021-12-1 19:30:00 | 2021-12-1 19:53:00 |
| 100 | 2 | 2021-12-1 21:01:00 | 2021-12-1 22:00:00 |
| 101 | 1 | 2021-12-1 19:05:00 | 2021-12-1 20:55:00 |
现要求统计各直播间最大同时在线人数，期望结果如下：
| live_id |
max_user_count |
|---|---|
| 1 | 4 |
| 2 | 3 |
| 3 | 2 |
题解：
-- 与上一题一样，多了个分组
select
live_id,
cast(max(amount) as int)as max_user_count
from (
select
live_id,
sum(flag) over(partition by live_id order by datetime) as amount
from (
select
live_id,
in_datetime as datetime,
"1" as flag
from live_events
union all
select
live_id,
out_datetime as datetime,
"-1" as flag
from live_events
) t1
) t2
group by live_id;
SQL 四十二：会话划分问题（没做出来）
现有页面浏览记录表（page_view_events）如下，表中有每个用户的每次页面访问记录。
| user_id | page_id | view_timestamp |
|---|---|---|
| 100 | home | 1659950435 |
| 100 | good_search | 1659950446 |
| 100 | good_list | 1659950457 |
| 100 | home | 1659950541 |
| 100 | good_detail | 1659950552 |
| 100 | cart | 1659950563 |
| 101 | home | 1659950435 |
| 101 | good_search | 1659950446 |
| 101 | good_list | 1659950457 |
| 101 | home | 1659950541 |
| 101 | good_detail | 1659950552 |
| 101 | cart | 1659950563 |
| 102 | home | 1659950435 |
| 102 | good_search | 1659950446 |
| 102 | good_list | 1659950457 |
| 103 | home | 1659950541 |
| 103 | good_detail | 1659950552 |
| 103 | cart | 1659950563 |
规定若同一用户的相邻两次访问记录时间间隔小于60s，则认为两次浏览记录属于同一会话。现有如下需求，为属于同一会话的访问记录增加一个相同的会话id字段，会话id格式为"user_id-number"，其中number从1开始，用于区分同一用户的不同会话，期望结果如下：
| user_id |
page_id |
view_timestamp |
session_id |
|---|---|---|---|
| 100 | home | 1659950435 | 100-1 |
| 100 | good_search | 1659950446 | 100-1 |
| 100 | good_list | 1659950457 | 100-1 |
| 100 | home | 1659950541 | 100-2 |
| 100 | good_detail | 1659950552 | 100-2 |
| 100 | cart | 1659950563 | 100-2 |
| 101 | home | 1659950435 | 101-1 |
| 101 | good_search | 1659950446 | 101-1 |
| 101 | good_list | 1659950457 | 101-1 |
| 101 | home | 1659950541 | 101-2 |
| 101 | good_detail | 1659950552 | 101-2 |
| 101 | cart | 1659950563 | 101-2 |
| 102 | home | 1659950435 | 102-1 |
| 102 | good_search | 1659950446 | 102-1 |
| 102 | good_list | 1659950457 | 102-1 |
| 103 | home | 1659950541 | 103-1 |
| 103 | good_detail | 1659950552 | 103-1 |
题解：
select
user_id,
page_id,
view_timestamp,
concat(user_id,"-",flag) session_id -- 拼接
from (
select
user_id,
page_id,
view_timestamp,
sum(flag) over(partition by user_id order by view_timestamp) as flag -- 开窗聚合，一路加下去，1+0等于1，1+1等于2（表示进入第二个会话区域）
from (
select
user_id,
page_id,
view_timestamp,
if ((view_timestamp - last_view_timestamp)>60,1,0) flag -- 间隔大于60秒的标记为1，此时会话开头必为1，同一会话内的其他数据为0
from (
select
user_id,
page_id,
view_timestamp,
lag(view_timestamp,1,0) over(partition by user_id order by view_timestamp) as last_view_timestamp -- 按用户分区，获取前一条记录的时间戳。每个用户开始记录的时间戳将为0
from page_view_events
) t1
) t2
) t3
SQL 四十三：间断连续登录用户问题（🥬，难）
现有各用户的登录记录表（login_events）如下，表中每行数据表达的信息是一个用户何时登录了平台。
| user_id | login_datetime |
|---|---|
| 100 | 2021-12-01 19:00:00 |
| 100 | 2021-12-01 19:30:00 |
| 100 | 2021-12-02 21:01:00 |
现要求统计各用户最长的连续登录天数，间断一天也算作连续，例如：一个用户在1,3,5,6登录，则视为连续6天登录。期望结果如下：
| user_id |
max_day_count |
|---|---|
| 100 | 3 |
| 101 | 6 |
| 102 | 3 |
| 104 | 3 |
| 105 | 1 |
题解：
自己写的一塌糊涂
with t1 as ( -- 起到过滤去重作用
select
user_id,
date_format(login_datetime,'yyyy-MM-dd') login_datetime
from login_events
group by user_id,date_format(login_datetime,'yyyy-MM-dd')
)
select
user_id,
max(day_count) max_day_count -- 按用户分组，取最大值
from (
select
user_id,
sum(if(date_diff>2,1,date_diff)) over(partition by user_id,session_id) as day_count -- 统计相同会话内的连续登陆天数
from (
select
user_id,
sum(if(date_diff>2,1,0)) over(partition by user_id order by login_datetime) session_id, -- 相差大于2的记为1，标志新的会话开口
date_diff
from (
select
user_id,
login_datetime,
datediff(login_datetime,lag_1_date) date_diff -- 计算两者之间的日期差距
from (
select
user_id,
login_datetime,
lag(login_datetime,1,'1970-01-01') over(partition by user_id order by login_datetime) lag_1_date --获取上一条的数据
from t1
) t2
) t3
) t4
)t5
group by user_id
网友写的：
--这题有一点难度，和42题会话划分的思路有点类似，把连续登录（包括间断一天）的登录划分为一个会话内，下面细说
WITH tmp as( --没啥好说的，做一个数据格式化，同时去重
SELECT
user_id,
date_format(login_datetime,"yyyy-MM-dd") login_date
from login_events
group by user_id,date_format(login_datetime,"yyyy-MM-dd")
)
SELECT
user_id,
--4.这就是每一条子sql都保留login_date这个字段的意义，同一个会话内的最大日期减去最小日期得到的就是连续登录的天数
datediff(max(login_date),min(login_date)) + 1 max_day_count
from(
SELECT
user_id,
login_date,
--3.这里拼上一个user_id的目的是给每一个会话建立一个唯一标识，方便后面按照会话的唯一标识进行group by
concat(user_id,"-",sum(flag) over(PARTITION by user_id order by login_date )) flag
from(
SELECT
user_id,
login_date,
last_login,
--2.进行判断，今天登录的日期减去上一次登录的日期，如果大于2的话，就是一个会话的新起点，给这个新起点标记一个1，后面开窗聚合，范围为上无边界到当前行，同一个会话sum的值就会相同
IF(datediff(login_date,last_login)>2,1,0) flag
from(
SELECT
user_id,
login_date,
--1.注解从这里看起，取出用户上一次登录的日期
lag(login_date,1,'1970-01-01') over(PARTITION by user_id order by login_date) last_login
from tmp
)t1
)t2
)t3 group by user_id,flag
网友2：
select
user_id,
if (
sum(if (lx <= 2, 1, 0)) > sum(if (lx <= 1, 1, 0)),
sum(if (lx <= 2, 1, 0)) + sum(if (lx <= 1, 1, 0)),
sum(if (lx <= 2, 1, 0))
) max_day_count
from
(
select
user_id,
datediff (
lead (login, 1, '1970-01-01') over (
PARTITION by
user_id
order by
login
),
login
) lx
from
(
select distinct
user_id,
date_format (login_datetime, 'yyyy-MM-dd') login
from
login_events
) t1
) t2
group by
user_id
会话结论：一般都是把一个会话的开头置为1，其他为0，然后开窗聚合往下加，并且拼接主键。
SQL 四十四：日期交叉问题（不会，思想值得学习）
现有各品牌优惠周期表（promotion_info）如下，其记录了每个品牌的每个优惠活动的周期，其中同一品牌的不同优惠活动的周期可能会有交叉。
| promotion_id | brand | start_date | end_date |
|---|---|---|---|
| 1 | oppo | 2021-06-05 | 2021-06-09 |
| 2 | oppo | 2021-06-11 | 2021-06-21 |
| 3 | vivo | 2021-06-05 | 2021-06-15 |
现要求统计每个品牌的优惠总天数，若某个品牌在同一天有多个优惠活动，则只按一天计算。期望结果如下：
| brand |
promotion_day_count |
|---|---|
| vivo | 17 |
| oppo | 16 |
| redmi | 22 |
| huawei | 22 |
网友题解：
--解题思路：最关键的是第一步的开窗取值，取出本次活动开始时间之前所有活动中最大的活动结束日期。
With tmp as (
SELECT
brand,
start_date,
end_date,
max(end_date) over(PARTITION by brand order by start_date rows BETWEEN UNBOUNDED PRECEDING and 1 PRECEDING) last_end_date
from promotion_info
)
SELECT
brand,
sum(promotion_day_count) promotion_day_count
from(
SELECT
brand,
datediff(end_date,new_start_date) + 1 promotion_day_count -- 此次活动的结束时间减去新的开始时间（开始统计活动完全脱节与有一定包含关系的时间）
from(
SELECT
brand,
IF(last_end_date is null or start_date>last_end_date,start_date,date_add(last_end_date,1)) new_start_date,-- 如果本次活动开始前的最大结束时间没有，或者本次的开始时间大于之前的最大结束时间，那就取这次活动的开始时间为新的活动开始时间（方便后面统计活动完全脱节的时间），否则取活动开始的最大结束时间作为新的起始时间
end_date
from tmp
)t1 where new_start_date<=end_date -- 过滤掉活动完全包含的情况
)t2 group by brand
这个思路好，最好画个图理解
SQL 四十五：复购率问题
现有电商订单表（order_detail）如下。
| order_id (订单id) | user_id (用户id) | product_id (商品id) | price （售价） | cnt （数量） | order_date （下单时间） |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 5000 | 1 | 2022-01-01 |
| 2 | 1 | 3 | 5500 | 1 | 2022-01-02 |
| 3 | 1 | 7 | 35 | 2 | 2022-02-01 |
| 4 | 2 | 2 | 3800 | 3 | 2022-03-03 |
注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率
此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
近90天指包含最大日期（以订单详情表(order_detail)中最后的日期）在内的近90天。结果中复购率保留2位小数，并按复购率倒序、商品ID升序排序。
期望结果如下：
| product_id |
crp <decimal(16,2)> (复购率) |
|---|---|
| 3 | 1.00 |
| 9 | 1.00 |
| 8 | 0.50 |
| 5 | 0.33 |
| 7 | 0.25 |
| 1 | 0.00 |
| 2 | 0.00 |
| 6 | 0.00 |
题解：
select
product_id,
cast(sum(cnt)/count(1) as decimal(16,2)) as cpr
from (
select
user_id,
product_id,
if (count(1)>=2,1,0) as cnt
from order_detail
where order_date >= (select max(order_date) - 90 from order_detail)
group by user_id,product_id
)t1
group by t1.product_id
order by cpr desc,product_id asc;
SQL 四十六：出勤率问题
现有用户出勤表（user_login）如下。
| user_id (用户id) | course_id (课程id) | login_in （登录时间） | login_out （登出时间） |
|---|---|---|---|
| 1 | 1 | 2022-06-02 09:08:24 | 2022-06-02 10:09:36 |
| 1 | 1 | 2022-06-02 11:07:24 | 2022-06-02 11:44:21 |
| 1 | 2 | 2022-06-02 13:50:24 | 2022-06-02 14:21:50 |
| 2 | 2 | 2022-06-02 13:50:10 | 2022-06-02 15:30:20 |
课程报名表（course_apply）如下。
| course_id (课程id) | course_name (课程名称) | user_id (用户id) |
|---|---|---|
| 1 | java | [1,2,3,4,5,6] |
| 2 | 大数据 | [1,2,3,6] |
| 3 | 前端 | [2,3,4,5] |
注：出勤率指用户看直播时间超过40分钟，求出每个课程的出勤率（结果保留两位小数）。
期望结果如下：
| course_id |
adr <decimal(16,2)> (出勤率) |
|---|---|
| 1 | 0.33 |
| 2 | 0.50 |
| 3 | 0.25 |
题解：
with t1 as (
select
*,
unix_timestamp(login_out) - unix_timestamp(login_in) as online_time
from user_login
),t2 as (
select
user_id,course_id,sum(online_time) as online_time
from t1
group by user_id,course_id
having sum(online_time)>2400 -- 40分钟的时间戳差值为2400
), t3 as (
select
course_id,
course_name,
my_id
from course_apply as t3 lateral view explode(user_id) my_id as my_id -- 炸开列表，便于识别没有观看直播的同学
)
select
t3.course_id,
cast(count(user_id)/count(1) as decimal(16,2)) as adr
from t3 left join t2 on t3.course_id = t2.course_id and t3.my_id = t2.user_id
group by t3.course_id;
SQL 四十七：打车问题
现有用户下单表（get_car_record）如下。
| uid (用户id) | city (城市) | event_time （下单时间） | end_time (结束时间:取消或者接单) | order_id (订单id) |
|---|---|---|---|---|
| 107 | 北京 | 2021-09-20 11:00:00 | 2021-09-20 11:00:30 | 9017 |
| 108 | 北京 | 2021-09-20 21:00:00 | 2021-09-20 21:00:40 | 9008 |
| 108 | 北京 | 2021-09-20 18:59:30 | 2021-09-20 19:01:00 | 9018 |
| 102 | 北京 | 2021-09-21 08:59:00 | 2021-09-21 09:01:00 | 9002 |
司机订单信息表（get_car_order）如下。
| order_id (课程id) | uid (用户id) | driver_id (用户id) | order_time (接单时间) | start_time (开始时间) | finish_time (结束时间) | fare (费用) | grade (评分) |
|---|---|---|---|---|---|---|---|
| 9017 | 107 | 213 | 2021-09-20 11:00:30 | 2021-09-20 11:02:10 | 2021-09-20 11:31:00 | 38 | 5 |
| 9008 | 108 | 204 | 2021-09-20 21:00:40 | 2021-09-20 21:03:00 | 2021-09-20 21:31:00 | 38 | 4 |
| 9018 | 108 | 214 | 2021-09-20 19:01:00 | 2021-09-20 19:04:50 | 2021-09-20 19:21:00 | 38 | 5 |
统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留2位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。
注：不同时段定义：早高峰 [07:00:00 , 09:00:00)、工作时间 [09:00:00 , 17:00:00）、晚高峰 [17:00:00 ,20:00:00）、休息时间 [20:00:00 , 07:00:00） 时间区间左闭右开（即7:00:00算作早高峰，而9:00:00不算做早高峰）
从开始打车到司机接单为等待接单时间，从司机接单到上车为调度时间
期望结果如下：
| period |
get_car_num |
wait_time <decimal(16,2)> (等待时长) | dispatch_time <decimal(16,2)> (调度时长) |
|---|---|---|---|
| 工作时间 | 1 | 0.50 | 1.67 |
| 休息时间 | 1 | 0.67 | 2.33 |
| 晚高峰 | 3 | 2.06 | 7.28 |
| 早高峰 | 4 | 2.21 | 8.00 |
题解：
with t1 as ( -- 过滤周末数据
select
*
from get_car_record
where dayofweek(end_time) not in (1,7)
),t3 as ( -- 计算每名用户的等待、调度时间
select
t1.uid,
t2.order_id,
t1.event_time,
t2.order_time,
t2.start_time,
unix_timestamp(order_time)-unix_timestamp(event_time) as wait_time,
unix_timestamp(start_time)-unix_timestamp(order_time) as dispatch_time,
case
when hour(event_time)>=7 and hour(event_time)<9 then '早高峰'
when hour(event_time)>=9 and hour(event_time)<17 then '工作时间'
when hour(event_time)>=17 and hour(event_time)<20 then '晚高峰'
else '休息时间'
end as period
from t1 left join get_car_order t2
on t1.uid = t2.uid and t1.order_id = t2.order_id -- 关联订单id可以过滤取消订单的数据
)select -- 正常统计
period,
count(order_id) as get_car_num,
cast(avg(wait_time)/60 as decimal(16,2)) as wait_time,
cast(avg(dispatch_time)/60 as decimal(16,2)) as dispatch_time
from t3
group by period
order by get_car_num;
SQL 四十八：排列问题
现有球队表（team）如下。
| team_name (球队名称) |
|---|
| 湖人 |
| 骑士 |
| 灰熊 |
| 勇士 |
拿到所有球队比赛的组合 每个队只比一次
期望结果如下：
| team_name_1 |
team_name_2 |
|---|---|
| 勇士 | 湖人 |
| 湖人 | 骑士 |
| 灰熊 | 骑士 |
| 勇士 | 骑士 |
| 湖人 | 灰熊 |
| 勇士 | 灰熊 |
题解：
with t1 as (
select
team_name,
collect_set(team_name) over(rows between unbounded preceding and 1 preceding) as team_name_set
from team
)select
team_name as team_name_1,
team_name_2
from t1 lateral view explode(team_name_set) team_name_info as team_name_2
where team_name_2 is not null;
网友新思路：
select
a.team_name as team_name_2,
b.team_name as team_name_1
from team a cross join team b where a.team_name > b.team_name;
SQL 四十九：视频热度问题
现有用户视频表（user_video_log）如下。
| uid (球队名称) | video_id (视频id) | start_time (开始时间) | end_time (结束时间) | if_like (是否点赞) | if_retweet (是否喜欢) | comment_id (评论id) |
|---|---|---|---|---|---|---|
| 101 | 2001 | 2021-09-24 10:00:00 | 2021-09-24 10:00:20 | 1 | 0 | null |
| 105 | 2002 | 2021-09-25 11:00:00 | 2021-09-25 11:00:30 | 0 | 1 | null |
| 102 | 2002 | 2021-09-25 11:00:00 | 2021-09-25 11:00:30 | 1 | 1 | null |
| 101 | 2002 | 2021-09-26 11:00:00 | 2021-09-26 11:00:30 | 0 | 1 | null |
视频信息表(video_info) 如下：
| video_id (视频id) | author (作者id) | tag (标签) | duration (视频时长) |
|---|---|---|---|
| 2001 | 901 | 旅游 | 30 |
| 2002 | 901 | 旅游 | 60 |
| 2003 | 902 | 影视 | 90 |
| 2004 | 902 | 美女 | 90 |
找出近一个月发布的视频中热度最高的top3视频。
注：热度=(a视频完播率+b点赞数+c评论数+d转发数)*新鲜度；
新鲜度=1/(最近无播放天数+1)；
当前配置的参数a,b,c,d分别为100、5、3、2。
最近播放日期以 end_time-结束观看时间 为准，假设为T，则最近一个月按 [T-29, T] 闭区间统计。
当天日期使用视频中最大的end_time
结果中热度保留为整数，并按热度降序排序。
期望结果如下：
| video_id |
heat <decimal(16,2)> (热度) |
|---|---|
| 2002 | 80.36 |
| 2001 | 20.33 |
网友题解：
-- 1、这就是语言的力量：
-- 当天日期使用视频中最大的end_time
-- 最近播放的一天也是使用视频中最大的end_time
-- 因此在本题中最近无播放天数 = 0
-- 新鲜度 = 1/(最近无播放天数+1) = 1
-- 2、视频完播 = 观看时长 > 视频时长
-- 视频完播率 = 观看时长 > 视频时长 的观看次数 / 总观看次数
-- 3、retweet就是转发的意思，并非"是否喜欢"
-- 4、热度最高的top3
with t1 as (
select uid, video_id, start_time, end_time,if_like, if_retweet, case when comment_id is null then 0 else 1 end comments,
max(date_format(end_time,'yyyy-MM-dd')) over(partition by video_id) today, date_add( max(date_format(end_time,'yyyy-MM-dd')) over(partition by video_id),-29) begin
from user_video_log
), t2 as (
select t1.uid, t1.video_id, t1.if_like, t1.if_retweet, t1.comments, vi.duration,
unix_timestamp(t1.end_time) - unix_timestamp(t1.start_time) watch_duration,
datediff(today, today) no_play_days
from t1 join video_info vi on t1.video_id = vi.video_id
where date_format(t1.start_time,'yyyy-MM-dd') > t1.begin
), t3 as (
select distinct video_id, no_play_days,
sum(case when watch_duration >= duration then 1 else 0 end) over(partition by video_id) / count(1) over(partition by video_id) complete_pay_rate,
sum(if_like) over(partition by video_id) like_num,
sum(comments) over(partition by video_id) comments,
sum(if_retweet) over(partition by video_id) retweet_num
from t2
), t4 as (
select video_id, ceil( (100*complete_pay_rate + 5*like_num + 3*comments + 2*retweet_num)/(1/(no_play_days + 1)) ) heat
from t3
), t5 as (
select video_id, heat, row_number() over(order by heat desc) rn
from t4
), t6 as (
select video_id, cast(heat as decimal(16,1)) heat
from t5
where rn <= 3
order by rn
)
select * from t6
SQL 五十：员工在职人数问题
现有用户表（emp）如下。
| id (员工id) | en_dt (入职日期) | le_dt (离职日期) |
|---|---|---|
| 1001 | 2020-01-02 | null |
| 1002 | 2020-01-02 | 2020-03-05 |
| 1003 | 2020-02-02 | 2020-02-15 |
| 1004 | 2020-02-12 | 2020-03-08 |
日历表(cal) 如下：
| dt (日期) |
|---|
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
| 2020-01-04 |
统计2020年每个月实际在职员工数量(只统计2020-03-31之前)，如果1个月在职天数只有1天，数量计算方式：1/当月天数。
如果一个月只有一天的话，只算30分之1个人
期望结果如下：
| mnt |
ps <decimal(16,2)> (在职人数) |
|---|---|
| 1 | 1.94 |
| 2 | 3.62 |
| 3 | 2.23 |
题解：
with t1 as (
select
*,
date_add(en_dt, pos) as mid_date
from emp
lateral view posexplode(
split(space(datediff(le_dt, nvl(en_dt,'2020-03-31'))), '') -- 获取两个日期间的所有日期
) t as pos,val
)select
month(t2.dt) as mnt,
count(t1.mid_date) / count(distinct t2.dt) as ps
from t1 rigth join cal t2 on t1.mid_date = t2.dt
group by month(t2.dt)
