oracle的开窗函数
oracle的开窗函数
开窗函数指的是OVER()，和分析函数配合使⽤。
语法：OVER(PARTITION BY 分组字段 ORDER BY 排序字段 ROWS BETWEEN 排序字段范围值1 AND 排序字段范围值2)
语法说明：开窗函数为分析函数带有的，包含三个分析⼦句：1. 分组（PARTITION BY）。
2. 排序（ORDER BY）。
3. 窗⼝（ROWS）-- 指定范围。
ROWS 有多个范围值：
1. UNBOUNDED PRECEDING ⽆限/不限定 先前⾏。
2. N PRECEDING N个先前⾏（N为1则是1个先前⾏，2则是2个先前⾏，以此类推）。
3. UNBOUNDED FOLLOWING ⽆限/不限定 的跟随⾏。
4. N FOLLOWING N个跟随⾏（N为1则是1个跟随⾏，2则是2个跟随⾏，以此类推）。
5. CURRENT ROW 当前⾏。
⽰例1：SELECT
CCTI.CTR_TYPE_ID,
CCTI.ORDER_NUM,
CCTI.NAME,
WMSYS.WM_CONCAT(CCTI.NAME) OVER
(PARTITION BY CCTI.CTR_TYPE_ID) CTR_TYPE_ITEM_STR
FROM T_CTRG_CTR_TYPE_ITEM CCTI;
结果1：
分析1：
区别于GROUP BY⼦句的只返回分组⾏的结果，开窗函数每⼀⾏都会返回⼀个结果。
⽰例1的写法相当于指定了ROWS范围从不限定先前⾏到不限定跟随⾏（默认）：SELECT
CCTI.CTR_TYPE_ID,
CCTI.ORDER_NUM,
CCTI.NAME,
WMSYS.WM_CONCAT(CCTI.NAME) OVER
(PARTITION BY CCTI.CTR_TYPE_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) CTR_TYPE_ITEM_STR
FROM T_CTRG_CTR_TYPE_ITEM CCTI;
⽰例2：SELECT
CCTI.CTR_TYPE_ID,
CCTI.ORDER_NUM,
CCTI.NAME,
WMSYS.WM_CONCAT(CCTI.NAME) OVER
(PARTITION BY CCTI.CTR_TYPE_ID ORDER BY CCTI.ORDER_NUM) CTR_TYPE_ITEM_STR
FROM T_CTRG_CTR_TYPE_ITEM CCTI;
结果2：
分析2：
加上了ORDER BY 之后，返回结果变成了逐级递增的效果。
写法相当于指定了ROWS范围从不限定先前⾏到当前⾏（加了ORDER BY默认）：SELECT
CCTI.CTR_TYPE_ID,
CCTI.ORDER_NUM,
CCTI.NAME,
WMSYS.WM_CONCAT(CCTI.NAME) OVER
(PARTITION BY CCTI.CTR_TYPE_ID ORDER BY CCTI.ORDER_NUM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CTR_TYPE_ITEM_STR
FROM T_CTRG_CTR_TYPE_ITEM CCTI;
特别说明：如果SQL语句中存在ORDER BY语句（⾮开窗函数中的分析⼦句），会再分析函数分析结束后再执⾏排序，优先级低于分析⼦句。
后话：
因为开窗函数每⼀⾏都会返回⼀个结果，所以要取分组结果的话，可以在语句加上DISTINCT修饰即可返回分组结果（分组结果内有排序）。语句：SELECT DISTINCT
CCTI.CTR_TYPE_ID,
WMSYS.WM_CONCAT(CCTI.NAME) OVER
(PARTITION BY CCTI.CTR_TYPE_ID ORDER BY CCTI.ORDER_NUM ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) CTR_TYPE_ITEM_STR
FROM T_CTRG_CTR_TYPE_ITEM CCTI;结果：
"⼈⽣中最让⼈痛苦的，不在于【得不到】，⽽在于【我本来可以】。"
oracle函数
许多软件公司都理解开发不依赖于特定数据库类型(例如Oracle、SQL Server、DB2)的应用程序的重要性，它可以让客户们选择自己习惯的平台。一般来说，软件开发人员都能够识别出他们的负责数据库维护的客户，和必须使用现有平台和个性化的客户。
关于Oracle和SQL Server之间的区别，已经有很多篇文章从企业的角度和数据库管理员的角度出发描写了两者之间的一般差别。在这篇文章里面，我将会从应用程序的角度向你描述SQL erver 和oracle平台之间的区别，并且将会讨论开发不依赖于数据库环境的应用程序的几种可能的方法。与此同时，我不会再论讨对于应用程序来说，两个平台之间显而易见的区别，例如表的分割和索引。
定义通用接口和语言有很少的几种通用语言和接口可以让应用程序不依赖于数据库，想来也可以以同样的方式应用在关系型数据库上面: ANSI是美国国家标准局定义的，这是一家志愿者成员的组织(用私人基金运转)，他们在有关设备和程序等广泛领域内开发了国家承认的标准。在数据库领域， ANSI定义了编写SQL命令的标准，假设命令可以运行在任何的数据库上，而不需要更改命令的语法。
ODBC是开放数据库连接(ODBC)接口，微软定义的，它可以让应用程序访问数据库管理系统(DBMS)中的数据，使用SQL作为访问数据的标准。 ODBC允许最大的互联性，这意味着一个单个的应用程序可以访问不同的数据库管理系统。然后，应用程序终端用户可以添加开放数据库连接(ODBC)数据库驱动来将应用程序与他们所选择的数据库管理系统链接上。 OLE DB是ODBC的继承者，是一组允许例如基于VB、C++、Access等任何可以连接到类似SQL Server、Oracle、DB2、MySQL等后台的“前台”的软件组件。在许多种情况下，OLE DB组件都比原有的ODBC提供了好得多的性能。
JDBC(Java数据库连接)应用程序接口是Java编程语言和广泛范围的数据库，SQL数据库和其他表列数据源(例如电子表格或者普通文本文件)之间，进行不依赖于数据库的连接的行业标准。JDBD应用程序接口为基于SQL的数据库访问提供了调用级别的应用程序接口。真实世界中的通用接口不幸的是，并不是所有数据库级别的命令都是ANSI，每个数据库平台都有自己的扩展功能。对于ANSI或者通用接口，一般来说都代表着几本功能，因此也可能意味着丧失了性能方面的竞争力。对于小型数据库和小型应用程序来说，要维护对数据库的通用访问是简单的，但是当数据库和/或应用程序变得越来越大，越来越复杂，你就不得不向代码中添加功能。
SQL开窗函数用法
SQL开窗函数⽤法
开窗函数分类：
根据使⽤的⽬的，开窗函数可以分为两类：聚合开窗函数和排序开窗函数。
下⾯主要解析四种常⽤的排序开窗函数:
1、ROW_NUMBER() OVER () ：
对相等的值不进⾏区分，序号连续
2、RANK() OVER ()：
相等的值排名相同，但若有相等的值，则序号1到n不连续。
3、DENSE_RANK() OVER () ：
对相等的值排名相同，但序号从1到n连续。
4、NTILE(n) OVER () .
可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每⼀⾏，排序对应的数字为桶号。如果不能平均分配，则较⼩桶
号的桶分配额外的⾏，并且各个桶中能放的数据条数最多相差1。
还有⼀些常⽤的开窗：
5、max() over(partition by ... order by ...)
6、min() over(partition by ... order by ...)
7、sum() over(partition by ... order by ...)
8、avg() over(partition by ... order by ...)
9、first_value() over(partition by ... order by ...)
10、last_value() over(partition by ... order by ...)
11、lag() over(partition by ... order by ...)
12、lead() over(partition by ... order by ...)
lag ，lead 分别是向前，向后；
lag 和lead 有三个参数，第⼀个参数是列名，第⼆个参数是偏移的offset，第三个参数是 超出记录窗⼝时的默认值）13、count() over(partition by ... order by ...)
开窗函数用法
开窗函数 OVER(PARTITION BY)函数介绍
开窗函数
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1：over后的写法：
over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（partition by deptno order by salary）
2：开窗的窗口范围：
over（order by salary range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。
举例：
--sum(s)over(order by s range between 2 preceding and 2 following) 表示加2或2的范围内的求和
select name,class,s, sum(s)over(order by s range between 2 preceding and 2 following)
mm from t2
adf 3 45 45 --45加2减2即43到47，但是s在这个范围内只有45
asdf 3 55 55
cfe 2 74 74
3dd 3 78 158 --78在76到80范围内有78，80，求和得158
fda 1 80 158
gds 2 92 92
ffd 1 95 190
dss 1 95 190
ddd 3 99 198
gf 3 99 198
over（order by salaryrows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。
oracle常用的开窗函数使用技巧
oracle常用的开窗函数使用技巧
在数据库操作中，常常需要对数据进行分组、排序、过滤、统计等操作，这时候开窗函数就成为了一种非常有用的技巧。Oracle是一种支持开窗函数的数据库，他们可以被用于查询、分析、排序、聚合等操作。下面将为大家介绍几种常见的开窗函数使用技巧。
一、聚合函数实现
在数据库中，聚合函数是非常常见的操作，例如SUM、COUNT、AVG等。在某些情况下，我们需要在查询中同时获得数据的总量或平均值，这时候开窗函数就可以派上用场了。下面是一个求平均值并加上一个平均值窗口的例子：
SELECT name, salary, AVG(salary) OVER () AS
average_salary FROM employee
结果如下所示：
name | salary | average_salary John
Smith | 50000 | 66667 Jane Doe | 75000 |
66667 Harry Zheng| 83333 | 66667 Eva Li |
58000 | 66667
二、分组函数实现 有时候，我们需要按照某些条件来对数据进行分组操作，这就需要使用到分组函数。例如以下查询就需要按照部门将员工数据进行分组：
SELECT department, name, salary, AVG(salary)
OVER (PARTITION BY department) AS average_salary
FROM employee
结果如下所示：
department | name | salary |
average_salary IT | John Smith | 50000 |
51667 IT | Harry Zheng| 83333 | 51667 HR
Oracle数据库分析函数用法
Oracle数据库分析函数⽤法⽬录
1、什么是窗⼝函数？
2、窗⼝函数——开窗
3、⼀些分析函数的使⽤⽅法
4、OVER()参数——分组函数
5、OVER()参数——排序函数
1、什么是窗⼝函数？
窗⼝函数也属于分析函数。Oracle从8.1.6开始提供窗⼝函数，窗⼝函数⽤于计算基于组的某种聚合值，
窗⼝函数指定了分析函数⼯作的数据窗⼝⼤⼩，这个数据窗⼝⼤⼩可能会随着⾏的变化⽽变化。
与聚合函数的不同之处是：对于每个组返回多⾏，⽽聚合函数对于每个组只返回⼀⾏
基本语法: ‹分析函数› over (partition by ‹⽤于分组的列名› order by ‹⽤于排序的列名›)。
语法中的‹分析函数›主要由序列函数（rank、dense_rank和row_number等组成）
与聚合函数（sum、avg、count、max和min等）作为窗⼝函数组成。
从窗⼝函数组成上看，它是group by 和 order by的功能组合，group by分组汇总后改变了表的⾏数，⼀⾏只有⼀个类别，⽽
partiition by则不会减少原表中的⾏数。
恰如窗⼝函数的组成，它同时具有分组和排序的功能，且不减少原表的⾏数。
OVER 关键字表⽰把函数当成窗⼝函数⽽不是聚合函数。SQL 标准允许将所有聚合函数⽤做窗⼝函数，使⽤ OVER 关键字来
区分这两种⽤法。
2、窗⼝函数——开窗
OVER 关键字后的括号中经常添加选项⽤以改变进⾏聚合运算的窗⼝范围。如果 OVER 关键字后的括号中的选项为空，则窗
⼝函数会对结果集中的所有⾏进⾏聚合运算。
分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)
为什么叫开窗呢？
因为在over()括号中的，partition() 函数可以将查询到的数据进⾏单独开⼀个窗⼝处理。譬如，查询每个班级的学⽣的排名情
况，查询每个国家的历年⼈⼝等，诸如此类，都是在查询到的每⼀个班级、每⼀个国家中都开⼀个窗⼝，单独去执⾏命令。
110个oracle函数总结
1. ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2. CHR给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵A
3. CONCAT
连接两个字符串;
SQL> select concat(010-,)||转23 高乾竞电话 from dual;
高乾竞电话
----------------
010-转23
4. INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap(smith) upp from dual;
UPP
-----
Smith 5.INSTR(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1被搜索的字符串
C2希望搜索的字符串
I 搜索的开始位置,默认为1 J 出现的位置,默认为1 SQL> select instr(oracle traning,ra,1,2)
instring from dual;
INSTRING
---------
9
6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from
gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
Oracle开发之窗口函数
Oracle开发之窗⼝函数
⼀、窗⼝函数简介：
到⽬前为⽌，我们所学习的分析函数在计算/统计⼀段时间内的数据时特别有⽤，但是假如计算/统计需要随着遍历记录集的每
⼀条记录⽽进⾏呢？举些例⼦来说：
①列出每⽉的订单总额以及全年的订单总额
②列出每⽉的订单总额以及截⾄到当前⽉的订单总额
③列出上个⽉、当⽉、下⼀⽉的订单总额以及全年的订单总额
④列出每天的营业额及⼀周来的总营业额
⑤列出每天的营业额及⼀周来每天的平均营业额
仔细回顾⼀下前⾯我们介绍到的分析函数，我们会发现这些需求和前⾯有⼀些不同：前⾯我们介绍的分析函数⽤于计算/统计
⼀个明确的阶段/记录集，⽽这⾥有部分需求例如2，需要随着遍历记录集的每⼀条记录的同时进⾏统计。
也即是说：统计不⽌发⽣⼀次，⽽是发⽣多次。统计不⾄发⽣在记录集形成后，⽽是发⽣在记录集形成的过程中。
这就是我们这次要介绍的窗⼝函数的应⽤了。它适⽤于以下⼏个场合：
①通过指定⼀批记录：例如从当前记录开始直⾄某个部分的最后⼀条记录结束 ②通过指定⼀个时间间隔：例如在交易⽇之前的
前30天 ③通过指定⼀个范围值：例如所有占到当前交易量总额5%的记录
⼆、窗⼝函数⽰例－全统计：
下⾯我们以需求：列出每⽉的订单总额以及全年的订单总额为例，来看看窗⼝函数的应⽤。
【1】测试环境：
复制代码 代码如下:SQL> desc orders;
名称 是否为空? 类型
----------------------- -------- ----------------
MONTH NUMBER(2)
TOT_SALES NUMBER
SQL>
【2】测试数据：
复制代码 代码如下:SQL> select * from orders;
MONTH TOT_SALES
---------- ----------
1 610697
2 428676
3 637031
4 541146
5 592935
6 501485
7 606914
8 460520
Oracle分析函数笔记
Oracle分析函数
Oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。Oracle从8.1.6开始提供分析函数。
一、基本语法
oracle分析函数的语法：
function_name(arg1,arg2,...)
over
( )
说明：
1. partition-clause 数据记录集分组
2. order-by-clause 数据记录集排序
3. windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合。有三种开窗方式: range、row、specifying。
二、常用分析函数
1. avg(distinct|all expression) 计算组内平均值，distinct 可去除组内重复数据
select deptno,empno,sal,avg(sal) over (partition by deptno) avg_sal from t;
DEPTNO EMPNO SAL AVG_SAL
---------- ---------- ---------- ----------
10 7782 2450 2916.66667
7839 5000 2916.66667
7934 1300 2916.66667
20 7566 2975 2175
7902 3000 2175
Oracle开发专题之：窗口函数
【原】Oracle开发专题之：窗口函数
目录
=========================================
1.窗口函数简介
2.窗口函数示例-全统计
3.窗口函数进阶-滚动统计(累积/均值)
4.窗口函数进阶-根据时间范围统计
5.窗口函数进阶-first_value/last_value
6.窗口函数进阶-比较相邻记录
一、窗口函数简介：
到目前为止，我们所学习的分析函数在计算/统计一段时间内的数据时特别有用，但是假如计算/统计需要随着遍历记录集的每一条记录而进行呢？举些例子来说：
①列出每月的订单总额以及全年的订单总额
②列出每月的订单总额以及截至到当前月的订单总额
③列出上个月、当月、下一月的订单总额以及全年的订单总额
④列出每天的营业额及一周来的总营业额
⑤列出每天的营业额及一周来每天的平均营业额
仔细回顾一下前面我们介绍到的分析函数，我们会发现这些需求和前面有一些不同：前面我们介绍的分析函数用于计算/统计一个明确的阶段/记录集，而这里有部分需求例如2，需要随着遍历记录集的每一条记录的同时进行统计。
也即是说：统计不止发生一次，而是发生多次。统计不至发生在记录集形成后，而是发生在记录集形成的过程中。
这就是我们这次要介绍的窗口函数的应用了。它适用于以下几个场合：
①通过指定一批记录：例如从当前记录开始直至某个部分的最后一条记录结束
②通过指定一个时间间隔：例如在交易日之前的前30天
③通过指定一个范围值：例如所有占到当前交易量总额5%的记录
二、窗口函数示例－全统计：
下面我们以需求：列出每月的订单总额以及全年的订单总额为例，来看看窗口函数的应用。
【1】测试环境：
SQL> desc orders;
名称 是否为空? 类型
----------------------- -------- ----------------
分析及开窗函数
转自/edgar108/blog/item/e24c7fd66b0817d7a144dfc8.html
我以oracle中的emp 和dept表为例，讲一下开窗函数。
假如，现在有这样的要求：查出所有的员工的名字ename,薪水sal 以及他的薪水占说有员工薪水的比例。
一开始，我们的思路可能是这样：
select ename ,sal ,sal/sum(sal) from emp;
但是这样写是不对的，sum()是一个单行统计函数，只返回一个值，不能和其他字段同时出现。
解决办法就是使用开窗函数over()
select ename ,sal ,sal/sum(sal) over() as percent from emp;
查询结果：
ENAME SAL PERCENT
---------- ---------- ----------
SMITH 800 .027562446
ALLEN 1600 .055124892
WARD 1250 .043066322
JONES 2975 .102497847
MARTIN 1250 .043066322
BLAKE 2850 .098191214
CLARK 2450 .084409991
SCOTT 3000 .103359173
KING 5000 .172265289
TURNER 1500 .051679587
ADAMS 1100 .037898363
ENAME SAL PERCENT
---------- ---------- ----------