Posted 2022-05-14Updated 2022-05-19 minkuen SQL / oracle7 minutes read (About 1040 words)

Oracle_practice7

- WINDOW Function
- 분석 함수 : 특정 그룹별 집계를 담당함
              함수의 종류

- PARTITION BY : 분석 함수로 계산될 대상 row의 그룹(파티션)을 지정

1	%load_ext sql

1	%sql oracle://ora_user:evan@127.0.0.1:1521/myoracle

윈도우 함수
ROW_NUMBER()

%%sql

SELECT
    department_id
    , emp_name
    , ROW_NUMBER() OVER(PARTITION BY department_id
                        ORDER BY department_id, emp_name) dep_rows
FROM employees

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

department_id	emp_name	dep_rows
10	Jennifer Whalen	1
20	Michael Hartstein	1
20	Pat Fay	2
30	Alexander Khoo	1
30	Den Raphaely	2
30	Guy Himuro	3
30	Karen Colmenares	4
30	Shelli Baida	5
30	Sigal Tobias	6
40	Susan Mavris	1
50	Adam Fripp	1
50	Alana Walsh	2
50	Alexis Bull	3
50	Anthony Cabrio	4
50	Britney Everett	5
50	Curtis Davies	6
50	Donald OConnell	7
50	Douglas Grant	8
50	Girard Geoni	9
50	Hazel Philtanker	10
50	Irene Mikkilineni	11
50	James Landry	12
50	James Marlow	13
50	Jason Mallin	14
50	Jean Fleaur	15
50	Jennifer Dilly	16
50	John Seo	17
50	Joshua Patel	18
50	Julia Dellinger	19
50	Julia Nayer	20
50	Kelly Chung	21
50	Kevin Feeney	22
50	Kevin Mourgos	23
50	Ki Gee	24
50	Laura Bissot	25
50	Martha Sullivan	26
50	Matthew Weiss	27
50	Michael Rogers	28
50	Mozhe Atkinson	29
50	Nandita Sarchand	30
50	Payam Kaufling	31
50	Peter Vargas	32
50	Randall Matos	33
50	Randall Perkins	34
50	Renske Ladwig	35
50	Samuel McCain	36
50	Sarah Bell	37
50	Shanta Vollman	38
50	Stephen Stiles	39
50	Steven Markle	40
50	TJ Olson	41
50	Timothy Gates	42
50	Trenna Rajs	43
50	Vance Jones	44
50	Winston Taylor	45
60	Alexander Hunold	1
60	Bruce Ernst	2
60	David Austin	3
60	Diana Lorentz	4
60	Valli Pataballa	5
70	Hermann Baer	1
80	Alberto Errazuriz	1
80	Allan McEwen	2
80	Alyssa Hutton	3
80	Amit Banda	4
80	Charles Johnson	5
80	Christopher Olsen	6
80	Clara Vishney	7
80	Danielle Greene	8
80	David Bernstein	9
80	David Lee	10
80	Eleni Zlotkey	11
80	Elizabeth Bates	12
80	Ellen Abel	13
80	Gerald Cambrault	14
80	Harrison Bloom	15
80	Jack Livingston	16
80	Janette King	17
80	John Russell	18
80	Jonathon Taylor	19
80	Karen Partners	20
80	Lindsey Smith	21
80	Lisa Ozer	22
80	Louise Doran	23
80	Mattea Marvins	24
80	Nanette Cambrault	25
80	Oliver Tuvault	26
80	Patrick Sully	27
80	Peter Hall	28
80	Peter Tucker	29
80	Sarath Sewall	30
80	Sundar Ande	31
80	Sundita Kumar	32
80	Tayler Fox	33
80	William Smith	34
90	Lex De Haan	1
90	Neena Kochhar	2
90	Steven King	3
100	Daniel Faviet	1
100	Ismael Sciarra	2
100	John Chen	3
100	Jose Manuel Urman	4
100	Luis Popp	5
100	Nancy Greenberg	6
110	Shelley Higgins	1
110	William Gietz	2
None	Kimberely Grant	1

RANK(), DENSE_RANK()

%%sql

SELECT 
    department_id
    , emp_name
    , salary
    , RANK() OVER (PARTITION BY department_id
                   ORDER BY salary) dep_rank
    , DENSE_RANK() OVER (PARTITION BY department_id
                         ORDER BY salary) dep_denserank
FROM employees

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

