SQL分析函数-初次邂逅(一篇)

已于 2022-10-05 17:23:00 修改
阅读量760 收藏 5
点赞数
分类专栏： 数据库 文章标签： sql 数据库 database
于 2022-02-24 11:17:09 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/nanyangnongye/article/details/122322488
版权

描述：习题需求，利用开窗函数取出每个年级中最小的人员的学号/年级/姓名/分数

目录

🏆一、 开窗函数引子

⭐️1.1 建表语句

⭐️1.2 描述

⭐️1.3 题解

⭐️1.4 输出

⭐️1.5 思考 如果我不用开窗函数呢

🏆二、开窗函数概念

⭐️2.1 什么是开窗函数

⭐️2.2 为要用开窗函数

⭐️2.3 怎么用分析、开窗函数

🏆三、分析函数实战

⭐️2.1 sum()  over()求和函数

⭐️2.2 rank()、 dense_rank()、row_number()排序函数

⭐️2.6 First_value() 和Last_value()

⭐️2.7 求之前或之后的第N行

⭐️2.8 使用多个开窗函数

⭐️2.9 order by、group by

⭐️2.10 定位聚合计算范围

🏆四、参考文章

⭐️4.1 基础

⭐️4.2 进阶

 

 

🏆一、 开窗函数引子

描述中的需求是引子，自己又要充电了....

⭐️1.1 建表语句

/*
 Navicat Premium Data Transfer

 Source Server         : 本机 SQL SERVER
 Source Server Type    : SQL Server
 Source Server Version : 11003128
 Source Host           : :1433
 Source Catalog        : account
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 11003128
 File Encoding         : 65001

 Date: 05/01/2022 15:19:25
*/


-- ----------------------------
-- Table structure for student_2005
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[student_2005]') AND type IN ('U'))
    DROP TABLE [dbo].[student_2005]
GO

CREATE TABLE [dbo].[student_2005] (
  [num] int  NOT NULL,
  [txt] int  NOT NULL,
  [xm] char(1) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [fs] int  NOT NULL
)
GO

ALTER TABLE [dbo].[student_2005] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of student_2005
-- ----------------------------
INSERT INTO [dbo].[student_2005] ([num], [txt], [xm], [fs]) VALUES (N'1', N'1', N'A', N'65')
GO

INSERT INTO [dbo].[student_2005] ([num], [txt], [xm], [fs]) VALUES (N'2', N'1', N'B', N'34')
GO

INSERT INTO [dbo].[student_2005] ([num], [txt], [xm], [fs]) VALUES (N'3', N'2', N'C', N'53')
GO

INSERT INTO [dbo].[student_2005] ([num], [txt], [xm], [fs]) VALUES (N'4', N'2', N'D', N'12')
GO


-- ----------------------------
-- Primary Key structure for table student_2005
-- ----------------------------
ALTER TABLE [dbo].[student_2005] ADD CONSTRAINT [PK__student___DF908D6563DA5A89] PRIMARY KEY CLUSTERED ([num])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

大意了 MYSQL 没有开窗函数 所以才会一直报错

> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(group by  by txt)  from student_2005' at line 1

⭐️1.2 描述

⭐️1.3 题解

select

        num ,

        txt,

        xm,

        fs

from

        student_2005

where num in (select min(num) over(partition by txt) from student_2005 )

⭐️1.4 输出

⭐️1.5 思考 如果我不用开窗函数呢

题解

select num ,txt,xm,fs from student_2005 where num in (
select min(num) as num from student_2005 group by txt
)

 

从sql的长度上看用开窗并没有特别简洁 反倒是查询出来的结果集 重复了 单行多行这个没有太多关系 看结果来说 二者都是多行数据

好吧 那么继续往下面看吧 

🏆二、开窗函数概念

⭐️2.1 什么是开窗函数

        开窗函数：在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。

所谓的开窗函数，依本人愚见，开窗函数就是 over([query_partition_clase] order_by_clause)。比如说，我采用sum求和，rank排序等等，但是我根据什么来呢？over提供一个窗口，可以根据什么什么分组，就用partition by，然后在组内根据什么什么进行内部排序，就用 order by。而下面整理的函数除over()外，都为分析函数

⭐️2.2 为要用开窗函数

开窗函数简介:与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数，在 Oracle 中则被称为分析函数。

