Posted 2022-05-15Updated 2022-05-20 minkuen SQL / oracle20 minutes read (About 2978 words)

Oracle_practice7_2

분석함수 : RANK, DENSE_RANK, LAG, LEAD
RANK() OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명)

1	%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

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

분석 함수: 분석 함수 역시 특정 그룹별 집계를 담당하므로 집계 함수에 속한다.
PARTITION BY 절: 분석 함수로 계산될 대상 로우의 그룹(파티션)을 지정한다.
ORDER BY 절: 파티션 안에서의 순서를 지정한다.
WINDOW 절: 파티션으로 분할된 그룹에 대해서 더 상세한 그룹으로 분할할 때 사용된다. # Window 절
ROWS: 로우 단위로 window 절을 지정한다.
RANGE: 로우가 아닌 논리적인 범위로 window 절을 지정한다.
BETWEEN~AND: window 절의 시작과 끝 지점을 명시한다.
BETWEEN을 명시하지 않고 두 번째 옵션만 지정하면이 지점이 시작 지점이 되고 끝 지점은 현재 로우가 된다.
UNBOUNDED PRECEDING: 파티션으로 구분된 첫 번째 로우가 시작 지점이 된다.
UNBOUNDED FOLLOWING: 파티션으로 구분된 마지막 로우가 끝 지점이 된다.
CURRENT ROW: 시작 및 끝 지점이 현재 로우가된다.
value_expr PRECEDING: 끝 지점일 경우, 시작 지점은 value_expr PRECEDING.
value_expr FOLLOWING: 시작 지점일 경우, 끝 지점은 value_expr FOLLOWING.

%%sql

