如何做电商运营_安阳区号邮编_温州seo团队_西安百度推广代运营

一. 数据介绍

二. 数据汇总

三. 使用CASE WHEN 和 GROUP BY将数据分组

四. 窗口函数

0. 数据准备

window_function.sql

1. 窗口函数简介

• MYSQL 8.0 之后，加入了窗口函数功能，简化了数据分析工作中查询语句的书写
• 在没有窗口函数之前，我们需要通过定义临时变量和大量的子查询才能完成的工作，使用窗口函数实现起来更加简洁高效
• 窗口函数是数据分析工作中必须掌握的工具，在SQL笔试中也是高频考点
• 什么是窗口函数? 为什么说窗口函数可以使复杂的查询变得更加简单方便？

① 什么是窗口函数

• 窗口函数是类似于可以返回聚合值的函数，例如SUM()，COUNT()，MAX()。但是窗口函数又与普通的聚合函数不同，它不会对结果进行分组，使得输出中的行数与输入中的行数相同。

• 一个窗口函数大概看起来是这样：

  SELECT SUM() OVER(PARTITION BY ___ ORDER BY___) FROM Table 
  

这里有3点需要牢记：

• 聚合功能：在上述例子中，我们用了SUM()，但是你也可以用COUNT(), AVG()之类的计算功能
• PARTITION BY：你只需将它看成GROUP BY子句，但是在窗口函数中，你要写PARTITION BY
• ORDER BY：ORDER BY和普通查询语句中的ORDER BY没什么不同。注意，输出的顺序要仔细考虑

② 窗口函数示例

示例：集合函数VS窗口函数

假设我们有如下这个表格：

如果要按性别获取平均GPA，可以使用聚合函数并运行以下查询：

SELECT Gender, AVG(GPA) as avg_gpa FROM students GROUP BY Gender

结果如下：

现在我们想得到如下结果：

我们当然可以用我们刚刚提到的聚合函数，然后再将结果join到初始表，但这需要两个步骤。但如果我们使用窗口函数，我们则可以一步到位，并得到相同的结果：

SELECT *, AVG(GPA) OVER (PARTITION BY Gender) as avg_gpa FROM students 

通过上面的查询，就按性别对数据进行划分，并计算每种性别的平均GPA。然后，它将创建一个称为avg_gpa的新列，并为每行附加关联的平均GPA

③ 窗口函数的优点

• 简单，窗口函数更易于使用。在上面的示例中，与使用聚合函数然后合并结果相比，使用窗口函数仅需要多一行就可以获得所需要的结果。
• 快速，这一点与上一点相关，使用窗口函数比使用替代方法要快得多。当你处理成百上千个千兆字节的数据时，这非常有用。
• 多功能性，最重要的是，窗口函数具有多种功能，比如，添加移动平均线，添加行号和滞后数据，等等。

2. OVER()

• 首先看OVER (...) 的最基本用法： OVER() 意思是所有的数据都在窗口中，看下面的SQL

  SELECTfirst_name,last_name,salary,  AVG(salary) OVER()
  FROM employee;
  

• SQL并不复杂，主要看跟 OVER() 相关的部分

  AVG(salary) OVER()
  

• AVG(salary) 意思是要计算平均工资，加上 OVER() 意味着对全部数据进行计算，并将平均值添加到每行记录的后面

• 需要注意的是，我们没有使用GROUP BY进行分组，这样在查询结果中除了聚合函数的结果之外，我们还可以显示其他数据

• 如果使用GROUP BY 想将聚合结果与原始数据放到一个结果中，需要使用子查询，效率相对低

① over avg

• 求平均薪资

  selectfirst_name,last_name,salary,avg(salary) over()
  from employee;
  

• 需求：创建报表，除了查询每个人的工资之外，还要统计出公司每月的工资支出

  selectfirst_name,last_name,salary,sum(salary) over()
  from employee;
  

• 将OVER()的结果用于进一步计算
• 通常，OVER()用于将当前行与一个聚合值进行比较，例如，我们可以计算出员工的薪水和平均薪水之间的差。

  selectfirst_name,last_name,salary,avg(salary) over(),salary-avg(salary) over() as '差值'
  from employee
  order by salary;
  

  
  上面查询结果的最后一列显示了每名员工的工资和平均工资之间的差额，这就是窗口函数的典型应用场景：将当前行与一组数据的聚合值进行比较

② over where

• 在WHERE条件执行之后，才会执行窗口函数。
• 不能在WHERE子句中使用窗口函数

• 查询了id为2的部门所采购的所有商品，并将计算了每项支出占总采购金额的占比

  selectid,item,price,price/sum(price) over()
  from purchase
  where department_id=2;
  

  

• 需求:统计人力资源部(部门ID为3)的员工薪资，并将每名员工的薪资与部门平均薪资进行比较

  selectfirst_name,last_name,salary,salary-avg(salary) over() as differnt
  from employee
  where department_id=3
  

  

③ over count

• 查询出每个部门的 id 和 name 部门名称，以及所有部门的数量，最后通过部门名字排序

  selectid,name,count(id) over()
  from department
  order by name;
  

  

• 需求:查询月薪超过4000的员工，并统计所有月薪超过4000的员工数量和
  查询结果字段:first_name，1ast_name，salary 和超过4000的员工数量

  select first_name,last_name,salary,count(id) over()
  from employee
  where salary>4000;
  

  

④ 小结

