分析函数over

开窗函数 - first_value/last_value select distinct a.date,a.name,first_value(date)over(partition by name order by date asc)as `每个人对应最早的date`select '张三'as name,'2021-04-11' as date。select '张三'as name,'2021-04-11' as date。select '张三'as name,'2021-04-11' as date。 阅读详情

Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是
对于每个组返回多行,而聚合函数对于每个组只返回一行。

1:统计某商店的营业额。       
     date       sale
     1           20
     2           15
     3           14
     4           18
     5           30
    规则:按天统计:每天都统计前面几天的总额

c:/>select id,sale ,sum(sale) over (partition by id,sale order by sale) from t1;


        ID       SALE SUM(SALE)OVER(PARTITIONBYID,SALEORDERBYSALE)
---------- ---------- --------------------------------------------
         1         20                                           20
         2         15                                           15
         3         14                                           14
         4         18                                           18
         5         30                                           30

c:/>select id,sale ,sum(sale) over (order by sale) from t1;

        ID       SALE SUM(SALE)OVER(ORDERBYSALE)
---------- ---------- --------------------------
         3         14                         14
         2         15                         29
         4         18                         47
         1         20                         67
         5         30                         97
        
        
2:统计各班成绩第一名的同学信息
    NAME   CLASS S                        
    ----- ----- ----------------------
    fda    1      80                    
    ffd    1      78                    
    dss    1      95                    
    cfe    2      74                    
    gds    2      92                    
    gf     3      99                    
    ddd    3      99                    
    adf    3      45                    
    asdf   3      55                    
    3dd    3      78      
   
   
   
c:/>select name,class ,score,max(score) over(partition by class order by score d
esc) from t2;

NAME                      CLASS      SCORE   MAX(SCORE)OVER(PARTITIONBYCLASSORDERBYSCOREDESC)
-------------------- ---------- ----------   ---------------


dss                           1         95     95
                                                
                                                
fda                           1         80     95
                                                
                                                
ffd                           1         78     95
                                                
                                                
gds                           2         92     92
                                                
                                                
cfe                           2         74     92
                                                
                                                
gf                            3         99     99
                                                
                                                
ddd                           3         99     99
                                                
                                                
3dd                           3         78     99
                                                
                                                
asdf                          3         55     99
                                                
                                                
adf                           3         45     99
                                             
已选择10行。

select * from                                                                      
    (                                                                           
    select name,class,s,rank()over(partition by class order by s desc) mm from t2
    )                                                                           
    where mm=1


NAME                      CLASS      SCORE         MM
-------------------- ---------- ---------- ----------
dss                           1         95          1
gds                           2         92          1
ddd                           3         99          1
gf                            3         99          1

注意:
    1.在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果         
    2.rank()和dense_rank()的区别是:
    --rank()是跳跃排序,有两个第二名时接下来就是第四名
    --dense_rank()l是连续排序,有两个第二名时仍然跟着第三名


 Rank() over()的用法
创建一个test表,并插入6条数据。
CREATE TABLE test
(
 a INT,
 b INT,
 c CHAR
)
INSERT INTO test VALUES(1,3,'E');
INSERT INTO test VALUES(2,4,'A');
INSERT INTO test VALUES(3,2,'D');
INSERT INTO test VALUES(3,5,'B');
INSERT INTO test VALUES(4,2,'C');
INSERT INTO test VALUES(2,4,'B');

SELECT * from test
 
a           b           c
----------- ----------- ----
1           3           E
2           4           A
3           2           D
3           5           B
4           2           C
2           4           B

(6 行受影响)

1、整个结果集是一个分组,以a进行排名

SELECT a,b,c,rank () OVER (ORDER BY a) rank FROM test
 

 

a           b           c    rank
----------- ----------- ---- --------------------
1           3           E    1
2           4           A    2
2           4           B    2
3           2           D    4
3           5           B    4
4           2           C    6

(6 行受影响)

2、整个结果集是一个分组,以b进行排名

SELECT a,b,c,rank () OVER (ORDER BY b) rank FROM test
 

 

a           b           c    rank
----------- ----------- ---- --------------------
3           2           D    1
4           2           C    1
1           3           E    3
2           4           A    4
2           4           B    4
3           5           B    6

(6 行受影响)

3、以a,b进行分组,在每个组内以b进行排名。分了5个组,第2行跟第3行是一个组,其他的每行是一个组。
   在第2行与第3行的组内以b排名,并列为1

SELECT a,b,c,rank () OVER (PARTITION BY a,b ORDER BY b) rank FROM test
 

a           b           c    rank
----------- ----------- ---- --------------------
1           3           E    1
2           4           A    1
2           4           B    1
3           2           D    1
3           5           B    1
4           2           C    1

