01. 서브쿼리 (Subquery) vs 조인 (JOIN)
쿼리 안에 쿼리를 넣는 서브쿼리와, 이를 동일한 결과의 조인(JOIN)으로 변환하는 과정을 비교함.
1) WHERE 절 서브쿼리
평균값과 같이 집계된 결과를 조건으로 사용할 때 필수적임.
-- 1. 전체 평균 계란 무게 확인
select avg(egg_weight) from fms.chick_info;
-- 2. 평균보다 무거운 계란을 낳은 닭 조회 (서브쿼리)
select chick_no, egg_weight
from fms.chick_info
where egg_weight > (select avg(egg_weight) from fms.chick_info);
2) SELECT 절 서브쿼리 (스칼라 서브쿼리)
코드 테이블에서 명칭을 가져오는 등 단일 값을 매핑할 때 사용함.
-- 서브쿼리를 이용한 품종명 조회
select a.chick_no, a.breeds,
(
select m.code_desc
from fms.master_code m
where m.column_nm = 'breeds' and m.code = a.breeds
) as breeds_nm
from fms.chick_info a;
3) JOIN으로 변환하기 (성능 비교)
위의 스칼라 서브쿼리는 INNER JOIN으로 동일하게 구현 가능하며, 대량 데이터 처리 시 JOIN이 성능상 유리한 경우가 많음.
-- JOIN을 이용한 품종명 조회
select a.chick_no, a.breeds, b.code_desc as "breeds_nm"
from fms.chick_info a
inner join fms.master_code b
on a.breeds = b.code
where b.column_nm = 'breeds';
4) FROM 절 서브쿼리 (Inline View)
조회된 결과를 마치 테이블처럼 취급하여 조인함.
select a.chick_no, a.breeds, b.breeds_nm
from fms.chick_info a,
(
select code, code_desc as "breeds_nm"
from fms.master_code
where column_nm = 'breeds'
) b
where a.breeds = b.code;
02. 뷰 (View)
복잡한 쿼리를 가상의 테이블로 저장하여 재사용함.
1) 기본 뷰 생성 (품종별 생산량)
-- 뷰 생성
create or replace view fms.breeds_prod
(prod_date, breeds_nm, total_sum)
as
select a.prod_date,
(select m.code_desc from fms.master_code m where m.column_nm = 'breeds' and m.code = b.breeds),
sum(a.raw_weight)
from fms.prod_result a, fms.chick_info b
where a.chick_no = b.chick_no and a.pass_fail = 'P'
group by a.prod_date, b.breeds;
-- 뷰 조회
select * from fms.breeds_prod;
2) [응용] 뷰를 활용한 품종별 비율 분석
SELECT
TO_CHAR(prod_date, 'YYYYMM') AS prod_month,
SUM(CASE WHEN breeds_nm = 'Cornish' THEN total_sum else 0 END) AS Cornish_Total,
SUM(CASE WHEN breeds_nm = 'Cochin' THEN total_sum else 0 END) AS Cochin_Total,
SUM(CASE WHEN breeds_nm = 'Dorking' THEN total_sum else 0 END) AS Dorking_Total,
SUM(total_sum) AS monthly_total,
ROUND(
SUM(CASE WHEN breeds_nm = 'Cornish' THEN total_sum ELSE 0 END) * 100.0
/ NULLIF(SUM(total_sum), 0), 2
) || '%' AS cornish_rate
FROM fms.breeds_prod
GROUP BY TO_CHAR(prod_date, 'YYYYMM')
ORDER BY prod_month;
3) [추가 실습] 일별 출하 요약 뷰
고객사별, 품종별 주문 및 출하 마릿수를 요약하는 뷰를 생성함.
CREATE OR REPLACE VIEW fms.daily_shipment_summary
( ship_date, customer_nm, breeds_nm, total_orders, total_chicks )
AS
SELECT
sh.arrival_date,
sh.customer,
ma.code_desc,
COUNT(sh.order_no) AS "total_orders",
COUNT(ch.chick_no) AS "total_chicks"
FROM fms.ship_result sh, fms.chick_info ch, fms.master_code ma
WHERE sh.chick_no = ch.chick_no
AND ch.breeds = ma.code
AND ma.column_nm = 'breeds'
GROUP BY sh.arrival_date, sh.customer, ma.code_desc
ORDER BY sh.arrival_date;
-- 특정 고객사('YESYES') 조회
select ship_date, breeds_nm, total_chicks
from fms.daily_shipment_summary
where customer_nm = 'YESYES';
03. 피벗과 언피벗 (Pivot & Unpivot)
1) Pivot (행 열)
기초 데이터 확인
select hatchday, gender, count(chick_no)::int as cnt
from fms.chick_info
group by hatchday, gender order by hatchday, gender;
CASE WHEN을 이용한 Pivot (표준)
select
a.hatchday,
sum(case when a.gender = 'M' then a.cnt else 0 end) as "Male",
sum(case when a.gender = 'F' then a.cnt else 0 end) as "Female"
from
(
select hatchday, gender, count(chick_no)::int as cnt
from fms.chick_info
group by hatchday, gender
) a
group by a.hatchday;
Crosstab을 이용한 Pivot (확장 기능)
create extension tablefunc;
select * from crosstab(
'select hatchday, gender, count(chick_no)::int from fms.chick_info group by 1, 2 order by 1, 2 DESC'
) as pivot_r(hatchday date, "Male" int, "Female" int);
2) Unpivot (열 행)
기초 데이터 확인
select chick_no, body_temp, breath_rate, feed_intake
from fms.health_cond hc
where check_date = '2025-01-20' and chick_no like 'A%';
UNION을 이용한 Unpivot
select a.chick_no, a.health, a.cond
from
(
select chick_no, 'body_temp' as health, body_temp as cond
from fms.health_cond where check_date = '2025-01-20' and chick_no like 'A%'
union
select chick_no, 'breath_rate' as health, breath_rate as cond
from fms.health_cond where check_date = '2025-01-20' and chick_no like 'A%'
union
select chick_no, 'feed_intake' as health, feed_intake as cond
from fms.health_cond where check_date = '2025-01-20' and chick_no like 'A%'
) a
order by a.chick_no, a.health;
UNNEST를 이용한 Unpivot (배열 함수)
select
chick_no,
unnest(array['body_temp','breath_rate','feed_intake']) as health,
unnest(array[body_temp, breath_rate, feed_intake]) as cond
from fms.health_cond
where check_date = '2025-01-20' and chick_no like 'A%'
order by chick_no, health;
04. 데이터 조작 (DML)
INSERT, UPDATE, DELETE 구문의 기본 구조와 실습임.
1) 데이터 추가 (INSERT)
-- 기본 구조: INSERT INTO 테이블(컬럼) VALUES (값);
insert into fms.master_code(column_nm, type, code, code_desc)
values('breeds','txt','R1','Ross');
-- 여러 건 동시 추가
insert into fms.master_code(column_nm, type, code, code_desc)
values('breeds','txt','N1','New Hampshire Red'),
('breeds','txt','R2','Rhode Island Red'),
('breeds','txt','A1','Australorp');
-- 확인
select * from fms.master_code where column_nm = 'breeds';
2) 데이터 수정 (UPDATE)
-- 기본 구조: UPDATE 테이블 SET 컬럼=값 WHERE 조건;
update fms.master_code
set code_desc = '암컷'
where column_nm = 'gender' and code = 'F';
-- 확인
select * from fms.master_code where column_nm = 'gender';
3) 데이터 삭제 (DELETE)
-- 기본 구조: DELETE FROM 테이블 WHERE 조건;
delete from fms.master_code
where code = 'N1';
-- 트랜잭션 확정
commit;
05. 사용자 정의 함수 (User Defined Function)
자주 쓰는 로직을 함수로 정의하여 사용함.
기본 구조 (Syntax)
CREATE FUNCTION [함수명]() RETURNS [반환타입] AS $$ [쿼리] $$ LANGUAGE [언어];
함수 생성 및 실행
-- 전체 닭 마릿수를 반환하는 함수 생성
create function fms.get_chick_count()
returns integer
as $$
select count(*) from chick_info;
$$
language sql;
-- 함수 실행
select fms.get_chick_count();
06. 도전 미션 풀이 (Solutions)
미션 1: 농장별 합격률 계산
select farm,
ROUND(COUNT(CASE WHEN pr.pass_fail = 'P' THEN 1 END) * 100.0
/ COUNT(ch.chick_no), 2) pass_rate
FROM fms.chick_info ch
JOIN fms.prod_result pr ON ch.chick_no = pr.chick_no
group by ch.farm;
미션 2: 평균 체온보다 높은 닭 조회
select ch.chick_no, body_temp, farm
from fms.chick_info ch
join fms.health_cond he on ch.chick_no = he.chick_no
where he.body_temp > (select avg(body_temp) from fms.health_cond);
미션 3: 농장별/고객사별 출하 요약 뷰 생성
create view fms.view_farm_ship_summary
( farm, customer, shipped_count )
as
select ch.farm, sh.customer, count(sh.chick_no)
from fms.ship_result sh
join fms.prod_result pr on sh.chick_no = pr.chick_no
join fms.chick_info ch on ch.chick_no = sh.chick_no
group by ch.farm, sh.customer;
-- 뷰 확인 (농장 A만 조회)
select * from fms.view_farm_ship_summary where farm = 'A';
미션 4: 일자별 농장 생산량 Pivot (A, B, C 그룹)
select a.prod_date,
sum(case when a.farm = 'A' then a.cnt else 0 end) as "Farm A",
sum(case when a.farm = 'B' then a.cnt else 0 end) as "Farm B",
sum(case when a.farm not in ('A', 'B') then a.cnt else 0 end) as "Farm C"
from
(
select pr.prod_date, ch.farm, count(ch.chick_no)::int as cnt
from fms.prod_result pr
join fms.chick_info ch on ch.chick_no = pr.chick_no
group by pr.prod_date, ch.farm
) a
group by a.prod_date
order by a.prod_date;
제 별명이 감자입니다