05_window_functions.sql

Download
sql 344 lines 9.7 KB 
  1-- =============================================================================
  2-- PostgreSQL μœˆλ„μš° ν•¨μˆ˜ 예제
  3-- Window Functions (Analytic Functions)
  4-- =============================================================================
  5
  6-- λ¨Όμ € 이전 예제 νŒŒμΌλ“€μ„ μ‹€ν–‰ν•˜μ—¬ ν…Œμ΄λΈ”κ³Ό 데이터λ₯Ό μƒμ„±ν•˜μ„Έμš”.
  7
  8-- =============================================================================
  9-- 1. κΈ°λ³Έ μœˆλ„μš° ν•¨μˆ˜ ꡬ쑰
 10-- =============================================================================
 11
 12-- OVER() - 전체 ν…Œμ΄λΈ”μ„ ν•˜λ‚˜μ˜ μœˆλ„μš°λ‘œ
 13SELECT
 14    first_name,
 15    last_name,
 16    salary,
 17    SUM(salary) OVER() AS total_salary,
 18    ROUND(AVG(salary) OVER(), 2) AS avg_salary,
 19    COUNT(*) OVER() AS total_count
 20FROM employees;
 21
 22-- =============================================================================
 23-- 2. PARTITION BY - 그룹별 μœˆλ„μš°
 24-- =============================================================================
 25
 26-- λΆ€μ„œλ³„λ‘œ νŒŒν‹°μ…˜ λ‚˜λˆ„κΈ°
 27SELECT
 28    e.first_name,
 29    e.last_name,
 30    d.dept_name,
 31    e.salary,
 32    SUM(e.salary) OVER(PARTITION BY e.dept_id) AS dept_total,
 33    ROUND(AVG(e.salary) OVER(PARTITION BY e.dept_id), 2) AS dept_avg,
 34    COUNT(*) OVER(PARTITION BY e.dept_id) AS dept_count
 35FROM employees e
 36JOIN departments d ON e.dept_id = d.dept_id
 37ORDER BY d.dept_name, e.salary DESC;
 38
 39-- λΆ€μ„œλ³„ κΈ‰μ—¬ λΉ„μœ¨
 40SELECT
 41    e.first_name,
 42    e.last_name,
 43    d.dept_name,
 44    e.salary,
 45    SUM(e.salary) OVER(PARTITION BY e.dept_id) AS dept_total,
 46    ROUND(100.0 * e.salary / SUM(e.salary) OVER(PARTITION BY e.dept_id), 2) AS salary_pct
 47FROM employees e
 48JOIN departments d ON e.dept_id = d.dept_id
 49ORDER BY d.dept_name, salary_pct DESC;
 50
 51-- =============================================================================
 52-- 3. μˆœμœ„ ν•¨μˆ˜ (Ranking Functions)
 53-- =============================================================================
 54
 55-- ROW_NUMBER: 연속 번호 (λ™μ μžλ„ λ‹€λ₯Έ 번호)
 56-- RANK: λ™μ μžλŠ” 같은 μˆœμœ„, λ‹€μŒ μˆœμœ„ κ±΄λ„ˆλœ€
 57-- DENSE_RANK: λ™μ μžλŠ” 같은 μˆœμœ„, λ‹€μŒ μˆœμœ„ 연속
 58
 59SELECT
 60    first_name,
 61    last_name,
 62    salary,
 63    ROW_NUMBER() OVER(ORDER BY salary DESC) AS row_num,
 64    RANK() OVER(ORDER BY salary DESC) AS rank,
 65    DENSE_RANK() OVER(ORDER BY salary DESC) AS dense_rank
 66FROM employees;
 67
 68-- λΆ€μ„œλ³„ κΈ‰μ—¬ μˆœμœ„
 69SELECT
 70    e.first_name,
 71    e.last_name,
 72    d.dept_name,
 73    e.salary,
 74    RANK() OVER(PARTITION BY e.dept_id ORDER BY e.salary DESC) AS dept_salary_rank
 75FROM employees e
 76JOIN departments d ON e.dept_id = d.dept_id
 77ORDER BY d.dept_name, dept_salary_rank;
 78
 79-- λΆ€μ„œλ³„ Top 2 κΈ‰μ—¬μž
 80SELECT * FROM (
 81    SELECT
 82        e.first_name,
 83        e.last_name,
 84        d.dept_name,
 85        e.salary,
 86        ROW_NUMBER() OVER(PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn
 87    FROM employees e
 88    JOIN departments d ON e.dept_id = d.dept_id
 89) ranked
 90WHERE rn <= 2;
 91
 92-- NTILE: N개의 λ²„ν‚·μœΌλ‘œ λΆ„ν• 
 93SELECT
 94    first_name,
 95    last_name,
 96    salary,
 97    NTILE(4) OVER(ORDER BY salary DESC) AS salary_quartile
 98FROM employees;
 99
100-- =============================================================================
101-- 4. μ˜€ν”„μ…‹ ν•¨μˆ˜ (LAG, LEAD, FIRST_VALUE, LAST_VALUE)
102-- =============================================================================
103
104-- LAG: 이전 ν–‰ κ°’
105-- LEAD: λ‹€μŒ ν–‰ κ°’
106SELECT
107    first_name,
108    hire_date,
109    LAG(first_name, 1) OVER(ORDER BY hire_date) AS prev_hire,
110    LEAD(first_name, 1) OVER(ORDER BY hire_date) AS next_hire
111FROM employees
112ORDER BY hire_date;
113
114-- κΈ‰μ—¬ λ³€ν™” 뢄석
115SELECT
116    first_name,
117    salary,
118    LAG(salary) OVER(ORDER BY emp_id) AS prev_salary,
119    salary - LAG(salary) OVER(ORDER BY emp_id) AS salary_diff
120FROM employees;
121
122-- λΆ€μ„œ λ‚΄ 이전/λ‹€μŒ 직원
123SELECT
124    e.first_name,
125    d.dept_name,
126    e.salary,
127    LAG(e.salary) OVER(PARTITION BY e.dept_id ORDER BY e.salary) AS lower_salary,
128    LEAD(e.salary) OVER(PARTITION BY e.dept_id ORDER BY e.salary) AS higher_salary
129FROM employees e
130JOIN departments d ON e.dept_id = d.dept_id
131ORDER BY d.dept_name, e.salary;
132
133-- FIRST_VALUE, LAST_VALUE
134SELECT
135    e.first_name,
136    d.dept_name,
137    e.salary,
138    FIRST_VALUE(e.first_name) OVER(
139        PARTITION BY e.dept_id ORDER BY e.salary DESC
140    ) AS highest_paid,
141    LAST_VALUE(e.first_name) OVER(
142        PARTITION BY e.dept_id ORDER BY e.salary DESC
143        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
144    ) AS lowest_paid
145FROM employees e
146JOIN departments d ON e.dept_id = d.dept_id;
147
148-- =============================================================================
149-- 5. 집계 μœˆλ„μš° ν•¨μˆ˜
150-- =============================================================================
151
152-- λˆ„μ  합계 (Running Total)
153SELECT
154    first_name,
155    hire_date,
156    salary,
157    SUM(salary) OVER(ORDER BY hire_date) AS running_total
158FROM employees
159ORDER BY hire_date;
160
161-- λΆ€μ„œλ³„ λˆ„μ  합계
162SELECT
163    e.first_name,
164    d.dept_name,
165    e.salary,
166    SUM(e.salary) OVER(
167        PARTITION BY e.dept_id
168        ORDER BY e.emp_id
169    ) AS dept_running_total
170FROM employees e
171JOIN departments d ON e.dept_id = d.dept_id
172ORDER BY d.dept_name, e.emp_id;
173
174-- 이동 평균 (Moving Average)
175SELECT
176    first_name,
177    salary,
178    ROUND(AVG(salary) OVER(
179        ORDER BY emp_id
180        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
181    ), 2) AS moving_avg_3
182FROM employees;
183
184-- =============================================================================
185-- 6. ν”„λ ˆμž„ 절 (Frame Specification)
186-- =============================================================================
187
188-- ROWS: 물리적 ν–‰ κΈ°μ€€
189-- RANGE: 논리적 κ°’ κΈ°μ€€
190
191-- ν˜„μž¬ ν–‰ κΈ°μ€€ μ•žλ’€ 1ν–‰
192SELECT
193    first_name,
194    salary,
195    AVG(salary) OVER(
196        ORDER BY salary
197        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
198    ) AS avg_neighbors
199FROM employees;
200
201-- μ²˜μŒλΆ€ν„° ν˜„μž¬ ν–‰κΉŒμ§€
202SELECT
203    first_name,
204    salary,
205    MAX(salary) OVER(
206        ORDER BY emp_id
207        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
208    ) AS max_so_far
209FROM employees;
210
211-- ν˜„μž¬ ν–‰λΆ€ν„° λκΉŒμ§€
212SELECT
213    first_name,
214    salary,
215    COUNT(*) OVER(
216        ORDER BY emp_id
217        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
218    ) AS remaining_count
219FROM employees;
220
221-- =============================================================================
222-- 7. λ°±λΆ„μœ„μˆ˜μ™€ 뢄포 ν•¨μˆ˜
223-- =============================================================================
224
225-- PERCENT_RANK: 0~1 μ‚¬μ΄μ˜ μƒλŒ€μ  μˆœμœ„
226-- CUME_DIST: λˆ„μ  뢄포
227
228SELECT
229    first_name,
230    salary,
231    ROUND(PERCENT_RANK() OVER(ORDER BY salary)::numeric, 4) AS pct_rank,
232    ROUND(CUME_DIST() OVER(ORDER BY salary)::numeric, 4) AS cumulative_dist
233FROM employees;
234
235-- λΆ€μ„œ λ‚΄ λ°±λΆ„μœ„
236SELECT
237    e.first_name,
238    d.dept_name,
239    e.salary,
240    ROUND(PERCENT_RANK() OVER(
241        PARTITION BY e.dept_id ORDER BY e.salary
242    )::numeric, 4) AS dept_percentile
243FROM employees e
244JOIN departments d ON e.dept_id = d.dept_id;
245
246-- =============================================================================
247-- 8. μ‹€μ „ 예제
248-- =============================================================================
249
250-- κΈ‰μ—¬ 톡계 μ’…ν•©
251SELECT
252    e.first_name,
253    e.last_name,
254    d.dept_name,
255    e.salary,
256    -- λΆ€μ„œ 톡계
257    ROUND(AVG(e.salary) OVER(PARTITION BY e.dept_id), 2) AS dept_avg,
258    MIN(e.salary) OVER(PARTITION BY e.dept_id) AS dept_min,
259    MAX(e.salary) OVER(PARTITION BY e.dept_id) AS dept_max,
260    -- 전사 톡계
261    ROUND(AVG(e.salary) OVER(), 2) AS company_avg,
262    -- μˆœμœ„
263    RANK() OVER(PARTITION BY e.dept_id ORDER BY e.salary DESC) AS dept_rank,
264    RANK() OVER(ORDER BY e.salary DESC) AS company_rank,
265    -- 전사 λŒ€λΉ„ λΉ„μœ¨
266    ROUND(100.0 * e.salary / SUM(e.salary) OVER(), 2) AS company_pct
267FROM employees e
268JOIN departments d ON e.dept_id = d.dept_id
269ORDER BY d.dept_name, e.salary DESC;
270
271-- 연속 고용 뢄석 (μž…μ‚¬μΌ κΈ°μ€€ 간격)
272SELECT
273    first_name,
274    hire_date,
275    LAG(hire_date) OVER(ORDER BY hire_date) AS prev_hire_date,
276    hire_date - LAG(hire_date) OVER(ORDER BY hire_date) AS days_since_last_hire
277FROM employees
278ORDER BY hire_date;
279
280-- κΈ‰μ—¬ λŒ€μ—­ λΆ„λ₯˜
281SELECT
282    first_name,
283    salary,
284    CASE NTILE(3) OVER(ORDER BY salary)
285        WHEN 1 THEN 'Low'
286        WHEN 2 THEN 'Medium'
287        WHEN 3 THEN 'High'
288    END AS salary_band
289FROM employees;
290
291-- =============================================================================
292-- 9. μœˆλ„μš° ν•¨μˆ˜ 별칭 (WINDOW 절)
293-- =============================================================================
294
295-- 같은 μœˆλ„μš° μ •μ˜ μž¬μ‚¬μš©
296SELECT
297    first_name,
298    salary,
299    SUM(salary) OVER w AS running_total,
300    AVG(salary) OVER w AS running_avg,
301    COUNT(*) OVER w AS running_count
302FROM employees
303WINDOW w AS (ORDER BY emp_id)
304ORDER BY emp_id;
305
306-- =============================================================================
307-- μœˆλ„μš° ν•¨μˆ˜ μš”μ•½
308-- =============================================================================
309/*
310μˆœμœ„ ν•¨μˆ˜:
311- ROW_NUMBER(): 연속 번호
312- RANK(): 동점 μ‹œ 같은 μˆœμœ„, κ±΄λ„ˆλœ€
313- DENSE_RANK(): 동점 μ‹œ 같은 μˆœμœ„, 연속
314- NTILE(n): n개 λ²„ν‚·μœΌλ‘œ λΆ„ν• 
315
316μ˜€ν”„μ…‹ ν•¨μˆ˜:
317- LAG(col, n, default): nν–‰ 이전 κ°’
318- LEAD(col, n, default): nν–‰ 이후 κ°’
319- FIRST_VALUE(col): ν”„λ ˆμž„μ˜ 첫 κ°’
320- LAST_VALUE(col): ν”„λ ˆμž„μ˜ λ§ˆμ§€λ§‰ κ°’
321- NTH_VALUE(col, n): ν”„λ ˆμž„μ˜ n번째 κ°’
322
323집계 ν•¨μˆ˜:
324- SUM(), AVG(), COUNT(), MIN(), MAX() - OVER()와 ν•¨κ»˜
325
326뢄포 ν•¨μˆ˜:
327- PERCENT_RANK(): λ°±λΆ„μœ¨ μˆœμœ„ (0~1)
328- CUME_DIST(): λˆ„μ  뢄포
329
330ν”„λ ˆμž„ 절:
331- ROWS BETWEEN ... AND ...
332- RANGE BETWEEN ... AND ...
333- UNBOUNDED PRECEDING / FOLLOWING
334- CURRENT ROW
335- n PRECEDING / FOLLOWING
336
337ꡬ문:
338window_function() OVER(
339    [PARTITION BY col, ...]
340    [ORDER BY col [ASC|DESC], ...]
341    [frame_clause]
342)
343*/