mysql命令窗口_HLOOKUP函数

在这里插入图片描述

窗口：记录集合 窗口函数：在满足某些条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同，窗口大小都是固定的，称为静态窗口；有的函数则相反，不同的记录对应着不同的窗口，称为滑动窗口。

1. 窗口函数和普通聚合函数的区别：

①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。 ②聚合函数也可以用于窗口函数。

2. 窗口函数的基本用法：

函数名 OVER 子句

over关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4中语法来设置窗口。 ①window_name：给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读； ②PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行； ③ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号； ④FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。

3. 按功能划分可将MySQL支持的窗口函数分为如下几类：

①序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()

• 用途：显示分区中的当前行号
• 应用场景：查询每个学生的分数最高的前3门课程

ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY score)

对于stu_id=1的同学，有两门课程的成绩均为98，序号随机排了1和2。但很多情况下二者应该是并列第一，则他的成绩为88的这门课的序号可能是第2名，也可能为第3名。 这时候，ROW_NUMBER()就不能满足需求，需要RANK()和DENSE_RANK()出场，它们和ROW_NUMBER()非常类似，只是在出现重复值时处理逻辑有所不同。

ROW_NUMBER()：顺序排序——1、2、3 RANK()：并列排序，跳过重复序号——1、1、3 DENSE_RANK()：并列排序，不跳过重复序号——1、1、2

②分布函数：PERCENT_RANK()、CUME_DIST()

PERCENT_RANK()

• 用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
• 应用场景：不常用

给窗口指定别名：WINDOW w AS (PARTITION BY stu_id ORDER BY score) rows = 5

CUME_DIST()

• 用途：分组内小于、等于当前rank值的行数 / 分组内总行数
• 应用场景：查询小于等于当前成绩（score）的比例

cd1：没有分区，则所有数据均为一组，总行数为8 cd2：按照lesson_id分成了两组，行数各为4

③前后函数：LAG(expr,n)、LEAD(expr,n)

• 用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
• 应用场景：查询前1名同学的成绩和当前同学成绩的差值

内层SQL先通过LAG()函数得到前1名同学的成绩，外层SQL再将当前同学和前1名同学的成绩做差得到成绩差值diff。

④头尾函数：FIRST_VALUE(expr)、LAST_VALUE(expr)

• 用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
• 应用场景：截止到当前成绩，按照日期排序查询第1个和最后1个同学的分数

⑤其它函数：NTH_VALUE(expr, n)、NTILE(n)

NTH_VALUE(expr,n)

• 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
• 应用场景：截止到当前成绩，显示每个同学的成绩中排名第2和第3的成绩的分数

NTILE(n)

• 用途：将分区中的有序数据分为n个等级，记录等级数
• 应用场景：将每门课程按照成绩分成3组

NTILE(n)函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到n个并行的进程分别计算，此时就可以用NTILE(n)对数据进行分组（由于记录数不一定被n整除，所以数据不一定完全平均），然后将不同桶号的数据再分配。

4. 聚合函数作为窗口函数：

• 用途：在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），可以动态计算在指定的窗口内的各种聚合函数值
• 应用场景：截止到当前时间，查询stu_id=1的学生的累计分数、分数最高的科目、分数最低的科目

参考链接：http://www.cnblogs.com/DataArt/p/9961676.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/186072.html原文链接：https://javaforall.cn