首页 > 数据库 >神奇的 SQL ,高级处理之 Window Functions → 打破我们的局限!

神奇的 SQL ,高级处理之 Window Functions → 打破我们的局限!

时间:2023-12-26 18:05:21浏览次数:45  
标签:Functions 12 窗口 函数 2023 Window SQL 15 ware

开心一刻

  今天儿子跟老婆聊天

  儿子:妈妈,我为什么没有两个爸爸呀

  老婆:每个人都只有一个爸爸呀,你看谁有两个爸爸了

  儿子一脸真诚的看着老婆:那你为什么就有两个爸爸呢

  老婆一脸疑惑的望向儿子:我哪有两个爸爸了?

  儿子有点不服气,温柔地说道:你管爷爷叫爸爸,你管姥爷还叫爸爸,这不就是两个爸爸吗

  老婆轻声解释道:虽然我管他们两个都叫爸爸,但是姥爷才是我的爸爸,爷爷是爸爸的爸爸,是我公公,明白了吗

  儿子两眼朝天上看了下,若有所思道:公公不是太监吗

  老婆惊讶道:什么太监呀,我说的公公和你说的公公不是一回事,你这一天天的脑子里都想什么呢

  儿子生气道:你不用解释了,待会我就告诉奶奶,你说爷爷是太监

什么是窗口函数

Window Function 也称为 OLAP(Online Analytical Processing)函数

是标准的 SQL 功能

窗口函数 ,或者 开窗函数 ,在 Oracle 中也称 分析函数

聚合函数 一样,也是对集合进行聚合计算,但和 聚合函数 又不一样,使用 聚合函数 时,每组只返回一个值,但 开窗函数

  你们懂我说的意思吧

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_聚合函数

  现在不懂也没关系哈,继续往下看,看完之后你肯定就懂了

  支持情况

窗口函数 是 标准 SQL 功能

Oracle 11g 、 SQL Server 2008 、 DB2 9.7 、 PostgreSQL 8.4

MySQL 从 8 开始才支持, MySQL5.7 及之前的版本不支持 窗口函数

SQL

SQL 终将能在所有的 DBMS

窗口函数的语法

  基本语法如下

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_聚合函数_02

  看着很简单,但却很陌生,我们将其进行拆分下

窗口函数

OVER 子句, OVER 是约定好的固定写法,其内容是规则的指定,告诉 窗口函数

PARTITION BY 类似 GROUP BY

ORDER BY 就跟我们平时使用的 ORDER BY

  看完这个语法介绍,我相信大家还是很懵,我非常理解大家

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_SQL_03

  但先别慌,结合案例来看,慢慢就懂了

  能够作为窗口函数使用的函数分两种

RANK 、 ROW_NUMBER 、  DENSE_RANK

SUM 、 AVG 、 COUNT 、 MAX 、 MIN

MySQL8.0.30 ,初始表 tbl_ware

