【MySql】基础教学训练

解题步骤

分布实现法

1. 翻译成计算机运行语言

2. 找对应关系

3. 将代码排列组合

4. 进行合理优化

   用if函数省略子查询 
   直接用having筛选 
   表连接进行数据筛选
   

基础语法

补充函数

round：

小数点保留几位小数
用法round（计算式，位数）

concat：

函数用于将两个字符串连接起来，形成一个单一的字符串。
用法：concat(文本1，文本2)
二者结合通常是百分比连用

concat(round((a.当月gmv/b.季度gmv)*100,0),'%') 占比

percent_rank()

percent_rank()从0开始，排名只有第一名没有第0名，并且没有并列

cume_dist()

cume_dist()从0之后开始，并且可以并列

lag()、lead() 偏移

lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值。举例如下：

原始数据表：items
lag ：向前，形象的理解就是把数据从上向下推，上端出现空格
lead ：向后，形象的理解就是把数据从下向上推，下端出现空格
运用lag函数：

select *,lag(item_brand,1,0) over (order by item_id) from items
#lag(选择列，向下偏移一个位置)

运用lead函数：

---

业务例题

去年第一季度下单人数前三名的门店业绩表现

思路：

• 去年第一季度：2020.1/2/3
  ----where 日期 between ‘2020-01-01’ and ‘2020-03-31’
• 下单人数：累计下单人数
  ----sum(下单人数) 累计下单人数
• 前三
  ----order by 累计下单人数 limit 3
• 门店名称
• 业绩表现：累计GMV、累计曝光人数、累计进店人数
  -----sum(GMV)累计GMV、sum(曝光人数) 累计曝光人数、sum(进店人数)累计进店人数

select
品牌名称
,门店名称
,sum(GMV) 累计GMV
,sum(下单人数) 累计下单人数
,sum(曝光人数) 累计曝光人数
,sum(进店人数) 累计进店人数
from ddm.shop
where 日期 between '2020-01-01' and '2020-03-31'
group by 1,2 //品牌门店名称缩写，一般企业有固定的缩写模式
order by 累计下单人数 desc //排序
limit 3

去年第一季度每个月每个门店的累计GMV

思路

• 去年第一个季度：
  -----where 日期 between 2020-01-01 and 2020-03-31
• 每个月： substring(日期1,7) 月份 group by 月份
• 品牌名称、门店名称
• 累计GMV:SUM(GMV) 累计GMV

select
门店名称
,品牌名称
,substring(日期,1,7) 月份
,sum(GMV) 累计GMV
,sum(下单人数) 累计下单人数
from ddm.shop
where 日期 between '2020-01-01' and '2020-03-31'
group by 1,2,3
order by 门店名称,品牌名称,月份

去年第一季度曝光人数大于10w的门店，每个月的GMV

思路：

• 去年第一季度
  ----where 日期 between ‘2020-01-01’and’2020-03-31’
• 曝光人数
  ----sum(曝光人数) 累计曝光人数
  大于10w
• 的门店
  ----select 门店 from 表
• having 累计曝光人数 > 100000
• 每个月
  ----- substring(日期,1,7) group by 月份
• 的gmv
  ----sum(gmv) 累计gmv

select
品牌名称
,门店名称
,substring(日期,1,7) 月份
,sum(GMV)        累计GMV
,sum(曝光人数)  累计曝光人数
from ddm.shop
where 日期 between '2020-01-01' and '2020-03-31'
group by 品牌名称, 门店名称, 月份
having 累计曝光人数 > 100000
order by 品牌名称,门店名称,月份

显示：只有去年第一季度曝光人数大于10w的门店，每个月的GMV（不是每个门店）
曝光人数要依据季度算，GMV要依据月份算
但是一次查询只能有一个聚合运算的依据，怎么办？
把这三个门店查出来，再计算这三家门店不就行了？
where子查询

	 筛选条件的聚合计算依据与需要聚合运算的字段的聚合计算依据不同时使用        

1. 同一个表格依据不同
2. 根本不在同一个表格
   先把三个门店查出来
   select
   门店名称
   from ddm.shop
   where 日期 between ‘2020-01-01’ and ‘2020-03-31’
   group by 1
   having sum(曝光人数) > 100000

select
门店名称
,substring(日期,1,7) 月份
,sum(GMV)        累计GMV
,sum(曝光人数)  累计曝光人数
from ddm.shop
where 日期 between '2020-01-01' and '2020-03-31'
and 门店名称 in 
    (
        select 门店名称
        from ddm.shop
        where 日期 between '2020-01-01' and '2020-03-31'
        group by 门店名称
        having sum(曝光人数)  > 100000
    )
group by 门店名称,月份
order by 门店名称,月份

去年第一季度cpc总费用小于1000的门店，每个月的GMV

思路：

• 去年第一个季度
  ----where 日期 between ‘2020-01-01’ and ‘2020-03-31’
