Оконные функции

Цель

Получить практические навыки применения оконные функций.

EMP_NO	FIRST_NAME	LAST_NAME	DEPT_NO	SALARY	SALARY_RANK
105	Oliver H.	Bender	0	212850	1
12	Terri	Lee	0	53793	2
85	Mary S.	MacDonald	100	111262.5	1
127	Michael	Yanowski	100	44000	2
61	Luke	Leung	110	68805	1
34	Janet	Baldwin	110	61637.81	2
118	Takashi	Yamamoto	115	7480000	1
110	Yuki	Ichida	115	6000000	2
37	Willie	Stansbury	120	39224.06	1
…	…	…	…	…	…

EMP_NO	FIRST_NAME	LAST_NAME	DEPT_NO	SALARY	CUMULATIVE_SALARY
12	Terri	Lee	0	53793	53793
105	Oliver H.	Bender	0	212850	266643
85	Mary S.	MacDonald	100	111262.5	111262.5
127	Michael	Yanowski	100	44000	155262.5
34	Janet	Baldwin	110	61637.81	61637.81
61	Luke	Leung	110	68805	130442.81
110	Yuki	Ichida	115	6000000	6000000
118	Takashi	Yamamoto	115	7480000	13480000
…	…	…	…	…	…

EMP_NO	FIRST_NAME	LAST_NAME	HIRE_DATE	SALARY	MOVING_AVG
2	Robert	Nelson	1988-12-28T00:00	105900	105900
4	Bruce	Young	1988-12-28T00:00	97500	101700
5	Kim	Lambert	1989-02-06T00:00	102750	102050
8	Leslie	Johnson	1989-04-05T00:00	64635	88295
9	Phil	Forest	1989-04-17T00:00	75060	80815
20	Chris	Papadopoulos	1990-01-01T00:00	89655	76450
11	K. J.	Weston	1990-01-17T00:00	86292.94	83669.31
12	Terri	Lee	1990-05-01T00:00	53793	76580.31
14	Stewart	Hall	1990-06-04T00:00	69482.63	69856.19

EMP_NO	FIRST_NAME	LAST_NAME	DEPT_NO	SALARY	SALARY_PERCENT
12	Terri	Lee	0	53793	20.17416
105	Oliver H.	Bender	0	212850	79.82583
85	Mary S.	MacDonald	100	111262.5	71.66089
127	Michael	Yanowski	100	44000	28.3391
34	Janet	Baldwin	110	61637.81	47.25274
61	Luke	Leung	110	68805	52.74725
110	Yuki	Ichida	115	6000000	44.51038
118	Takashi	Yamamoto	115	7480000	55.48961
28	Ann	Bennet	120	22935	23.94557

EMP_NO	FIRST_NAME	LAST_NAME	DEPT_NO	SALARY	SALARY_DIFF
12	Terri	Lee	0	53793	-79528.5
105	Oliver H.	Bender	0	212850	79528.5
85	Mary S.	MacDonald	100	111262.5	33631.25
127	Michael	Yanowski	100	44000	-33631.25
34	Janet	Baldwin	110	61637.81	-3583.59
61	Luke	Leung	110	68805	3583.6
110	Yuki	Ichida	115	6000000	-740000
118	Takashi	Yamamoto	115	7480000	740000
28	Ann	Bennet	120	22935	-8991.56
36	Roger	Reeves	120	33620.63	1694.07

EMP_NO	FIRST_NAME	LAST_NAME	DEPT_NO	ROW_NUM
105	Oliver H.	Bender	0	1
12	Terri	Lee	0	2
85	Mary S.	MacDonald	100	1
127	Michael	Yanowski	100	2
34	Janet	Baldwin	110	1
61	Luke	Leung	110	2
110	Yuki	Ichida	115	1
118	Takashi	Yamamoto	115	2

EMP_NO	FIRST_NAME	LAST_NAME	DEPT_NO	SALARY	MAX_SALARY
12	Terri	Lee	0	53793	212850
105	Oliver H.	Bender	0	212850	212850
85	Mary S.	MacDonald	100	111262.5	111262.5
127	Michael	Yanowski	100	44000	111262.5
34	Janet	Baldwin	110	61637.81	68805
61	Luke	Leung	110	68805	68805
110	Yuki	Ichida	115	6000000	7480000
118	Takashi	Yamamoto	115	7480000	7480000
28	Ann	Bennet	120	22935	39224.06