CREATE TABLE `tbl_ware` (
  `ware_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `ware_name` VARCHAR(100) NOT NULL COMMENT '商品名称',
  `ware_category` VARCHAR(100) NOT NULL COMMENT '商品类别',
    `sale_unit_price` INT COMMENT '销售单价',
    `purchase_unit_price` INT COMMENT '进货单价',
    `registration_date` DATE COMMENT '等级日期',
  PRIMARY KEY (`ware_id`) USING BTREE
) ENGINE=InnoDB COMMENT='产品';

INSERT INTO tbl_ware VALUES
(1,'T恤衫','衣服',100, 50,'2023-12-15'),
(2,'打孔器','办公用品',25, 10,'2023-12-15'),
(3,'运动T恤','衣服',150, 50,'2023-12-15'),
(4,'菜刀','厨房用具',75, 30,'2023-12-15'),
(5,'高压锅','厨房用具',600, 200,'2023-12-15'),
(6,'叉子','厨房用具',7, 3,'2023-12-15'),
(7,'菜板','厨房用具',98, 30,'2023-12-15'),
(8,'圆珠笔','办公用品',5, 2,'2023-12-15');

View Code

专用窗口函数

SQL 定义的 OLAP 专用函数,通过函数名很容易看出其 OLAP

  RANK

  从名字可知,该函数用来排名、排序

tbl_ware 按售价从高到低进行排名, SQL

SELECT * FROM tbl_ware ORDER BY sale_unit_price DESC;

RANK 也能实现: SELECT *, RANK() OVER(ORDER BY sale_unit_price DESC) AS ranking FROM

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_窗口函数_04

tbl_ware 按类别进行分组,然后组内按售价从高到低进行排名, SQL

GROUP BY

GROUP BY 往往结合 聚合函数

RANK 可以: SELECT *, RANK() OVER(PARTITION BY ware_category ORDER BY sale_unit_price DESC) AS ranking FROM

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_SQL_05

PARTITION BY 对表的横向进行分组,类似 GROUP BY

ORDER BY 则决定了纵向排序的规则,与 SELECT 子句末尾的 ORDER BY

PARTITION BY 分组后的记录集合称为“窗口”,代表“范围”。这也是 窗口函数

  DENSE_RANK

RANK

INSERT INTO tbl_ware VALUES
(9,'带帽卫衣','衣服', 150, 90, '2023-12-15'),
(10,'砍骨刀','厨房用具', 150, 69, '2023-12-15');

View Code

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_SQL_06

RANK 排序时,如果存在相同位次的记录,会跳过之后的位次,如: 1,2,2,2,5 , 3,4

DENSE_RANK 排序时,如果存在相同位次的记录,则不会跳过之后的位次,如: 1,2,2,2,3,4

  ROW_NUMBER

  获取行数或者行号

SQL 可写成: SELECT *, ROW_NUMBER() OVER(ORDER BY sale_unit_price DESC) AS row_num FROM

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_SQL_07

SELECT *, ROW_NUMBER() OVER(PARTITION BY ware_category ORDER BY sale_unit_price DESC) AS row_num FROM

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_SQL_08

  此刻大家应该想起点什么了

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_窗口函数_09

  分组取前N条,是不是很适合用这种方式实现?

  我都跟你们实现好了:MySQL 分组排序后 → 如何取前N条或倒数N条

专用窗口函数

聚合函数的窗口化使用

聚合函数 都能用作窗口函数,其语法和 专用窗口函数

聚合函数

  SUM

聚合函数 , SUM

  但是窗口化之后了,我们来看看效果

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_聚合函数_10

  发现什么了?

  并不是一个单独的汇总值,而是逐行汇总,是不是有点意思?

  如果再加上分组

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_SQL_11

  分组后,对每一组进行逐行汇总

  AVG

SUM

神奇的 SQL ,高级处理之 Window Functions →  打破我们的局限!_SQL_12

  分组后,对每一组的每一行求历史平均值

聚合函数

窗口函数的适用范围

  通过上述的几个案例,相信大家对这个问题已经有了一个大致的答案

窗口函数 只能在 SELECT 子句中使用,不能在 WHERE 子句或者 GROUP BY

窗口函数 是对 WHERE 子句或者 GROUP BY

窗口函数 是不会改变结果行数的,而 WHERE 是会改变结果行数的,那把 窗口函数 放到 WHERE

窗口函数 只能在 SELECT

总结

窗口函数 是标准的 SQL

SQL

    提供了标准,数据库厂商不一定实现,或者说暂时不实现

窗口函数 与 聚合函数

窗口函数

窗口函数

参考

  《SQL 基础教程》



标签:Functions,12,窗口,函数,2023,Window,SQL,15,ware
From: https://blog.51cto.com/u_13423706/8985905

相关文章

  • 神奇的 SQL ,高级处理之 Window Functions → 打破我们的局限!
    开心一刻今天儿子跟老婆聊天儿子:妈妈,我为什么没有两个爸爸呀老婆:每个人都只有一个爸爸呀,你看谁有两个爸爸了儿子一脸真诚的看着老婆:那你为什么就有两个爸爸呢老婆一脸疑惑的望向儿子:我哪有两个爸爸了?儿子有点不服气,温柔地说道:你管爷爷叫爸爸,你管姥爷还叫爸......
  • Windows事件日志
    本文主旨:了解Windows事件日志的相关信息,以便于后续进行Windows日志分析。事件日志简介事件日志记录[1]为(的应用程序和操作系统)记录重要软件和硬件事件提供了一种标准的集中式方法。事件日志记录服务记录来自各种源的事件,并将其存储在名为事件日志的单个集合中。事件日志......
  • kafka 自定义开发Sink Connector组件(兼容mysql和oracle)
    1.情景展示目前,市场上已有不少能从kafka消费数据的插件,如:io.confluent.connect.jdbc.JdbcSinkConnector,但这个组件有个致命的问题是,只能同步字符串类型。具体意思是:源库源表的日期类型字段,往目标库目标表插入数据的时候,只能是字符串类型,无法自动将其转成日期类型。这样一来的......
  • Sqlserver 中的一些SET参数、系统表的查询
    SQL:BatchStarting:是SQLServerProfiler中的一个事件,它指示一个新的SQL批处理正在开始执行。当SQLServer开始执行一个新的批处理时,它会生成此事件。批处理可以包含一个或多个SQL语句,它们将作为一个单独的单元执行。在Profiler或ExtendedEvents中捕获这个事件可以......
  • MySQL 8.0.12 深入理解bit类型
     背景:在阿里巴巴推荐的MySQL建表规范里要求如下:表达是与否概念的字段,必须使用is_xxx的方式命名,数据类型是unsignedtinyint(1表示是,0表示否)。解释:在MySQL里表示是和否的概念可以使用如下三种方案:1.使用bit(1)类型,此时bit允许存储的是ASCII中的0和1.0表示否1表示是的概念。2......
  • PostgreSQL的安装和卸载
    一、PostgreSQL的卸载查看版本号和系统类别:cat/etc/redhat-release如果是redhat/centos:(yuminstall)yum删除软件包:yumremovepostgresql*删除相关目录文件:rm-rf/var/lib/pgsqlrm-rf/usr/pgsql*删除pg相关用户组/用户userdel-rpostgresgroupdelpostgresrpm包安装的......
  • Windows下用rclone代替RaiDrive将ftp映射为本地盘
    Windows下用rclone代替RaiDrive将ftp映射为本地盘1.软件准备:nssm:官网下载地址:https://nssm.cc/downloadrclone:官网下载地址:https://rclone.org/downloads/WinFsp:官网下载地址:https://winfsp.dev/rel/2.使用:下载WinFsp,如我下载的是winfsp-2.0.23075.msi,下一步下......
  • .NET8操作Mysql,Pomelo.EntityFrameworkCore.MySql版本目前最高只有7.0解决办法
    Pomelo.EntityFrameworkCore.MySql7.0是不支持.NET8的,但现在Pomelo.EntityFrameworkCore.MySql的最新版本只有7.0因为 Pomelo.EntityFrameworkCore.MySql8.0还在研发中,还没有正式的版本,只有公测版本,可以在控制台中直接输入命令安装即可Install-PackagePomelo.EntityFrameworkCor......
  • windows下gitlab安装
    第一步:搜索引擎搜索gitlabinstall进入链接InstallGitLab|GitLab第二步:点击Installationmethods在此步骤发现没有windows下的安装,但是看到了docker安装方式,所以先安装dockerforwindows(这里不着急安装,可以先往后看)第三步:点击Docker第四步:查看docker方式安装文档......
  • window10下生成目录结构树
    大家看博客的时候应该看到过这种目录结构展示可以手敲出来,但是麻烦,我们可以命令生成 cmd,进入要生成目录结构树的目录 预览目录结构(展示到最后一级目录名):tree 目录结构写到文件:tree>dir.txt dir.txt内容 如果要显示文件名,加个参数即可:tree/f,tree/f>dir.txt __EOF__本......