• cpc
  ----sum(cpc.cpc总费用) 累计cpc费用
• 每个门店每个月
  ----子查询
  select 门店名称 from ddm.cpc
  where 日期 between ‘2020-01-01’ and ‘2020-03-31’
  group by 门店名称
  having sum(cpc总费用) < 1000
• 每个月的GMV
  ----sum(GMV) 累计GMV，substring(日期,1,7) 月份

select
门店名称
,substring(日期,1,7) 月份

,sum(GMV) 累计GMV
from ddm.shop
where 日期 between '2020-01-01' and '2020-03-31'
and shop.门店ID in
    (
        select 门店ID from ddm.cpc
        where 日期 between '2020-01-01' and '2020-03-31'
        group by 门店ID
        having sum(cpc.cpc总费用) < 1000
    )
group by 门店名称,月份
order by 门店名称,月份

去年一月GMV小于去年平均GMV的门店的业绩表现

• 去年一月门店gmv
  ----select
  门店名称
  ,substring(日期,1,7) 月份
  ,sum(GMV) 累计GMV
  from ddm.shop
  where substring(日期,1,7) = ‘2020-01’
  group by 门店名称,substring(日期,1,7) 月份
• 小于
  having sum(GMV) < (去年平均GMV)
• 去年平均门店
  ----每个门店每个月gmv日平均月平均还是年平均，其实默认应该是月平均
  select
  ,~~avg(GMV)~~聚合维度内部即日均 avg(a.GMV)
  from shop
  优化去年平均如下

select
		avg(a.*GMV*)
		from
		(
		    select
		    门店名称
		    ,substring(日期, 1, 7)
		    ,sum(GMV) *GMV*
		    from ddm.shop
		    where substring(日期, 1, 4) = '2020'
		    group by 1,2
		    having sum(GMV) > 0
		
		)a//相当于对整个表求平均值
		

• 业绩表现
  曝光人数，进店人数，gmv

select
substring(日期,1,7) 月份
,门店名称
,sum(GMV)
,sum(下单人数) 累计下单人数
,sum(曝光人数) 累计曝光人数
,sum(进店人数) 累计进店人数
from ddm.shop
where substring(日期,1,7) = '2020-01'
group by 1,2
having sum(GMV) <
                (
                    select
                    avg(a.GMV)
                    from
                    (
                        select
                        substring(日期, 1, 7)
                        ,门店名称
                        ,sum(GMV) GMV
                        from ddm.shop
                        where substring(日期, 1, 4) = '2020'
                        group by 1,2
                        having sum(GMV) > 0
                    ) a
                )
order by 1,2

所有门店2020年七月的累计GMV、累计商家实收、累计用户实付

思路：
店铺表是对应日期每天一条
cpc表对应日期每天一条
订单表是每个订单一条不是每天一条

订单表如果对应其他两表会产生笛卡尔积
所以可以先聚合后链接
则订单表 需要先聚合运算再连接了

select
s.门店名称
,substring(s.日期,1,7) 月份
,sum(s.GMV) 累计GMV
,sum(c.cpc总费用) 累计cpc总费用
,sum(s.商家实收)  累计商家实收
,sum(o.啊用户实付)  累计用户实付
from ddm.shop s
left join ddm.cpc c on s.门店ID = c.门店ID and s.日期=c.日期
left join
    (
        select
        门店ID
        ,下单日期
        ,sum(用户实付) 啊用户实付
        from ods.orders
        group by 门店ID,下单日期
    )o on s.门店ID = o.门店ID and s.日期=o.下单日期 # 对应关系不一致，则订单表 需要先聚合运算再连接了
where substring(s.日期,1,7) = '2020-07'
group by s.门店名称,月份

补充group by别名报错问题
SQL 标准中不允许 SELECT 列表，HAVING 条件语句，或 ORDER BY 语句中出现 GROUP BY 中未列表的可聚合列。而 MySQL 中有一个状态 ONLY_FULL_GROUP_BY 来标识是否遵从这一标准，默认为开启状态

优化以上代码的运行效率
先锁定月份

X店铺在饿了么 2020年第二季度每个月的GMV，以及每个月GMV在20年第二季度累计GMV的占比

思路

• 月度GMV和季度GMV不是统一梯度
  ----先聚合后链接sum出一个季度表
• where需要的店铺和平台

select
a.平台
,a.品牌名称
,a.月份
,a.当月gmv
,b.季度gmv
,concat(round((a.当月gmv/b.季度gmv)*100,0),'%') 占比
from
     (
         select
         平台
         ,品牌名称
         ,substring(日期,1,7) 月份
         ,sum(GMV) 当月gmv
         from ddm.shop
         where substring(日期,1,7) between '2020-04-01' and '2020-06-30'
         and 平台 = 'eleme'
         and 品牌名称 like '%拌客%'
         group by 品牌名称,月份
     ) a