department_id	emp_name	salary	dep_rank	dep_denserank
10	Jennifer Whalen	4400	1	1
20	Pat Fay	6000	1	1
20	Michael Hartstein	13000	2	2
30	Karen Colmenares	2500	1	1
30	Guy Himuro	2600	2	2
30	Sigal Tobias	2800	3	3
30	Shelli Baida	2900	4	4
30	Alexander Khoo	3100	5	5
30	Den Raphaely	11000	6	6
40	Susan Mavris	6500	1	1
50	TJ Olson	2100	1	1
50	Steven Markle	2200	2	2
50	Hazel Philtanker	2200	2	2
50	Ki Gee	2400	4	3
50	James Landry	2400	4	3
50	Randall Perkins	2500	6	4
50	Martha Sullivan	2500	6	4
50	Joshua Patel	2500	6	4
50	Peter Vargas	2500	6	4
50	James Marlow	2500	6	4
50	Donald OConnell	2600	11	5
50	Douglas Grant	2600	11	5
50	Randall Matos	2600	11	5
50	John Seo	2700	14	6
50	Irene Mikkilineni	2700	14	6
50	Mozhe Atkinson	2800	16	7
50	Vance Jones	2800	16	7
50	Girard Geoni	2800	16	7
50	Michael Rogers	2900	19	8
50	Timothy Gates	2900	19	8
50	Kevin Feeney	3000	21	9
50	Anthony Cabrio	3000	21	9
50	Jean Fleaur	3100	23	10
50	Curtis Davies	3100	23	10
50	Alana Walsh	3100	23	10
50	Stephen Stiles	3200	26	11
50	Winston Taylor	3200	26	11
50	Julia Nayer	3200	26	11
50	Samuel McCain	3200	26	11
50	Laura Bissot	3300	30	12
50	Jason Mallin	3300	30	12
50	Julia Dellinger	3400	32	13
50	Trenna Rajs	3500	33	14
50	Renske Ladwig	3600	34	15
50	Jennifer Dilly	3600	34	15
50	Kelly Chung	3800	36	16
50	Britney Everett	3900	37	17
50	Sarah Bell	4000	38	18
50	Alexis Bull	4100	39	19
50	Nandita Sarchand	4200	40	20
50	Kevin Mourgos	5800	41	21
50	Shanta Vollman	6500	42	22
50	Payam Kaufling	7900	43	23
50	Matthew Weiss	8000	44	24
50	Adam Fripp	8200	45	25
60	Diana Lorentz	4200	1	1
60	Valli Pataballa	4800	2	2
60	David Austin	4800	2	2
60	Bruce Ernst	6000	4	3
60	Alexander Hunold	9000	5	4
70	Hermann Baer	10000	1	1
80	Sundita Kumar	6100	1	1
80	Charles Johnson	6200	2	2
80	Amit Banda	6200	2	2
80	Sundar Ande	6400	4	3
80	David Lee	6800	5	4
80	Oliver Tuvault	7000	6	5
80	Sarath Sewall	7000	6	5
80	Mattea Marvins	7200	8	6
80	Elizabeth Bates	7300	9	7
80	William Smith	7400	10	8
80	Nanette Cambrault	7500	11	9
80	Louise Doran	7500	11	9
80	Lindsey Smith	8000	13	10
80	Christopher Olsen	8000	13	10
80	Jack Livingston	8400	15	11
80	Jonathon Taylor	8600	16	12
80	Alyssa Hutton	8800	17	13
80	Allan McEwen	9000	18	14
80	Peter Hall	9000	18	14
80	Danielle Greene	9500	20	15
80	Patrick Sully	9500	20	15
80	David Bernstein	9500	20	15
80	Tayler Fox	9600	23	16
80	Janette King	10000	24	17
80	Peter Tucker	10000	24	17
80	Harrison Bloom	10000	24	17
80	Eleni Zlotkey	10500	27	18
80	Clara Vishney	10500	27	18
80	Gerald Cambrault	11000	29	19
80	Ellen Abel	11000	29	19
80	Lisa Ozer	11500	31	20
80	Alberto Errazuriz	12000	32	21
80	Karen Partners	13500	33	22
80	John Russell	14000	34	23
90	Lex De Haan	17000	1	1
90	Neena Kochhar	17000	1	1
90	Steven King	24000	3	2
100	Luis Popp	6900	1	1
100	Ismael Sciarra	7700	2	2
100	Jose Manuel Urman	7800	3	3
100	John Chen	8200	4	4
100	Daniel Faviet	9000	5	5
100	Nancy Greenberg	12008	6	6
110	William Gietz	8300	1	1
110	Shelley Higgins	12008	2	2
None	Kimberely Grant	7000	1	1

