Oracle PL/SQL/Analytical Functions/Aggregrate Analytical
Содержание
- 1 analytic order-by clause
- 2 A seven-day MAX and MIN on Tuesdays
- 3 A seven-day MAX and MIN on Tuesdays: using TO_CHAR function
- 4 average 5 before, after
- 5 avg over and avg over order by
- 6 avg over order by range
- 7 avg over range between
- 8 Avg over ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
- 9 count(*) over partition by, order by, range unbounded preceding
- 10 count(*) over partition, order by, range unbounded preceding
- 11 Displaying a Running Total Using SUM as an Analytical Function
- 12 Employee salary report with avg salary for the previous 12 months
- 13 Is our average total_order_price increasing or decreasing?
- 14 Reporting on a Sum
- 15 Row-ordering is done first and then the moving average
- 16 Sum over order by
- 17 Sum over partition by and order by
- 18 SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
- 19 To see how the moving average window can expand
- 20 Uses dates and logical offset of seven days preceding
- 21 Use the COUNT aggregate analytical function to show how many rows are included in each window
analytic order-by clause
<source lang="sql">
SQL> SQL> SQL> create table employee(
2 emp_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,salary number(3) 14 ,birthdate date 15 ,hiredate date 16 ,title varchar2(20) 17 ,dept_no integer 18 ,mgr integer 19 ,region number 20 ,division number 21 ,total_sales number 22 );
Table created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created. SQL> SQL> select lastname , dept_no, salary,
2 sum(salary) over (partition by dept_no 3 order by lastname) dept_total 4 from employee 5 order by dept_no, lastname;
LASTNAME DEPT_NO SALARY DEPT_TOTAL
---------- ---------- ----------
Bush 1 22 22 Wash 1 12 34 Will 1 25 59 Anderson 2 4 4 Last 2 8 12 Pete 2 9 21 Bar 3 12 12 Horry 3 13 25 Roke 3 10 35 9 rows selected. SQL> SQL> drop table employee; Table dropped. SQL> SQL> --
</source>
A seven-day MAX and MIN on Tuesdays
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> -- A seven-day MAX and MIN on Tuesdays: SQL> SQL> SELECT "Tuesday, "||TO_CHAR(x.start_date,"Month dd,yyyy") "Tuesdays",
2 x.minp "Minimum salary", x.maxp "Maximum salary" 3 FROM 4 (SELECT i.start_date, i.salary, 5 MIN(i.salary) OVER( ORDER BY i.start_date 6 RANGE BETWEEN INTERVAL "7" day PRECEDING 7 AND CURRENT ROW) minp, 8 MAX(i.salary) OVER( ORDER BY i.start_date 9 RANGE BETWEEN INTERVAL "7" day PRECEDING 10 AND CURRENT ROW) maxp 11 FROM employee i 12 ORDER BY i.start_date) x 13 WHERE x.start_date in 14 (SELECT z.start_date -- , NEXT_DAY(z.start_date-1,"Tuesday") 15 FROM employee z 16 WHERE z.start_date = NEXT_DAY(z.start_date-1,"Tuesday"));
Tuesdays Minimum salary Maximum salary
-------------- --------------
Tuesday, December 12,1978 6544.78 6544.78 Tuesday, September 17,1996 1232.78 1232.78 SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
A seven-day MAX and MIN on Tuesdays: using TO_CHAR function
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> -- Another way to get Tuesdays would be to use the TO_CHAR: SQL> SQL> SQL> SELECT "Tuesday, "||TO_CHAR(x.start_date,"Month dd,yyyy") "Tuesdays",
2 x.minp "Minimum Salary", x.maxp "Maximum Salary" 3 FROM 4 (SELECT i.start_date, i.salary, 5 MIN(i.salary) OVER( ORDER BY i.start_date 6 RANGE BETWEEN INTERVAL "7" day PRECEDING 7 AND CURRENT ROW) minp, 8 MAX(i.salary) OVER( ORDER BY i.start_date 9 RANGE BETWEEN INTERVAL "7" day PRECEDING 10 AND CURRENT ROW) maxp 11 FROM employee i 12 ORDER BY i.start_date) x 13 WHERE to_char(x.start_date,"d") = 3;
Tuesdays Minimum Salary Maximum Salary
-------------- --------------
Tuesday, July 30,1987 4322.78 4322.78 Tuesday, July 25,1996 1234.56 1234.56 SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
average 5 before, after
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20 7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30 7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00 7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20 7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30 7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00 7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20 7839.00 KING PRESIDENT 17-NOV-81 5000.00 10 7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30 7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20 7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20 7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected. SQL> SQL> set numformat 9999.99 SQL> select ename, hiredate, sal,
2 avg(sal) 3 over ( order by hiredate asc rows 5 preceding ) avg_5_before, 4 count(*) 5 over ( order by hiredate asc rows 5 preceding ) obs_before, 6 avg(sal) 7 over ( order by hiredate desc rows 5 preceding ) avg_5_after, 8 count(*) 9 over ( order by hiredate desc rows 5 preceding ) obs_after 10 from emp 11 order by hiredate 12 /
ENAME HIREDATE SAL AVG_5_BEFORE OBS_BEFORE AVG_5_AFTER OBS_AFTER
--------- -------- ------------ ---------- ----------- ---------
SMITH 17-DEC-80 800.00 800.00 1.00 1987.50 6.00 ALLEN 20-FEB-81 1600.00 1200.00 2.00 2104.17 6.00 WARD 22-FEB-81 1250.00 1216.67 3.00 2045.83 6.00 JONES 02-APR-81 2975.00 1656.25 4.00 2670.83 6.00 BLAKE 01-MAY-81 2850.00 1895.00 5.00 2333.33 6.00 CLARK 09-JUN-81 2450.00 1987.50 6.00 2358.33 6.00 TURNER 08-SEP-81 1500.00 2104.17 6.00 2166.67 6.00 MARTIN 28-SEP-81 1250.00 2045.83 6.00 2416.67 6.00 KING 17-NOV-81 5000.00 2670.83 6.00 2391.67 6.00 JAMES 03-DEC-81 950.00 2333.33 6.00 1870.00 5.00 FORD 03-DEC-81 3000.00 2358.33 6.00 2100.00 4.00 ENAME HIREDATE SAL AVG_5_BEFORE OBS_BEFORE AVG_5_AFTER OBS_AFTER
--------- -------- ------------ ---------- ----------- ---------
MILLER 23-JAN-82 1300.00 2166.67 6.00 1800.00 3.00 SCOTT 09-DEC-82 3000.00 2416.67 6.00 2050.00 2.00 ADAMS 12-JAN-83 1100.00 2391.67 6.00 1100.00 1.00 14 rows selected. SQL> SQL> drop table emp; Table dropped.
</source>
avg over and avg over order by
<source lang="sql">
SQL> SQL> set echo on SQL> set numformat 99999.99 SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select ename, sal, avg(sal) over ()
2 from emp 3 /
ENAME SAL AVG(SAL)OVER()
--------- --------------
SMITH 800.00 2073.21 ALLEN 1600.00 2073.21 WARD 1250.00 2073.21 JONES 2975.00 2073.21 MARTIN 1250.00 2073.21 BLAKE 2850.00 2073.21 CLARK 2450.00 2073.21 SCOTT 3000.00 2073.21 KING 5000.00 2073.21 TURNER 1500.00 2073.21 ADAMS 1100.00 2073.21 ENAME SAL AVG(SAL)OVER()
--------- --------------
JAMES 950.00 2073.21 FORD 3000.00 2073.21 MILLER 1300.00 2073.21 14 rows selected. SQL> SQL> select ename, sal, avg(sal) over ( ORDER BY ENAME )
2 from emp 3 order by ename 4 /
ENAME SAL AVG(SAL)OVER(ORDERBYENAME)
--------- --------------------------
ADAMS 1100.00 1100.00 ALLEN 1600.00 1350.00 BLAKE 2850.00 1850.00 CLARK 2450.00 2000.00 FORD 3000.00 2200.00 JAMES 950.00 1991.67 JONES 2975.00 2132.14 KING 5000.00 2490.63 MARTIN 1250.00 2352.78 MILLER 1300.00 2247.50 SCOTT 3000.00 2315.91 ENAME SAL AVG(SAL)OVER(ORDERBYENAME)
--------- --------------------------
SMITH 800.00 2189.58 TURNER 1500.00 2136.54 WARD 1250.00 2073.21 14 rows selected. SQL> SQL> drop table emp; Table dropped. SQL>
</source>
avg over order by range
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> set numformat 9999.99 SQL> select ename, hiredate, sal,
2 avg(sal) 3 over ( order by hiredate asc range 100 preceding ) avg_sal_100_days_BEFORE, 4 avg(sal) 5 over ( order by hiredate desc range 100 preceding ) avg_sal_100_days_AFTER 6 from emp 7 order by hiredate 8 /
ENAME HIREDATE SAL AVG_SAL_100_DAYS_BEFORE AVG_SAL_100_DAYS_AFTER
--------- -------- ----------------------- ----------------------
SMITH 17-DEC-80 800.00 800.00 1216.67 ALLEN 20-FEB-81 1600.00 1200.00 2168.75 WARD 22-FEB-81 1250.00 1216.67 2358.33 JONES 02-APR-81 2975.00 1941.67 2758.33 BLAKE 01-MAY-81 2850.00 2168.75 2650.00 CLARK 09-JUN-81 2450.00 2758.33 1975.00 TURNER 08-SEP-81 1500.00 1975.00 2340.00 MARTIN 28-SEP-81 1250.00 1375.00 2550.00 KING 17-NOV-81 5000.00 2583.33 2562.50 JAMES 03-DEC-81 950.00 2340.00 1750.00 FORD 03-DEC-81 3000.00 2340.00 1750.00 ENAME HIREDATE SAL AVG_SAL_100_DAYS_BEFORE AVG_SAL_100_DAYS_AFTER
--------- -------- ----------------------- ----------------------
MILLER 23-JAN-82 1300.00 2562.50 1300.00 SCOTT 09-DEC-82 3000.00 3000.00 2050.00 ADAMS 12-JAN-83 1100.00 2050.00 1100.00 14 rows selected. SQL> SQL> drop table emp; Table dropped.
</source>
avg over range between
<source lang="sql">
SQL> SQL> create table employee(
2 emp_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,salary number(3) 14 ,birthdate date 15 ,hiredate date 16 ,title varchar2(20) 17 ,dept_no integer 18 ,mgr integer 19 ,region number 20 ,division number 21 ,total_sales number 22 );
Table created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created. SQL> SQL> select * from employee;
EMP_NO LASTNAME FIRSTNAME M STREET CITY ST ZIP ZIP_ ARE PHONE SALARY
-------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- ----------
BIRTHDATE HIREDATE TITLE DEPT_NO MGR REGION DIVISION TOTAL_SALES
-------------------- -------------------- ---------- ---------- ---------- ---------- -----------
1 Anderson Nancy N 33 Ave London NY 11111 1111 212 234-1111 4
21-MAR-1927 00:00:00 01-FEB-1947 00:00:00 Sales Manager 2 100 10 40000
2 Last First F 12 Ave Paris CA 22222 2222 221 867-2222 8
14-FEB-1976 00:00:00 15-MAR-1985 00:00:00 Sales Clerk 2 1 100 10 10000
3 Wash Georgia G 1 Street14 Barton NJ 33333 3333 214 340-3333 12
02-JUL-1977 00:00:00 21-APR-2004 00:00:00 Designer 1 2 100 10 40000
4 Bush Dave D 56 Street Island RI 44444 4444 215 777-4444 22
15-MAY-1945 00:00:00 02-AUG-1975 00:00:00 Designer 1 2 100 10 40000
5 Will Robin W 56 Street Island MA 55555 5555 216 777-5555 25
10-DEC-1980 00:00:00 02-AUG-2007 00:00:00 Designer 1 5 100 10 40000
6 Pete Mona M 13 Ave York MO 66666 6666 217 111-6666 9
14-FEB-1966 00:00:00 15-MAR-1985 00:00:00 Sales Clerk 2 5 100 10 40000
7 Roke John J 67 Ave New York BC 77777 7777 218 122-7777 10
14-JUN-1955 00:00:00 15-MAR-1975 00:00:00 Accountant 3 2 100 10 40000
8 Horry Tedi T 1236 Lane Newton NY 88888 8888 219 222-8888 13
10-JUN-1955 00:00:00 15-AUG-1985 00:00:00 Sales Representative 3 2 100 10 50000
9 Bar Candi C 400 East Street Yorken NY 99999 9999 220 321-9999 12
10-OCT-1933 00:00:00 15-JAN-1969 00:00:00 Sales Representative 3 5 100 10 35000
9 rows selected. SQL> SQL> select lastname , hiredate, salary,
2 avg(salary) over (order by hiredate 3 range between interval "6" month preceding and current row) avg_sal 4 from employee 5 /
LASTNAME HIREDATE SALARY AVG_SAL
-------------------- ---------- ----------
Anderson 01-FEB-1947 00:00:00 4 4 Bar 15-JAN-1969 00:00:00 12 12 Roke 15-MAR-1975 00:00:00 10 10 Bush 02-AUG-1975 00:00:00 22 16 Last 15-MAR-1985 00:00:00 8 8.5 Pete 15-MAR-1985 00:00:00 9 8.5 Horry 15-AUG-1985 00:00:00 13 10 Wash 21-APR-2004 00:00:00 12 12 Will 02-AUG-2007 00:00:00 25 25 9 rows selected. SQL> SQL> SQL> drop table employee; Table dropped. SQL> SQL> --
</source>
Avg over ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
<source lang="sql">
SQL> create table TestTable (
2 x number primary key, 3 y number 4 );
Table created. SQL> insert into TestTable values (1, 7 ); 1 row created. SQL> insert into TestTable values (2, 1 ); 1 row created. SQL> insert into TestTable values (3, 2 ); 1 row created. SQL> insert into TestTable values (4, 5 ); 1 row created. SQL> insert into TestTable values (5, 7 ); 1 row created. SQL> insert into TestTable values (6, 34 ); 1 row created. SQL> insert into TestTable values (7, 32 ); 1 row created. SQL> insert into TestTable values (8, 43 ); 1 row created. SQL> insert into TestTable values (9, 87 ); 1 row created. SQL> insert into TestTable values (10, 32 ); 1 row created. SQL> insert into TestTable values (11, 12 ); 1 row created. SQL> insert into TestTable values (12, 16 ); 1 row created. SQL> insert into TestTable values (13, 63 ); 1 row created. SQL> insert into TestTable values (14, 74 ); 1 row created. SQL> insert into TestTable values (15, 36 ); 1 row created. SQL> insert into TestTable values (16, 56 ); 1 row created. SQL> insert into TestTable values (17, 2 ); 1 row created. SQL> SQL> select * from TestTable;
X Y
----------
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87
10 32
11 12
12 16
13 63
14 74
15 36
16 56
17 2
17 rows selected. SQL> SQL> SQL> SELECT x, y,
2 AVG(y) OVER(ORDER BY x
3 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ma
4 FROM TestTable
5 ORDER BY x;
X Y MA
---------- ----------
1 7 4
2 1 3.33333333
3 2 2.66666667
4 5 4.66666667
5 7 15.3333333
6 34 24.3333333
7 32 36.3333333
8 43 54
9 87 54
10 32 43.6666667
11 12 20
12 16 30.3333333
13 63 51
14 74 57.6666667
15 36 55.3333333
16 56 31.3333333
17 2 29
17 rows selected. SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>
count(*) over partition by, order by, range unbounded preceding
<source lang="sql">
SQL> SQL> create table t
2 as 3 select object_name ename, 4 mod(object_id,50) deptno, 5 object_id sal 6 from all_objects 7 where rownum <= 1000 8 /
Table created. SQL> SQL> SQL> select *
2 from (select deptno, ename, sal, 3 count(*) over (partition by deptno 4 order by sal desc 5 range unbounded preceding) 6 cnt from t) 7 where cnt <= 3 8 order by deptno, sal desc 9 /
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
0 V_$LOCK 1050.00 1.00
V_$BUFFER_POOL_STATISTICS 1000.00 2.00
V_$DLM_ALL_LOCKS 950.00 3.00
1 V$LOCK 1051.00 1.00
V$BUFFER_POOL_STATISTICS 1001.00 2.00
V$DLM_ALL_LOCKS 951.00 3.00
2 V_$SESSTAT 1052.00 1.00
V_$INSTANCE_RECOVERY 1002.00 2.00
V_$DLM_LOCKS 952.00 3.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
3 V$SESSTAT 1053.00 1.00
V$INSTANCE_RECOVERY 1003.00 2.00
V$DLM_LOCKS 953.00 3.00
4 V_$MYSTAT 1054.00 1.00
V_$CONTROLFILE 1004.00 2.00
V_$DLM_RESS 954.00 3.00
5 V$MYSTAT 1055.00 1.00
V$CONTROLFILE 1005.00 2.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
5 V$DLM_RESS 955.00 3.00
6 V_$SUBCACHE 1056.00 1.00
V_$LOG 1006.00 2.00
V_$HVMASTER_INFO 956.00 3.00
7 V$SUBCACHE 1057.00 1.00
V$LOG 1007.00 2.00
V$HVMASTER_INFO 957.00 3.00
8 V_$SYSSTAT 1058.00 1.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
8 V_$STANDBY_LOG 1008.00 2.00
V_$GCSHVMASTER_INFO 958.00 3.00
9 V$SYSSTAT 1059.00 1.00
V$STANDBY_LOG 1009.00 2.00
V$GCSHVMASTER_INFO 959.00 3.00
10 V_$STATNAME 1060.00 1.00
V_$DATAGUARD_STATUS 1010.00 2.00
V_$GCSPFMASTER_INFO 960.00 3.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
11 V$STATNAME 1061.00 1.00
V$DATAGUARD_STATUS 1011.00 2.00
V$GCSPFMASTER_INFO 961.00 3.00
12 V_$OSSTAT 1062.00 1.00
V_$THREAD 1012.00 2.00
GV_$DLM_TRAFFIC_CONTROLLER 962.00 3.00
13 V$OSSTAT 1063.00 1.00
V$THREAD 1013.00 2.00
GV$DLM_TRAFFIC_CONTROLLER 963.00 3.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
14 V_$ACCESS 1064.00 1.00
V_$PROCESS 1014.00 2.00
V_$DLM_TRAFFIC_CONTROLLER 964.00 3.00
15 V$ACCESS 1065.00 1.00
V$PROCESS 1015.00 2.00
V$DLM_TRAFFIC_CONTROLLER 965.00 3.00
16 V_$OBJECT_DEPENDENCY 1066.00 1.00
V_$BGPROCESS 1016.00 2.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
16 V_$GES_ENQUEUE 966.00 3.00
17 V$OBJECT_DEPENDENCY 1067.00 1.00
V$BGPROCESS 1017.00 2.00
V$GES_ENQUEUE 967.00 3.00
18 V_$DBFILE 1068.00 1.00
V_$SESSION 1018.00 2.00
V_$GES_BLOCKING_ENQUEUE 968.00 3.00
19 V$DBFILE 1069.00 1.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
19 V$SESSION 1019.00 2.00
V$GES_BLOCKING_ENQUEUE 969.00 3.00
20 V_$FILESTAT 1070.00 1.00
V_$LICENSE 1020.00 2.00
V_$GC_ELEMENT 970.00 3.00
21 V$FILESTAT 1071.00 1.00
V$LICENSE 1021.00 2.00
V$GC_ELEMENT 971.00 3.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
22 V_$TEMPSTAT 1072.00 1.00
V_$TRANSACTION 1022.00 2.00
V_$CR_BLOCK_SERVER 972.00 3.00
23 V$TEMPSTAT 1073.00 1.00
V$TRANSACTION 1023.00 2.00
V$CR_BLOCK_SERVER 973.00 3.00
24 V_$LOGFILE 1074.00 1.00
V_$BSP 1024.00 2.00
V_$CURRENT_BLOCK_SERVER 974.00 3.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
25 V$LOGFILE 1075.00 1.00
V$BSP 1025.00 2.00
V$CURRENT_BLOCK_SERVER 975.00 3.00
26 V_$FLASHBACK_DATABASE_LOGFILE 1076.00 1.00
V_$FAST_START_SERVERS 1026.00 2.00
V_$GC_ELEMENTS_W_COLLISIONS 976.00 3.00
27 V$FLASHBACK_DATABASE_LOGFILE 1077.00 1.00
V$FAST_START_SERVERS 1027.00 2.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
27 V$GC_ELEMENTS_WITH_COLLISIONS 977.00 3.00
28 V_$FLASHBACK_DATABASE_LOG 1078.00 1.00
V_$FAST_START_TRANSACTIONS 1028.00 2.00
V_$FILE_CACHE_TRANSFER 978.00 3.00
29 V$FLASHBACK_DATABASE_LOG 1079.00 1.00
V$FAST_START_TRANSACTIONS 1029.00 2.00
V$FILE_CACHE_TRANSFER 979.00 3.00
30 V_$FLASHBACK_DATABASE_STAT 1080.00 1.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
30 V_$LOCKED_OBJECT 1030.00 2.00
V_$TEMP_CACHE_TRANSFER 980.00 3.00
31 V$FLASHBACK_DATABASE_STAT 1081.00 1.00
V$LOCKED_OBJECT 1031.00 2.00
V$TEMP_CACHE_TRANSFER 981.00 3.00
32 V_$RESTORE_POINT 1082.00 1.00
V_$LATCH 1032.00 2.00
V_$CLASS_CACHE_TRANSFER 982.00 3.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
33 V$RESTORE_POINT 1083.00 1.00
V$LATCH 1033.00 2.00
V$CLASS_CACHE_TRANSFER 983.00 3.00
34 V_$ROLLNAME 1084.00 1.00
V_$LATCH_CHILDREN 1034.00 2.00
V_$BH 984.00 3.00
35 V$ROLLNAME 1085.00 1.00
V$LATCH_CHILDREN 1035.00 2.00
V$BH 985.00 3.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
36 V_$ROLLSTAT 1086.00 1.00
V_$LATCH_PARENT 1036.00 2.00
V_$LOCK_ELEMENT 986.00 3.00
37 V$ROLLSTAT 1087.00 1.00
V$LATCH_PARENT 1037.00 2.00
V$LOCK_ELEMENT 987.00 3.00
38 V_$UNDOSTAT 1088.00 1.00
V_$LATCHNAME 1038.00 2.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
38 V_$LOCKS_WITH_COLLISIONS 988.00 3.00
39 V$UNDOSTAT 1089.00 1.00
V$LATCHNAME 1039.00 2.00
V$LOCKS_WITH_COLLISIONS 989.00 3.00
40 V_$SGA 1090.00 1.00
V_$LATCHHOLDER 1040.00 2.00
V_$FILE_PING 990.00 3.00
41 V$LATCHHOLDER 1041.00 1.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
41 V$FILE_PING 991.00 2.00
V$SQL_OPTIMIZER_ENV 941.00 3.00
42 V_$LATCH_MISSES 1042.00 1.00
V_$TEMP_PING 992.00 2.00
V_$DLM_MISC 942.00 3.00
43 V$LATCH_MISSES 1043.00 1.00
V$TEMP_PING 993.00 2.00
V$DLM_MISC 943.00 3.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
44 V_$SESSION_LONGOPS 1044.00 1.00
V_$CLASS_PING 994.00 2.00
V_$DLM_LATCH 944.00 3.00
45 V$SESSION_LONGOPS 1045.00 1.00
V$CLASS_PING 995.00 2.00
V$DLM_LATCH 945.00 3.00
46 V_$RESOURCE 1046.00 1.00
V_$INSTANCE_CACHE_TRANSFER 996.00 2.00
V_$DLM_CONVERT_LOCAL 946.00 3.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
47 V$RESOURCE 1047.00 1.00
V$INSTANCE_CACHE_TRANSFER 997.00 2.00
V$DLM_CONVERT_LOCAL 947.00 3.00
48 V_$_LOCK 1048.00 1.00
V_$BUFFER_POOL 998.00 2.00
V_$DLM_CONVERT_REMOTE 948.00 3.00
49 V$_LOCK 1049.00 1.00
V$BUFFER_POOL 999.00 2.00
DEPTNO ENAME SAL CNT
------------------------------ -------- --------
49 V$DLM_CONVERT_REMOTE 949.00 3.00
150 rows selected. SQL> SQL> drop table t; Table dropped. SQL>
</source>
count(*) over partition, order by, range unbounded preceding
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20 7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30 7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00 7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20 7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30 7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00 7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20 7839.00 KING PRESIDENT 17-NOV-81 5000.00 10 7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30 7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20 7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20 7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected. SQL> SQL> SQL> set echo on SQL> SQL> break on deptno skip 1 SQL> SQL> update emp set sal = 99 where deptno = 30; 6 rows updated. SQL> SQL> select *
2 from (select deptno, ename, sal, 3 row_number() over ( partition by deptno 4 order by sal desc ) rn 5 from emp ) 6 where rn <= 3 7 /
DEPTNO ENAME SAL RN
---------- -------- --------
10 KING 5000.00 1.00
CLARK 2450.00 2.00
MILLER 1300.00 3.00
20 SCOTT 3000.00 1.00
FORD 3000.00 2.00
JONES 2975.00 3.00
30 ALLEN 99.00 1.00
BLAKE 99.00 2.00
MARTIN 99.00 3.00
9 rows selected. SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Displaying a Running Total Using SUM as an Analytical Function
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> -- Displaying a Running Total Using SUM as an Analytical Function SQL> SQL> SQL> SELECT start_date "Date", city,
2 SUM(salary) OVER(ORDER BY start_date 3 ROWS BETWEEN UNBOUNDED PRECEDING 4 AND CURRENT ROW) "Running total" 5 FROM employee;
Date CITY Running total
---------- -------------
21-MAR-76 Vancouver 6661.78 12-DEC-78 Vancouver 13206.56 24-OCT-82 Vancouver 15551.34 15-JAN-84 Vancouver 17886.12 30-JUL-87 New York 22208.9 31-DEC-90 New York 30106.68 25-JUL-96 Toronto 31341.24 17-SEP-96 Vancouver 32574.02 8 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Employee salary report with avg salary for the previous 12 months
<source lang="sql">
SQL> SQL> SQL> create table employee(
2 emp_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,salary number(3) 14 ,birthdate date 15 ,hiredate date 16 ,title varchar2(20) 17 ,dept_no integer 18 ,mgr integer 19 ,region number 20 ,division number 21 ,total_sales number 22 );
Table created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (1,"Anderson","Nancy","N","33 Ave","London","NY","11111","1111","212","234-1111",3.75,"21-mar-1927","1-feb-1947","Sales Manager",2,null,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (2,"Last","First","F","12 Ave","Paris","CA","22222","2222","221","867-2222",7.75,"14-feb-1976","15-mar-1985","Sales Clerk",2,1,100,10,10000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (3,"Wash","Georgia","G","1 Street14","Barton","NJ","33333","3333","214","340-3333",11.50,"2-jul-1977","21-apr-2004","Designer",1,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (4,"Bush","Dave","D","56 Street","Island","RI","44444","4444","215","777-4444",21.65,"15-may-1945","2-aug-1975","Designer",1,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (5,"Will","Robin","W","56 Street","Island","MA","55555","5555","216","777-5555",24.65,"10-dec-1980","2-aug-2007","Designer",1,5,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (6,"Pete","Mona","M","13 Ave","York","MO","66666","6666","217","111-6666",9,"14-feb-1966","15-mar-1985","Sales Clerk",2,5,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (7,"Roke","John","J","67 Ave","New York","BC","77777","7777","218","122-7777",10.00,"14-jun-1955","15-mar-1975","Accountant",3,2,100,10,40000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (8,"Horry","Tedi","T","1236 Lane","Newton","NY","88888","8888","219","222-8888",13.00,"10-jun-1955","15-aug-1985","Sales Representative",3,2,100,10,50000);
1 row created. SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
2 values (9,"Bar","Candi","C","400 East Street","Yorken","NY","99999","9999","220","321-9999",12.00,"10-oct-1933","15-jan-1969","Sales Representative",3,5,100,10,35000);
1 row created. SQL> SQL> select * from employee;
EMP_NO LASTNAME FIRSTNAME M STREET CITY ST ZIP ZIP_ ARE PHONE SALARY
-------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- ----------
BIRTHDATE HIREDATE TITLE DEPT_NO MGR REGION DIVISION TOTAL_SALES
-------------------- -------------------- ---------- ---------- ---------- ---------- -----------
1 Anderson Nancy N 33 Ave London NY 11111 1111 212 234-1111 4
21-MAR-1927 00:00:00 01-FEB-1947 00:00:00 Sales Manager 2 100 10 40000
2 Last First F 12 Ave Paris CA 22222 2222 221 867-2222 8
14-FEB-1976 00:00:00 15-MAR-1985 00:00:00 Sales Clerk 2 1 100 10 10000
3 Wash Georgia G 1 Street14 Barton NJ 33333 3333 214 340-3333 12
02-JUL-1977 00:00:00 21-APR-2004 00:00:00 Designer 1 2 100 10 40000
4 Bush Dave D 56 Street Island RI 44444 4444 215 777-4444 22
15-MAY-1945 00:00:00 02-AUG-1975 00:00:00 Designer 1 2 100 10 40000
5 Will Robin W 56 Street Island MA 55555 5555 216 777-5555 25
10-DEC-1980 00:00:00 02-AUG-2007 00:00:00 Designer 1 5 100 10 40000
6 Pete Mona M 13 Ave York MO 66666 6666 217 111-6666 9
14-FEB-1966 00:00:00 15-MAR-1985 00:00:00 Sales Clerk 2 5 100 10 40000
7 Roke John J 67 Ave New York BC 77777 7777 218 122-7777 10
14-JUN-1955 00:00:00 15-MAR-1975 00:00:00 Accountant 3 2 100 10 40000
8 Horry Tedi T 1236 Lane Newton NY 88888 8888 219 222-8888 13
10-JUN-1955 00:00:00 15-AUG-1985 00:00:00 Sales Representative 3 2 100 10 50000
9 Bar Candi C 400 East Street Yorken NY 99999 9999 220 321-9999 12
10-OCT-1933 00:00:00 15-JAN-1969 00:00:00 Sales Representative 3 5 100 10 35000
9 rows selected. SQL> SQL> select lastname , hiredate, salary,
2 avg(salary) over (order by hiredate 3 range 365 preceding) avg_sal 4 from employee 5 order by hiredate asc 6 /
LASTNAME HIREDATE SALARY AVG_SAL
-------------------- ---------- ----------
Anderson 01-FEB-1947 00:00:00 4 4 Bar 15-JAN-1969 00:00:00 12 12 Roke 15-MAR-1975 00:00:00 10 10 Bush 02-AUG-1975 00:00:00 22 16 Last 15-MAR-1985 00:00:00 8 8.5 Pete 15-MAR-1985 00:00:00 9 8.5 Horry 15-AUG-1985 00:00:00 13 10 Wash 21-APR-2004 00:00:00 12 12 Will 02-AUG-2007 00:00:00 25 25 9 rows selected. SQL> SQL> drop table employee; Table dropped. SQL> SQL> --
</source>
Is our average total_order_price increasing or decreasing?
<source lang="sql">
SQL> SQL> SQL> create table ord(
2 order_no integer primary key 3 ,cust_no integer 4 ,order_date date not null 5 ,total_order_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment_method varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,gift_message varchar2(100) 12 );
Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created. SQL> SQL> SQL> SQL> select order_no, order_date, total_order_price,
2 avg(total_order_price) over ( order by order_date 3 range between interval "6" month preceding 4 and interval "6" month following ) AS annual_avg 5 from ord 6 order by order_Date 7 / ORDER_NO ORDER_DATE TOTAL_ORDER_PRICE ANNUAL_AVG
-------------------- ----------------- ----------
4 14-FEB-1999 00:00:00 191.95 191.95
8 31-DEC-1999 00:00:00 135.95 135.95
1 14-FEB-2002 00:00:00 23 62.475
5 04-MAR-2002 00:00:00 101.95 62.475
2 14-FEB-2003 00:00:00 510.98 366.465
6 07-APR-2003 00:00:00 221.95 366.465
9 26-DEC-2003 00:00:00 715.95 449.296667
3 14-FEB-2004 00:00:00 315.99 449.296667
7 20-JUN-2004 00:00:00 315.95 449.296667
12 13-JUN-2008 18:00:55 121.95 190.9625
10 15-JUN-2008 18:00:55 119.95 190.9625
11 16-JUN-2008 18:00:55 310 190.9625
13 16-JUN-2008 18:00:55 211.95 190.9625
13 rows selected. SQL> SQL> drop table ord; Table dropped. SQL> --
</source>
Reporting on a Sum
<source lang="sql">
SQL> CREATE TABLE all_sales (
2 year INTEGER, 3 month INTEGER, 4 prd_type_id INTEGER, 5 emp_id INTEGER , 6 amount NUMBER(8, 2) 7 );
Table created. SQL> SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,1 ,1 ,21 ,16034.84);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,2 ,1 ,21 ,15644.65);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,3 ,2 ,21 ,20167.83);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,4 ,2 ,21 ,25056.45);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,5 ,2 ,21 ,NULL);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,6 ,1 ,21 ,15564.66);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,7 ,1 ,21 ,15644.65);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,8 ,1 ,21 ,16434.82);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,9 ,1 ,21 ,19654.57);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,10 ,1 ,21 ,21764.19);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,11 ,1 ,21 ,13026.73);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,12 ,2 ,21 ,10034.64);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,22 ,16634.84);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,21 ,26034.84);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,2 ,1 ,21 ,12644.65);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,3 ,1 ,21 ,NULL);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,4 ,1 ,21 ,25026.45);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,5 ,1 ,21 ,17212.66);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,6 ,1 ,21 ,15564.26);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,7 ,2 ,21 ,62654.82);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,8 ,2 ,21 ,26434.82);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,9 ,2 ,21 ,15644.65);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,10 ,2 ,21 ,21264.19);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,11 ,1 ,21 ,13026.73);
1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,12 ,1 ,21 ,10032.64);
1 row created. SQL> SQL> select * from all_sales;
YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ----------- ---------- ----------
2006 1 1 21 16034.84
2006 2 1 21 15644.65
2006 3 2 21 20167.83
2006 4 2 21 25056.45
2006 5 2 21
2006 6 1 21 15564.66
2006 7 1 21 15644.65
2006 8 1 21 16434.82
2006 9 1 21 19654.57
2006 10 1 21 21764.19
2006 11 1 21 13026.73
2006 12 2 21 10034.64
2005 1 2 22 16634.84
2005 1 2 21 26034.84
2005 2 1 21 12644.65
2005 3 1 21
2005 4 1 21 25026.45
2005 5 1 21 17212.66
2005 6 1 21 15564.26
2005 7 2 21 62654.82
2005 8 2 21 26434.82
2005 9 2 21 15644.65
2005 10 2 21 21264.19
2005 11 1 21 13026.73
2005 12 1 21 10032.64
25 rows selected. SQL> SQL> SQL> SQL> --Reporting on a Sum SQL> SQL> --The total sum of all sales for all three months (labeled as total_month_amount) SQL> SQL> --The total sum of all sales for all product types (labeled as total_product_type_amount) SQL> SQL> SELECT
2 month, prd_type_id,
3 SUM(SUM(amount)) OVER (PARTITION BY month)
4 AS total_month_amount,
5 SUM(SUM(amount)) OVER (PARTITION BY prd_type_id)
6 AS total_product_type_amount
7 FROM all_sales
8 WHERE month <= 3
9 GROUP BY month, prd_type_id
10 ORDER BY month, prd_type_id;
MONTH PRD_TYPE_ID TOTAL_MONTH_AMOUNT TOTAL_PRODUCT_TYPE_AMOUNT
----------- ------------------ -------------------------
1 1 58704.52 44324.14
1 2 58704.52 62837.51
2 1 28289.3 44324.14
3 1 20167.83 44324.14
3 2 20167.83 62837.51
SQL> SQL> SQL> drop table all_sales; Table dropped. SQL> SQL>
</source>
Row-ordering is done first and then the moving average
<source lang="sql">
SQL> create table TestTable (
2 x number primary key, 3 y number 4 );
Table created. SQL> SQL> insert into TestTable values (1, 7 ); 1 row created. SQL> insert into TestTable values (2, 1 ); 1 row created. SQL> insert into TestTable values (3, 2 ); 1 row created. SQL> insert into TestTable values (4, 5 ); 1 row created. SQL> insert into TestTable values (5, 7 ); 1 row created. SQL> insert into TestTable values (6, 34 ); 1 row created. SQL> insert into TestTable values (7, 32 ); 1 row created. SQL> insert into TestTable values (8, 43 ); 1 row created. SQL> insert into TestTable values (9, 87 ); 1 row created. SQL> insert into TestTable values (10, 32 ); 1 row created. SQL> insert into TestTable values (11, 12 ); 1 row created. SQL> insert into TestTable values (12, 16 ); 1 row created. SQL> insert into TestTable values (13, 63 ); 1 row created. SQL> insert into TestTable values (14, 74 ); 1 row created. SQL> insert into TestTable values (15, 36 ); 1 row created. SQL> insert into TestTable values (16, 56 ); 1 row created. SQL> insert into TestTable values (17, 2 ); 1 row created. SQL> SQL> select * from TestTable;
X Y
----------
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87
10 32
11 12
12 16
13 63
14 74
15 36
16 56
17 2
17 rows selected. SQL> SQL> SQL> -- Row-ordering is done first and then the moving average: SQL> SQL> SELECT x, y,
2 AVG(y) OVER(ORDER BY y
3 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ma
4 FROM TestTable
5 ORDER BY y;
X Y MA
---------- ----------
2 1 1.5
3 2 1.66666667
17 2 3
4 5 4.66666667
1 7 6.33333333
5 7 8.66666667
11 12 11.6666667
12 16 20
7 32 26.6666667
10 32 32.6666667
6 34 34
15 36 37.6666667
8 43 45
16 56 54
13 63 64.3333333
14 74 74.6666667
9 87 80.5
17 rows selected. SQL> SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>
Sum over order by
<source lang="sql">
SQL> SQL> set echo on SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select ename, deptno,
2 sum(sal) over (order by ename, deptno) sum_ename_deptno, 3 sum(sal) over (order by deptno, ename) sum_deptno_ename 4 from emp 5 order by ename, deptno 6 /
ENAME DEPTNO SUM_ENAME_DEPTNO SUM_DEPTNO_ENAME
------ ---------------- ----------------
ADAMS 20 1100.00 9850.00 ALLEN 30 2700.00 21225.00 BLAKE 5550.00 24075.00 CLARK 10 8000.00 2450.00 FORD 20 11000.00 12850.00 JAMES 30 11950.00 25025.00
ENAME DEPTNO SUM_ENAME_DEPTNO SUM_DEPTNO_ENAME
------ ---------------- ----------------
JONES 20 14925.00 15825.00 KING 10 19925.00 7450.00 MARTIN 30 21175.00 26275.00 MILLER 10 22475.00 8750.00 SCOTT 20 25475.00 18825.00 SMITH 26275.00 19625.00
ENAME DEPTNO SUM_ENAME_DEPTNO SUM_DEPTNO_ENAME
------ ---------------- ----------------
TURNER 30 27775.00 27775.00 WARD 29025.00 29025.00
14 rows selected. SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Sum over partition by and order by
<source lang="sql">
SQL> SQL> SQL> set echo on SQL> break on deptno skip 1 SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select deptno, ename, sal,
2 sum(sal) over 3 (partition by deptno 4 order by ename 5 rows 2 preceding) sliding_total 6 from emp 7 order by deptno, ename 8 /
DEPTNO ENAME SAL SLIDING_TOTAL
---------- --------- -------------
10 CLARK 2450.00 2450.00
KING 5000.00 7450.00
MILLER 1300.00 8750.00
20 ADAMS 1100.00 1100.00
FORD 3000.00 4100.00
JONES 2975.00 7075.00
SCOTT 3000.00 8975.00
SMITH 800.00 6775.00
30 ALLEN 1600.00 1600.00
DEPTNO ENAME SAL SLIDING_TOTAL
---------- --------- -------------
30 BLAKE 2850.00 4450.00
JAMES 950.00 5400.00
MARTIN 1250.00 5050.00
TURNER 1500.00 3700.00
WARD 1250.00 4000.00
14 rows selected. SQL> SQL> drop table emp; Table dropped. SQL> SQL>
</source>
SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
<source lang="sql">
SQL> create table TestTable (
2 x number primary key, 3 y number 4 );
Table created. SQL> insert into TestTable values (1, 7 ); 1 row created. SQL> insert into TestTable values (2, 1 ); 1 row created. SQL> insert into TestTable values (3, 2 ); 1 row created. SQL> insert into TestTable values (4, 5 ); 1 row created. SQL> insert into TestTable values (5, 7 ); 1 row created. SQL> insert into TestTable values (6, 34 ); 1 row created. SQL> insert into TestTable values (7, 32 ); 1 row created. SQL> insert into TestTable values (8, 43 ); 1 row created. SQL> insert into TestTable values (9, 87 ); 1 row created. SQL> insert into TestTable values (10, 32 ); 1 row created. SQL> insert into TestTable values (11, 12 ); 1 row created. SQL> insert into TestTable values (12, 16 ); 1 row created. SQL> insert into TestTable values (13, 63 ); 1 row created. SQL> insert into TestTable values (14, 74 ); 1 row created. SQL> insert into TestTable values (15, 36 ); 1 row created. SQL> insert into TestTable values (16, 56 ); 1 row created. SQL> insert into TestTable values (17, 2 ); 1 row created. SQL> SQL> select * from TestTable;
X Y
----------
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87
10 32
11 12
12 16
13 63
14 74
15 36
16 56
17 2
17 rows selected. SQL> SQL> SQL> SQL> COLUMN ma FORMAT 99.999 SQL> COLUMN sum LIKE ma SQL> COLUMN "sum/3" LIKE ma SQL> SQL> SELECT x, y,
2 AVG(y) OVER(ORDER BY x
3 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ma,
4 SUM(y) OVER(ORDER BY x
5 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum,
6 (SUM(y) OVER(ORDER BY y
7 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING))/3 "Sum/3"
8 FROM TestTable
9 ORDER BY x;
X Y MA SUM Sum/3
---------- ------- ------- -------
1 7 4.000 8.000 6.333
2 1 3.333 10.000 1.000
3 2 2.667 8.000 1.667
4 5 4.667 14.000 4.667
5 7 15.333 46.000 8.667
6 34 24.333 73.000 34.000
7 32 36.333 ####### 26.667
8 43 54.000 ####### 45.000
9 87 54.000 ####### 53.667
10 32 43.667 ####### 32.667
11 12 20.000 60.000 11.667
12 16 30.333 91.000 20.000
13 63 51.000 ####### 64.333
14 74 57.667 ####### 74.667
15 36 55.333 ####### 37.667
16 56 31.333 94.000 54.000
17 2 29.000 58.000 3.000
17 rows selected. SQL> SQL> COLUMN ma FORMAT 999999.999 SQL> COLUMN sum LIKE ma SQL> COLUMN "sum/3" LIKE ma SQL> SQL> SQL> SELECT x, y,
2 AVG(y) OVER(ORDER BY x
3 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ma,
4 SUM(y) OVER(ORDER BY x
5 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum,
6 (SUM(y) OVER(ORDER BY y
7 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING))/3 "Sum/3"
8 FROM TestTable
9 ORDER BY x;
X Y MA SUM Sum/3
---------- ----------- ----------- -----------
1 7 4.000 8.000 6.333
2 1 3.333 10.000 1.000
3 2 2.667 8.000 1.667
4 5 4.667 14.000 4.667
5 7 15.333 46.000 8.667
6 34 24.333 73.000 34.000
7 32 36.333 109.000 26.667
8 43 54.000 162.000 45.000
9 87 54.000 162.000 53.667
10 32 43.667 131.000 32.667
11 12 20.000 60.000 11.667
12 16 30.333 91.000 20.000
13 63 51.000 153.000 64.333
14 74 57.667 173.000 74.667
15 36 55.333 166.000 37.667
16 56 31.333 94.000 54.000
17 2 29.000 58.000 3.000
17 rows selected. SQL> SQL> drop table TestTable; Table dropped. SQL>
</source>
To see how the moving average window can expand
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> -- To see how the moving average window can expand SQL> SQL> SQL> SELECT start_date, salary,
2 AVG(salary) OVER(ORDER BY start_date 3 ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) ma 4 FROM employee 5 ORDER BY first_name;
START_DAT SALARY MA
---------- -----------
21-MAR-76 6661.78 6603.280 24-OCT-82 2344.78 4471.530 31-DEC-90 7897.78 3626.936 12-DEC-78 6544.78 5183.780 17-SEP-96 1232.78 3671.975 25-JUL-96 1234.56 3404.536 30-JUL-87 4322.78 4688.980 15-JAN-84 2334.78 4441.780 8 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Uses dates and logical offset of seven days preceding
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> -- Uses dates and logical offset of seven days preceding: SQL> SQL> SELECT start_date "Date", city, salary,
2 SUM(salary) OVER(PARTITION BY city 3 ORDER BY start_date 4 RANGE BETWEEN INTERVAL "7" day PRECEDING 5 AND CURRENT ROW) "Running total" 6 FROM employee 7 ORDER BY city, start_date;
Date CITY SALARY Running total
---------- ---------- -------------
30-JUL-87 New York 4322.78 4322.78 31-DEC-90 New York 7897.78 7897.78 25-JUL-96 Toronto 1234.56 1234.56 21-MAR-76 Vancouver 6661.78 6661.78 12-DEC-78 Vancouver 6544.78 6544.78 24-OCT-82 Vancouver 2344.78 2344.78 15-JAN-84 Vancouver 2334.78 2334.78 17-SEP-96 Vancouver 1232.78 1232.78 8 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Use the COUNT aggregate analytical function to show how many rows are included in each window
<source lang="sql">
SQL> create table TestTable (
2 x number primary key, 3 y number 4 );
Table created. SQL> insert into TestTable values (1, 7 ); 1 row created. SQL> insert into TestTable values (2, 1 ); 1 row created. SQL> insert into TestTable values (3, 2 ); 1 row created. SQL> insert into TestTable values (4, 5 ); 1 row created. SQL> insert into TestTable values (5, 7 ); 1 row created. SQL> insert into TestTable values (6, 34 ); 1 row created. SQL> insert into TestTable values (7, 32 ); 1 row created. SQL> insert into TestTable values (8, 43 ); 1 row created. SQL> insert into TestTable values (9, 87 ); 1 row created. SQL> insert into TestTable values (10, 32 ); 1 row created. SQL> insert into TestTable values (11, 12 ); 1 row created. SQL> insert into TestTable values (12, 16 ); 1 row created. SQL> insert into TestTable values (13, 63 ); 1 row created. SQL> insert into TestTable values (14, 74 ); 1 row created. SQL> insert into TestTable values (15, 36 ); 1 row created. SQL> insert into TestTable values (16, 56 ); 1 row created. SQL> insert into TestTable values (17, 2 ); 1 row created. SQL> SQL> select * from TestTable;
X Y
----------
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87
10 32
11 12
12 16
13 63
14 74
15 36
16 56
17 2
17 rows selected. SQL> SQL> -- Use the COUNT aggregate analytical function to show how many rows are included in each window: SQL> SQL> SELECT x, y,
2 COUNT(y) OVER(ORDER BY x
3 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) Howmanyrows
4 FROM TestTable
5 ORDER BY x;
X Y HOWMANYROWS
---------- -----------
1 7 2
2 1 3
3 2 3
4 5 3
5 7 3
6 34 3
7 32 3
8 43 3
9 87 3
10 32 3
11 12 3
12 16 3
13 63 3
14 74 3
15 36 3
16 56 3
17 2 2
17 rows selected. SQL> SQL> SQL> drop table TestTable; Table dropped. SQL> SQL>
</source>
