锐客网

  1. 首页 > it技术 > >

firstvalue函数mysql_MySQL|firstvalue函数mysql_MySQL 窗口函数之头尾函数

本文标识 :MQ0018
本文编辑 :长安月下赏美人儿
编程工具 :MySQL、DBeaver
阅读时长 :6分钟
1、头尾函数
(1)应用场景：快速查看某个窗口范围中的第一个或者最后一个指定的字段的数值
(2)头尾函数分类及基础语法
FIRST_value(指定字段)OVER(PARTITION BY 分区的字段 ORDER BY 排序的字段 DESC/ASC)LAST_value(字段)OVER (PARTITION BY 分区字段 ORDER BY 排序的字段 DESC/ASC)
(3)两种头尾函数的区别
first_value() 函数
指定排序字段，不同分区中，指定字段在窗口范围第一个值
last_value() 函数
指定排序字段，不同分区中，指定字段在窗口范围最后一个值
(4)实例比较两种头尾函数
SELECT t2.*,FIRST_value(t2.amt)OVER()AS f1,LAST_value(t2.amt)OVER()AS l2FROM(SELECT t1.dimShopID,t1.dimDateID,SUM(AMT) AS amtFROM dw.fct_sales AS t1WHERE dimDateID BETWEEN 20170801 AND 20170810GROUP BY t1.dimShopID,t1.dimDateID)AS t2ORDER BY dimShopID,amt DESC;
数据结果：
firstvalue函数mysql_MySQL|firstvalue函数mysql_MySQL 窗口函数之头尾函数
文章图片

注意：当函数后无指定分区及排序字段，即 over() 括号内容为空，则会出现上面的结果。
如果函数后有指定的分区及排序的字段又会如何呢？！
此刻，将分享的知识点为窗口的滑动函数！！！
2、滑动函数
(1)基础语法与头尾函数相似
(2)作用：在 over() 中以参数限制窗口分析范围
(3)表示行范围语法
BETWEEN frame_start AND frame_end
(4)frame_start 和 frame_end 如下关键字，精准确定窗口函数分析范围
CURRENT ROW ：边界是当前行，常与其他关键字组合使用
UNBOUNDED PRECEDING ：边界是分区中的第一行
UNBOUNDED FOLLOWING ：边界分区中的最后一行
expr PRECEDING：边界为以当前行减去 expr 数值
expr FOLLOWING：边界为以当前行加上 expr 数值
(5)举例
#窗口范围是当前行，前 2 行，后 1 行，共计 4 行记录RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING
#窗口范围是当前行到分区中的最后一行RANGE UNBOUNDED FOLLOWING
#窗口范围是当前分区中所有行RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
(6)实例展示窗口滑动函数
SELECT t2.*,FIRST_value(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS f1,LAST_value(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS l2FROM(SELECT t1.dimShopID,t1.dimDateID,SUM(AMT) AS amtFROM dw.fct_sales AS t1WHERE dimDateID BETWEEN 20170801 AND 20170810GROUP BY t1.dimShopID,t1.dimDateID)AS t2ORDER BY dimShopID,amt DESC;
数据结果：
firstvalue函数mysql_MySQL|firstvalue函数mysql_MySQL 窗口函数之头尾函数
文章图片

注意：经过对比发现，在没有加入限定范围的情况下，得到的结果，并不是理想中的结果。l2 数据列，数据值是混乱的，并不是每个分组中最小的值。
为什么会出现这种情况？！
因为，窗口函数，默认限制范围是第一行到当前行！！！
分析：
目前以 dimShopID 分成两组，且以 amt 数列倒序排列，理想中的结果 l2 数列，当 dimShopID =33 时，数值应当为 37,233.64；当 dimShopID =34时，数值应当为 44,691.52；
因窗口函数，默认限制范围是第一行到当前行，所以 l2 数列呈现与 amt 数列相同。
添加限制范围，则
SELECT t2.*,FIRST_value(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC)AS f1,LAST_value(t2.amt)OVER(PARTITION BY dimShopID ORDER BY t2.amt DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)AS l2FROM(SELECT t1.dimShopID,t1.dimDateID,SUM(AMT) AS amtFROM dw.fct_sales AS t1WHERE dimDateID BETWEEN 20170801 AND 20170810GROUP BY t1.dimShopID,t1.dimDateID)AS t2ORDER BY dimShopID,amt DESC;
数据结果：
firstvalue函数mysql_MySQL|firstvalue函数mysql_MySQL 窗口函数之头尾函数
文章图片

【firstvalue函数mysql_MySQL|firstvalue函数mysql_MySQL 窗口函数之头尾函数】猜 你 喜 欢

    推荐阅读


    上一篇：数据库|mysql窗口函数中的滑动窗口

    下一篇：MYSQL|MySQL - SQL窗口函数