MySQL 窗口函数之头尾函数

DataScience

发布于 2020-01-01 22:51:06

1.7K0

发布于 2020-01-01 22:51:06

文章被收录于专栏：A2DataA2Data

本文标识 : 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 l2
FROM(SELECT t1.dimShopID
      ,t1.dimDateID
      ,SUM(AMT) AS amt
FROM dw.fct_sales AS t1
WHERE dimDateID BETWEEN 20170801 AND 20170810
GROUP BY t1.dimShopID
        ,t1.dimDateID)AS t2
ORDER BY dimShopID,amt DESC;

数据结果：

注意：当函数后无指定分区及排序字段，即 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 l2
FROM(SELECT t1.dimShopID
      ,t1.dimDateID
      ,SUM(AMT) AS amt
FROM dw.fct_sales AS t1
WHERE dimDateID BETWEEN 20170801 AND 20170810
GROUP BY t1.dimShopID
        ,t1.dimDateID)AS t2
ORDER BY dimShopID,amt DESC;

数据结果：

注意：经过对比发现，在没有加入限定范围的情况下，得到的结果，并不是理想中的结果。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 l2
FROM(SELECT t1.dimShopID
      ,t1.dimDateID
      ,SUM(AMT) AS amt
FROM dw.fct_sales AS t1
WHERE dimDateID BETWEEN 20170801 AND 20170810
GROUP BY t1.dimShopID
        ,t1.dimDateID)AS t2
ORDER BY dimShopID,amt DESC;

数据结果：