matlabif函数多个条件并列_第七关：窗口函数

窗口函数介绍

• 介绍：窗口函数也叫OLAP函数，可以对数据库数据进行实时分析处理
• 语法：<窗口函数> over （partition by 用于分组的列名 order by 用于排序的列名）
• 注意：窗口函数原则上只能写在select子句中；“partition by”可省略，即不进行分组

1.1 专用窗口函数（区别见图）

• rank() over （partition by 用于分组的列名 order by 用于排序的列名）

排序时如果有并列则序号一样，但会占用下一位排名

• dense_rank() over （partition by 用于分组的列名 order by 用于排序的列名）

排序时如果有并列则序号一样，且之后的名词顺延

• row_number() over （partition by 用于分组的列名 order by 用于排序的列名）

排序时不会考虑并列情况，依次顺延

三个专用窗口函数区别

1.1.2 用窗口函数解决TOP N 问题，例：查找每个学生成绩最高的2门课程

#注意：因ORDER BY执行顺序在WHERE之后，所以要用子查询，在窗口函数排好序的情况下
再用WHERE限定条件进行取数

SELECT *
FROM 
(SELECT 成绩
      ,学号
      ,课程号
      ,row_number()over(PARTITION BY 课程号 ORDER BY 成绩 DESC) AS ranking
FROM score)h1
WHERE h1.ranking<=2

1.2 聚合窗口函数

• sum(用于统计的列名) over （partition by 用于分组的列名 order by 用于排序的列名）
• avg(用于统计的列名) over （partition by 用于分组的列名 order by 用于排序的列名）
• count(用于统计的列名) over （partition by 用于分组的列名 order by 用于排序的列名）
• max(用于统计的列名) over （partition by 用于分组的列名 order by 用于排序的列名）
• min(用于统计的列名) over （partition by 用于分组的列名 order by 用于排序的列名）

介绍：聚合窗口函数是对截止到本行记录的所有数据进行聚合函数计算

作用：除了可以看到截止本行为止的统计数据外，还可以看出每一行对整体统计数据的影响

1.2.1 应用聚合函数解决组内比较问题，例：查找成绩高于该科平均成绩的学生名单

#聚合窗口函数做法
SELECT *
FROM 
(SELECT 学号
       ,课程号
       ,成绩
       ,AVG(成绩)over(PARTITION BY 课程号) AS avg_score
FROM score)h1
WHERE h1.成绩 > h1.avg_score


#关联子查询做法
SELECT *
FROM score s1
WHERE s1.成绩 >( SELECT AVG(s2.成绩)
                 FROM score s2
                 WHERE s1.课程号 =s2.课程号
                 GROUP BY s2.课程号 )

1.2.2 聚合窗口函数计算移动平均

• avg(用于统计的列名) over （partition by 用于分组的列名 order by 用于排序的列名

rows N preceding）

• 注解：“rows N preceding” 表“前~行”，如“rows 2 preceding”的话就是“前2行”，计算时就是【（本行-2）+（本行-1）+本行】/3