CUME_DIST & PERCENT_RANK
CUME_DIST : 주어진 그룹에 대한 상대 누적 분포도값
분포도 값(비율) 반환 값의 범위 0초와 1이하 사이의 값 반환

%%sql

SELECT
    department_id
    , emp_name
    , CUME_DIST() over (PARTITION BY department_id
                        ORDER BY salary) dep_dist
FROM employees

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

department_id	emp_name	dep_dist
10	Jennifer Whalen	1
20	Pat Fay	0.5
20	Michael Hartstein	1
30	Karen Colmenares	0.1666666666666666666666666666666666666667
30	Guy Himuro	0.3333333333333333333333333333333333333333
30	Sigal Tobias	0.5
30	Shelli Baida	0.6666666666666666666666666666666666666667
30	Alexander Khoo	0.8333333333333333333333333333333333333333
30	Den Raphaely	1
40	Susan Mavris	1
50	TJ Olson	0.0222222222222222222222222222222222222222
50	Steven Markle	0.0666666666666666666666666666666666666667
50	Hazel Philtanker	0.0666666666666666666666666666666666666667
50	Ki Gee	0.1111111111111111111111111111111111111111
50	James Landry	0.1111111111111111111111111111111111111111
50	Randall Perkins	0.2222222222222222222222222222222222222222
50	Martha Sullivan	0.2222222222222222222222222222222222222222
50	Joshua Patel	0.2222222222222222222222222222222222222222
50	Peter Vargas	0.2222222222222222222222222222222222222222
50	James Marlow	0.2222222222222222222222222222222222222222
50	Donald OConnell	0.2888888888888888888888888888888888888889
50	Douglas Grant	0.2888888888888888888888888888888888888889
50	Randall Matos	0.2888888888888888888888888888888888888889
50	John Seo	0.3333333333333333333333333333333333333333
50	Irene Mikkilineni	0.3333333333333333333333333333333333333333
50	Mozhe Atkinson	0.4
50	Vance Jones	0.4
50	Girard Geoni	0.4
50	Michael Rogers	0.4444444444444444444444444444444444444444
50	Timothy Gates	0.4444444444444444444444444444444444444444
50	Kevin Feeney	0.4888888888888888888888888888888888888889
50	Anthony Cabrio	0.4888888888888888888888888888888888888889
50	Jean Fleaur	0.5555555555555555555555555555555555555556
50	Curtis Davies	0.5555555555555555555555555555555555555556
50	Alana Walsh	0.5555555555555555555555555555555555555556
50	Stephen Stiles	0.6444444444444444444444444444444444444444
50	Winston Taylor	0.6444444444444444444444444444444444444444
50	Julia Nayer	0.6444444444444444444444444444444444444444
50	Samuel McCain	0.6444444444444444444444444444444444444444
50	Laura Bissot	0.6888888888888888888888888888888888888889
50	Jason Mallin	0.6888888888888888888888888888888888888889
50	Julia Dellinger	0.7111111111111111111111111111111111111111
50	Trenna Rajs	0.7333333333333333333333333333333333333333
50	Renske Ladwig	0.7777777777777777777777777777777777777778
50	Jennifer Dilly	0.7777777777777777777777777777777777777778
50	Kelly Chung	0.8
50	Britney Everett	0.8222222222222222222222222222222222222222
50	Sarah Bell	0.8444444444444444444444444444444444444444
50	Alexis Bull	0.8666666666666666666666666666666666666667
50	Nandita Sarchand	0.8888888888888888888888888888888888888889
50	Kevin Mourgos	0.9111111111111111111111111111111111111111
50	Shanta Vollman	0.9333333333333333333333333333333333333333
50	Payam Kaufling	0.9555555555555555555555555555555555555556
50	Matthew Weiss	0.9777777777777777777777777777777777777778
50	Adam Fripp	1
60	Diana Lorentz	0.2
60	Valli Pataballa	0.6
60	David Austin	0.6
60	Bruce Ernst	0.8
60	Alexander Hunold	1
70	Hermann Baer	1
80	Sundita Kumar	0.0294117647058823529411764705882352941176
80	Charles Johnson	0.0882352941176470588235294117647058823529
80	Amit Banda	0.0882352941176470588235294117647058823529
80	Sundar Ande	0.1176470588235294117647058823529411764706
80	David Lee	0.1470588235294117647058823529411764705882
80	Oliver Tuvault	0.2058823529411764705882352941176470588235
80	Sarath Sewall	0.2058823529411764705882352941176470588235
80	Mattea Marvins	0.2352941176470588235294117647058823529412
80	Elizabeth Bates	0.2647058823529411764705882352941176470588
80	William Smith	0.2941176470588235294117647058823529411765
80	Nanette Cambrault	0.3529411764705882352941176470588235294118
80	Louise Doran	0.3529411764705882352941176470588235294118
80	Lindsey Smith	0.4117647058823529411764705882352941176471
80	Christopher Olsen	0.4117647058823529411764705882352941176471
80	Jack Livingston	0.4411764705882352941176470588235294117647
80	Jonathon Taylor	0.4705882352941176470588235294117647058824
80	Alyssa Hutton	0.5
80	Allan McEwen	0.5588235294117647058823529411764705882353
80	Peter Hall	0.5588235294117647058823529411764705882353
80	Danielle Greene	0.6470588235294117647058823529411764705882
80	Patrick Sully	0.6470588235294117647058823529411764705882
80	David Bernstein	0.6470588235294117647058823529411764705882
80	Tayler Fox	0.6764705882352941176470588235294117647059
80	Janette King	0.7647058823529411764705882352941176470588
80	Peter Tucker	0.7647058823529411764705882352941176470588
80	Harrison Bloom	0.7647058823529411764705882352941176470588
80	Eleni Zlotkey	0.8235294117647058823529411764705882352941
80	Clara Vishney	0.8235294117647058823529411764705882352941
80	Gerald Cambrault	0.8823529411764705882352941176470588235294
80	Ellen Abel	0.8823529411764705882352941176470588235294
80	Lisa Ozer	0.9117647058823529411764705882352941176471
80	Alberto Errazuriz	0.9411764705882352941176470588235294117647
80	Karen Partners	0.9705882352941176470588235294117647058824
80	John Russell	1
90	Lex De Haan	0.6666666666666666666666666666666666666667
90	Neena Kochhar	0.6666666666666666666666666666666666666667
90	Steven King	1
100	Luis Popp	0.1666666666666666666666666666666666666667
100	Ismael Sciarra	0.3333333333333333333333333333333333333333
100	Jose Manuel Urman	0.5
100	John Chen	0.6666666666666666666666666666666666666667
100	Daniel Faviet	0.8333333333333333333333333333333333333333
100	Nancy Greenberg	1
110	William Gietz	0.5
110	Shelley Higgins	1
None	Kimberely Grant	1