EMP_NO	FIRST_NAME	LAST_NAME	DEPT_NO	SALARY	DIFFFROMMAX
12	Terri	Lee	0	53793	159057
105	Oliver H.	Bender	0	212850	0
85	Mary S.	MacDonald	100	111262.5	0
127	Michael	Yanowski	100	44000	67262.5
34	Janet	Baldwin	110	61637.81	7167.19
61	Luke	Leung	110	68805	0
110	Yuki	Ichida	115	6000000	1480000
118	Takashi	Yamamoto	115	7480000	0
28	Ann	Bennet	120	22935	16289.06

EMP_NO	FIRST_NAME	LAST_NAME	HIRE_DATE	EXPERIENCE_RANK
4	Bruce	Young	1988-12-28T00:00	1
2	Robert	Nelson	1988-12-28T00:00	1
5	Kim	Lambert	1989-02-06T00:00	3
8	Leslie	Johnson	1989-04-05T00:00	4
9	Phil	Forest	1989-04-17T00:00	5
20	Chris	Papadopoulos	1990-01-01T00:00	6
11	K. J.	Weston	1990-01-17T00:00	7
12	Terri	Lee	1990-05-01T00:00	8
14	Stewart	Hall	1990-06-04T00:00	9

EMP_NO	ORDER_DATE	TOTAL_VALUE	CUMULATIVE_SALES
11	1991-03-04T00:00	5000	5000
11	1992-10-15T00:00	2000	77000
11	1992-10-15T00:00	70000	77000
11	1993-02-03T00:00	600.5	77600.5
11	1993-04-27T00:00	20000	97600.5
11	1993-11-11T00:00	27000	124600.5
11	1993-12-27T00:00	14850	139450.5
11	1993-12-31T00:00	0	139450.5
61	1992-07-26T00:00	2985	2985
61	1993-08-01T00:00	9000	11985
61	1993-10-10T00:00	490.69	12475.69
61	1993-12-12T00:00	16000	28475.69
61	1994-02-13T00:00	9000	37475.69
72	1993-03-22T00:00	47.5	47.5
72	1993-08-09T00:00	399960.5	960008
72	1993-08-09T00:00	560000	960008
118	1993-08-20T00:00	18000.4	18000.4
118	1993-10-30T00:00	210	18210.4
118	1993-12-12T00:00	5980	24190.4
121	1993-10-27T00:00	2693	122693
121	1993-10-27T00:00	120000	122693
127	1993-08-16T00:00	0	0
127	1993-09-09T00:00	12582.12	12582.12
127	1993-12-12T00:00	60000	72582.12
127	1994-01-04T00:00	3999.99	76582.11
127	1994-01-17T00:00	3399.15	79981.26
127	1994-02-07T00:00	422210.97	502192.23
134	1993-08-27T00:00	10000	10000
134	1993-09-20T00:00	100.02	10100.02
134	1993-12-12T00:00	450000.49	460100.51
134	1993-12-18T00:00	999.98	462600.49
134	1993-12-18T00:00	1500	462600.49
141	1994-01-06T00:00	1980.72	1980.72

EMP_NO	ORDER_DATE	TOTAL_VALUE	MOVING_AVG
11	1991-03-04T00:00	5000	5000
11	1992-10-15T00:00	2000	36000
11	1992-10-15T00:00	70000	36000
11	1993-02-03T00:00	600.5	24200.16
11	1993-04-27T00:00	20000	23150.12
11	1993-11-11T00:00	27000	15866.83
11	1993-12-27T00:00	14850	15612.62
11	1993-12-31T00:00	0	12490.1
61	1992-07-26T00:00	2985	2985
61	1993-08-01T00:00	9000	9000
61	1993-10-10T00:00	490.69	4745.34
61	1993-12-12T00:00	16000	8496.89
61	1994-02-13T00:00	9000	8622.67
72	1993-03-22T00:00	47.5	47.5
72	1993-08-09T00:00	399960.5	320002.66
72	1993-08-09T00:00	560000	320002.66
118	1993-08-20T00:00	18000.4	18000.4
118	1993-10-30T00:00	210	9105.2
118	1993-12-12T00:00	5980	8063.46
121	1993-10-27T00:00	2693	61346.5
121	1993-10-27T00:00	120000	61346.5
127	1993-08-16T00:00	0	0
127	1993-09-09T00:00	12582.12	6291.06
127	1993-12-12T00:00	60000	24194.04
127	1994-01-04T00:00	3999.99	19145.52
127	1994-01-17T00:00	3399.15	15996.25
127	1994-02-07T00:00	422210.97	83698.7
134	1993-08-27T00:00	10000	10000
134	1993-09-20T00:00	100.02	5050.01
134	1993-12-12T00:00	450000.49	153366.83
134	1993-12-18T00:00	999.98	92520.09
134	1993-12-18T00:00	1500	92520.09
141	1994-01-06T00:00	1980.72	1980.72