窗口函数 oracle_11g

标签：11g CUS NO TRAN DATE 子句 oracle 窗口 AMT

数据库中的窗口函数也叫分析函数，顾名思义，窗口函数可用于一些复杂的统计分析计算，另外，窗口函数还具有优越的性能表现，可以节约时间和资源，因此窗口函数经常用于数据仓库和大型报表应用中。

窗口函数的结构

窗口函数由四部分组成，分别是分析函数名、分区子句、排序子句和开窗子句，语法结构为：

分析函数名(参数1,参数2 ...) OVER([分区子句] [排序子句] [开窗子句])

开窗子句
[Rows | Range] BETWEEN <Start expr> AND <End expr> 

<Start expr>   -- [UNBOUNDED PRECEDING |CURRENT ROW |n PRECEDING |n FOLLOWING]
<End expr>     -- [UNBOUNDED FOLLOWING |CURRENT ROW |n PRECEDING |n FOLLOWING]

UNBOUNDED PRECEDING:表示从前面的起点
UNBOUNDED FOLLOWING:表示到后面的终点
CURRENT ROW: 当前行
n PRECEDING: 往前n行数据，加上自身行
n FOLLOWING: 往后n行数据，加上自身行

默认：Rows Between UNBOUNDED PRECEDING and CURRENT ROW

语法注解：

分析函数名：
	Oracle中目前有30个左右的分析函数
	mysql8.0之前不支持，之后支持
	
参数：
	依具体函数而定，参数可以是字段名或表达式
	
OVER：
	标识分析函数的关键字，函数名后面跟上OVER表示这是一个窗口函数
	
分区子句：Partition by  可选  默认将所有数据行作为一个单一的大区
	类似聚合函数的group by，数据按Partition by定义的分区列来分区(组)，所有分区列相同的数据行会被分到同一个分区，分区的数据会按分区列进行排序
	
排序子句：order by  可选
	按给定的排序列来对分组的数据行进行排序，每个排序字段后面可以指定升序(ASC)或降序(DESC)。对于存在空值null的排序列，可以使用NULLS FIRST将空值排到最上面或使用NULLS LAST将空值排到最下面
	注：
	排序子句的使用方法跟sql中的order by一样
	开窗函数的order by和sql语句的order by的执行时机
　　　　　　分析及开窗函数是在整个sql查询结束后再进行的, 即sql语句的order by也会影响分析函数的执行结果，有以下两种情况：
　　　　　　　　1) 两者一致，即sql语句中的order by语句与开窗函数的order by一致，则sql语句中的order by先执行,分析函数在分析时就不必再排序
　　　　　　　　2) 两者不一致，即sql语句中的order by语句与开窗函数的order by不一致，则分析及开窗函数先分析排序，sql语句中的order by再最后执行

窗口子句: 
	划定分析函数进行计算时的数据子集，这个数据子集对应的窗口可以是动态的滑动窗口，滑动窗口的上下边界依排序后的分区数据集且通过 [Rows | Range] 配合 <Start expr> / <End expr> 的几种关键字指定，其中Rows通过与当前行数的比较来指定物理窗口范围，Range通过与当前行值的比较来指定逻辑窗口范围。如果不显示指定窗口子句，默认为 Rows Between Unbounded Preceding and Current Row 。注意不是所有分析函数都支持窗口子句。
	注：
	无论是否省略分组子句，都有：
　　　　　　窗口子句(rows)不能单独存在，必须有order by子句时才能出现
　　　　　　相反，有order by子句，可以没有窗口子句(rows)

　　当省略窗口子句时
　　　　　　如果存在order by，则默认的窗口是unbounded preceding and current row，即当前组的第一行到当前行
　　　　　　如果不存在order by，则默认的窗口是unbounded preceding and unbounded following，即整个组

常用的分析函数

