Oracle学习笔记--range/rows

最新推荐文章于 2024-08-02 19:56:33 发布
转载 最新推荐文章于 2024-08-02 19:56:33 发布 · 837 阅读 · 2
文章标签：

#oracle #sql #360 #null

本文详细介绍了SQL中的Range和Rows窗口函数用法，通过实例展示了如何使用这些函数进行数据聚合及计算，特别关注了如何指定前后数据范围来实现不同的业务需求。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

Range表示数值范围，Rows表示行范围。
Range的语法：
over（order by xx range between n preceding and n following）
Range between必须为聚合函数/开窗函数服务，而且必须针对数值字段，因为表示的是一个数值范围，这个很容易和行范围rows混淆

SQL> select a.loan_typ, a.loan_no, a.deal_orig_prs_amt,
  2  sum(a.deal_orig_prs_amt) over (order by a.deal_orig_prs_amt range between 1 preceding and 1 following)
  3  from loan_dealer_info a;

LOAN_TYP LOAN_NO              DEAL_ORIG_PRS_AMT SUM(A.DEAL_ORIG_PRS_AMT)OVER(O
-------- -------------------- ----------------- ------------------------------
11383    2100001001                        0.00                              0
11221    2100003801                        0.00                              0
11350    2100001901                        0.00                              0
11388    2100005204                        0.00                              0
11121    2100005601                        0.00                              0
11388    2100005201                        0.00                              0
11388    2100004401                        0.00                              0
11388    2100005202                        0.00                              0
11121    2100016301                    10000.00                          80000
11122    2100014501                    10000.00                          80000
11151    2100013601                    10000.00                          80000
11121    2100012101                    10000.00                          80000
11121    2100013901                    10000.00                          80000
11222    2100007801                    10000.00                          80000
11202    2100002001                    10000.00                          80000
11101    210000032101                  10000.00                          80000
11101    2100000131                    15000.00                          30001
11388    2100042401                    15001.00                          30001
11121    2100020401                    20000.00                          60000
11141    2100014501                    20000.00                          60000

LOAN_TYP LOAN_NO              DEAL_ORIG_PRS_AMT SUM(A.DEAL_ORIG_PRS_AMT)OVER(O
-------- -------------------- ----------------- ------------------------------
11102    2100006401                    20000.00                          60000
11121    2100021201                    30000.00                          60000
11223    2100001801                    30000.00                          60000
11221    2100002001                    34567.00                          34567
11388    2100040801                    50000.00                         250000
11141    2100012901                    50000.00                         250000
11201    2100016301                    50000.00                         250000
11151    2100002901                    50000.00                         250000
11102    2100002201                    50000.00                         250000
11114    2100001901                   100000.00                         400001
11151    2100004501                   100000.00                         400001
11226    2100001201                   100000.00                         400001
11202    2100005401                   100001.00                         400001
11152    2100004301                   150000.00                         150000
11101    2100000214                   200000.00                         400000
11151    2100001101                   200000.00                         400000
11121    2100004801                   500000.00                         500000
11202    2100013701                            
11222    2100003601                            
11162    2100009201                            
11123    2100008601 
（后面省略）

142 rows selected
（原始表的记录数）

这个例子可以看到，这个查询会先按照DEAL_ORIG_PRS_AMT排序， 然后计算新字段，新字段是全表中所有行（包括本行）的 >= 本行的DEAL_ORIG_PRS_AMT - 1 AND <= 本行的DEAL_ORIG_PRS_AMT + 1 的DEAL_ORIG_PRS_AMT字段的合计，比如loan_no为2100016301的这行，DEAL_ORIG_PRS_AMT值为10000，全表中符合 >= 10000-1 AND <= 10000+1的为8条10000,所以新字段值为80000.
看loan_no 为2100000131和2100042401这2条记录，符合条件的刚好是这2行的值，所以新字段都是30001。
显然这个例子里设置范围为1显得很奇怪。

前后范围必须设置，如果不想要前面或后面的范围，可以把偏移量设为0，但不能为负数。 如果遇到null值，显然结果也是null。


如果前后的范围想要不设边界，即全部都要，可以用unbounded代替范围值。
但是一旦设置了边界，就会包含边界。

SQL> select a.loan_typ, a.loan_no, a.deal_orig_prs_amt,
  2  sum(a.deal_orig_prs_amt) over (order by a.deal_orig_prs_amt range between unbounded preceding and 10000 following)
  3  from loan_dealer_info a;

LOAN_TYP LOAN_NO              DEAL_ORIG_PRS_AMT SUM(A.DEAL_ORIG_PRS_AMT)OVER(O
-------- -------------------- ----------------- ------------------------------
11383    2100001001                        0.00                          80000
11221    2100003801                        0.00                          80000
11350    2100001901                        0.00                          80000
11388    2100005204                        0.00                          80000
11121    2100005601                        0.00                          80000
11388    2100005201                        0.00                          80000
11388    2100004401                        0.00                          80000
11388    2100005202                        0.00                          80000
11121    2100016301                    10000.00                         170001
11122    2100014501                    10000.00                         170001
11151    2100013601                    10000.00                         170001
11121    2100012101                    10000.00                         170001
11121    2100013901                    10000.00                         170001
11222    2100007801                    10000.00                         170001
11202    2100002001                    10000.00                         170001
11101    210000032101                  10000.00                         170001
11101    2100000131                    15000.00                         170001
11388    2100042401                    15001.00                         170001
11121    2100020401                    20000.00                         230001
11141    2100014501                    20000.00                         230001

Rows的语法：

over（order by xx rows between x preceding and y following）
每行对应的数据窗口是之前x行，之后y行，共包括x+1+y行

SQL> select a.loan_typ, a.loan_no, a.deal_orig_prs_amt,
  2  sum(a.deal_orig_prs_amt) over (order by a.deal_orig_prs_amt rows between 1 preceding and 2 following)
  3  from loan_dealer_info a;

LOAN_TYP LOAN_NO              DEAL_ORIG_PRS_AMT SUM(A.DEAL_ORIG_PRS_AMT)OVER(O
-------- -------------------- ----------------- ------------------------------
11383    2100001001                        0.00                              0
11221    2100003801                        0.00                              0
11350    2100001901                        0.00                              0
11388    2100005204                        0.00                              0
11121    2100005601                        0.00                              0
11388    2100005201                        0.00                              0
11388    2100004401                        0.00                          10000
11388    2100005202                        0.00                          20000
11121    2100016301                    10000.00                          30000
11122    2100014501                    10000.00                          40000
11151    2100013601                    10000.00                          40000
11121    2100012101                    10000.00                          40000
11121    2100013901                    10000.00                          40000
11222    2100007801                    10000.00                          40000
11202    2100002001                    10000.00                          45000
11101    210000032101                  10000.00                          50001
11101    2100000131                    15000.00                          60001
11388    2100042401                    15001.00                          70001
11121    2100020401                    20000.00                          75001
11141    2100014501                    20000.00                          90000
（后面省略）

142 rows selected
（原始表的记录数）

新字段为从前一条记录开始、到后2条记录为止（即包括4条记录）的DEAL_ORIG_PRS_AMT值的统计。

如果前后的范围想要不设边界，即全部都要，可以用unbounded代替范围值。

SQL> select a.loan_typ, a.loan_no, a.deal_orig_prs_amt,
  2  sum(a.deal_orig_prs_amt) over (order by a.deal_orig_prs_amt rows between unbounded preceding and 2 following)
  3  from loan_dealer_info a;

LOAN_TYP LOAN_NO              DEAL_ORIG_PRS_AMT SUM(A.DEAL_ORIG_PRS_AMT)OVER(O
-------- -------------------- ----------------- ------------------------------
11383    2100001001                        0.00                              0
11221    2100003801                        0.00                              0
11350    2100001901                        0.00                              0
11388    2100005204                        0.00                              0
11121    2100005601                        0.00                              0
11388    2100005201                        0.00                              0
11388    2100004401                        0.00                          10000
11388    2100005202                        0.00                          20000
11121    2100016301                    10000.00                          30000
11122    2100014501                    10000.00                          40000
11151    2100013601                    10000.00                          50000
11121    2100012101                    10000.00                          60000
11121    2100013901                    10000.00                          70000
11222    2100007801                    10000.00                          80000
11202    2100002001                    10000.00                          95000
11101    210000032101                  10000.00                         110001
11101    2100000131                    15000.00                         130001
11388    2100042401                    15001.00                         150001
11121    2100020401                    20000.00                         170001
11141    2100014501                    20000.00                         200001


0 preceding和0 following是和current row等价的。

混世浮萍
关注
Doris 学习 Queries Acceleration -Performance Tuning Overview学习笔记
weixin_40455124的博客
01-10 192
Doris 学习 Queries Acceleration -Performance Tuning Overview学习笔记。
26 Oracle深度学习笔记——SQL基线
ugghhj的博客
11-08 276
26 Oracle深度学习笔记——SQL基线
ORACLE之 分析函数（一）
東風￠芝彩™
02-01 768
ORACLE 分析函数（一） 定义 分析函数通过将多行分组后，在计算这些分组的值。它们与聚合函数不同之处 在于能够对每一分组返回多行值；而聚合函数对于每个组只返回一行。 分析函数根据 analytic_clause（分析子句）将行分组，一个分组称为一个窗口。每一行都对应有一个在行上滑动的窗口，该窗口确定当行的计算范围。窗口大小可以用多个物理进行度量，也可以使用逻辑区间进行度量，比如时间。
rows和range的区别--窗口函数
最新发布
weixin_58468790的博客
08-02 889
rows和range的区别--窗口函数 快动手试试吧~
oracle rows range keep 的使用
rudy gao
05-15 997
1. rows --按salary排序，计算当前行到下面两行的salary累加值，红色部分为例，可以看到2340等于3个salary的值，物理行范围，一眼看就看出来了 SQL> select id,last_name,salary,dept_id,   2  sum(salary) over(order by salary   3   rows between current row an
Oracle 执行计划了的rows概念
weixin_30553837的博客
06-05 477
alter session set statistics_level=all; select t1.* from t1,t2 where t1.id=t2.id and t1.id<3; select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); SQL> select * from...
oracle分析函数系列之sum() rows/range between ... preceding and ... following,上下范围内求值
热门推荐
rfb0204421的专栏
06-19 2万+
Oracle为这种情况提供了一个子句：rows between ... preceding and ... following。从字面上猜测它的意思是：在XXX之前和XXX之后的所有记录，实际情况如何让我们通过示例来验证： SQL> select month,   2         sum(tot_sales) month_sales,   3         sum(sum(tot_sa
oracle中rows和range的理解
weixin_30376083的博客
10-11 236
select deptno,empno,ename,sal,last_value(sal) over(partition by deptno order by sal ROWS BETWEEN unbounded preceding AND current row ) max_sal,last_value(sal) over(partition by deptno order by sal ) ...
oracle函数 rows,Oracle自定义函数——f_henry_ConcatRowsByColumn
weixin_34947914的博客
04-06 195
Create Table henry_test (a varchar2(10),b int);Insert Into henry_test values ('aa',1);Insert Into henry_test values ('bb',1);Insert Into henry_test values ('cc',1);Insert Into henry_test values ('dd',...
数据库学习笔记之Oracle（自总结带目录）
兰海泽的博客
09-03 842
目录 Oracle要点 2 第01讲：Oracle基础 2 1.1基本概念： 2 第02讲：oracle安装 2 2.1oracle安装 2 2.2登录数据库 4 2.3查询库表信息 5 第03讲：Oracle体系结构 5 3.1数据存储结构 5 3.2内存体系结构 6 3.3oracle库开启顺序 7 第04讲：表格管理 8 ...
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串
05-06
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，它产生于距今五十年前。简单来说是本身可视...
A002-185-2537-翁格婉（个人期末作业）
qq_33680482的博客
01-02 3739
Excel查找结合项目主题说明1.作业查词说明1.1第一次查词1.1.1Requirements baseline（需求基线）1.1.2Enterprise Architect（企业架构师）1.1.3Unified Modeling Language（ 统一建模语言）1.1.3Unified Modeling Language（ 统一建模语言）1.1.5Asynchronous Processes（异步进程）1.1.6association class（关联类）1.1.7behavioral model a
oracle 每日一题-分析函数的RANGE/ROW窗口
zhq200902的博客
02-15 2580
作者：        Kim Berg Hansen   运行环境：SQLPLUS, SERVEROUTPUT已打开 我有一张表，登记着我在哪些日期有手机漫游（漫游的意思是我在国外）。我还有一个视图，生成了特定期间的所有日期： create table plch_roaming (    roam_date   date primary key ) / inser
oracle range rows,[20180917]关于分析函数的range与rows的区别.txt
weixin_30851637的博客
04-04 296
[20180917]关于分析函数的range与rows的区别.txt--//这几天看文档,主要想了解学习分析函数这方面内容.--//遇到一个问题,P99.SCOTT@test01p> @ ver1PORT_STRING VERSION BANNER...
oracle中分析函数range值范围,Oracle 分析函数
weixin_42118701的博客
04-14 966
--每一个值占总数的百分比SELECT x, y, z,round(z/sum(z) over()*100,2)||'%' propn , sum(z) over() sum FROM t1;--每一个值占分组的百分比SELECT x, y, z,round(z/sum(z) over(partition by x)*100,2)||'%' propn , sum(z) over(partitio...
oracle中rows,执行计划中的rows的含义？
weixin_34577501的博客
04-09 1393
有如下的执行计划，计划中的每一步骤都有rows，该rows意思不明白，有人说是该步骤处理的数据行数，但在我的计划中，最后一个步骤执行622行了，按道理说最终出来的结果只有622行,但sql文最终出来了9966行数据，谁能解释一下，谢谢！9966行が選択されました。経過: 00:01:37.24実行計画-------------------------------------------------...
窗口函数rows between 、range between的区分
nuhao的博客
01-30 1838
移动窗口，顾名思义，“窗口”（也就是操作数据的范围）不是固定的，而是随着设定条件逐行移动的。在over后面的子句中，使用rows加“范围关键字”可以设置移动窗口，语法如下：其中，“范围起始行”和“范围终止行”使用特定关键字表示，常用的特定关键字如下。• n preceding：当前行的前n行。• n following：当前行的后n行。• current row：当前行。• unbounded preceding：第1行。• unbounded following：最后1行。
oracle函数range,Oracle中range的使用
weixin_30739265的博客
04-07 1779
-- 需求：求出下表的一个小时内id个数的最大值和最小值。SQL> WITH t AS (2 SELECT 1 "id",TO_DATE('2011-04-27 14:05:12','yyyy-mm-dd hh24:mi:ss') c_time FROM DUAL UNION ALL3 SELECT 2,TO_DATE('2011-04-27 15:10:42','yyyy-mm-dd ...
Oracle之开窗函数 - 详解 AND Rows
qq_33879355的博客
11-05 3204
解析： 三个位置：最前边一行，最后边一行，当前行 位置永远是自上而下的 drop table emp purge; CREATE TABLE emp (   emp_id    NUMBER(6),   ename  VARCHAR2(45),   dept_id   NUMBER(4),   hire_date DATE,   sal    NUMBER(8,
oracle between
04-01
The BETWEEN operator in Oracle is used to check if a value lies within a specified range. It is commonly used in SQL queries to filter data based on a specific condition. The syntax for the BETWEEN operator in Oracle is as follows: SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2; In this example, "column_name" refers to the column in the table that we want to filter, "table_name" is the name of the table we are querying, and "value1" and "value2" are the lower and upper bounds of the range we want to filter. The operator checks whether the column value falls between the two given values and returns the rows that meet this condition.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值