(6 行受影响)

4、以a,b进行分组,在每个组内以c进行排名。分了5个组,第2行跟第3行是一个组,其他的每行是一个组。
   在第2行与第3行的组内以c排名,由于c列一个是A,一个是B,所以Rank分别为1、2。

SELECT a,b,c,rank () OVER (PARTITION BY a,b ORDER BY c) rank FROM test
 

 

a           b           c    rank
----------- ----------- ---- --------------------
1           3           E    1
2           4           A    1
2           4           B    2
3           2           D    1
3           5           B    1
4           2           C    1

(6 行受影响)

总结:1、partition  by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组。

 

 

 


first_value,last_value用法
原始数据:

SQL>select * from a;

NO      NAME SCOTT

-- ---------- ----------

1         LIFEN         86    

2         HONGGE     76     

3         AIKAI         90      

4         LIFEN         73       

5         KUJUHD       69       

6         LIFEN         93       

7         KOU            80 

8        HONGGE       83       

......................

select T.*, first_value(scoot) over (partition by name order by scott) F,last_value(scoot) over (partition by name order by scott) L from a T order by no ;

NO      NAME SCOTT F  L

-- ---------- ----------   -----   -----

1         LIFEN           86     73       86  

2         HONGGE       76     76     76

3         AIKAI           90       90    90

4         LIFEN           73       73    73

5         KUJUHD        69       69    69

6         LIFEN           93       73    93

7         KOU             80      80     80

8        HONGGE       83    76       83

可以看出 能得到姓名相同中的最小分数 却不能得到最大分数 也就是last_value這函數里存在order和partition會得不到預期的結果﹐

可以將order by 放在最后面

找出最高分数 (如果有重复姓名)和最低分数的NO

SQL>SELECT T.*,

  2  first_value(scott) OVER (PARTITION BY NAME) lowest_scott,

  3  last_value(scott) OVER (PARTITION BY NAME) highest_scott

  4  FROM a T

  5  ORDER BY scott;


        NO NAME                      SCOTT          F          L
---------- -------------------- ---------- ---------- ----------
         5 kujuhd                       69         69         69
         4 lifen                           73         93         73
         2 hongge                      76         76         83
         7 kou                            80         80         80
         8 hongge                      83         76         83
         1 lifen                           86         93         73
         3 aikai                          90         90         90
         6 lifen                           93         93         73

已选择8行。

第二种方法 就是

select T.*, first_value(scoot) over (partition by name order by scott) F,

last_value(scoot) over (partition by name order by scott rows between unbounded preceding and unbounded following)  L from a T order by no ;

ps:分析函数分析的时候是在一个分析窗口中进行的,默认的窗口是当前组的第一行到当前行,如果让窗口是整个组,需要指定窗口子句

删除姓名重复的 分数最高的 保留下来

SQL>delete from a where no not in (select first_value(no) over (partition by name order by scott desc ) no  from a );


select deptno,ename,hiredate,count(1) over (partition by deptno order by hiredate nulls
first range 100 preceding) cnt_range,count(1) over(partition by deptno order by hiredate
nulls first rows 2 preceding) cnt_rows from emp order by deptno,hiredate

Oracle分析函数Over() 一、Over()分析函数 说明:聚合函数(如sum()、max()等)可以计算基于组的某种聚合值,但是聚合函数对于某个组只能返回一行记录。若想对于某组返回多行记录,则需要使用分析函数。 1、rank()/dense_rank over(partition by ... order by ...) 说明:over()在什么条件之上;   partition by 按哪个字段划分组;   ... 阅读详情

相关推荐

Oracle的分析函数over()