• 可以使用 OVER()，对全部查询结果进行聚合计算。
• 在WHERE条件执行之后，才会执行窗口函数。
• 窗口函数在执行聚合计算的同时还可以保留每行的其它原始信息。
• 不能在WHERE子句中使用窗口函数

3. RANK(), DENSE_RANK(), ROW_NUMBER()

排序函数和窗口配合使用
到目前为止，我们已经介绍了如何在窗口函数中使用聚合计算函数SUM(), COUNT(), AVG(), MAX() 和 MIN()

接下来，我们将学习如何通过窗口函数实现排序，具体语法如下：

<ranking function> OVER (ORDER BY <order by columns>)

在后面的练习中我们会介绍常用的排序函数

① 数据准备

window.md

② rank()

根据某列进行排序，排序后返回排序序号；如果列值相同，排序的序号也是相同的，但序号不是连续的

• 作用及用法

• RANK()会返回每一行的等级（序号）

• ORDER BY对行进行排序将数据按升序或降序排列（DESC），RANK（）OVER（ORDER BY ...）是一个函数，与ORDER BY 配合返回序号

  RANK() OVER (ORDER BY 列 DESC)
  -- 根据列的值进行排序，返回排序的序号，DESC表示对ORDER BY的列排序采用倒序
  

• 具体看如下例子：

  SELECTname,platform,editor_rating,RANK() OVER(ORDER BY editor_rating) as rank_
  FROM game;
  

• 上面的SQL对所有数据按编辑评分排序，RANK()函数会返回排序后的排名序号

  

  • 观察上面的查询结果：
  • 最后一列 rank_ 中显示了游戏的得分排名，得分最低（4分）的三个游戏并列倒数第一
  • 得分为5分的游戏，排名为4，这里并没有2和3 ，这个就是rank()函数的特点，当有并列的情况出现时，序号是不连续的

• 需求：统计每个游戏的名字，分类，更新日期，更新日期的顺序

  SELECTname,genre,updated,RANK() OVER(ORDER BY updated) as date_rank
  FROM game;
  

  

③ DENSE_RANK()

RANK() 函数返回的序号，可能会出现不连续的情况；如果想在有并列情况发生的时候仍然返回连续序号可以使用dense_rank()函数。将 2.1示例 中的代码做一个修改

SELECTname,platform,editor_rating,DENSE_RANK() OVER(ORDER BY editor_rating) as dense_rank_
FROM game;
-- 这里注意：在部分mysql版本中起的别名会出现语法冲突造成的异常错误，加一个下划线即可解决

查询结果 从结果中看出，dense_rank列的序号是连续的，1，2，3，4，5 （跟使用rank 有明显的区别，rank 会跳过2，3）

• 需求：对游戏的安装包大小进行排序，使用DENSE_RANK()，返回游戏名称，包大小以及序号

  SELECTname,size,DENSE_RANK() OVER(ORDER BY size) AS dense_rank_ 
  FROM game;
  

  

④ ROW_NUMBER()

• 想获取排序之后的序号，也可以通过ROW_NUMBER() 来实现，从名字上就能知道，意思是返回行号
• ROW_NUMBER()函数 根据某列进行排序，排序后返回排序序号；如果列值相同，排序的序号是不同的！

• 需求：对游戏的安装包大小进行排序，使用ROW_NUMBER()，返回游戏名称，包大小以及序号

  SELECTname,size,ROW_NUMBER() OVER(ORDER BY size) AS `row_number`
  FROM game;
  -- 对于别名冲突，也可以使用``
  

  查询结果 从结果可以看出，ROW_NUMBER()返回的是唯一行号（唯一序号），跟RANK() 和 DENSE_RANK() 返回的是序号，序号会有并列情况出现
  

• 需求，将游戏按发行时间排序，返回唯一序号

  SELECTname,released,ROW_NUMBER() OVER(ORDER BY released) `row_number`
  FROM game;
  

  查询结果

  

• 对比 RANK(), DENSE_RANK(), ROW_NUMBER() 之间的区别，对上面的案例同时使用三个函数

  SELECTname,genre,released,RANK() OVER(ORDER BY released) as rank_num,DENSE_RANK() OVER(ORDER BY released) as dense_rank_num,ROW_NUMBER() OVER(ORDER BY released) as row_num
  FROM game;
  

  查询结果

  

⑤ 小结

• 最基本的排序函数: RANK() OVER(ORDER BY column1, column2...).
• 通过排序获取序号的函数介绍了如下三个：
• RANK() – 返回排序后的序号 rank ，有并列的情况出现时序号不连续
• DENSE_RANK() – 返回 ‘连续’ 序号
• ROW_NUMBER() – 返回连续唯一的行号，与排序ORDER BY 配合返回的是连续不重复的序号

4. partition by

partition by 和 group by 语义是类似的, 效果有区别

• group by 分组之后会聚合成一条
• partition by 也会分组, 但是分组之后结合窗口函数不会改变结果的条目数
• 通过partition by 可以很方便的实现组内排序

select first_name,last_name,department_id,salary,row_number() over (partition by department_id order by salary DESC) as 薪资排名,rank() over (partition by department_id order by salary DESC) as 薪资排名,dense_rank()  over (partition by department_id order by salary DESC) as 薪资排名
from employee

• 按不同的部门进行分组, 对每个部门的员工, 做部门内部的薪资排序
  

• 每个部门挑选出薪资最高的两个员工

  select * from
  (select first_name,last_name,department_id,salary,row_number() over (partition by department_id order by salary DESC) as 薪资排名 from employee) a
  where a.薪资排名<3;