left join
    (
          select
          平台
         ,品牌名称
         ,sum(GMV) 季度gmv
         from ddm.shop
         where 日期 between '2020-04-01' and '2020-06-30'
         and 平台 = 'eleme'
         and 品牌名称 like '%拌客%'
         group by 品牌名称

    )b on a.品牌名称 = b.品牌名称

窗口函数

2020年拌客和蛙小辣两个品牌在饿了么和美团两个平台上

select *
from
     (
         select

         品牌名称
         ,平台
         ,日期
         ,GMV
         ,row_number() over (partition by 品牌名称,平台 order by GMV desc ) 排名#用于分组排名
         from ddm.shop
         where 平台 in('eleme','meituan')
         and substring(日期,1,4) ='2020'
         and (品牌名称 like'%拌客%' or 品牌名称 like '%蛙小辣%')
    ) a
where 排名 in (1,2,3)

2020年拌客和蛙小辣两个品牌在饿了么和美团两个平台上，GMV不为零的日子里，GMV的极差

select
品牌名称
,平台
,avg(极大值-极小值) 极差
from (
         select
         品牌名称
         ,平台
         ,日期
         ,GMV
         ,first_value(GMV) over (partition by 品牌名称,平台 order by GMV desc) 极大值
         ,first_value(GMV) over (partition by 品牌名称,平台 order by GMV) 极小值
         from ddm.shop
         where 平台 in ('eleme','meituan')
         and substring(日期, 1, 4) = '2020'
         and (品牌名称 like '%拌客%' or 品牌名称 like '%蛙小辣%')
         and GMV > 0
     ) a #每天都要最大最小
group by 品牌名称,平台

2020年拌客和蛙小辣两个品牌各自门店在饿了么GMV排名前5%（GMV为0不计入排名）的日期、GMV、以及具体排名及排名百分比（取一位小数）

思路：

• 先筛选品牌、平台、日期
• GMV排名前5%
  -----row_number排名再cume_dist算排名占比

select
品牌名称
,日期
,门店名称
,平台
,排名
,GMV
,concat(round(排名占比 * 100,1),'%') 排名的百分占比
from (
        select
        品牌名称
        ,门店名称
        ,日期
        ,平台
        ,GMV
        ,row_number() over(partition by 品牌名称,门店名称 order by GMV desc) 排名
        ,cume_dist() over(partition by 品牌名称,门店名称 order by GMV desc) 排名占比 #排名占比可以并列
        from ddm.shop
        where 平台 = 'eleme'
        and substring(日期,1,4) = '2020'
        and (品牌名称 like '%拌客%' or 品牌名称 like '%蛙小辣%')
        and GMV > 0
    ) a
where 排名占比 <= 0.05

2020年拌客 在双平台每日GMV 及前一日GMV，及GMV 周同比增长差异值 与百分比

• 前一日gmv、GMV周同比（前一周那天的gmv）
  ----lag(GMV,1,0) over(partition by 品牌名称,平台 order by 日期) 前一日
  lag(GMV,7,0) over(partition by 品牌名称,平台 order by 日期) 前一周
• 差值
  ----子查询嵌套提升运行效率

select *
,本日GMV - 前一周 GMV同比差异值
,concat(round(((本日GMV - 前一周)/前一周) * 100,0),'%') GMV同比增长百分比值
from (
        select
        品牌名称
        ,平台
        ,日期
        ,GMV 本日GMV
        ,lag(GMV,1,0) over(partition by 品牌名称,平台 order by 日期) 前一日
        ,lag(GMV,7,0) over(partition by 品牌名称,平台 order by 日期) 前一周
        # 
        from ddm.shop
        where substring(日期,1,4) = '2020'
        and 品牌名称 like '%拌客%'
    ) a

总结

1、分布解题（不会就搁置）
2、子查询（各表时间日期不一致，运算比例不一致，窗口函数）
3、尝试回忆数据表内容
4、尝试优化代码

习题

1、查询2020年3月GMV小于2020年各门店月平均GMV的门店，以及这些门店在2019年日GMV最高的三天，以及这三天的GMV
2、查询出2020年全年月均GMV小于前年所有门店第四季度月均GMV的门店，以及这些门店在去年第一二季度的月累计GMV和月累计下单人数,按照门店、月份升序排列
3、2019年蛙小辣各门店在双平台GMV排名后3%（GMV为0不计入排名）的日期、GMV、以及具体排名及排名百分比（取2位小数）
4、查询2020年拌客在双平台各自订单金额最高的订单ID和订单金额，以及在双平台订单金额的极差
5、查询2020年第二季度所有品牌门店的当月GMV、前一个月GMV、月GMV同比增长绝对值及百分比
6、查询互联网金融场景中，19年5月份时，各分行成交金额第一的小组及这些小组成交金额前三的销售的5月份累计成交额、成交额排名、以及与小组内成交额倒数第3的销售的成交额差值

答案下期更

---

特别鸣谢戴师兄