当前位置：网站首页>Detailed explanation of the use of Oracle's windowing function (2)
Detailed explanation of the use of Oracle's windowing function (2)
2022-08-10 21:03:00 【Our firm sida DBA_WGX】
Oracle 的开窗函数使用详解（二）
目录
- Oracle 的开窗函数使用详解（二）
- 一、创建表并添加数据
- 二、Basic usage of window function
- 三、Define the window range
- 1、Description of relevant keywords of the window clause
- 2、Application example of window range
- （1）统计所有记录
- （2）The first record statistics to the current record（默认）
- （3）Statistics from the current record to the last record
- （4）Count the previous record of the current record to the current row record
- （5）Count the last three records from the current record to the current row record
- （6）Count the previous record of the current record to the last two records of the current record
- （7）统计【than the currently recorded sal 小于或等于200 ,greater than or equal to the currently recorded value 200 的记录】的和
- 四、Usage of other windowing functions
Windowing function is also called analytical function,包含三个分析子句：partition by（分组）、order by（排序）、window（窗口）,使用形式如下：
over(partition by xxx order by yyy rows|range between zzz)
/* 说明： （1）partition by：分组 （2）order by：排序 （3）rows|range：定义窗口（window）范围 */
When querying, you can use three clauses to define the size of the window.The three clauses can either be used alone,也可以组合使用.一共有如下几种形式：
（1）pow（partition by、order by、rows|range）：分组、排序、Define the window range.
（2）po（partition by、order by）：分组,排序,窗口默认为 range between unbounded preceding and current row.
（3）p（partition by）：分组,不排序,没有窗口.
（4）ow（order by、rows|range）：Grouping for the entire query result,排序,Define the window range.
（5）o（order by）：Grouping for the entire query result,排序,窗口默认为 range between unbounded preceding and current row.
（6）over()：Grouping for the entire query result,不排序,没有窗口.
一、创建表并添加数据
/* create table employee as select empno, ename, sal, deptno from emp; */
SQL> select * from employee;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7782 CLARK 2450 10
7788 SCOTT 3000 20
7839 KING 5000 10
7844 TURNER 1500 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 10
14 rows selected.
二、Basic usage of window function
1、over 不带参数
不分区,不排序,Summarize the entire query result.
/* select empno, ename, sal, deptno, sum(sal) over() sum_salary, avg(sal) over() avg_salary, max(sal) over() max_salary, min(sal) over() min_salary, count(sal) over() cnt_salary from employee; */
SQL> select empno, ename, sal, deptno,
sum(sal) over() sum_salary,
avg(sal) over() avg_salary,
max(sal) over() max_salary,
min(sal) over() min_salary,
count(sal) over() cnt_salary
from employee; 2 3 4 5 6 7
EMPNO ENAME SAL DEPTNO SUM_SALARY AVG_SALARY MAX_SALARY MIN_SALARY CNT_SALARY
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 800 20 29025 2073.21429 5000 800 14
7499 ALLEN 1600 30 29025 2073.21429 5000 800 14
7521 WARD 1250 30 29025 2073.21429 5000 800 14
7566 JONES 2975 20 29025 2073.21429 5000 800 14
7654 MARTIN 1250 30 29025 2073.21429 5000 800 14
7698 BLAKE 2850 30 29025 2073.21429 5000 800 14
7782 CLARK 2450 10 29025 2073.21429 5000 800 14
7788 SCOTT 3000 20 29025 2073.21429 5000 800 14
7839 KING 5000 10 29025 2073.21429 5000 800 14
7844 TURNER 1500 30 29025 2073.21429 5000 800 14
7876 ADAMS 1100 20 29025 2073.21429 5000 800 14
7900 JAMES 950 30 29025 2073.21429 5000 800 14
7902 FORD 3000 20 29025 2073.21429 5000 800 14
7934 MILLER 1300 10 29025 2073.21429 5000 800 14
14 rows selected.
2、over(partition by)：分区
Partition by a column,Data summary for each partition.
/* select empno, ename, sal, deptno, sum(sal) over(partition by deptno) sum_salary, avg(sal) over(partition by deptno) avg_salary, max(sal) over(partition by deptno) max_salary, min(sal) over(partition by deptno) min_salary, count(sal) over(partition by deptno) cnt_salary from employee; */
SQL> select empno, ename, sal, deptno,
sum(sal) over(partition by deptno) sum_salary,
avg(sal) over(partition by deptno) avg_salary,
max(sal) over(partition by deptno) max_salary,
min(sal) over(partition by deptno) min_salary,
2 3 4 5 6 count(sal) over(partition by deptno) cnt_salary
from employee; 7
EMPNO ENAME SAL DEPTNO SUM_SALARY AVG_SALARY MAX_SALARY MIN_SALARY CNT_SALARY
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7782 CLARK 2450 10 8750 2916.66667 5000 1300 3
7839 KING 5000 10 8750 2916.66667 5000 1300 3
7934 MILLER 1300 10 8750 2916.66667 5000 1300 3
7566 JONES 2975 20 10875 2175 3000 800 5
7902 FORD 3000 20 10875 2175 3000 800 5
7876 ADAMS 1100 20 10875 2175 3000 800 5
7369 SMITH 800 20 10875 2175 3000 800 5
7788 SCOTT 3000 20 10875 2175 3000 800 5
7521 WARD 1250 30 9400 1566.66667 2850 950 6
7844 TURNER 1500 30 9400 1566.66667 2850 950 6
7499 ALLEN 1600 30 9400 1566.66667 2850 950 6
7900 JAMES 950 30 9400 1566.66667 2850 950 6
7698 BLAKE 2850 30 9400 1566.66667 2850 950 6
7654 MARTIN 1250 30 9400 1566.66667 2850 950 6
14 rows selected.
3、over(order by)：排序
Sort by a column,Summarize data less than or equal to the current value.
/* select empno, ename, sal, deptno, sum(sal) over(order by sal) sum_salary, avg(sal) over(order by sal) avg_salary, max(sal) over(order by sal) max_salary, min(sal) over(order by sal) min_salary, count(sal) over(order by sal) cnt_salary from employee; */
SQL> select empno, ename, sal, deptno,
sum(sal) over(order by sal) sum_salary,
avg(sal) over(order by sal) avg_salary,
max(sal) over(order by sal) max_salary,
min(sal) over(order by sal) min_salary,
count(sal) over(order by sa 2 3 4 5 6 l) cnt_salary
from employee; 7
EMPNO ENAME SAL DEPTNO SUM_SALARY AVG_SALARY MAX_SALARY MIN_SALARY CNT_SALARY
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 800 20 800 800 800 800 1
7900 JAMES 950 30 1750 875 950 800 2
7876 ADAMS 1100 20 2850 950 1100 800 3
7521 WARD 1250 30 5350 1070 1250 800 5
7654 MARTIN 1250 30 5350 1070 1250 800 5
7934 MILLER 1300 10 6650 1108.33333 1300 800 6
7844 TURNER 1500 30 8150 1164.28571 1500 800 7
7499 ALLEN 1600 30 9750 1218.75 1600 800 8
7782 CLARK 2450 10 12200 1355.55556 2450 800 9
7698 BLAKE 2850 30 15050 1505 2850 800 10
7566 JONES 2975 20 18025 1638.63636 2975 800 11
7788 SCOTT 3000 20 24025 1848.07692 3000 800 13
7902 FORD 3000 20 24025 1848.07692 3000 800 13
7839 KING 5000 10 29025 2073.21429 5000 800 14
14 rows selected.
4、over(partition by xx order by yy)：分区、排序
按 xx 列分组,按 yy 列排序,For less than or equal to the current yy Data Aggregation of Values.
/* select empno, ename, sal, deptno, sum(sal) over(partition by deptno order by sal) sum_salary, avg(sal) over(partition by deptno order by sal) avg_salary, max(sal) over(partition by deptno order by sal) max_salary, min(sal) over(partition by deptno order by sal) min_salary, count(sal) over(partition by deptno order by sal) cnt_salary from employee; */
SQL> select empno, ename, sal, deptno,
sum(sal) over(partition by deptno order by sal) sum_salary,
avg(sal) over(partition by deptno order by sal) avg_salary,
max(sal) over(partition by deptno order by sal) max_salary,
min(sal) over( 2 3 4 5 partition by deptno order by sal) min_salary,
count(sal) over(partition by deptno order by sal) cnt_salary
from employee; 6 7
EMPNO ENAME SAL DEPTNO SUM_SALARY AVG_SALARY MAX_SALARY MIN_SALARY CNT_SALARY
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7934 MILLER 1300 10 1300 1300 1300 1300 1
7782 CLARK 2450 10 3750 1875 2450 1300 2
7839 KING 5000 10 8750 2916.66667 5000 1300 3
7369 SMITH 800 20 800 800 800 800 1
7876 ADAMS 1100 20 1900 950 1100 800 2
7566 JONES 2975 20 4875 1625 2975 800 3
7788 SCOTT 3000 20 10875 2175 3000 800 5
7902 FORD 3000 20 10875 2175 3000 800 5
7900 JAMES 950 30 950 950 950 950 1
7654 MARTIN 1250 30 3450 1150 1250 950 3
7521 WARD 1250 30 3450 1150 1250 950 3
7844 TURNER 1500 30 4950 1237.5 1500 950 4
7499 ALLEN 1600 30 6550 1310 1600 950 5
7698 BLAKE 2850 30 9400 1566.66667 2850 950 6
14 rows selected.
三、Define the window range
if no window clause（rows | range）,Then the default window range is from the first row of the current group to the current row.Window clause cannot stand alone,必须有 order by window clause.有 order by 子句,can have no window clause.
当省略窗口子句时：
如果存在 order by,则默认的窗口是：unbounded preceding and current row,That is, the first row of the current group to the current row.
如果不存在 order by,则默认的窗口是：unbounded preceding and unbounded following,i.e. the whole group.
例如：
sum(sal) over(order by sal)
over(order by salary) 意义如下：
（1）Since the grouping clause is omitted,So the scope of the current group is the data rows of the entire table.
（2）in the current group（This is the data row of the entire table）perform sorting in range,即 order by salary.
（3）分析函数 sum(sal) in the current group（This is the data row of the entire table）The window in the range ranges from the first row of the current group to the current row.
查询结果如下：
SQL> select empno, ename, sal, deptno,sum(sal) over(order by sal)
from employee; 2
EMPNO ENAME SAL DEPTNO SUM(SAL)OVER(ORDERBYSAL)
---------- ---------- ---------- ---------- ------------------------
7369 SMITH 800 20 800
7900 JAMES 950 30 1750
7876 ADAMS 1100 20 2850
7521 WARD 1250 30 5350
7654 MARTIN 1250 30 5350
7934 MILLER 1300 10 6650
7844 TURNER 1500 30 8150
7499 ALLEN 1600 30 9750
7782 CLARK 2450 10 12200
7698 BLAKE 2850 30 15050
7566 JONES 2975 20 18025
7788 SCOTT 3000 20 24025
7902 FORD 3000 20 24025
7839 KING 5000 10 29025
14 rows selected.
1、Description of relevant keywords of the window clause
/* rows | range 的含义： rows 是物理窗口,根据 order by 子句排序后,取前 N 行及后 N 行的数据计算（与当前行的值无关,只与排序后的行号相关）. range 是逻辑窗口,是指定当前行对应值的范围取值,行数不固定,只要行值在范围内,对应行都包含在内. */
unbounded：不受控制的,无限的
preceding：表示在...之前
-- 1 preceding：Indicates the previous record of the current record 1 条记录
-- 2 preceding：Indicates the previous record of the current record 2 条记录
-- n preceding：Indicates the previous record of the current record n 条记录
-- unbounded preceding：不受控制的.无限的,若无分组,All the records of the first 1 条记录.If there is a group,it means that after grouping,the first in the group 1 条记录.
following：表示在...之后
-- 1 following：Represents the next record of the current record
-- 2 following：Indicates the last two records of the current record
-- n following：Indicates after the current recordn条记录
-- unbounded following：不受控制的.无限的,若无分组,it means the last record of all records.If there is a group,it means that after grouping,the last record in the group.
-- 例如：
rows between unbounded preceding and unbounded following：所有记录
rows between unbounded preceding and current row：The first record to the current record
rows between current row and unbounded following：The current record to the last record
rows between 1 preceding and current row：The previous record of the current record（rownum-1）record to the current line
rows between current row and 3 following：The current record to the last three records of the current row record
rows between 1 preceding and 2 following：The previous record of the current record（rownum-1）Record to the last two records of the current record
-- The meaning of logical windows is similar to that of physical windows,It's just that the meaning of the numbers is different：rows The numeric value in represents the number of rows,range The value is greater than or equal to or less than or equal to the current value（和 order by Compare the following columns）.
sum(sal) over(order by sal range between unbounded preceding and current row)：The first record to the current record
sum(sal) over(order by sal range between -500 preceding and 500 following)：针对【than the currently recorded sal 小于或等于500 ,The value of the current record in the table is greater than or equal to 500 的记录】求和.
2、Application example of window range
（1）统计所有记录
/* select empno,ename,mgr,sal,deptno, sum(sal) over(order by sal rows between unbounded preceding and unbounded following) sum_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
sum(sal) over(order by sal rows between unbounded preceding and unbounded following) sum_sal
from emp; 2 3
EMPNO ENAME MGR SAL DEPTNO SUM_SAL
---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902 800 20 29025
7900 JAMES 7698 950 30 29025
7876 ADAMS 7788 1100 20 29025
7521 WARD 7698 1250 30 29025
7654 MARTIN 7698 1250 30 29025
7934 MILLER 7782 1300 10 29025
7844 TURNER 7698 1500 30 29025
7499 ALLEN 7698 1600 30 29025
7782 CLARK 7839 2450 10 29025
7698 BLAKE 7839 2850 30 29025
7566 JONES 7839 2975 20 29025
7788 SCOTT 7566 3000 20 29025
7902 FORD 7566 3000 20 29025
7839 KING 5000 10 29025
14 rows selected.
（2）The first record statistics to the current record（默认）
/* select empno,ename,mgr,sal,deptno, sum(sal) over(order by sal rows between unbounded preceding and current row) sum_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
sum(sal) over(order by sal rows between unbounded preceding and current row) sum_sal
from emp; 2 3
EMPNO ENAME MGR SAL DEPTNO SUM_SAL
---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902 800 20 800
7900 JAMES 7698 950 30 1750
7876 ADAMS 7788 1100 20 2850
7521 WARD 7698 1250 30 4100
7654 MARTIN 7698 1250 30 5350
7934 MILLER 7782 1300 10 6650
7844 TURNER 7698 1500 30 8150
7499 ALLEN 7698 1600 30 9750
7782 CLARK 7839 2450 10 12200
7698 BLAKE 7839 2850 30 15050
7566 JONES 7839 2975 20 18025
7788 SCOTT 7566 3000 20 21025
7902 FORD 7566 3000 20 24025
7839 KING 5000 10 29025
14 rows selected.
/* select empno,ename,mgr,sal,deptno, sum(sal) over(order by sal) sum_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
sum(sal) over(order by sal) sum_sal
from emp;
2 3
EMPNO ENAME MGR SAL DEPTNO SUM_SAL
---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902 800 20 800
7900 JAMES 7698 950 30 1750
7876 ADAMS 7788 1100 20 2850
7521 WARD 7698 1250 30 5350
7654 MARTIN 7698 1250 30 5350
7934 MILLER 7782 1300 10 6650
7844 TURNER 7698 1500 30 8150
7499 ALLEN 7698 1600 30 9750
7782 CLARK 7839 2450 10 12200
7698 BLAKE 7839 2850 30 15050
7566 JONES 7839 2975 20 18025
7788 SCOTT 7566 3000 20 24025
7902 FORD 7566 3000 20 24025
7839 KING 5000 10 29025
14 rows selected.
（3）Statistics from the current record to the last record
/* select empno,ename,mgr,sal,deptno, sum(sal) over(order by sal rows between current row and unbounded following) sum_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
sum(sal) over(order by sal rows between current row and unbounded following) sum_sal
from emp;
2 3
EMPNO ENAME MGR SAL DEPTNO SUM_SAL
---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902 800 20 29025
7900 JAMES 7698 950 30 28225
7876 ADAMS 7788 1100 20 27275
7521 WARD 7698 1250 30 26175
7654 MARTIN 7698 1250 30 24925
7934 MILLER 7782 1300 10 23675
7844 TURNER 7698 1500 30 22375
7499 ALLEN 7698 1600 30 20875
7782 CLARK 7839 2450 10 19275
7698 BLAKE 7839 2850 30 16825
7566 JONES 7839 2975 20 13975
7788 SCOTT 7566 3000 20 11000
7902 FORD 7566 3000 20 8000
7839 KING 5000 10 5000
14 rows selected.
（4）Count the previous record of the current record to the current row record
/* select empno,ename,mgr,sal,deptno, sum(sal) over(order by sal rows between 1 preceding and current row) sum_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
sum(sal) over(order by sal rows between 1 preceding and current row) sum_sal
from emp;
2 3
EMPNO ENAME MGR SAL DEPTNO SUM_SAL
---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902 800 20 800
7900 JAMES 7698 950 30 1750
7876 ADAMS 7788 1100 20 2050
7521 WARD 7698 1250 30 2350
7654 MARTIN 7698 1250 30 2500
7934 MILLER 7782 1300 10 2550
7844 TURNER 7698 1500 30 2800
7499 ALLEN 7698 1600 30 3100
7782 CLARK 7839 2450 10 4050
7698 BLAKE 7839 2850 30 5300
7566 JONES 7839 2975 20 5825
7788 SCOTT 7566 3000 20 5975
7902 FORD 7566 3000 20 6000
7839 KING 5000 10 8000
14 rows selected.
（5）Count the last three records from the current record to the current row record
/* select empno,ename,mgr,sal,deptno, sum(sal) over(order by sal rows between current row and 3 following) sum_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
sum(sal) over(order by sal rows between current row and 3 following) sum_sal
from emp; 2 3
EMPNO ENAME MGR SAL DEPTNO SUM_SAL
---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902 800 20 4100
7900 JAMES 7698 950 30 4550
7876 ADAMS 7788 1100 20 4900
7521 WARD 7698 1250 30 5300
7654 MARTIN 7698 1250 30 5650
7934 MILLER 7782 1300 10 6850
7844 TURNER 7698 1500 30 8400
7499 ALLEN 7698 1600 30 9875
7782 CLARK 7839 2450 10 11275
7698 BLAKE 7839 2850 30 11825
7566 JONES 7839 2975 20 13975
7788 SCOTT 7566 3000 20 11000
7902 FORD 7566 3000 20 8000
7839 KING 5000 10 5000
14 rows selected.
（6）Count the previous record of the current record to the last two records of the current record
/* select empno,ename,mgr,sal,deptno, sum(sal) over(order by sal rows between 1 preceding and 2 following) sum_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
sum(sal) over(order by sal rows between 1 preceding and 2 following) sum_sal
from emp; 2 3
EMPNO ENAME MGR SAL DEPTNO SUM_SAL
---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902 800 20 2850
7900 JAMES 7698 950 30 4100
7876 ADAMS 7788 1100 20 4550
7521 WARD 7698 1250 30 4900
7654 MARTIN 7698 1250 30 5300
7934 MILLER 7782 1300 10 5650
7844 TURNER 7698 1500 30 6850
7499 ALLEN 7698 1600 30 8400
7782 CLARK 7839 2450 10 9875
7698 BLAKE 7839 2850 30 11275
7566 JONES 7839 2975 20 11825
7788 SCOTT 7566 3000 20 13975
7902 FORD 7566 3000 20 11000
7839 KING 5000 10 8000
14 rows selected.
（7）统计【than the currently recorded sal 小于或等于200 ,greater than or equal to the currently recorded value 200 的记录】的和
/* select empno,ename,mgr,sal,deptno, sum(sal) over(order by sal range between 200 preceding and 200 following) sum_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
sum(sal) over(order by sal range between 200 preceding and 200 following) sum_sal
from emp;
2 3
EMPNO ENAME MGR SAL DEPTNO SUM_SAL
---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902 800 20 1750
7900 JAMES 7698 950 30 2850
7876 ADAMS 7788 1100 20 5850
7521 WARD 7698 1250 30 4900
7654 MARTIN 7698 1250 30 4900
7934 MILLER 7782 1300 10 6400
7844 TURNER 7698 1500 30 4400
7499 ALLEN 7698 1600 30 3100
7782 CLARK 7839 2450 10 2450
7698 BLAKE 7839 2850 30 11825
7566 JONES 7839 2975 20 11825
7788 SCOTT 7566 3000 20 11825
7902 FORD 7566 3000 20 11825
7839 KING 5000 10 5000
14 rows selected.
四、Usage of other windowing functions
1、ntile(n) over()
将数据等分成n组（不够等分的按顺序添加到每个组内）,This function must add order by,but cannot use the window clause.
例如：
/* -- 共 14 条记录,分 5 组,前 4 组每组 3 条记录,第 5 组 2 条记录 select empno,ename,mgr,sal,deptno, ntile(5) over(order by sal desc) ntile from emp; -- 共 14 组,分 2 组,每组 7 条记录 select empno,ename,mgr,sal,deptno, ntile(2) over(order by sal desc) ntile from emp; */
SQL> select empno,ename,mgr,sal,deptno,
ntile(5) over(order by sal desc) ntile
from emp;
2 3
EMPNO ENAME MGR SAL DEPTNO NTILE
---------- ---------- ---------- ---------- ---------- ----------
7839 KING 5000 10 1
7902 FORD 7566 3000 20 1
7788 SCOTT 7566 3000 20 1
7566 JONES 7839 2975 20 2
7698 BLAKE 7839 2850 30 2
7782 CLARK 7839 2450 10 2
7499 ALLEN 7698 1600 30 3
7844 TURNER 7698 1500 30 3
7934 MILLER 7782 1300 10 3
7521 WARD 7698 1250 30 4
7654 MARTIN 7698 1250 30 4
7876 ADAMS 7788 1100 20 4
7900 JAMES 7698 950 30 5
7369 SMITH 7902 800 20 5
14 rows selected.
SQL> select empno,ename,mgr,sal,deptno,
ntile(2) over(order by sal desc) ntile
from emp; 2 3
EMPNO ENAME MGR SAL DEPTNO NTILE
---------- ---------- ---------- ---------- ---------- ----------
7839 KING 5000 10 1
7902 FORD 7566 3000 20 1
7788 SCOTT 7566 3000 20 1
7566 JONES 7839 2975 20 1
7698 BLAKE 7839 2850 30 1
7782 CLARK 7839 2450 10 1
7499 ALLEN 7698 1600 30 1
7844 TURNER 7698 1500 30 2
7934 MILLER 7782 1300 10 2
7521 WARD 7698 1250 30 2
7654 MARTIN 7698 1250 30 2
7876 ADAMS 7788 1100 20 2
7900 JAMES 7698 950 30 2
7369 SMITH 7902 800 20 2
14 rows selected.
2、lag 和 lead 函数
格式如下：
-- 说明：Both functions must add order by
lag(列名 [ignore nulls], n, 默认值) over()
-- take out the front n 行数据（Benchmarking with current behavior,往前数 n 行记录）
lead(列名 [ignore nulls], n, 默认值) over()
-- Take out behind the first n 行数据（Benchmarking with current behavior,往后数 n 行记录）
例如：
/* -- lag(sal, 1, 0) 和 lead(sal, 1, 0) 中的参数 0 是默认值,如果省略,则填入 null. select empno,ename,mgr,sal,deptno, lag(sal, 1, 0) over(order by sal) lag_sal, lead(sal, 1, 0) over(order by sal) lead_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
lag(sal, 1, 0) over(order by sal) lag_sal,
lead(sal, 1, 0) over(order by sal) lead_sal
from emp;
2 3 4
EMPNO ENAME MGR SAL DEPTNO LAG_SAL LEAD_SAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902 800 20 0 950
7900 JAMES 7698 950 30 800 1100
-- The current record is 950 元,LAG_SAL for the record preceding the current record sal 值（800）,
-- LEAD_SAL for the record following the current record sal 值（1100）
7876 ADAMS 7788 1100 20 950 1250
7521 WARD 7698 1250 30 1100 1250
7654 MARTIN 7698 1250 30 1250 1300
7934 MILLER 7782 1300 10 1250 1500
7844 TURNER 7698 1500 30 1300 1600
7499 ALLEN 7698 1600 30 1500 2450
7782 CLARK 7839 2450 10 1600 2850
7698 BLAKE 7839 2850 30 2450 2975
7566 JONES 7839 2975 20 2850 3000
7788 SCOTT 7566 3000 20 2975 3000
7902 FORD 7566 3000 20 3000 5000
7839 KING 5000 10 3000 0
14 rows selected.
3、first_value() 和 last_value()
格式如下：
-- ignore nulls：Count the most recent non-null value
first_value(列名 [ignore nulls]) over()
-- Take the corresponding first record
last_value(列名 [ignore nulls]) over()
-- Get the corresponding last record
例如：
（1）Find the first and last records in a table
/* select empno,ename,mgr,sal,deptno, first_value(sal) over() first_sal, last_value(sal) over() last_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
first_value(sal) over() first_sal,
last_value(sal) over() last_sal
from emp;
2 3 4
EMPNO ENAME MGR SAL DEPTNO FIRST_SAL LAST_SAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH 7902 800 20 800 1300
7499 ALLEN 7698 1600 30 800 1300
7521 WARD 7698 1250 30 800 1300
7566 JONES 7839 2975 20 800 1300
7654 MARTIN 7698 1250 30 800 1300
7698 BLAKE 7839 2850 30 800 1300
7782 CLARK 7839 2450 10 800 1300
7788 SCOTT 7566 3000 20 800 1300
7839 KING 5000 10 800 1300
7844 TURNER 7698 1500 30 800 1300
7876 ADAMS 7788 1100 20 800 1300
7900 JAMES 7698 950 30 800 1300
7902 FORD 7566 3000 20 800 1300
7934 MILLER 7782 1300 10 800 1300
14 rows selected.
（2）Find the first and last records in each department
/* select empno,ename,mgr,sal,deptno, first_value(sal) over(partition by deptno) first_sal, last_value(sal) over(partition by deptno) last_sal from emp; */
SQL> select empno,ename,mgr,sal,deptno,
first_value(sal) over(partition by deptno) first_sal,
last_value(sal) over(partition by deptno) last_sal
from emp; 2 3 4
EMPNO ENAME MGR SAL DEPTNO FIRST_SAL LAST_SAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
7782 CLARK 7839 2450 10 2450 1300
7839 KING 5000 10 2450 1300
7934 MILLER 7782 1300 10 2450 1300
7566 JONES 7839 2975 20 2975 3000
7902 FORD 7566 3000 20 2975 3000
7876 ADAMS 7788 1100 20 2975 3000
7369 SMITH 7902 800 20 2975 3000
7788 SCOTT 7566 3000 20 2975 3000
7521 WARD 7698 1250 30 1250 1250
7844 TURNER 7698 1500 30 1250 1250
7499 ALLEN 7698 1600 30 1250 1250
7900 JAMES 7698 950 30 1250 1250
7698 BLAKE 7839 2850 30 1250 1250
7654 MARTIN 7698 1250 30 1250 1250
14 rows selected.
边栏推荐
- "POJ 3666" Making the Grade problem solution (two methods)
- CMU博士论文 | 视频多模态学习：探索模型和任务复杂性
- LeetCode questions 1-10
- Rider调试ASP.NET Core时报thread not gc-safe的解决方法
- 这些mysql基础命令、基础知识还记得吗？（面试，学习，复习都可以）一万三千字总结
- 【语义分割】2015-UNet MICCAI
- idea插件 协议 。。 公司申请软件用
- Iridium Ruthenium Alloy/Iridium Oxide Biomimetic Nanozyme | Palladium Nanozyme | GMP-Pd Nanozyme | Gold-Palladium Composite Nanozyme | Ternary Metal Pd-M-Ir Nanozyme |shell nanozyme
- （十二）STM32——NVIC中断优先级管理
- ctfshow-osint
猜你喜欢
echart 特例-多分组X轴
win10 xbox录屏功能不能录声音怎么办
mysql踩坑----case when then用法
日期选择器组件（限制年份 设定仅展示的月份）
Demis Hassabis：AI 的强大，超乎我们的想象
Multifunctional Nanozyme Ag/PANI | Flexible Substrate Nano ZnO Enzyme | Rhodium Sheet Nanozyme | Ag-Rh Alloy Nanoparticle Nanozyme | Iridium Ruthenium Alloy/Iridium Oxide Biomimetic Nanozyme
【CMU博士论文】视频多模态学习：探索模型和任务复杂性，152页pdf
Single-click to cancel the function
leetcode 84.柱状图中最大的矩形 单调栈应用
Future-oriented IT infrastructure management architecture - Unified IaaS
随机推荐
How to submit a PR?【OpenHarmony Growth Plan】【OpenHarmony Open Source Community】
ctfshow-osint
图扑智慧电力可视化大屏，赋能虚拟电厂精准减碳
什么是抽象类？什么时候用？什么是接口？抽象类与接口的区别？
The evolution history of Go programmers
Tf ferritin particles contain cisplatin / oxaliplatin / doxorubicin / methotrexate MTX / paclitaxel PTX and other drugs
@Autowired annotation --required a single bean, but 2 were found causes and solutions
C语言详解系列——关于调试那些事
LeetCode questions 1-10
详叙c中的分支与循环
双 TL431 级联振荡器
导入FontForge生成字体
指针常量和常量指针
PostgreSQL — Installation and Common Commands
机器学习模型验证：被低估的重要一环
将视图模型转换为使用 Hilt 依赖注入
电信保温杯笔记——《统计学习方法（第二版）——李航》第17章 潜在语义分析
二级指针的简单理解
Apache DolphinScheduler 3.0.0 正式版发布！
PostgreSQL — 安装及常用命令