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
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.
Include a final ordering of the result set with an ORDER BY at the end of the query
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>
Lag salary over, lead salary over
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>
sum over (nothing)
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.
sum over partition by, order by
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.
Use over partition in subquery
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.
Using Analytic Functions: AVG(Mark) OVER
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.
Using Analytic Functions: AVG(Mark) OVER (ORDER BY StudentID, Mark)
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.
Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
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.
Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
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.
Using Analytic Functions: AVG(Mark) OVER(PARTITION BY StudentID ORDER BY StudentID, Mark ROWS 1 preceding
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.