带你快速了解SQL窗口函数

目录
  • 底表
  • 分组排序
    • 各分组排序函数的差异
  • 累计聚合
    • 与 GROUP BY 组合使用
  • 总结

窗口函数形如：

表达式 OVER (PARTITION BY 分组字段 ORDER BY 排序字段)

有两个能力：

  • 当表达式为 rank() dense_rank() row_number() 时，拥有分组排序能力。
  • 当表达式为 sum() 等聚合函数时，拥有累计聚合能力。

无论何种能力，窗口函数都不会影响数据行数，而是将计算平摊在每一行。

这两种能力需要区分理解。

底表

以上是示例底表，共有 8 条数据，城市1、城市2 两个城市，下面各有地区1～4，每条数据都有该数据的人口数。

分组排序

如果按照人口排序，ORDER BY people 就行了，但如果我们想在城市内排序怎么办？

此时就要用到窗口函数的分组排序能力：

SELECT *, rank() over (PARTITION BY city ORDER BY people) FROM test

该 SQL 表示在 city 组内按照 people 进行排序。

其实 PARTITION BY 也是可选的，如果我们忽略它：

SELECT *, rank() over (ORDER BY people) FROM test

也是生效的，但该语句与普通 ORDER BY 等价，因此利用窗口函数进行分组排序时，一般都会使用 PARTITION BY。

各分组排序函数的差异

我们将 rank() dense_rank() row_number() 的结果都打印出来：

SELECT *,
rank() over (PARTITION BY city ORDER BY people),
dense_rank() over (PARTITION BY city ORDER BY people),
row_number() over (PARTITION BY city ORDER BY people)
FROM test

其实从结果就可以猜到，这三个函数在处理排序遇到相同值时，对排名统计逻辑有如下差异：

  • rank(): 值相同时排名相同，但占用排名数字。
  • dense_rank(): 值相同时排名相同，但不占用排名数字，整体排名更加紧凑。
  • row_number(): 无论值是否相同，都强制按照行号展示排名。

上面的例子可以优化一下，因为所有窗口逻辑都是相同的，我们可以利用 WINDOW AS 提取为一个变量：

SELECT *,
rank() over wd, dense_rank() over wd, row_number() over wd
FROM test
WINDOW wd as (PARTITION BY city ORDER BY people)

累计聚合

我们之前说过，凡事使用了聚合函数，都会让查询变成聚合模式。如果不用 GROUP BY，聚合后返回行数会压缩为一行，即使用了 GROUP BY，返回的行数一般也会大大减少，因为分组聚合了。

然而使用窗口函数的聚合却不会导致返回行数减少，那么这种聚合是怎么计算的呢？我们不如直接看下面的例子：

SELECT *,
sum(people) over (PARTITION BY city ORDER BY people)
FROM test

可以看到，在每个 city 分组内，按照 people 排序后进行了 累加（相同的值会合并在一起），这就是 BI 工具一般说的 RUNNGIN_SUM 的实现思路，当然一般我们排序规则使用绝对不会重复的日期，所以不会遇到第一个红框中合并计算的问题。

累计函数还有 avg() min() 等等，这些都一样可以作用于窗口函数，其逻辑可以按照下图理解：

你可能有疑问，直接 sum(上一行结果，下一行) 不是更方便吗？为了验证猜想，我们试试 avg() 的结果：

可见，如果直接利用上一行结果的缓存，那么 avg 结果必然是不准确的，所以窗口累计聚合是每行重新计算的。当然也不排除对于 sum、max、min 做额外性能优化的可能性，但 avg 只能每行重头计算。

与 GROUP BY 组合使用

窗口函数是可以与 GROUP BY 组合使用的，遵循的规则是，窗口范围对后面的查询结果生效，所以其实并不关心是否进行了 GROUP BY。我们看下面的例子：

按照地区分组后进行累加聚合，是对 GROUP BY 后的数据行粒度进行的，而不是之前的明细行。

总结

窗口函数在计算组内排序或累计 GVM 等场景非常有用，我们只要牢记两个知识点就行了：

  • 分组排序要结合 PARTITION BY 才有意义。
  • 累计聚合作用于查询结果行粒度，支持所有聚合函数。

讨论地址是：精读《SQL 窗口函数》· Issue #405 · ascoders/weekly