SELECT
    department_id
    , emp_name
    , hire_date
    , salary
    , SUM(salary) OVER (PARTITION BY department_id
                        ORDER BY hire_date
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS all_salary
    , SUM(salary) OVER (PARTITION BY department_id
                        ORDER BY hire_date
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_current_sal
    , SUM(salary) OVER (PARTITION BY department_id
                        ORDER BY hire_date
                        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_end_sal
FROM employees
WHERE department_id IN (30, 90)

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

department_id	emp_name	hire_date	salary	all_salary	first_current_sal	current_end_sal
30	Den Raphaely	2002-12-07 00:00:00	11000	24900	11000	24900
30	Alexander Khoo	2003-05-18 00:00:00	3100	24900	14100	13900
30	Sigal Tobias	2005-07-24 00:00:00	2800	24900	16900	10800
30	Shelli Baida	2005-12-24 00:00:00	2900	24900	19800	8000
30	Guy Himuro	2006-11-15 00:00:00	2600	24900	22400	5100
30	Karen Colmenares	2007-08-10 00:00:00	2500	24900	24900	2500
90	Lex De Haan	2001-01-13 00:00:00	17000	58000	17000	58000
90	Steven King	2003-06-17 00:00:00	24000	58000	41000	41000
90	Neena Kochhar	2005-09-21 00:00:00	17000	58000	58000	17000

%%sql

SELECT
    department_id
    , emp_name
    , hire_date
    , salary
    , FIRST_VALUE(salary) OVER (PARTITION BY department_id
                        ORDER BY hire_date
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS all_salary
    , FIRST_VALUE(salary) OVER (PARTITION BY department_id
                        ORDER BY hire_date
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_current_sal
    , FIRST_VALUE(salary) OVER (PARTITION BY department_id
                        ORDER BY hire_date
                        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_end_sal
FROM employees
WHERE department_id IN (30, 90)

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

department_id	emp_name	hire_date	salary	all_salary	first_current_sal	current_end_sal
30	Den Raphaely	2002-12-07 00:00:00	11000	11000	11000	11000
30	Alexander Khoo	2003-05-18 00:00:00	3100	11000	11000	3100
30	Sigal Tobias	2005-07-24 00:00:00	2800	11000	11000	2800
30	Shelli Baida	2005-12-24 00:00:00	2900	11000	11000	2900
30	Guy Himuro	2006-11-15 00:00:00	2600	11000	11000	2600
30	Karen Colmenares	2007-08-10 00:00:00	2500	11000	11000	2500
90	Lex De Haan	2001-01-13 00:00:00	17000	17000	17000	17000
90	Steven King	2003-06-17 00:00:00	24000	17000	17000	24000
90	Neena Kochhar	2005-09-21 00:00:00	17000	17000	17000	17000

p.211
계층형 쿼리
핵심 포인트 : CONNECT BY 조건문 정리- 계층형 쿼리의 구문은 다음과 같다.

SELECT expr1, expr2, …
FROM 테이블
WHERE 조건
START WITH[최상위 조건]
CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건]; –> 구조 : CONNECT BY PRIOR 하위 = 상위

START WITH 조건
- 계층형 구조에서 최상위 계층의 로우를 식별하는 조건을 명시한다.
- START WITH가 시작한다는 의미이므로, 이 조건에 맞는 로우부터 시작해 계층형 구조를 풀어 나간다.
CONNECT BY 조건
- 계층형 구조가 어떤 식으로 연결되는지를 기술하는 부분이다.
- 부서 테이블은 parent_id에 상위 부서 정보를 갖고 있는데,
  이를 표현하려면 ‘CONNECT BY PRIOR department_id = parent_id’로 기술해야 한다.
- PRIOR는 계층형 쿼리에서만 사용할 수 있는 연산자로 ‘앞서의, 직전의’란 뜻이 있으므로,
  “이전 department_id = parent_id”라고 알아두면 이해하기 쉬울 것이다.
- 또한 ‘CONNECT BY parent_id = PRIOR department_id’처럼 PRIOR의 위치를 바꿀 수 있다.

%%sql

SELECT
    department_id
    , LPAD(' ', 3*(LEVEL-1)) || department_name
    , LEVEL
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR department_id = parent_id

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

department_id	LPAD('',3*(LEVEL-1))\|\|DEPARTMENT_NAME	LEVEL
10	총무기획부	1
20	마케팅	2
30	구매/생산부	2
170	생산팀	3
180	건설팀	3
200	운영팀	3
210	IT 지원	3
220	NOC	3
40	인사부	2
260	채용팀	3
50	배송부	2
80	영업부	2
190	계약팀	3
240	공공 판매사업팀	3
250	판매팀	3
90	기획부	2
60	IT	3
230	IT 헬프데스크	4
70	홍보부	3
100	자금부	3
130	세무팀	4
140	신용관리팀	4
150	주식관리팀	4
160	수익관리팀	4
110	경리부	3
120	재무팀	4
270	급여팀	4

manager_id는 해당 사원의 매니저 사번이 있다.
각 매니저는 사원 ID가 있다.
사원별 계층 구조를 만들고, 부서 테이블과 조인해서 부서명까지 조회한다.

%%sql

SELECT 
    a.employee_id
    , LPAD(' ', 3 * (LEVEL - 1)) || a.emp_name
    , LEVEL
    , b.department_name
FROM
    employees a
    , departments b
WHERE a.department_id = b.department_id
START WITH a.manager_id IS NULL
CONNECT BY PRIOR a.employee_id = a.manager_id

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

employee_id	LPAD('',3*(LEVEL-1))\|\|A.EMP_NAME	LEVEL	department_name
100	Steven King	1	기획부
101	Neena Kochhar	2	기획부
108	Nancy Greenberg	3	자금부
109	Daniel Faviet	4	자금부
110	John Chen	4	자금부
111	Ismael Sciarra	4	자금부
112	Jose Manuel Urman	4	자금부
113	Luis Popp	4	자금부
200	Jennifer Whalen	3	총무기획부
203	Susan Mavris	3	인사부
204	Hermann Baer	3	홍보부
205	Shelley Higgins	3	경리부
206	William Gietz	4	경리부
102	Lex De Haan	2	기획부
103	Alexander Hunold	3	IT
104	Bruce Ernst	4	IT
105	David Austin	4	IT
106	Valli Pataballa	4	IT
107	Diana Lorentz	4	IT
114	Den Raphaely	2	구매/생산부
115	Alexander Khoo	3	구매/생산부
116	Shelli Baida	3	구매/생산부
117	Sigal Tobias	3	구매/생산부
118	Guy Himuro	3	구매/생산부
119	Karen Colmenares	3	구매/생산부
120	Matthew Weiss	2	배송부
125	Julia Nayer	3	배송부
126	Irene Mikkilineni	3	배송부
127	James Landry	3	배송부
128	Steven Markle	3	배송부
180	Winston Taylor	3	배송부
181	Jean Fleaur	3	배송부
182	Martha Sullivan	3	배송부
183	Girard Geoni	3	배송부
121	Adam Fripp	2	배송부
129	Laura Bissot	3	배송부
130	Mozhe Atkinson	3	배송부
131	James Marlow	3	배송부
132	TJ Olson	3	배송부
184	Nandita Sarchand	3	배송부
185	Alexis Bull	3	배송부
186	Julia Dellinger	3	배송부
187	Anthony Cabrio	3	배송부
122	Payam Kaufling	2	배송부
133	Jason Mallin	3	배송부
134	Michael Rogers	3	배송부
135	Ki Gee	3	배송부
136	Hazel Philtanker	3	배송부
188	Kelly Chung	3	배송부
189	Jennifer Dilly	3	배송부
190	Timothy Gates	3	배송부
191	Randall Perkins	3	배송부
123	Shanta Vollman	2	배송부
137	Renske Ladwig	3	배송부
138	Stephen Stiles	3	배송부
139	John Seo	3	배송부
140	Joshua Patel	3	배송부
192	Sarah Bell	3	배송부
193	Britney Everett	3	배송부
194	Samuel McCain	3	배송부
195	Vance Jones	3	배송부
124	Kevin Mourgos	2	배송부
141	Trenna Rajs	3	배송부
142	Curtis Davies	3	배송부
143	Randall Matos	3	배송부
144	Peter Vargas	3	배송부
196	Alana Walsh	3	배송부
197	Kevin Feeney	3	배송부
198	Donald OConnell	3	배송부
199	Douglas Grant	3	배송부
145	John Russell	2	영업부
150	Peter Tucker	3	영업부
151	David Bernstein	3	영업부
152	Peter Hall	3	영업부
153	Christopher Olsen	3	영업부
154	Nanette Cambrault	3	영업부
155	Oliver Tuvault	3	영업부
146	Karen Partners	2	영업부
156	Janette King	3	영업부
157	Patrick Sully	3	영업부
158	Allan McEwen	3	영업부
159	Lindsey Smith	3	영업부
160	Louise Doran	3	영업부
161	Sarath Sewall	3	영업부
147	Alberto Errazuriz	2	영업부
162	Clara Vishney	3	영업부
163	Danielle Greene	3	영업부
164	Mattea Marvins	3	영업부
165	David Lee	3	영업부
166	Sundar Ande	3	영업부
167	Amit Banda	3	영업부
148	Gerald Cambrault	2	영업부
168	Lisa Ozer	3	영업부
169	Harrison Bloom	3	영업부
170	Tayler Fox	3	영업부
171	William Smith	3	영업부
172	Elizabeth Bates	3	영업부
173	Sundita Kumar	3	영업부
149	Eleni Zlotkey	2	영업부
174	Ellen Abel	3	영업부
175	Alyssa Hutton	3	영업부
176	Jonathon Taylor	3	영업부
177	Jack Livingston	3	영업부
179	Charles Johnson	3	영업부
201	Michael Hartstein	2	마케팅
202	Pat Fay	3	마케팅

CONNECT BY ~ AND : AND를 통해 추가로 조건을 붙인다.

%%sql

SELECT 
    a.employee_id
    , LPAD(' ', 3 * (LEVEL - 1)) || a.emp_name
    , LEVEL
    , b.department_name
FROM
    employees a
    , departments b
WHERE a.department_id = b.department_id
START WITH a.manager_id IS NULL
CONNECT BY PRIOR a.employee_id = a.manager_id
    AND a.department_id = 30

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

employee_id	LPAD('',3*(LEVEL-1))\|\|A.EMP_NAME	LEVEL	department_name
100	Steven King	1	기획부
114	Den Raphaely	2	구매/생산부
115	Alexander Khoo	3	구매/생산부
116	Shelli Baida	3	구매/생산부
117	Sigal Tobias	3	구매/생산부
118	Guy Himuro	3	구매/생산부
119	Karen Colmenares	3	구매/생산부

CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리 구성 (TOP DOWN) -> 일반적인 구조
CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom UP)
CONNECT BY NOCYCLE PRIOR = 무한루프 방지- p.226 WITH 절
kor_loan_status 테이블에서 연도별 최종월 기준 가장 대출이 많은 도시와 잔액을 구한다.

%%sql

SELECT b2.*
FROM ( SELECT period, region, sum(loan_jan_amt) jan_amt
         FROM kor_loan_status 
         GROUP BY period, region
      ) b2,      
      ( SELECT b.period,  MAX(b.jan_amt) max_jan_amt
         FROM ( SELECT period, region, sum(loan_jan_amt) jan_amt
                  FROM kor_loan_status 
                 GROUP BY period, region
              ) b,
              ( SELECT MAX(PERIOD) max_month
                  FROM kor_loan_status
                 GROUP BY SUBSTR(PERIOD, 1, 4)
              ) a
         WHERE b.period = a.max_month
         GROUP BY b.period
      ) c   
 WHERE b2.period = c.period
   AND b2.jan_amt = c.max_jan_amt
 ORDER BY 1

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

period	region	jan_amt
201112	서울	334728.3
201212	서울	331572.3
201311	서울	334062.7

1	- WITH 절은 별칭으로 사용한 SELECT 문의 FROM 절에 다른 SELECT 구문의 별칭 참조가 가능하다.

WITH 동일 구문 반복 사용 시

%%sql

WITH b2 AS (SELECT period, region, sum(loan_jan_amt) jan_amt
         FROM kor_loan_status 
         GROUP BY period, region)
     , c AS (SELECT b.period, MAX(b.jan_amt) max_jan_amt
             FROM (SELECT period, region, sum(loan_jan_amt) jan_amt
                 FROM kor_loan_status 
                 GROUP BY period, region) b
                 , (SELECT MAX(PERIOD) max_month
                    FROM kor_loan_status
                    GROUP BY SUBSTR(PERIOD, 1, 4)
                 ) a
             WHERE b.period = a.max_month
             GROUP BY b.period
           ) -- AS SELECT 
SELECT b2.*
FROM b2, c
WHERE b2.period = c.period
    AND b2.jan_amt = c.max_jan_amt
ORDER BY 1

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

period	region	jan_amt
201112	서울	334728.3
201212	서울	331572.3
201311	서울	334062.7

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

Oracle_practice7_2

https://kmk3593.github.io/2022/05/15/Oracle_ch07_0429/

Author

minkuen

Posted on

2022-05-15

Updated on

2022-05-20

Licensed under

#oracle

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

Oracle_practice7_2

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