Oracle 中Over的用法
随便GOOGLE一把ORACLE分析函数，就能找到很多Oracle 分析函数的使用
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面例子中使用的表来自Oracle自带的HR用户下的表，如果没有安装该用户，可以在SYS用户下运行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建。
除本文内容外，你还可参考：
ROLLUP与CUBE /post/419/29159
分析函数使用例子介绍：/post/419/44634
本文如果未指明，缺省是在HR用户下运行例子。
开窗函数的的理解：
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（order by salary rows between 50 preceding and 150 following）
每行对应的数据窗口是之前50行，之后150行
over（order by salary rows between unbounded preceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following）
主要参考资料：《expert one-on-one》 Tom Kyte 《Oracle9i SQL Reference》第6章
AVG
功能描述：用于计算一个组和数据窗口内表达式的平均值。
SAMPLE：下面的例子中列c_mavg计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来；
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees;
MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG
---------- ------------------------- --------- ---------- ----------
100 Kochhar 21-SEP-89 17000 17000
100 De Haan 13-JAN-93 17000 15000
100 Raphaely 07-DEC-94 11000 11966.6667 100 Kaufling 01-MAY-95 7900 10633.3333
100 Hartstein 17-FEB-96 13000 9633.33333
100 Weiss 18-JUL-96 8000 11666.6667
100 Russell 01-OCT-96 14000 11833.3333
CORR
功能描述：返回一对表达式的相关系数，它是如下的缩写：
COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))
从统计上讲，相关性是变量之间关联的强度，变量之间的关联意味着在某种程度
上一个变量的值可由其它的值进行预测。通过返回一个-1~1之间的一个数, 相关
系数给出了关联的强度，0表示不相关。
SAMPLE：下例返回1998年月销售收入和月单位销售的关系的累积系数（本例在SH用户下运行）
SELECT t.calendar_month_number,
CORR (SUM(s.amount_sold), SUM(s.quantity_sold))
OVER (ORDER BY t.calendar_month_number) as CUM_CORR
FROM sales s, times t
WHERE s.time_id = t.time_id AND calendar_year = 1998
GROUP BY t.calendar_month_number
ORDER BY t.calendar_month_number;
CALENDAR_MONTH_NUMBER CUM_CORR
--------------------- ----------
1
2 1
3 .994309382
4 .852040875
5 .846652204
6 .871250628
7 .910029803
8 .917556399
9 .920154356
10 .86720251
11 .844864765
12 .903542662
COVAR_POP
功能描述：返回一对表达式的总体协方差。
SAMPLE：下例CUM_COVP返回定价和最小产品价格的累积总体协方差
SELECT product_id, supplier_id,
COVAR_POP(list_price, min_price) OVER (ORDER BY product_id, supplier_id) AS CUM_COVP,
COVAR_SAMP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id) AS CUM_COVS
FROM product_information p
WHERE category_id = 29
ORDER BY product_id, supplier_id;
PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS
---------- ----------- ---------- ----------
1774 103088 0
1775 103087 1473.25 2946.5
1794 103096 1702.77778 2554.16667
1825 103093 1926.25 2568.33333
2004 103086 1591.4 1989.25
2005 103086 1512.5 1815
2416 103088 1475.97959 1721.97619
.
.
COVAR_SAMP
功能描述：返回一对表达式的样本协方差
SAMPLE：下例CUM_COVS返回定价和最小产品价格的累积样本协方差
SELECT product_id, supplier_id,
COVAR_POP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id) AS CUM_COVP,
COVAR_SAMP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id) AS CUM_COVS
FROM product_information p
WHERE category_id = 29
ORDER BY product_id, supplier_id;
PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS
---------- ----------- ---------- ----------
1774 103088 0
1775 103087 1473.25 2946.5
1794 103096 1702.77778 2554.16667
1825 103093 1926.25 2568.33333
2004 103086 1591.4 1989.25
2005 103086 1512.5 1815
2416 103088 1475.97959 1721.97619
.
.
COUNT
功能描述：对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。
SAMPLE：下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水
例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行没有，所以count计数值cnt3为2（包括自己当前行）；cnt2值相当于小于等于当前行的SALARY值的所有行数
SELECT last_name, salary, COUNT(*) OVER () AS cnt1,
COUNT(*) OVER (ORDER BY salary) AS cnt2,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING
AND 150 FOLLOWING) AS cnt3 FROM employees;
LAST_NAME SALARY CNT1 CNT2 CNT3
------------------------- ---------- ---------- ---------- ----------
Olson 2100 107 1 3
Markle 2200 107 3 2
Philtanker 2200 107 3 2
Landry 2400 107 5 8
Gee 2400 107 5 8
Colmenares 2500 107 11 10
Patel 2500 107 11 10
CUME_DIST
功能描述：计算一行在组中的相对位置，CUME_DIST总是返回大于0、小于或等于1的数，该数表示该行在N行中的位置。例如，在一个3行的组中，返回的累计分布值为1/3、2/3、3/3
SAMPLE：下例中计算每个工种的员工按薪水排序依次累积出现的分布百分比
SELECT job_id, last_name, salary, CUME_DIST()
range between current row and interval 用法
range between current row and interval 用法
"range between current row and interval" 是 Oracle SQL 中的语法，用于在窗口函数中指定一个范围。它的用法是在窗口函数的 OVER 子句中使用。
具体用法如下：
```
SELECT column1, column2, ..., window_function()
OVER (ORDER BY column1
RANGE BETWEEN current row AND interval)
FROM table_name;
```
其中，column1 到 columnN 是要查询的列名，window_function() 是要应用的窗口函数，table_name 是要查询的表名。
current row 表示当前行，而 interval 是一个数值表达式，用于指定范围的大小。可以是具体的数值，也可以是某个列的值。
通过使用 RANGE BETWEEN current row AND interval，可以在窗口函数中指定一个范围，该范围的起始点是当前行，结束点是当前行加上指定的 interval。
范围可以是具体的行数，也可以是距离当前行的单元格数。具体使用何种方式取决于 interval 的具体取值。
需要注意的是，使用 RANGE BETWEEN current row AND
interval 时，ORDER BY 子句是必需的，用于确定行的顺序。
Oracle分组排序函数
Oracle分组排序函数
项⽬开发中，我们有时会碰到需要分组排序来解决问题的情况：
1、要求取出按field1分组后，并在每组中按照field2排序；
2、亦或更加要求取出1中已经分组排序好的前多少⾏的数据
这⾥通过⼀张表的⽰例和SQL语句阐述下oracle数据库中⽤于分组排序函数的⽤法。
1.row_number() over()
row_number()over(partition by col1 order by col2)表⽰根据col1分组，在分组内部根据col2排序，⽽此函数计算的值就表⽰每组内部排序后的顺序编号
（组内连续的唯⼀的）。
与rownum的区别在于：使⽤rownum进⾏排序的时候是先对结果集加⼊伪劣rownum然后再进⾏排序，⽽此函数在包含排序从句后是先排序再计算⾏
号码。row_number()和rownum差不多，功能更强⼀点（可以在各个分组内从1开始排序）。
2.rank() over()
rank()是跳跃排序，有两个第⼆名时接下来就是第四名（同样是在各个分组内）
3.dense_rank() over()
dense_rank()也是连续排序，有两个第⼆名时仍然跟着第三名。相⽐之下row_number是没有重复值的。
⽰例：
如有表Test，数据如下
SQL代码：
CREATEDATE ACCNO MONEY
2014/6/5 111 200
2014/6/4 111 600
2014/6/5 111 400
2014/6/6 111 300
2014/6/6 222 200
2014/6/5 222 800
2014/6/6 222 500
2014/6/7 222 100
2014/6/6 333 800
2014/6/7 333 500
2014/6/8 333 200
2014/6/9 333 0
⽐如要根据ACCNO分组，并且每组按照CREATEDATE排序，是组内排序，并不是所有的数据统⼀排序，
oracle max over partition by用法
oracle max over partition by用法
全文共四篇示例，供读者参考
第一篇示例：
Oracle数据库是一种关系数据库管理系统，提供了丰富的功能和语法来处理数据。在处理数据的时候，我们经常需要使用分析函数来进行复杂的计算和分析，max over partition by是一种常用的功能之一。本文将介绍max over partition by的用法以及它在实际应用中的作用。
在Oracle数据库中，max over partition by是一种分析函数，它可以在一组数据中查找指定列的最大值，并返回结果。它的语法如下：
```
max(column) over (partition by column_name)
```
column是要查找最大值的列，而column_name则是根据哪个列进行分区。通过在max后面加上over partition by关键字，我们可以在指定的分区内查找最大值。
举个例子来说明max over partition by的用法： 假设有一个销售订单表orders，包含了订单号(order_id)、商品编号(product_id)和销售额(amount)三个字段，我们现在想要查找每个商品的销售额最大值。我们可以使用max over partition by来实现：
```
select order_id, product_id, amount,
max(amount) over (partition by product_id) as
max_amount
from orders
```
在实际应用中，max over partition by有很多用途。我们可以使用它来查找每个员工的最高工资、每个部门的最大利润等等。通过对数据进行分区并利用分析函数，我们可以更方便地对数据进行深入分析和计算。
oracle lagover函数用法
第 1 页 共 3 页 oracle lagover函数用法
一、概述
Oracle数据库中的Lagover函数是一种用于获取滞后值（Lag）和领先值（Lead）的函数，它可以帮助您在查询中获取数据之间的差异和关系。Lagover函数可以根据指定的列或表达式，在给定的窗口区域内返回滞后值和领先值的结果。
二、Lagover函数的语法
Lagover函数的语法如下：
```scss
Lagover(expression, partition_by_clause, order_by_clause,
lead_lag)
```
参数说明：
* `expression`：要比较的列或表达式。
* `partition_by_clause`：可选参数，用于指定根据哪个列或表达式对数据进行分区。
* `order_by_clause`：可选参数，用于指定数据的排序顺序。
* `lead_lag`：指定要返回的滞后值和领先值的数量，可以是正数（表示滞后）或负数（表示领先）。
三、使用示例
下面是一些使用Lagover函数的示例，展示了如何使用它来获取数据之间的差异和关系。
1. 获取某一列的滞后值：
```sql 第 2 页 共 3 页 SELECT lag(column_name) OVER (ORDER BY column_name) AS
lag_value
FROM table_name;
```
这将返回指定列的上一行值，即滞后值。
2. 获取某一列的领先值：
```sql
SELECT lead(column_name) OVER (ORDER BY column_name) AS
lead_value
FROM table_name;
```
这将返回指定列的下-一行值，即领先值。
3. 使用partition_by_clause对数据进行分区：
```sql
SELECT lag(column_name) OVER (PARTITION BY
mysql、sqlserver、oracle获取最后一条数据
mysql、sqlserver、oracle获取最后⼀条数据
在⽇常项⽬中经常会遇到查询第⼀条或者最后⼀条数据的情况，针对不同数据库，我整理了mysql、sqlserver、oracle数据库的获取⽅法。
1、mysql 使⽤limit
select * from table order by col limit index,rows；
表在经过col排序后，取从index+1条数据开始的rows条数据。
select * from table order by col limit rows；
表⽰返还前rows条数据，相当于 limit 0,rows
select * from table order by col limit rows,-1；
表⽰查询第rows+1条数据后的所有数据。
2、oracle 使⽤ row_number()over(partition by col1 order by col2)
select row_number()over(partition by col1 order by col2) rnm from table rnm = 1；
表在经过col1分组后根据col2排序，row_number()over返还排序后的结果
3、sql server top
select top n * from table order by col ;
查询表的前n条数据。
select top n percent from table order by col ;
查询前百分之n条数据。
Oracle之分析函数
Oracle之分析函数
⼀、分析函数
1、分析函数
分析函数是Oracle专门⽤于解决复杂报表统计需求的功能强⼤的函数，它可以在数据中进⾏分组然后计算基于组的某种统计值，并且每
⼀组的每⼀⾏都可以返回⼀个统计值。
2、分析函数和聚合函数的区别
普通的聚合函数⽤group by分组，每个分组返回⼀个统计值，⽽分析函数采⽤partition by分组，并且每组每⾏都可以返回⼀个统计值。
3、分析函数的形式
分析函数带有⼀个开窗函数over()，包含分析⼦句 。
分析⼦句⼜由下⾯三部分组成：
partition by ：分组⼦句，表⽰分析函数的计算范围，不同的组互不相⼲；
ORDER BY： 排序⼦句，表⽰分组后，组内的排序⽅式；
ROWS/RANGE：窗⼝⼦句，是在分组(PARTITION BY)后，组内的⼦分组（也称窗⼝），此时分析函数的计算范围窗⼝，⽽不是PARTITON。窗⼝有两种，ROWS和RANGE；
使⽤形式如下：
OVER(PARTITION BY xxx PORDER BY yyy ROWS BETWEEN rowStart AND rowEnd)
注：窗⼝⼦句在这⾥我只说rows⽅式的窗⼝,range⽅式和滑动窗⼝也不提。
⼆、OVER() 函数
1、sql 查询语句的 order by 和 OVER() 函数中的 ORDER BY 的执⾏顺序
分析函数是在整个sql查询结束后(sql语句中的order by的执⾏⽐较特殊)再进⾏的操作, 也就是说sql语句中的order by也会影响分析函数
的执⾏结果：
[1] 两者⼀致：如果sql语句中的order by满⾜分析函数分析时要求的排序，那么sql语句中的排序将先执⾏,分析函数在分析时就不必再排
序；
[2] 两者不⼀致：如果sql语句中的order by不满⾜分析函数分析时要求的排序，那么sql语句中的排序将最后在分析函数分析结束后执⾏
排序。
oracle先分组后获取每组最大值的该条全部信息
oracle先分组后获取每组最⼤值的该条全部信息
⽤⼀个实例说明：
TEST表
SELECT
a.* FROM ( SELECT ROW_NUMBER () OVER ( PARTITION BY MM ORDER BY DD DESC ) rn, TEST.* FROM TEST ) a WHERE a.rn =1
执⾏结果如下：
另⼀个实例：
主要⽅式是使⽤rank（） over⽅法.
查询思想为：⾸先按照需要条件进⾏分组（PARTITION BY），然后通过order by 对每⼀组数据进⾏排序，每组中的每条数据
会存在⼀个rank（可⾃⼰命名）值，根据分组条件和排序⽅式进⾏组内排序，最后通过每组rank值取数据即可。
以下是⼀个完整的查询语句。
select usco.login_name,usco.employee_number,,usco.paper_name,usco.exam_score from
(select distinct u.login_name,u.employee_number,,pic.paper_name,gs.exam_score,
rank() OVER (PARTITION BY u.login_name, ORDER BY gs.exam_score desc) rank
from got_score gs
leftjoin util_sys_user u on er_id=er_id
leftjoin got_exam_grant eg on er_id=er_id
leftjoin got_paper_info_copy pic on eg.exam_id=pic.exam_id
where( pic.paper_name like'%市场培训知识考试%')
Oracle分析函数sumover介绍
Oracle分析函数sumover介绍
其中，sum over函数是一种常用的分析函数，它用于对指定列进行求和计算，并返回每一行的累计总和。以下是sum over函数的基本语法：
```
SUM(expression) OVER (PARTITION BY col1 [, col2, ...] ORDER
BY col3 [, col4, ...] [ROWS ])
```
其中，expression是要进行求和的列或表达式，col1、col2等是用于分组的列，col3、col4等是用于排序的列，frame specification是用于定义计算总和的范围。
sum over函数的作用可以通过一个简单的示例来说明。假设我们有一个包含销售订单的表，其中包含订单号、产品名称和销售量等列。我们想要计算每个产品的累计销售量，可以使用sum over函数来实现：
```sql
SELECT order_id, product_name, sales_quantity,
SUM(sales_quantity) OVER (PARTITION BY product_name ORDER BY
order_id) AS cumulative_sales
FROM sales_orders;
```
在上述示例中，我们使用了PARTITION BY子句来按照产品名称进行分组，然后使用ORDER BY子句按照订单号进行排序。通过在SUM函数中使用over子句，我们可以计算每个产品的累计销售量，并将结果作为新的列返回。
除了基本的用法之外，sum over函数还可以与其他函数组合使用，进一步扩展其功能。例如，我们可以使用sum over函数来计算百分比：
```sql
SELECT order_id, product_name, sales_quantity,
sales_quantity / SUM(sales_quantity) OVER (PARTITION BY
oracle中查询多个字段并根据部分字段进行分组去重
oracle中查询多个字段并根据部分字段进⾏分组去重
说到分组和去重⼤家率先想到的肯定是group by和distinct，
1.distinct对去重数据是要根据所有要查询的字段去重，不能对查询结果部分去重。
例如:
select name ,age ,sex from user where sex = "男"；
要是只根据name和age去重，这⾥⽆法使⽤distinct关键字了。
2.group by ，可以在mysql中进⾏分组查询
select name ,age ,sex from user where sex = "男" group by name,age；
但是在Oracle数据库中该sql语句是⽆法正常执⾏的，会报如下错误
意思是在Oracle中，group by后的字段需要与select中查询的字段需要⼀⼀对应（函数除外）；
3.使⽤over()分析函数
⾸先看原始sql
SELECT t3.*
FROM (
SELECT t1.cateid, t1.product_id, er_type, t2.expire_time
FROM (
SELECT cfg.cateid, cfg.product_id, er_type
FROM xshe_product_cfg cfg
WHERE cfg.product_id IN (1080005002, 1100000001, 1100000002)
) t1
LEFT JOIN (
SELECT *
FROM xshe_stock
WHERE status = '04'
AND expire_time >= sysdate
) t2
ON t1.cateid = t2.cateid
) t3
得到的数据结果集
我们想根据cateid和product_id查询出有效期离得最近的⼀条记录，这⾥把重复数据都查询出来了
这⾥我们使⽤row_number() over()函数进⾏去重
over在sql中的用法
在SQL中，"OVER"是⼀个⽤于在查询结果之上执⾏窗⼝函数的关键字。窗⼝函数计算结果
根据指定的窗⼝范围，⽽不是基于整个结果集。它通常与"PARTITION BY"、"ORDER BY"和
"ROWS RANGE"（或"ROWS BETWEEN"）⼦句⼀起使⽤。
以下是"OVER"关键字和相关⼦句的详细分析：
1. PARTITION BY⼦句：它⽤于将结果集分割成多个分区，然后在每个分区上执⾏窗⼝函数。
它类似于GROUP BY⼦句，但不会对结果进⾏分组，⽽是将数据划分为⾮重叠的⼦集。例
如，如果你想对每个部⻔的员⼯按照⼯资进⾏排名，可以使⽤"PARTITION BY department_id"。
2. ORDER BY⼦句：它定义了窗⼝函数内部排序的⽅式。它可以根据⼀个或多个列指定排序
顺序。窗⼝函数将按照指定的顺序计算结果。例如，如果你想在每个部⻔内按照⼯资降序排
列员⼯，可以使⽤"ORDER BY salary DESC"。
3. ROWS RANGE⼦句（或ROWS BETWEEN⼦句）：它定义了窗⼝函数作⽤的⾏范围。它指
定了窗⼝函数计算的⾏的集合。例如，"ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW"表示窗⼝函数计算当前⾏和该分区的开始⾏之间的所有⾏。
使⽤"OVER"关键字和相关⼦句的语法如下所示：
SELECT column_name1, column_name2, ..., window_function()
OVER (PARTITION BY partition_column1, partition_column2, ...
ORDER BY order_column1 [ASC|DESC]
ROWS BETWEEN start_row AND end_row)
FROM table_name;
这个语法中，"window_function()"代表要执⾏的窗⼝函数，可以是SUM、AVG、COUNT、