到此这篇关于SQL窗口函数的文章就介绍到这了,更多相关SQL窗口函数内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • SQL 窗口函数实现高效分页查询的案例分析

    SQL 窗口函数实现高效分页查询的案例分析

    🍺不闻不若闻之,闻之不若见之,见之不若知之,知之不若行之.学至于行之而止矣.--荀子 大家好!我是只谈技术不剪发的 Tony 老师. 在使用 SQL 语句实现分页查询时,我们需要知道一些额外的参数信息,例如查询返回的总行数.当前所在的页数.最后一页的页数等.在传统的实现方法中我们需要执行额外的查询语句获得这些信息,本文介绍一种只需要一个查询语句就可以返回所有数据的方法,也就是通过 SQL 窗口函数实现高效的分页查询功能. 本文使用的示例表和数据可以这里下载. 传统方法实现分页查询 在 SQL 中实现分页查询的传统方法就是利用标准的 OFFSET - FETCH 语句或者许多数据库支持的 LIMIT - OFFSET 语句,例如: -- Oracle.SQL Server.PostgreSQL SELECT emp_name, sex, email FROM employee ORDER B

  • MySQL窗口函数的具体使用

    MySQL窗口函数的具体使用

    目录 一.什么是窗口函数 1.怎么理解窗口? 2.什么是窗口函数 二.窗口函数用法 1.序号函数:row_number() / rank() / dense_rank() 2.分布函数:percent_rank() / cume_dist() 3.前后函数:lag(expr,n) / lead(expr,n) 4.头尾函数:FIRST_VALUE(expr).LAST_VALUE(expr) 5.其他函数:nth_value() / nfile() 本章小结   之前我给粉丝们搞过个投票,寻找M

  • MySQL8.0窗口函数入门实践及总结

    前言 MySQL8.0之前,做数据排名统计等相当痛苦,因为没有像Oracle.SQL SERVER .PostgreSQL等其他数据库那样的窗口函数.但随着MySQL8.0中新增了窗口函数之后,针对这类统计就再也不是事了,本文就以常用的排序实例介绍MySQL的窗口函数. 1.准备工作 创建表及测试数据 mysql> use testdb; Database changed /* 创建表 */ mysql> create table tb_score(id int primary key aut

  • Mysql8.0使用窗口函数解决排序问题

    MySQL窗口函数简介 MySQL从8.0开始支持窗口函数,这个功能在大多商业数据库和部分开源数据库中早已支持,有的也叫分析函数. 什么叫窗口? 窗口的概念非常重要,它可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数.对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口:有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口. 窗口函数和普通聚合函数也很容易混淆,二者区别如下: 聚合函数是将多条记录聚合为一条:

  • 带你快速了解SQL窗口函数

    带你快速了解SQL窗口函数

    目录 底表 分组排序 各分组排序函数的差异 累计聚合 与 GROUP BY 组合使用 总结 窗口函数形如: 表达式 OVER (PARTITION BY 分组字段 ORDER BY 排序字段) 有两个能力: 当表达式为 rank() dense_rank() row_number() 时,拥有分组排序能力. 当表达式为 sum() 等聚合函数时,拥有累计聚合能力. 无论何种能力,窗口函数都不会影响数据行数,而是将计算平摊在每一行. 这两种能力需要区分理解. 底表 以上是示例底表,共有 8 条数据

  • 还在用if(obj!=null)做非空判断,带你快速上手Optional

    1.前言 相信不少小伙伴已经被java的NPE(Null Pointer Exception)所谓的空指针异常搞的头昏脑涨, 有大佬说过"防止 NPE,是程序员的基本修养."但是修养归修养,也是我们程序员最头疼的问题之一,那么我们今天就要尽可能的利用Java8的新特性 Optional来尽量简化代码同时高效处理NPE(Null Pointer Exception 空指针异常) 2.认识Optional并使用 简单来说,Opitonal类就是Java提供的为了解决大家平时判断对象是否为空

  • 带你快速上手Servlet

    带你快速上手Servlet

    一.Servlet与Tomcat的关系 (1)Tomcat是什么? Tomcat其实是Web服务器和Servlet容器的结合体 (2)什么是Web服务器? 比如,我当前在杭州,你能否用自己的电脑访问我桌面上的一张图片?恐怕不行,我们太习惯通过URL访问的一个网站.下载一部电影了.一个资源,如果没有URL映射,那么外界几乎很难访问,而Web服务器的作用说穿了就是:将某个主机上的资源映射为一个URL供外界访问 二.什么是Servlet (1)什么是Servlet容器? Servlet是运行在Web服

  • 带你快速了解Java中类和对象的关系

    带你快速了解Java中类和对象的关系

    本文重点解读Java里的类和对象的关系 1.首先介绍两个概念 类:类是对现实生活中一类具有共同属性和行为事务的抽象,确定对象将会拥有的属性和行为. 对象:对象是类的一个实例(对象不是找个女朋友),有状态和行为.例如,一条狗是一个对象,它的状态有:颜色.名字.品种:行为有:摇尾巴.叫.吃等. 概念比较抽象,我们来讲一个具体的项目开发场景,来理解类和对象.假如,我们现在要开发一套图书管理系统,我们如何在程序里面表示出一本书呢?这个时候就用到了类的概念,我们可以定义一个表示书的数据类型,这个新的数据类

  • Python 带你快速上手 Apache APISIX 插件开发

    Python 带你快速上手 Apache APISIX 插件开发

    目录 一.了解:项目架构 二.安装:部署测试 1. 下载安装 Python Runner 2. 配置 Python Runner 3. 启动 Python Runner 4. 测试 Python Runner 三.实践:插件开发 1. 插件目录 2. 插件示例 3. 插件格式 4. 插件规范及注意事项 前言: 熟悉 Apache APISIX 的小伙伴都知道,之前在社区中我们已经支持了 Java 和 Go 语言的 Runner,今天 Apache APISIX Python Runner 也来了

  • 十分钟带你快速上手Vue3过渡动画

    十分钟带你快速上手Vue3过渡动画

    目录 写在前面 Vue的transition组件 过渡demo class的命名规则 使用animation 过渡模式 appear属性 animate.css库的使用 使用动画序列 使用自定义过渡class 写在最后 写在前面 在实际开发中,为了增加用户体验,经常会使用到过渡动画,而过渡动画在CSS中是通过transition和animation实现的.而在Vue中,Vue本身中内置了一些组件和API可以帮助我们方便的实现过渡动画效果:接下来我们就学习一下. Vue的transition组件

  • SQL窗口函数之取值窗口函数的使用

    SQL窗口函数之取值窗口函数的使用

    目录 案例分析 1.环比分析 2.同比分析 3.复合增长率 4.不同产品最高和最低销售额 示例表和脚本 关于窗口函数的基础,请看文章SQL窗口函数 取值窗口函数可以用于返回窗口内指定位置的数据行.常见的取值窗口函数如下: LAG函数可以返回窗口内当前行之前的第N行数据. LEAD函数可以返回窗口内当前行之后的第N行数据. FIRST_VALUE函数可以返回窗口内第一行数据. LAST_VALUE函数可以返回窗口内最后一行数据. NTH_VALUE函数可以返回窗口内第N行数据. 其中,LAG函数和

  • SQL窗口函数之排名窗口函数的使用

    SQL窗口函数之排名窗口函数的使用

    目录 案例分析 案例使用的示例表 1.环比分析 2.同比分析 3.复合增长率 4.不同产品最高和最低销售额 示例表和脚本 关于窗口函数的基础,请看文章SQL窗口函数 取值窗口函数可以用于返回窗口内指定位置的数据行.常见的取值窗口函数如下: LAG函数可以返回窗口内当前行之前的第N行数据.LEAD函数可以返回窗口内当前行之后的第N行数据.FIRST_VALUE函数可以返回窗口内第一行数据.LAST_VALUE函数可以返回窗口内最后一行数据.NTH_VALUE函数可以返回窗口内第N行数据. 其中,L

  • SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)

    SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)

    目录 案例分析 1.移动平均值 2.累计求和(ROW) 3.累计求和(RANGE) 示例表和脚本 关于窗口函数的基础,请看文章SQL窗口函数 许多常见的聚合函数也可以作为窗口函数使用,包括AVG().SUM().COUNT().MAX()以及MIN()等函数. 案例分析 案例使用的示例表 下面的查询中会用到两个表,其中sales_monthly表中存储了不同产品(苹果.香蕉.橘子)每个月的销售额情况.以下是该表中的部分数据: transfer_log表中记录了一些银行账户的交易日志.以下是该表中

随机推荐