问题:各市随即抽样5000条数据 聚合函数用group by分组,每个分组返回一个统计值。但不能对每行进行分析,得不到问题的结果。而分析函数采用partition by分组,并且每组每行都可以返回一个统计值。辅助row_number()函数和生成随机数函数,取rownum<=5000的数据。 select * from (select ROW_NUMBER() over(partition by 市代码 order by RAND()) row_num ,t1.* from 表

qq534178181的博客 1267

开窗函数-first_value/last_value

开窗函数用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。可以看到使用last_value函数求每个人最后一个日期,结果并不是想要的。last_value()默认是升序,如果限制了是降序,则等同于first_value()升序。开窗函数的功能本质是聚合,但是相比聚合,开窗函数可以提供的信息更多。3、first_value/last_value函数。1、开窗函数是什么?

yuhui666666的博客 7324

PCIe 6.0实战指南:从链路训练到信号完整性的避坑手册

本文是一份针对PCIe 6.0硬件工程师的实战避坑指南。文章深入解析了从NRZ到PAM4调制带来的信号完整性挑战,详细拆解了链路训练(LTSSM)的复杂过程与调试方法,并提供了从信道仿真到实验室实测(如使用Keysight示波器进行眼图与误码率分析)的完整信号完整性闭环验证策略,帮助工程师解决高速率下的链路不稳定与降速问题。

weixin_29306875的博客 345

first_value和last_value

SQL中分析函数first_value(),last_value,sum() over(partition by…)详解 首先,生成有一张原始表score,s_id表示学生id,c_id表示课程id,s_core表示最后课程的成绩,表结构数据如下所示: 首先,按照课程c_id进行分组,按照成绩s_core进行排序 select * ,row_number() over (partition by c_id order by s_score)rank_showfrom score 1 运行结果:

weixin_48109576的博客 3511

别再只用GWR了!用Python的mgtwr包搞定时空地理加权回归(GTWR)实战

本文详细介绍了如何使用Python的mgtwr包进行时空地理加权回归(GTWR)实战,突破传统GWR模型的局限。通过核心差异解析、实战全流程演示和进阶技巧,帮助读者掌握GTWR在房价波动、污染物扩散等时空数据分析中的应用,提升模型预测精度。

weixin_30899023的博客 349

AU混响终极指南:从‘干声’到‘空间感’,用总音轨和发送技巧打造专业人声

本文详细介绍了如何在Adobe Audition(AU)中使用混响效果器,通过总音轨和发送技巧将‘干声’转化为具有专业‘空间感’的人声。从混响基础原理到总线架构设置,再到精细控制和自动化调整,全面解析了提升音频质量的实用技巧,适用于音乐制作、播客和内容创作。

weixin_30512089的博客 336

oracle中查询最近的一条记录(FIRST_VALUE() OVER(PARTITION BY ORDER BY ))

分组查询,查询最近的一条记录 例: 车辆通行表PASS中: int  id, //主键ID varchar2(50) vehCode //车牌 date time //通行时间   现在要查询车牌为"北京-10001"的车牌的最近一条通行记录 SELECT DISTINCT FIRST_VALUE(a.id) OVER(PARTITION BY A.vehCodeORDER B

天马波波的专栏 7734

手把手带你搞懂电压跌落测试DIP

砖一用4分钟时间手把手带你搞懂电压跌落测试DIP,希望对你有用~

qq_45508321的博客 7925

Hive sql 中 over 总结

原文地址:https://blog.youkuaiyun.com/qq_28289405/article/details/81326767

leveretz的博客 2265

Spring AI Alibaba Agent开发实战:DeepResearch Agent调度系统

智能Agent调度系统通过专业化分工解决传统AI系统的三大痛点:通用模型专业性不足、处理效率低下和资源浪费。系统采用分层架构设计,核心组件包括智能调度器、Agent选择辅助器和集成工具类,实现问题分类、专业Agent匹配和搜索策略优化的全流程自动化。系统特点包括:1)配置驱动的灵活扩展;2)分层回退的容错机制;3)支持工具调用与传统搜索的智能切换。该架构通过策略模式和依赖注入等技术,在保证稳定性的同时显著提升了研究质量和效率,为企业级多Agent协作提供了完整解决方案。(149字)

wjianwei666的专栏 505

《网络安全自学教程》- Windows基线检查加固

Windows安全配置,基线检查标准,基线加固方式

wangyuxiang946的博客 1万+

oracle分析函数技术详解(配上开窗函数over())

一、Oracle分析函数入门 分析函数是什么? 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。            分析函数和聚合函数的不同之处是什么? 普通的聚合函数用group by分组,每个分组返回一个统计值,而分析函数采用partition by分组,并且每组每行

haiross的专栏 6万+

高通蓝牙学习-- 关于Android 蓝牙的介绍

本文详细介绍了AOSP蓝牙架构及高通QCA芯片的蓝牙功能实现方案。主要内容包括:1)AOSP蓝牙架构在不同Android版本中的层次结构,重点分析BTHIDL层和libbt-vendor关键组件;2)QCA芯片的引导流程说明;3)两种获取libbt-vendor源码的方式(Google官方源和国内镜像源);4)针对QCA6595/6696/66x8/67x7芯片的具体实现方案,包括固件文件定义、厂商事件处理和补丁下载流程;5)蓝牙调试方法,包括logcat日志分析、HSU和Ellisys硬件跟踪技术。文章为

专业开发者的博客,一个从事Android WiFi和蓝牙的工程师 496

over()分析函数

