题目取自戴师兄的视频：https://www.bilibili.com/video/BV1zX4y1u7ZH
数据：
shop.csv
cpc.csv
orders.csv
#第一关
2020年第一季度下单人数前3名的门店，业绩表现如何（GMV、下单人数、曝光人数、进店人数）
解答：
先按字面意思进行筛选。
1
2
3
4
5
6
7
8
9
10
11
|
select 门店id
,门店名称
,sum(GMV) 累计GMV
,sum(下单人数) 累计下单人数
,sum(曝光人数) 累计曝光人数
,sum(进店人数) 累计进店人数
from shop
where 日期 between '2020-01-01' and '2020-03-31'
group by 门店id,门店名称
order by 累计下单人数 desc
limit 3;
|
输出：
1
2
3
4
5
6
7
|
+------------+--------------+-----------+--------------------+--------------------+--------------------+
| 门店id | 门店名称 | 累计GMV | 累计下单人数 | 累计曝光人数 | 累计进店人数 |
+------------+--------------+-----------+--------------------+--------------------+--------------------+
| 2001104355 | 宝山店 | 116071 | 1639 | 143877 | 11659 |
| 2000507076 | 五角场店 | 80408 | 1282 | 87435 | 5935 |
| 8184590 | 五角场店 | 54560 | 875 | 58639 | 4173 |
+------------+--------------+-----------+--------------------+--------------------+--------------------+
|
这里有个很有意思的问题，就是门店名称虽然基本是唯一的，但是它们的id却会有多个，原因是因为门店会依据平台补贴政策灵活地进行业店重开，所以，最终答案是要按门店名称进行分组，否则得到的不是真实总业绩。后面的同理。
1
2
3
4
5
6
7
8
9
10
11
|
select 品牌名称
,门店名称
,sum(GMV) 累计GMV
,sum(下单人数) 累计下单人数
,sum(曝光人数) 累计曝光人数
,sum(进店人数) 累计进店人数
from shop
where 日期 between '2020-01-01' and '2020-03-31'
group by 品牌名称,门店名称
order by 累计下单人数 desc
limit 3;
|
输出：
1
2
3
4
5
6
7
8
|
+-----------------------------------+--------------+-----------+--------------------+--------------------+--------------------+
| 品牌名称 | 门店名称 | 累计GMV | 累计下单人数 | 累计曝光人数 | 累计进店人数 |
+-----------------------------------+--------------+-----------+--------------------+--------------------+--------------------+
| 蛙小辣火锅杯（总账号） | 五角场店 | 134968 | 2157 | 146074 | 10108 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 137384 | 1979 | 180783 | 14535 |
| 蛙小辣火锅杯（总账号） | 怒江路店 | 40900 | 689 | 46964 | 3446 |
+-----------------------------------+--------------+-----------+--------------------+--------------------+--------------------+
|
#第二关
2020年第一季度每个月，每个门店的累计GMV
解答：
1
2
3
4
5
6
7
8
|
select 品牌名称
,门店名称
,substr(日期,1,7) 月份
,sum(GMV) 累计GMV
from shop
where 日期 between '2020-01-01' and '2020-03-31'
group by 1,2,3
order by 1,2,3;
|
输出：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
+-----------------------------------+-------------------------------------+---------+-----------+
| 品牌名称 | 门店名称 | 月份 | 累计GMV |
+-----------------------------------+-------------------------------------+---------+-----------+
| 拌客（武宁路店） | 拌客干拌麻辣烫(武宁路店) | 2020-03 | 0 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-01 | 92062 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-02 | 0 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-03 | 42906 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01 | 37157 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-02 | 39550 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-03 | 60677 |
| 蛙小辣火锅杯（总账号） | 怒江路店 | 2020-01 | 12185 |
| 蛙小辣火锅杯（总账号） | 怒江路店 | 2020-02 | 6319 |
| 蛙小辣火锅杯（总账号） | 怒江路店 | 2020-03 | 22396 |
| 蛙小辣火锅杯（总账号） | 龙阳广场店 | 2020-01 | 36558 |
+-----------------------------------+-------------------------------------+---------+-----------+
|
#第三关
2020年第一季度曝光人数大于10w的门店，每个月的GMV
这道题只需在前一题的基础上，筛选出满足“2020年第一季度曝光人数大于10w”就行了（由于外层是按照月份累计的，所以才需要用两层select）
解答：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
select 品牌名称
,门店名称
,substr(日期,1,7) 月份
,sum(GMV) 累计GMV
from shop
where 日期 between '2020-01-01' and '2020-03-31'
and 门店名称 in (
select 门店名称
from shop where 日期 between '2020-01-01' and '2020-03-31'
group by 门店名称
having sum(曝光人数)>10000
)
group by 1,2,3
order by 1,2,3;
|
输出：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
+-----------------------------------+-----------------+---------+-----------+
| 品牌名称 | 门店名称 | 月份 | 累计GMV |
+-----------------------------------+-----------------+---------+-----------+
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-01 | 92062 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-02 | 0 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-03 | 42906 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01 | 37157 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-02 | 39550 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-03 | 60677 |
| 蛙小辣火锅杯（总账号） | 怒江路店 | 2020-01 | 12185 |
| 蛙小辣火锅杯（总账号） | 怒江路店 | 2020-02 | 6319 |
| 蛙小辣火锅杯（总账号） | 怒江路店 | 2020-03 | 22396 |
| 蛙小辣火锅杯（总账号） | 龙阳广场店 | 2020-01 | 36558 |
+-----------------------------------+-----------------+---------+-----------+
|
#第四关
2020年第一季度cpc总费用小于1000的门店（门店ID不同视为不同门店），每个月的GMV
解答：
和第三题几乎一样
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
select 品牌名称
,门店名称
,substr(日期,1,7) 月份
,sum(GMV) 累计GMV
from shop
where 日期 between '2020-01-01' and '2020-03-31'
and 门店id in (
select 门店id
from cpc c
where 日期 between '2020-01-01' and '2020-03-31'
group by 门店id
having sum(cpc总费用)<1000
)
group by 1,2,3
order by 1,2,3;
|
输出：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
+-----------------------------------+-----------------+---------+-----------+
| 品牌名称 | 门店名称 | 月份 | 累计GMV |
+-----------------------------------+-----------------+---------+-----------+
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-01 | 92062 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-02 | 0 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-03 | 42906 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01 | 0 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-02 | 1152 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-03 | 20161 |
| 蛙小辣火锅杯（总账号） | 怒江路店 | 2020-01 | 12185 |
| 蛙小辣火锅杯（总账号） | 怒江路店 | 2020-02 | 6319 |
| 蛙小辣火锅杯（总账号） | 怒江路店 | 2020-03 | 22396 |
| 蛙小辣火锅杯（总账号） | 龙阳广场店 | 2020-01 | 36558 |
+-----------------------------------+-----------------+---------+-----------+
|
#第五关
查询2020年饿了么平台上每个门店GMV最高那天的日期和GMV
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
select 平台
,门店名称
,日期
,GMV
from
(
select 平台
,门店名称
,日期
,GMV
,dense_rank() over(partition by 平台,门店名称 order by GMV desc) r
from shop
where 日期 between '2020-01-01' and '2020-12-31'
and 平台='eleme'
) a
where a.r=1
order by 日期;
|
输出：
1
2
3
4
5
6
7
8
|
+--------+-------------------------------------+------------+-------+
| 平台 | 门店名称 | 日期 | GMV |
+--------+-------------------------------------+------------+-------+
| eleme | 宝山店 | 2020-01-03 | 4093 |
| eleme | 龙阳广场店 | 2020-01-05 | 3451 |
| eleme | 五角场店 | 2020-01-22 | 3778 |
| eleme | 拌客干拌麻辣烫(武宁路店) | 2020-06-04 | 11013 |
+--------+-------------------------------------+------------+-------+
|
#第六关
所有门店20年7月的累计GMV、累计CPC、累计商家实收、累计用户实付
如果细心观察会发现，这题不能直接将这三张表连起来，因为orders表与另外两张表的层级不一样，orders更细致，同一天有多条记录，所以需要先聚合一下，再join。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
select 品牌名称
,门店名称
,sum(GMV) 累计GMV
,sum(cpc总费用) 累计cpc总费用
,sum(商家实收) 累计商家实收
,sum(用户实付) 累计用户实付
from shop s left join cpc c
on s.日期=c.日期
and s.门店ID=c.门店iD
left join (
select 门店ID,下单日期, sum(用户实付) 用户实付
from orders group by 1,2
) o
on s.日期=o.下单日期
and s.门店ID=o.门店ID
where s.日期 between '2020-07-01' and '2020-07-31'
group by 1,2;
|
输出：
1
2
3
4
5
6
|
+-----------------------------------+-------------------------------------+-----------+--------------------+--------------------+--------------------+
| 品牌名称 | 门店名称 | 累计GMV | 累计cpc总费用 | 累计商家实收 | 累计用户实付 |
+-----------------------------------+-------------------------------------+-----------+--------------------+--------------------+--------------------+
| 蛙小辣火锅杯（总账号） | 宝山店 | 60875 | 2190 | 22037 | NULL |
| 拌客（武宁路店） | 拌客干拌麻辣烫(武宁路店) | 215690 | 11424 | 70115 | 13685.13 |
+-----------------------------------+-------------------------------------+-----------+--------------------+--------------------+--------------------+
|
#第七关
拌客在饿了么上20年第二季度每个月的GMV，以及每个月GMV在20年第二季度累计GMV的占比
解答：
这个很好做，不过如果用窗口函数更简洁
1
2
3
4
5
6
7
8
9
|
select 品牌名称
,substring(日期,1,7) 月份
,sum(GMV) 当月GMV
,concat(round((sum(GMV)/sum(sum(GMV))over())*100,0),'%') 占比
from shop s
where s.日期 between '2020-04-01' and '2020-06-30'
and 品牌名称 like '%拌客%'
and 平台='eleme'
group by 1,2
|
注意sum(GMV)即group by的结果，然后给窗口函数接着处理。这里sum()over()如果没有partition by和order by，默认为全区的固定窗口。
输出：
1
2
3
4
5
6
|
+--------------------------+---------+-----------+--------+
| 品牌名称 | 月份 | 当月GMV | 占比 |
+--------------------------+---------+-----------+--------+
| 拌客（武宁路店） | 2020-05 | 126967 | 39% |
| 拌客（武宁路店） | 2020-06 | 196896 | 61% |
+--------------------------+---------+-----------+--------+
|
#第八关
2020年拌客和蛙小辣两个品牌在饿了么和美团两个平台上各自GMV最高的三天以及这三天的GMV
解答：
这题得用窗口函数。
1
2
3
4
5
6
7
8
9
10
11
12
13
|
select 品牌名称
,平台
,日期
from (
select *
,row_number() over(partition by 品牌名称,平台 order by GMV desc) r
from shop
where (品牌名称 like '%拌客%' or 品牌名称 like '%蛙小辣%')
and 平台 in ('eleme','meituan')
and substring(日期,1,4)='2020'
) a
where a.r<=3
order by 1,2,3;
|
输出：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
+-----------------------------------+---------+------------+
| 品牌名称 | 平台 | 日期 |
+-----------------------------------+---------+------------+
| 拌客（武宁路店） | eleme | 2020-06-02 |
| 拌客（武宁路店） | eleme | 2020-06-03 |
| 拌客（武宁路店） | eleme | 2020-06-04 |
| 拌客（武宁路店） | meituan | 2020-07-15 |
| 拌客（武宁路店） | meituan | 2020-07-16 |
| 拌客（武宁路店） | meituan | 2020-07-17 |
| 蛙小辣火锅杯（总账号） | eleme | 2020-01-03 |
| 蛙小辣火锅杯（总账号） | eleme | 2020-01-04 |
| 蛙小辣火锅杯（总账号） | eleme | 2020-01-22 |
| 蛙小辣火锅杯（总账号） | meituan | 2020-01-02 |
| 蛙小辣火锅杯（总账号） | meituan | 2020-01-06 |
| 蛙小辣火锅杯（总账号） | meituan | 2020-05-03 |
+-----------------------------------+---------+------------+
|
#第九关
2020年拌客和蛙小辣两个品牌各自门店在饿了么GMV排名前5%（GMV为0不计入排名）的日期、GMV、以及具体排名和排名百分比（取一位小数）
解答：
两个窗口函数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
select 品牌名称
,门店名称
,日期
,GMV
,concat(round(p*100,1),'%') 排名百分比
,r 排名
from (
select *
,cume_dist() over(partition by 品牌名称,门店名称 order by GMV desc) p
,row_number() over(partition by 品牌名称,门店名称 order by GMV desc) r
from shop
where (品牌名称 like '%拌客%' or 品牌名称 like '%蛙小辣%')
and 平台 = 'eleme'
and substring(日期,1,4)='2020'
and GMV != 0
) a
where a.p<=0.05;
|
输出：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
+-----------------------------------+-------------------------------------+------------+-------+-----------------+--------+
| 品牌名称 | 门店名称 | 日期 | GMV | 排名百分比 | 排名 |
+-----------------------------------+-------------------------------------+------------+-------+-----------------+--------+
| 拌客（武宁路店） | 拌客干拌麻辣烫(武宁路店) | 2020-06-04 | 11013 | 0.9% | 1 |
| 拌客（武宁路店） | 拌客干拌麻辣烫(武宁路店) | 2020-06-02 | 9615 | 1.7% | 2 |
| 拌客（武宁路店） | 拌客干拌麻辣烫(武宁路店) | 2020-06-03 | 9424 | 2.6% | 3 |
| 拌客（武宁路店） | 拌客干拌麻辣烫(武宁路店) | 2020-05-26 | 9231 | 3.4% | 4 |
| 拌客（武宁路店） | 拌客干拌麻辣烫(武宁路店) | 2020-05-30 | 8992 | 4.3% | 5 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-01-22 | 3778 | 1.1% | 1 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-01-10 | 3609 | 2.1% | 2 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-01-03 | 3455 | 3.2% | 3 |
| 蛙小辣火锅杯（总账号） | 五角场店 | 2020-01-21 | 3402 | 4.3% | 4 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01-03 | 4093 | 0.4% | 1 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01-04 | 4058 | 0.8% | 2 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01-07 | 3735 | 1.2% | 3 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01-05 | 3577 | 1.6% | 4 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01-01 | 3546 | 2% | 5 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01-10 | 3333 | 2.4% | 6 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01-08 | 3300 | 2.9% | 7 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01-02 | 2878 | 3.3% | 8 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-01-09 | 2757 | 3.7% | 9 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-03-12 | 2594 | 4.1% | 10 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-04-26 | 2434 | 4.5% | 11 |
| 蛙小辣火锅杯（总账号） | 宝山店 | 2020-02-26 | 2324 | 4.9% | 12 |
| 蛙小辣火锅杯（总账号） | 龙阳广场店 | 2020-01-05 | 3451 | 4.5% | 1 |
+-----------------------------------+-------------------------------------+------------+-------+-----------------+--------+
|
#第十关
2020年拌客在双平台每日GMV及前一日GMV，及GMV周同比增长量与百分比
这题显然要用窗口函数的lag函数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
select *
,当日GMV - 上周GMV GMV周同比增长量
,concat(round(((当日GMV - 上周GMV)/上周GMV)*100,0),'%') GMV周同比
from
(
select 品牌名称
,平台
,日期
,GMV 当日GMV
,lag(GMV,1,0) over(partition by 品牌名称,平台 order by 日期) 前日GMV
,lag(GMV,7,0) over(partition by 品牌名称,平台 order by 日期) 上周GMV
from shop
where 品牌名称 like '%拌客%'
and 平台 in ('eleme','meituan')
and substring(日期,1,4)='2020'
and GMV != 0
) a
|
输出：
戴师兄总结：
- 1、尽可能使用分步解题法，顺着题目一步步写
- 2、写SQL代码时应该尽量脱离数据库，在脑海里完成代码的组合和调试
- 3、遇到特别复杂的逻辑，一次查询无法解决，可以直接上子查询，然后再慢慢优化
- 4、遇到暂时想不清楚的地方，可以先搁置，最后基于其他步骤已成型的代码进行推导
- 5、实在不行就画图，或者用excel表格先处理出来，再一步步用SQL实现
- 6、完成代码后一定要尽量优化自己的代码，这样逻辑才能越来越好
- 7、不断反复练习，最终能在读题时就在脑海里理清代码逻辑