NTILE 함수
NTILE(4) 값을 4등분 함

%%sql

SELECT 
    department_id
    , emp_name
    , salary
    , NTILE(4) OVER (PARTITION BY department_id
                     ORDER BY salary) NTILES
FROM employees
WHERE department_id IN (30, 60)

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

department_id	emp_name	salary	ntiles
30	Karen Colmenares	2500	1
30	Guy Himuro	2600	1
30	Sigal Tobias	2800	2
30	Shelli Baida	2900	2
30	Alexander Khoo	3100	3
30	Den Raphaely	11000	4
60	Diana Lorentz	4200	1
60	Valli Pataballa	4800	1
60	David Austin	4800	2
60	Bruce Ernst	6000	3
60	Alexander Hunold	9000	4

LAG : 선행 로우의 값을 참조한다.
LEAD : 후행 로우의 값 참조

%%sql

SELECT 
    emp_name
    , hire_date
    , salary
    , LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
    , LEAD(salary, 1, 0) OVER (ORDER BY hire_date) AS next_sal
FROM employees
WHERE department_id = 30

 * oracle://ora_user:***@127.0.0.1:1521/myoracle
0 rows affected.

emp_name	hire_date	salary	prev_sal	next_sal
Den Raphaely	2002-12-07 00:00:00	11000	0	3100
Alexander Khoo	2003-05-18 00:00:00	3100	11000	2800
Sigal Tobias	2005-07-24 00:00:00	2800	3100	2900
Shelli Baida	2005-12-24 00:00:00	2900	2800	2600
Guy Himuro	2006-11-15 00:00:00	2600	2900	2500
Karen Colmenares	2007-08-10 00:00:00	2500	2600	0

Reference : 오라클 SQL과 PL/SQL을 다루는 기술

Oracle_practice7

https://kmk3593.github.io/2022/05/14/Oracle_ch07_0428/

Author

minkuen

Posted on

2022-05-14

Updated on

2022-05-19

Licensed under

#oracle

You need to set install_url to use ShareThis. Please set it in _config.yml.

Oracle_practice7

Author

Posted on

Updated on

Licensed under

Like this article? Support the author with

Comments

Links

Categories

Recents

Archives

Tags

Subscribe for updates

follow.it