Oracle PL/SQL/Analytical Functions/Over
Содержание
- 1 Get Products: RANK() OVER in subquery
- 2 Include a final ordering of the result set with an ORDER BY at the end of the query
- 3 Lag salary over, lead salary over
- 4 sum over (nothing)
- 5 sum over partition by, order by
- 6 Use over partition in subquery
- 7 Using Analytic Functions: AVG(Mark) OVER
- 8 Using Analytic Functions: AVG(Mark) OVER (ORDER BY StudentID, Mark)
- 9 Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
- 10 Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- 11 Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark ROWS 1 preceding
Get Products: RANK() OVER in subquery
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL, 4 Description VARCHAR(1000) NOT NULL, 5 Price NUMBER NULL, 6 ImagePath VARCHAR(50) NULL, 7 soldout NUMBER(1,0) NULL, 8 Promotion NUMBER(1,0) NULL);
Table created. SQL> SQL> CREATE SEQUENCE ProductIDSeq; Sequence created. SQL> SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product 3 FOR EACH ROW 4 BEGIN 5 SELECT ProductIDSeq.NEXTVAL 6 INTO :NEW.ProductID FROM DUAL; 7 END; 8 /
Trigger created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Ruler", "Long",14.99, "ruler.jpg", 0, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Keyboard", "keyboard",3.75, "keyboard.jpg", 0, 0);
1 row created. SQL> SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL, 3 CategoryID INT NOT NULL, 4 PRIMARY KEY (ProductID, CategoryID) 5 );
Table created. SQL> SQL> SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5); 1 row created. SQL> SQL> SQL> CREATE TABLE Category (
2 CategoryID INT NOT NULL PRIMARY KEY, 3 DepartmentID INT NOT NULL, 4 Name VARCHAR(50) NOT NULL, 5 Description VARCHAR (200) NULL);
Table created. SQL> SQL> CREATE SEQUENCE CategoryIDSeq; Sequence created. SQL> SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
2 BEFORE INSERT ON Category 3 FOR EACH ROW 4 BEGIN 5 SELECT CategoryIDSeq.NEXTVAL 6 INTO :NEW.CategoryID FROM DUAL; 7 END; 8 /
Trigger created. SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Local", "In town"); 1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Remote", "Telecommute"); 1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (2, "Masks", "By bits"); 1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wireless", "Not connected"); 1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wired", "Connected"); 1 row created. SQL> SQL> SELECT CategoryName, ProductName, ProductPrice
2 FROM 3 (SELECT C.Name as CategoryName, 4 P.Name as ProductName, 5 P.Price as ProductPrice, 6 RANK() OVER (PARTITION BY C.Name ORDER BY P.Price DESC) 7 AS rank 8 FROM Product P 9 JOIN ProductCategory PC ON P.ProductID = PC.ProductID 10 JOIN Category C ON C.CategoryID = PC.CategoryID) 11 WHERE rank <= 2 12 ORDER BY CategoryName;
CATEGORYNAME
PRODUCTNAME PRODUCTPRICE
------------
Local PC 49.99 Local Ruler 14.99 Masks Ruler 14.99 Masks Pen 5.99 Masks Desk 5.99 Remote Mouse 9.99 Wireless Keyboard 3.75
7 rows selected. SQL> SQL> SQL> drop table Product; Table dropped. SQL> drop table ProductCategory; Table dropped. SQL> drop table Category; Table dropped. SQL> drop sequence CategoryIDSeq; Sequence dropped. SQL> drop sequence ProductIDSeq; Sequence dropped.
</source>
Include a final ordering of the result set with an ORDER BY at the end of the query
<source lang="sql">
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"), 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("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","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("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2334.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"), 2334.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"), 2334.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"), 2334.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 2334.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 2334.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2334.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 2334.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 2334.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 2334.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> -- include a final ordering of the result set with an ORDER BY at the end of the query SQL> SQL> SELECT id, first_name, salary, ROW_NUMBER() OVER(ORDER BY salary desc) toprank
2 FROM employee 3 ORDER BY salary desc;
ID FIRST_NAME SALARY TOPRANK
---------- ---------- ----------
04 Celia 2334.78 1 02 Alison 2334.78 2 03 James 2334.78 3 08 James 2334.78 4 05 Robert 2334.78 5 06 Linda 2334.78 6 07 David 2334.78 7 01 Jason 1234.56 8 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee; Table dropped. SQL> SQL>
</source>
Lag salary over, lead salary over
<source lang="sql">
SQL> SQL> create table history
2 ( empno NUMBER(4) 3 , beginyear NUMBER(4) 4 , begindate DATE 5 , enddate DATE 6 , deptno NUMBER(2) 7 , sal NUMBER(6,2) 8 , comments VARCHAR2(60) 9 , constraint H_PK primary key (empno,begindate) 10 , constraint H_BEG_END check (begindate < enddate) 11 ) ;
Table created. SQL> SQL> SQL> alter session set NLS_DATE_FORMAT="DD-MM-YYYY"; Session altered. SQL> SQL> insert into history values (1,2000,"01-01-2000","01-02-2000",40, 950,""); 1 row created. SQL> insert into history values (1,2000,"01-02-2000", NULL ,20, 800,"restarted"); 1 row created. SQL> insert into history values (2,2009,"01-11-2009", NULL ,30,1600,"just hired"); 1 row created. SQL> SQL> SQL> select empno, begindate, sal
2 , LAG(sal) over
3 ( partition by empno
4 order by empno, begindate
5 ) as prev_sal
6 , LEAD(sal) over
7 ( partition by empno
8 order by empno, begindate
9 ) as next_sal
10 from history
11 order by empno, begindate;
EMPNO BEGINDATE SAL PREV_SAL NEXT_SAL
---------- ---------- ---------- ----------
1 01-01-2000 950 800
1 01-02-2000 800 950
2 01-11-2009 1600
SQL> SQL> drop table history; Table dropped. SQL>
</source>
sum over (nothing)
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
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> break on deptno skip 1 SQL> select deptno,
2 ename, 3 sal, 4 sum(sal) over (partition by deptno order by sal) CumDeptTot, 5 sum(sal) over (partition by deptno) SalByDept, 6 sum(sal) over (order by deptno, sal) CumTot, 7 sum(sal) over () TotSal 8 from emp 9 order by deptno, sal 10 / DEPTNO ENAME SAL CUMDEPTTOT SALBYDEPT CUMTOT TOTSAL
---------- ---------- ---------- ---------- ---------- ----------
10 MILLER 1300 1300 8750 1300 29025
CLARK 2450 3750 8750 3750 29025
KING 5000 8750 8750 8750 29025
20 SMITH 800 800 10875 9550 29025
ADAMS 1100 1900 10875 10650 29025
JONES 2975 4875 10875 13625 29025
SCOTT 3000 10875 10875 19625 29025
FORD 3000 10875 10875 19625 29025
30 JAMES 950 950 9400 20575 29025
DEPTNO ENAME SAL CUMDEPTTOT SALBYDEPT CUMTOT TOTSAL
---------- ---------- ---------- ---------- ---------- ----------
30 MARTIN 1250 3450 9400 23075 29025
WARD 1250 3450 9400 23075 29025
TURNER 1500 4950 9400 24575 29025
ALLEN 1600 6550 9400 26175 29025
BLAKE 2850 9400 9400 29025 29025
14 rows selected. SQL> SQL> SQL> SQL> drop table emp; Table dropped.
</source>
sum over partition by, order by
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
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> break on deptno skip 1 SQL> select deptno,
2 ename, 3 sal, 4 sum(sal) over (partition by deptno order by sal) CumDeptTot, 5 sum(sal) over (partition by deptno) SalByDept, 6 sum(sal) over (order by deptno, sal) CumTot, 7 sum(sal) over () TotSal 8 from emp 9 order by deptno, sal 10 / DEPTNO ENAME SAL CUMDEPTTOT SALBYDEPT CUMTOT TOTSAL
---------- ---------- ---------- ---------- ---------- ----------
10 MILLER 1300 1300 8750 1300 29025
CLARK 2450 3750 8750 3750 29025
KING 5000 8750 8750 8750 29025
20 SMITH 800 800 10875 9550 29025
ADAMS 1100 1900 10875 10650 29025
JONES 2975 4875 10875 13625 29025
SCOTT 3000 10875 10875 19625 29025
FORD 3000 10875 10875 19625 29025
30 JAMES 950 950 9400 20575 29025
DEPTNO ENAME SAL CUMDEPTTOT SALBYDEPT CUMTOT TOTSAL
---------- ---------- ---------- ---------- ---------- ----------
30 MARTIN 1250 3450 9400 23075 29025
WARD 1250 3450 9400 23075 29025
TURNER 1500 4950 9400 24575 29025
ALLEN 1600 6550 9400 26175 29025
BLAKE 2850 9400 9400 29025 29025
14 rows selected. SQL> SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Use over partition in subquery
<source lang="sql">
SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
Table created. SQL> SQL> SQL> select owner, last_ddl_time, object_name, object_type
2 from ( select myTable1.*,max(last_ddl_time) over (partition by owner) max_time from myTable myTable1) 3 where last_ddl_time = max_time 4 /
no rows selected SQL> SQL> SQL> drop table myTable; Table dropped.
</source>
Using Analytic Functions: AVG(Mark) OVER
<source lang="sql">
SQL> SQL> CREATE TABLE SAT (
2 StudentID INT NOT NULL, 3 ExamID INT NOT NULL, 4 Mark INT, 5 IfPassed SMALLINT, 6 Comments VARCHAR(255), 7 CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));
Table created. SQL> SQL> SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,"Simple"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SELECT StudentID, Mark, AVG(Mark) OVER
2 () Average_Mark 3 FROM SAT 4 ORDER BY StudentID, Mark; STUDENTID MARK AVERAGE_MARK
---------- ------------
1 55 54.8
1 73 54.8
2 39 54.8
2 44 54.8
2 63 54.8
5 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> drop table SAT; Table dropped.
</source>
Using Analytic Functions: AVG(Mark) OVER (ORDER BY StudentID, Mark)
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE SAT (
2 StudentID INT NOT NULL, 3 ExamID INT NOT NULL, 4 Mark INT, 5 IfPassed SMALLINT, 6 Comments VARCHAR(255), 7 CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));
Table created. SQL> SQL> SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,"Simple"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SQL> SELECT StudentID, Mark, AVG(Mark) OVER
2 (ORDER BY StudentID, Mark) Running_Average 3 FROM SAT 4 ORDER BY StudentID, Mark; STUDENTID MARK RUNNING_AVERAGE
---------- ---------------
1 55 55
1 73 64
2 39 55.6666667
2 44 52.75
2 63 54.8
5 rows selected. SQL> SQL> SQL> SQL> SQL> drop table SAT; Table dropped.
</source>
Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
<source lang="sql">
SQL> SQL> CREATE TABLE SAT (
2 StudentID INT NOT NULL, 3 ExamID INT NOT NULL, 4 Mark INT, 5 IfPassed SMALLINT, 6 Comments VARCHAR(255), 7 CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));
Table created. SQL> SQL> SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,"Simple"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SELECT StudentID, Mark, AVG(Mark) OVER
2 (PARTITION BY StudentID 3 ORDER BY StudentID, Mark) Running_Avg_by_Student 4 FROM SAT 5 ORDER BY StudentID, Mark; STUDENTID MARK RUNNING_AVG_BY_STUDENT
---------- ----------------------
1 55 55
1 73 64
2 39 39
2 44 41.5
2 63 48.6666667
5 rows selected. SQL> SQL> SQL> drop table SAT; Table dropped.
</source>
Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE SAT (
2 StudentID INT NOT NULL, 3 ExamID INT NOT NULL, 4 Mark INT, 5 IfPassed SMALLINT, 6 Comments VARCHAR(255), 7 CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));
Table created. SQL> SQL> SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,"Simple"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SELECT StudentID, Mark, AVG(Mark) OVER
2 (PARTITION BY StudentID 3 ORDER BY StudentID, Mark 4 RANGE BETWEEN UNBOUNDED PRECEDING AND 5 CURRENT ROW 6 ) Running_Avg_by_Student 7 FROM SAT 8 ORDER BY StudentID, Mark; STUDENTID MARK RUNNING_AVG_BY_STUDENT
---------- ----------------------
1 55 55
1 73 64
2 39 39
2 44 41.5
2 63 48.6666667
5 rows selected. SQL> SQL> SQL> SQL> SQL> drop table SAT; Table dropped.
</source>
Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark ROWS 1 preceding
<source lang="sql">
SQL> SQL> CREATE TABLE SAT (
2 StudentID INT NOT NULL, 3 ExamID INT NOT NULL, 4 Mark INT, 5 IfPassed SMALLINT, 6 Comments VARCHAR(255), 7 CONSTRAINT PK_SAT PRIMARY KEY (StudentID, ExamID));
Table created. SQL> SQL> SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,"Simple"); 1 row created. SQL> INSERT INTO SAT (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SELECT StudentID, Mark, AVG(Mark) OVER
2 (PARTITION BY StudentID 3 ORDER BY StudentID, Mark 4 ROWS 1 preceding 5 ) Running_Avg_by_Student 6 FROM SAT 7 ORDER BY StudentID, Mark; STUDENTID MARK RUNNING_AVG_BY_STUDENT
---------- ----------------------
1 55 55
1 73 64
2 39 39
2 44 41.5
2 63 53.5
5 rows selected. SQL> SQL> SQL> drop table SAT; Table dropped.
</source>
