03_aggregation.sql

  1-- =============================================================================
  2-- PostgreSQL 집계 함수 예제
  3-- Aggregation Functions and GROUP BY
  4-- =============================================================================
  5
  6-- 먼저 01_basic_crud.sql과 02_joins.sql을 실행하여 테이블과 데이터를 생성하세요.
  7
  8-- =============================================================================
  9-- 1. 기본 집계 함수
 10-- =============================================================================
 11
 12-- COUNT - 행 개수
 13SELECT COUNT(*) AS total_employees FROM employees;
 14
 15SELECT COUNT(email) AS employees_with_email FROM employees;  -- NULL 제외
 16
 17SELECT COUNT(DISTINCT dept_id) AS unique_departments FROM employees;
 18
 19-- SUM - 합계
 20SELECT SUM(salary) AS total_salary FROM employees;
 21
 22-- AVG - 평균
 23SELECT AVG(salary) AS average_salary FROM employees;
 24
 25SELECT ROUND(AVG(salary), 2) AS avg_salary_rounded FROM employees;
 26
 27-- MIN, MAX - 최솟값, 최댓값
 28SELECT
 29    MIN(salary) AS min_salary,
 30    MAX(salary) AS max_salary
 31FROM employees;
 32
 33SELECT
 34    MIN(hire_date) AS first_hire,
 35    MAX(hire_date) AS last_hire
 36FROM employees;
 37
 38-- 모든 집계 함수 함께 사용
 39SELECT
 40    COUNT(*) AS employee_count,
 41    SUM(salary) AS total_salary,
 42    ROUND(AVG(salary), 2) AS avg_salary,
 43    MIN(salary) AS min_salary,
 44    MAX(salary) AS max_salary,
 45    MAX(salary) - MIN(salary) AS salary_range
 46FROM employees;
 47
 48-- =============================================================================
 49-- 2. GROUP BY
 50-- =============================================================================
 51
 52-- 부서별 집계
 53SELECT
 54    dept_id,
 55    COUNT(*) AS employee_count,
 56    ROUND(AVG(salary), 2) AS avg_salary
 57FROM employees
 58WHERE dept_id IS NOT NULL
 59GROUP BY dept_id
 60ORDER BY employee_count DESC;
 61
 62-- JOIN과 함께 사용
 63SELECT
 64    d.dept_name,
 65    COUNT(e.emp_id) AS employee_count,
 66    SUM(e.salary) AS total_salary,
 67    ROUND(AVG(e.salary), 2) AS avg_salary
 68FROM departments d
 69LEFT JOIN employees e ON d.dept_id = e.dept_id
 70GROUP BY d.dept_id, d.dept_name
 71ORDER BY total_salary DESC NULLS LAST;
 72
 73-- 여러 컬럼으로 그룹화
 74SELECT
 75    dept_id,
 76    is_active,
 77    COUNT(*) AS employee_count
 78FROM employees
 79GROUP BY dept_id, is_active
 80ORDER BY dept_id, is_active;
 81
 82-- 표현식으로 그룹화
 83SELECT
 84    EXTRACT(YEAR FROM hire_date) AS hire_year,
 85    COUNT(*) AS hire_count
 86FROM employees
 87GROUP BY EXTRACT(YEAR FROM hire_date)
 88ORDER BY hire_year;
 89
 90-- =============================================================================
 91-- 3. HAVING - 그룹 필터링
 92-- =============================================================================
 93
 94-- 직원이 2명 이상인 부서
 95SELECT
 96    d.dept_name,
 97    COUNT(e.emp_id) AS employee_count
 98FROM departments d
 99JOIN employees e ON d.dept_id = e.dept_id