函数名	描述	是否支持窗口子句
sum	统计给定分区与窗口范围的数据的和	支持
count	统计给定分区与窗口范围的数据的条数，可以配合distinct一起使用，但要注意有些数据库不支持distinct的使用	支持
avg	统计给定分区与窗口范围的数据的均值	支持
max	统计给定分区与窗口范围的数据的最大值	支持
min	统计给定分区与窗口范围的数据的最小值	支持
lag	访问一个分区或结果集中之前的n行，n可由函数的参数指定	不支持
lead	访问一个分区或结果集中之后的n行，n可由函数的参数指定	不支持
first_value	访问一个分区或结果集中的第一行	支持
last_value	访问一个分区或结果集中的最后一行	支持
nth_value	访问一个分区或结果集中的任意一行	支持
row_number	对行进行排序并为每一行增加一个唯一编号	不支持
rank	将数据行值按照排序后的顺序进行排名，在有并列的情况下排名值将被跳过	不支持
dense_rank	将数据行值按照排序后的顺序进行排名，在有并列的情况下也不跳过排名值	不支持
listagg	将来自多行的列值以给定分隔符转化为一行的列表形式	不支持

窗口函数的使用

--建表
create table test1(
  CUS_NO varchar2(10),    --客户编号
  AGE int,                --年龄
  TRAN_MONTH varchar2(6), --交易月份
  TRAN_DATE date,         --交易日期
  TRAN_AMT numeric(20,2)  --交易金额
);
--插入测试数据
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101028',28,'201910',to_date('20191012', 'YYYYMMDD'),880.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101028',28,'201910',to_date('20191013', 'YYYYMMDD'),69.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101028',28,'201910',to_date('20191014', 'YYYYMMDD'),128.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101028',28,'201910',to_date('20191015', 'YYYYMMDD'),12.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101028',28,'201910',to_date('20191016', 'YYYYMMDD'),99.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101028',28,'201910',to_date('20191018', 'YYYYMMDD'),199.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101028',28,'201910',to_date('20191020', 'YYYYMMDD'),28.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101028',28,'201911',to_date('20191101', 'YYYYMMDD'),39.00);commit;

insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101029',null,'201910',to_date('20191012', 'YYYYMMDD'),33.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101029',null,'201910',to_date('20191013', 'YYYYMMDD'),28.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101029',null,'201910',to_date('20191014', 'YYYYMMDD'),120.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101029',null,'201910',to_date('20191015', 'YYYYMMDD'),230.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101029',null,'201910',to_date('20191016', 'YYYYMMDD'),129.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101029',null,'201911',to_date('20191102', 'YYYYMMDD'),321.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101029',null,'201911',to_date('20191103', 'YYYYMMDD'),25.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101029',null,'201911',to_date('20191104', 'YYYYMMDD'),89.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101029',null,'201911',to_date('20191105', 'YYYYMMDD'),60.00);commit;

insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101030',35,'201910',to_date('20191015', 'YYYYMMDD'),260.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101030',35,'201910',to_date('20191016', 'YYYYMMDD'),320.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101030',35,'201910',to_date('20191016', 'YYYYMMDD'),180.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101030',35,'201910',to_date('20191017', 'YYYYMMDD'),100.00);commit;
insert into test1(CUS_NO,AGE,TRAN_MONTH,TRAN_DATE,TRAN_AMT) values('cus_101030',35,'201910',to_date('20191018', 'YYYYMMDD'),40.00);commit;

分区子句的使用

需求：统计每个客户在保留原来数据行的基础上增加一列统计列（消费总额）

例：

客户编号年龄交易月份交易日期交易金额 消费总额(追加列) 每个月的消费总额(追加列)

-- 统计每个客户的消费总额
SELECT
	CUS_NO 客户编号,
	AGE 年龄,
	TRAN_MONTH 交易月份,
	TRAN_DATE 交易日期,
	TRAN_AMT 交易金额,
	sum( TRAN_AMT ) over ( PARTITION BY CUS_NO ) AS 消费总额, -- 每个客户的消费总额
	sum( TRAN_AMT ) over ( PARTITION BY CUS_NO, TRAN_MONTH ) AS 每个月的消费总额 -- 每个客户每个月的消费总额