分析函数的形式
　　分析函数带有一个开窗函数over()，包含分析子句 。

　　分析子句又由下面三部分组成：
　　partition by ：分组子句，表示分析函数的计算范围，不同的组互不相干；
　　ORDER BY： 排序子句，表示分组后，组内的排序方式；
　　ROWS/RANGE：窗口子句，是在分组(PARTITION BY)后，组内的子分组（也称窗口），此时分析函数的计算范围窗口，而不是PARTITON。窗口有两种，ROWS和RANGE；

　　使用形式如下：

OVER(PARTITION BY xxx PORDER BY yyy ROWS BETWEEN rowStart AND rowEnd)

思考：

下面的实战 避免开窗不支持 那就直接转战Oracle数据库吧

噢 看到这里就可以解释的通了 就是通过聚合函数查询的字段只能返回一个值

但是开窗函数的话 就可以查很多数据 两条数据甚至更多 

⭐️2.3 怎么用分析、开窗函数

聚合函数作用分析函数作用详细作用
COUNT()计数RANK()给查询结果集编排名  结果集中如果出现两个相同的数据，那么rank会进行跳跃式的排名，比如两个第二，那么没有第三接下来就是第四
SUM()求和DENSE_RANK()但是dense_rank不会跳跃式的排名，两个第二接下来还是第三
MIN()最小值ROW_NUMBER()row_number最牛，即使两个数据相同，排名也不一样
MAX()最大值RATIO_TO_REPORT()计算比率
AVG()平均值NTILE()平分组
LEAD()向上偏移
LAG() 向下偏移
FIRST_VALUE()求最小值
LAST_VALUE()求最大值
over()

不能单独使用要和聚合函数

排名函数后面使用 

关键字，用于标识分析函数

可取值说明示例
CURRENT ROW当前行
N PRECEDING前n行2 preceding
UNBOUNDED PRECEDING一直到第一条记录
N FOLLOWING后N行2 following

UNBOUNDED

FOLLOWING

一直到最后一条记录

🏆三、分析函数实战

1 、partition by 字段名字A：子句进行分组，partition by是固定的分组语法；

2、order by 字段名字B：子句进行排序，order by 是固定的排序语法。

比如我们上面的例子就是用到了partition by classid 和 order by score这样的用法了，注意：如果联合使用指的意思是：先分组然后再排序

OVER()函数不能单独使用，必须跟在 排名函数（ ROW_NUMBER、DENSE_RANK、RANK、NTILE） 或 5种聚合函数（SUM、MAX、MIN、AVG、COUNT）后边。

⭐️2.1 sum()  over()求和函数

建表语句 sql server数据库

CREATE TABLE student_2008 (
name VARCHAR ( 32 ), 
sale INT 
);

insert into student_2008(name,sale) values
('产品A',90),
('产品B',84),
('产品C',73),
('产品D',100),
('产品E',103),
('产品F',89);

求销售额的累计值

select *,sum(sale) over(order by sale) leiji from student_2008

意思就是先把sale使用over()函数排序 然后在求sale的和 

⭐️2.2 rank()、 dense_rank()、row_number()排序函数

select *,RANK() over(order by sale) leiji from student_2008
select *,DENSE_RANK() over(order by sale) leiji from student_2008
select *,RANK() over(order by sale) leiji from student_2008

以上三条查询语句 都是在sale排序后使用over开启的一个窗口 在这个窗口内进行的一个排序 三条查询语句 其结果集都是一样的 如图

 那一样的 我用哪个呢 有什么区别呢？

现在使用update 将我们的表数据稍稍修改一下 就可以测试 这三个排序函数到底是有什么区别 适用什么样的场景了

后续补充 .....

2022年2月24日11:05:51 先补一个 三者的区别在上面概念中详细说明了  后续有使用的可自行测试

准备数据库表

 要求

 sql

with tem as (
select 
*
from no2004a left join no2004b  using(PRODUCTID) WHERE  CUSTOMID = 'C004' 
) select RANK() over(ORDER BY PRICE DESC) as '排名' ,ZTIME,PRICE from tem

 注意看 虽然图中的第三行记录排名是2 但是rank()排名默认 “跳跃”排名 1、2、2、4

那我换个函数排名呢 这次我换DENSE_RANK()

 所以 现在 就剩下ROW_NUMBER()函数了 试一下  走起