这个函数长这个样子:OVER(PARTITION BY… ORDER BY…) 顾名思义,PARTITION 中文是分割的意思,ORDER 是排序的意思,所以翻译一下就是先把一组数据按照制定的字段进行分割成各种组,然后组内按照某个字段排序。 在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where 、group by、 早于order by 的执行。 伪列ROWNUM 常用的分析函数如下所列: - row_number() over(partit

chuxuan0215的博客 1496

第142篇 ROS2安全机制——SROS2和通信加密

本文探讨了机器人系统(特别是基于ROS2)的通信安全问题。文章以医疗机器人面试场景切入,指出产品级系统必须考虑安全机制,而ROS2默认的"信任一切"模式存在严重安全隐患。重点介绍了SROS2解决方案,包括其三大核心能力:身份验证(基于X.509证书)、访问控制(通过XML权限文件)和数据加密(AES-GCM算法)。同时分析了实际应用中的挑战,如性能开销、配置复杂度和生态兼容性问题,并给出了密钥管理、安全审计等实践经验。最后区分了功能安全与信息安全的概念,强调了安全设计的持续性和系统性。

郑伟强dev的专栏 97

VB摄像头监控

该VB模块可对摄像头捕获的画面进行录像等,可在创建的窗口进行控制

oracle分析函数over,oracle 分析函数over

drop table test111;create table test111(name varchar2(10),class number,grade number)select * from test111;insert into test111 values('a',1,23);insert into test111 values('b',213);insert into test111 v...

weixin_29279047的博客 130

SAP B1中文培訓教材_SDK

公司内部培训资料,对开发工具 ,DIAPI, UIAPI,Add-ON有详细解说

Oracle中分析函数Over、partition的用法

一、常用函数结构整理 row_number() over(partition by … order by …) rank() over(partition by … order by …) dense_rank() over(partition by … order by …) count() over(partition by … order by …) max() over(partitio...

weixin_40939076的博客 3411

Devexpress16.2.6破解工具

Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具

VC中sqlite的使用

在VC中对sqlite的使用,实现数据库大批数据的快速插入,读取数据库中的数据在ListCtrl中显示

oracle的分析函数over 及开窗函数

一.分析函数(OVER) 一、Oracle分析函数简介: 在日常的生产环境中,我们接触得比较多的是OLTP系统(即Online Transaction Process),这些系统的特点是具备实时要求,或者至少说对响应的时间多长有一定的要求;其次这些系统的业务逻辑一般比较复杂,可能需要经过多次的运算。比如我们经常接触到的电子商城。 在这些系统之外,还有一种称之为OLAP的系统(即O

taolinsen的专栏 3394

oracle中over()分析函数的用法

本文详细介绍了Oracle中OVER()分析函数的用法。该函数可与SUM()、RANK()等函数结合使用,通过PARTITION BY进行分组统计,ORDER BY实现累计计算。文章以scott用户的emp表为例,演示了基础用法、累计求和、分级排名等场景,并提供了综合实战示例(如ROW_NUMBER()排名和LAG()差值计算)和常见面试题解析。OVER()函数能显著增强SQL的数据分析能力,适用于薪酬分析、销售趋势等多种业务场景,是Oracle数据库中强大的分析工具。

ywl470812087的博客 11万+

oracle within over,Oracle分析函数OVER的用法

Oracle分析函数OVER使用方法举例---创建测试表SQL> desc t_test;名称 是否为空? 类型----------------------------------------- -------- --------------------------T_ID ...

weixin_36087577的博客 211

oracle中over()分析函数_day1

分析函数用于计算基础组的某种聚合值,分析函数对于每个组返回多行,而聚合函数对于每个组只返回一行。 select day,sale,sum(sale) over (order by day asc ) as 连续求和,sum(sale) over() as 总和 from t_temp; Sum()函数后面加了over()每个组返回多行,后面就不用加group by。 为了方便理解,可以简单的把o...

qq_39501609的博客 303

分析函数OVER

准备工作: table:oracle用户scott下的emp表 ; 一. Oracle 从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是   对于每个组返回多行,而聚合函数对于每个组只返回一行。 exp:

JIM110的专栏 1652

oracle中over 函数,oracle分析函数over()

0x00简介over(),oracle分析函数,使用分析函数对数据分析;0x01用法用在select之后,配合分组函数或分析函数分析数据;1.rank 排名查询每个班级的学生成绩及排名;selectclass_name,student_name,score,rank()over(partitionbyclass_nameORDERBYscoredesc)fromstudent_...

weixin_39586683的博客 204
上一篇: V$session表的妙用
下一篇: oracle常用函数
sql_xeppp
博客等级 码龄18年 34粉丝 66原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值