100GROUP BY d.dept_id, d.dept_name
101HAVING COUNT(e.emp_id) >= 2
102ORDER BY employee_count DESC;
103
104-- 평균 급여가 50000 이상인 부서
105SELECT
106    d.dept_name,
107    ROUND(AVG(e.salary), 2) AS avg_salary
108FROM departments d
109JOIN employees e ON d.dept_id = e.dept_id
110GROUP BY d.dept_id, d.dept_name
111HAVING AVG(e.salary) >= 50000
112ORDER BY avg_salary DESC;
113
114-- WHERE와 HAVING 함께 사용
115SELECT
116    d.dept_name,
117    COUNT(e.emp_id) AS employee_count,
118    ROUND(AVG(e.salary), 2) AS avg_salary
119FROM departments d
120JOIN employees e ON d.dept_id = e.dept_id
121WHERE e.is_active = TRUE  -- 행 필터링 (그룹 전)
122GROUP BY d.dept_id, d.dept_name
123HAVING COUNT(e.emp_id) >= 1  -- 그룹 필터링 (그룹 후)
124ORDER BY avg_salary DESC;
125
126-- =============================================================================
127-- 4. 고급 집계 함수
128-- =============================================================================
129
130-- STRING_AGG - 문자열 연결
131SELECT
132    d.dept_name,
133    STRING_AGG(e.first_name || ' ' || e.last_name, ', ' ORDER BY e.first_name) AS employee_names
134FROM departments d
135JOIN employees e ON d.dept_id = e.dept_id
136GROUP BY d.dept_id, d.dept_name;
137
138-- ARRAY_AGG - 배열로 집계
139SELECT
140    d.dept_name,
141    ARRAY_AGG(e.first_name ORDER BY e.first_name) AS employee_names_array
142FROM departments d
143JOIN employees e ON d.dept_id = e.dept_id
144GROUP BY d.dept_id, d.dept_name;
145
146-- JSON_AGG - JSON 배열로 집계
147SELECT
148    d.dept_name,
149    JSON_AGG(
150        JSON_BUILD_OBJECT(
151            'name', e.first_name || ' ' || e.last_name,
152            'salary', e.salary
153        )
154    ) AS employees_json
155FROM departments d
156JOIN employees e ON d.dept_id = e.dept_id
157GROUP BY d.dept_id, d.dept_name;
158
159-- =============================================================================
160-- 5. ROLLUP, CUBE, GROUPING SETS
161-- =============================================================================
162
163-- ROLLUP - 계층적 소계
164SELECT
165    d.dept_name,
166    EXTRACT(YEAR FROM e.hire_date) AS hire_year,
167    COUNT(*) AS employee_count,
168    SUM(e.salary) AS total_salary
169FROM employees e
170JOIN departments d ON e.dept_id = d.dept_id
171GROUP BY ROLLUP(d.dept_name, EXTRACT(YEAR FROM e.hire_date))
172ORDER BY d.dept_name NULLS LAST, hire_year NULLS LAST;
173
174-- CUBE - 모든 가능한 조합의 소계
175SELECT
176    d.dept_name,
177    e.is_active,
178    COUNT(*) AS employee_count
179FROM employees e
180JOIN departments d ON e.dept_id = d.dept_id
181GROUP BY CUBE(d.dept_name, e.is_active)
182ORDER BY d.dept_name NULLS LAST, e.is_active NULLS LAST;
183
184-- GROUPING SETS - 특정 조합만 소계
185SELECT
186    d.dept_name,
187    EXTRACT(YEAR FROM e.hire_date) AS hire_year,
188    COUNT(*) AS employee_count
189FROM employees e
190JOIN departments d ON e.dept_id = d.dept_id
191GROUP BY GROUPING SETS (
192    (d.dept_name),
193    (EXTRACT(YEAR FROM e.hire_date)),
194    ()
195)
196ORDER BY d.dept_name NULLS LAST, hire_year NULLS LAST;
197
198-- GROUPING() 함수로 소계 행 구분
199SELECT
200    CASE WHEN GROUPING(d.dept_name) = 1 THEN 'All Departments' ELSE d.dept_name END AS dept_name,
201    COUNT(*) AS employee_count,
202    SUM(e.salary) AS total_salary
203FROM employees e
204JOIN departments d ON e.dept_id = d.dept_id
205GROUP BY ROLLUP(d.dept_name)
206ORDER BY GROUPING(d.dept_name), d.dept_name;
207
208-- =============================================================================
209-- 6. FILTER 절
210-- =============================================================================
211
212-- 조건별로 다른 집계
213SELECT
214    d.dept_name,
215    COUNT(*) AS total_count,
216    COUNT(*) FILTER (WHERE e.salary > 50000) AS high_salary_count,
217    COUNT(*) FILTER (WHERE e.salary <= 50000) AS low_salary_count,
218    ROUND(AVG(e.salary) FILTER (WHERE e.is_active = TRUE), 2) AS active_avg_salary
219FROM departments d
220JOIN employees e ON d.dept_id = e.dept_id
221GROUP BY d.dept_id, d.dept_name;
222
223-- =============================================================================
224-- 7. 통계 함수
225-- =============================================================================
226
227-- 표준편차와 분산
228SELECT
229    d.dept_name,
230    ROUND(AVG(e.salary), 2) AS avg_salary,
231    ROUND(STDDEV(e.salary), 2) AS stddev_salary,
232    ROUND(VARIANCE(e.salary), 2) AS variance_salary
233FROM departments d
234JOIN employees e ON d.dept_id = e.dept_id
235GROUP BY d.dept_id, d.dept_name
236HAVING COUNT(*) > 1;  -- 표준편차는 2개 이상 필요
237
238-- 백분위수
239SELECT
240    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
241    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS q1_salary,
242    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS q3_salary
243FROM employees;
244
245-- 최빈값 (MODE)
246SELECT
247    MODE() WITHIN GROUP (ORDER BY dept_id) AS most_common_dept
248FROM employees;
249
250-- =============================================================================
251-- 8. 조건부 집계 (CASE와 함께)
252-- =============================================================================
253
254SELECT
255    d.dept_name,
256    COUNT(*) AS total,
257    SUM(CASE WHEN e.salary >= 55000 THEN 1 ELSE 0 END) AS high_earners,
258    SUM(CASE WHEN e.salary < 55000 THEN 1 ELSE 0 END) AS others,
259    ROUND(
260        100.0 * SUM(CASE WHEN e.salary >= 55000 THEN 1 ELSE 0 END) / COUNT(*),
261        1
262    ) AS high_earner_pct
263FROM departments d
264JOIN employees e ON d.dept_id = e.dept_id
265GROUP BY d.dept_id, d.dept_name;
266
267-- =============================================================================
268-- 집계 함수 요약
269-- =============================================================================
270/*
271기본 집계:
272- COUNT(*), COUNT(col), COUNT(DISTINCT col)
273- SUM(col), AVG(col)
274- MIN(col), MAX(col)
275
276문자열/배열 집계:
277- STRING_AGG(col, delimiter)
278- ARRAY_AGG(col)
279- JSON_AGG(value)
280
281그룹화:
282- GROUP BY: 기본 그룹화
283- HAVING: 그룹 필터링 (집계 후)
284- ROLLUP: 계층적 소계
285- CUBE: 모든 조합 소계
286- GROUPING SETS: 특정 조합 소계
287
288고급:
289- FILTER (WHERE ...): 조건부 집계
290- PERCENTILE_CONT(): 백분위수
291- STDDEV(), VARIANCE(): 통계
292
293순서:
294SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
295*/