OK 这才是我想要的排名效果 突然想起来一句话 “爱情不分对错，分先来后到”  噗

下面的函数 有空再试 感兴趣的可自行尝试

⭐️2.6 First_value() 和Last_value()

        这两个属于首尾函数 在本人的其他SQL分析函数有细节拆解 这里不做过多赘述

⭐️2.7 求之前或之后的第N行

　　注：行比较分析函数lead和lag无window(窗口)子句。

　　lag(arg1,arg2,arg3) 和 lead(arg1,arg2,arg3) 可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现，不过使用lag和lead有更高的效率。
　　[1] arg1:参数是列名，
　　[2] arg2:参数是偏移的offset，
　　[3] arg3:参数是超出记录窗口时的默认值。
　　4.1 lag() over()

　　　　lag()函数向下偏移。
　　4.2 lead() over()

　　　　lead()函数是向上偏移。

⭐️2.8 使用多个开窗函数

在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰

⭐️2.9 order by、group by

开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按
照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。

⭐️2.10 定位聚合计算范围

“RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2”部分用来定位聚合计算范围，这个子句又被称为定位框架。

🏆四、参考文章

⭐️4.1 基础

https://www.bbsmax.com/A/ke5jk3yydr/
https://blog.csdn.net/qq_43466457/article/details/100146096

https://blog.csdn.net/jerrytomcat/article/details/82790543?spm=1001.2101.3001.6650.11&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-11.no_search_link&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-11.no_search_link&utm_relevant_index=18

⭐️4.2 进阶

https://www.cnblogs.com/xinaixia/p/5806386.html

http://www.cnblogs.com/lihaoyang/p/6756956.html

http://blog.itpub.net/31397003/viewspace-2140411/