FROM
	test1;

分区子句 partition by CUS_NO 将数据集按每个不同的客户编号来分组，然后汇总交易金额即可得到每个客户的消费总额

未指定排序子句会使用分区列(CUS_NO)作为排序字段

未指定窗口子句会使用Rows Between Unbounded Preceding and Current Row

排序子句的使用

需求：统计每个客户逐日累计的消费金额

例：

2019-10-15当日消费金额 = 0+当日

2019-10-16当日消费金额 = 2019-10-15当日消费金额 + 当日

2019-10-17当日消费金额 = 2019-10-16当日消费金额 + 当日

以此类推。

-- 统计每个客户逐日累计的消费金额 
SELECT
CUS_NO 客户编号,
AGE 年龄,
TRAN_MONTH 交易月份,
TRAN_DATE 交易日期,
TRAN_AMT 交易金额,
sum( TRAN_AMT ) over ( PARTITION BY CUS_NO ORDER BY TRAN_DATE ) AS 逐日累计消费金额 
FROM
	test1;

分区子句 partition by CUS_NO 将数据集按每个不同的客户编号来分组

排序子句 order by TRAN_DATE 中，按交易日期正序排序

未指定窗口子句会使用Rows Between Unbounded Preceding and Current Row

窗口子句的使用

需求：统计每个客户每个交易日期及其前后一天的消费总额

例：

日期 2019-10-15、2019-10-16、2019-10-17

2019-10-15 消费总额 = 0 + 2019-10-15 消费总额 + 2019-10-16 消费总额

2019-10-16 消费总额 = 2019-10-15 消费总额 + 2019-10-16 消费总额 + 2019-10-17 消费总额

2019-10-17 消费总额 = 2019-10-16 消费总额 + 2019-10-17 消费总额 + 2019-10-18 消费总额

以此类推。

-- 统计每个客户每个交易日期及其前后一天的消费总额
SELECT
	CUS_NO 客户编号,
	AGE 年龄,
	TRAN_MONTH 交易月份,
	TRAN_DATE 交易日期,
	TRAN_AMT 交易金额,
	-- Range 通过与当前行值的比较来指定逻辑窗口范围    统计每个客户每个交易日期及其前后一天的消费总额  
	sum( TRAN_AMT ) over ( PARTITION BY CUS_NO ORDER BY TRAN_DATE RANGE BETWEEN 1 preceding AND 1 following ) AS range消费总额,
	-- Rows 通过与当前行数的比较指定物理窗口范围    统计每个客户每个交易日期及其前后一条的消费总额
	sum( TRAN_AMT ) over ( PARTITION BY CUS_NO ORDER BY TRAN_DATE rows BETWEEN 1 preceding AND 1 following ) AS rows消费总额 
FROM
	test1;

分区子句 partition by CUS_NO 将数据集按每个不同的客户编号来分组

排序子句 order by TRAN_DATE 中，按交易日期正序排序

窗口子句

range between 1 preceding and 1 following 表示滑动窗口为当前行的日期以及这个日期减去1天和加上1天对应的这个日期范围内的消费金额加总

rows BETWEEN 1 preceding AND 1 following 表示滑动窗口为当前行的日期以及这个日期上面一行的容器和下面一行的日期的消费金额加总

标签：11g,CUS,NO,TRAN,DATE,子句,oracle,窗口,AMT
From： https://www.cnblogs.com/fuqian/p/17277811.html

窗口函数 oracle_11g

窗口函数 oracle_11g

窗口函数的结构

常用的分析函数

窗口函数的使用

分区子句的使用

排序子句的使用

窗口子句的使用

相关文章

赞助商

阅读排行