妙趣生花
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
SQL常用函数记录
guanhuazhan的博客
12-25 2465
VALUE函数： 语法：VALUE(EXPRESSION1,EXPRESSION2) VALUE函数用于返回一个非空的值，当其第一个参数非空，直接返回该参数的值，如果第一个参数为空，则返回第二个参数的值。 示例： SELECT VALUE(ID,’’) FROM T1 表示如果T1.ID为空，则返回空串，如果T1.ID不为空，则返回T1.ID。 TRANSLATE函数： 语法：translate(string,from_str,to_str) translate函数以字符为单位，对应字符一一替换。 执行时,
第二章 SQL命令参考-VALUES
kygoal的博客
11-21 6633
VALUES Computes a setof rows. 概要 VALUES( expression [, ...] ) [, ...] [ORDER BY sort_expression [ASC | DESC | USINGoperator] [, ...]] [LIMIT{count | ALL}] [OFFSET start]   描述 VALUES计算由值表达式指定的行
如何用sql语句查询年龄最小或最大的信息
weixin_67908168的博客
02-22 5004
年龄最大或最小问题
[sql] SQL Server判断对象是否存在
weixin_30636089的博客
08-22 526
来源：http://www.jiaonan.tv/html/blog/1/27227.htm1 判断数据库是否存在Sql代码 if exists (select * from sys.databases where name = ’数据库名’) drop database [数据库名] if exists (select * from sys.databases where name = ...
SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）
Hehuyi_In的博客
10-23 7356
一、 构造测试数据 --创建测试表 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U')) DROP TABLE [dbo].[TestRows2Columns] GO CREATE TABLE [dbo].[TestRows2Columns]( [Id] [int] IDENTITY(1,1) NOT NULL,
sqlserver 常用函数
kaka的博客
12-17 1643
1.先建个简单的表 create table Test1( id int identity , name VARCHAR(30), age int, address varchar(255) ) 2.插入数据 insert Test1(name,age,address) values('kaka1',18,'临近街道') insert Test1(name,age,address) values('kaka2',19,'旧金山') insert Test1(name,age,address).
一个chm格式的 SQL 函数手册-SQL语言手册文档
最新发布
05-29
SQL是一种用于管理关系型数据库的标准语言，它允许用户通过特定的命令和查询来创建、读取、更新和删除（CRUD）数据库中的数据。SQL语言手册文档为用户提供了关于SQL语法、用法和最佳实践的详细指导。使用 SQL 合计...
SQL数据库-规范化函数依赖.ppt
11-21
SQL数据库-规范化函数依赖.ppt
SQLSERVER课件-函数多表查询(ppt文档).ppt
11-16
SQLSERVER课件-函数多表查询(ppt文档).ppt
SQL中row-number函数用法
05-22
SQL中row-number函数用法
SQL学习四-聚集函数
12-17
SQL学习四-聚集函数
SQL 使用 VALUES 生成带数据的临时表
weixin_41999297的博客
04-22 2400
VALUES 是 INSER 命令的子句。 INSERT INOT 表名(列名1,列名2,…) VALUES(值1,值2,…) SELECT * FROM ( 　　VALUES (123456),(123456),(123456),(123456),(123456) ) AS T(c1)
mysql的values使用_MySQL的SQL语句 - 数据操作语句（16）- VALUES 语句
热门推荐
weixin_36254564的博客
01-19 1万+
VALUES 语句VALUES 是 MySQL 8.0.19 中引入的一个 DML 语句，它以表的形式返回一个或多个行的集合。换句话说，它是一个表值构造器，也可以作为独立的 SQL 语句来运行。1. VALUES row_constructor_list [ORDER BY column_designator] [LIMIT BY number]2.3. row_constructor_list:...
on duplicate key update导致主键不连续自增的问题
z69183787的专栏
01-19 1966
http://blog.csdn.net/u011900448/article/details/55044990 最近项目上需要实现这么一个功能:统计每个人每个软件的使用时长,客户端发过来消息,如果该用户该软件已经存在增更新使用时间,如果没有则新添加一条记录,代码如下: [html] view plain copy     
第八十七章 SQL命令 VALUES
yaoxin521123的博客
11-26 8720
文章目录 第八十七章 SQL命令 VALUES大纲参数描述示例 第八十七章 SQL命令 VALUES 指定字段中使用的数据值的INSERT/UPDATE子句。 大纲 (field1{,fieldn}) VALUES (value1{,valuen}) 参数 field - 字段名或以逗号分隔的字段名列表。 value - 值或以逗号分隔的值列表。 每个值被赋给相应的字段。 描述 VALUES子句用于INSERT、UPDATE或INSERT or UPDATE语句中，以指定要插入到字段中的数
SQL 开窗函数
sinat_41207450的博客
09-05 903
1、开窗函数/分析函数：over() 2、几个排序函数的差异 3、主要开窗函数
这应该是最全的总结了！SQL中的开窗函数汇总！
Python数据挖掘
09-09 2785
MySQL从8.0版本开始支持窗口函数了，窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组（GROUP BY）的某种聚合值，它和聚合函数的不同之处是：窗口函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。窗口函数经常会在leetCode的题目中使用到窗口函数的格式函数（Function）的类型不是所有的函数（Function）都支持开窗函数。
sql开窗函数
qq_37527921的博客
07-18 198
开窗函数 over() 简介 窗口函数OVER（）指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。 开窗函数不需要使用GROUP BY就可以对数据进行分组， 还可以同时返回基础行的列和聚合列。 聚合函数例如collect_list 也可以在聚合的同时返回基础列 1.当其他列只是收集，不做复杂处理时可以使用聚合+聚合函数 2.需要明细及统计值，而且需要复杂处理使用开窗函数 分类有： 排名开窗函数、聚合开窗函数 -over() 的语法为：over([patition by ] )。需要注意的是，ove
C语言 找出总分最高的学生
Qianzshuo的博客
12-29 7274
题目描述 给定N个学生的基本信息，包括学号（由5个数字组成的字符串）、姓名（长度小于10的不包含空白字符的非空字符串）和3门课程的成绩（[0,100]区间内的整数），要求输出总分最高学生的姓名、学号和总分。 输入 输入在一行中给出正整数N（≤10）。随后N行，每行给出一位学生的信息，格式为“学号 姓名 成绩1 成绩2 成绩3”，中间以空格分隔。 输出 在一行中输出总分最高学生的姓名、学号和总分，间...
spark sql操作之-函数汇总篇-中
03-16
本篇文章主要介绍了Spark SQL中的函数汇总，包括数学函数、字符串函数、日期函数、聚合函数等。其中，数学函数包括常见的加减乘除、取余、绝对值、平方根、指数等；字符串函数包括字符串连接、截取、替换、转换大小...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

妙趣生花

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值