PostgreSQL 조건식과 프로시저 정리

CASE

• 특정 조건이 충족되었을 때 SQL코드를 실행하기 위해 CASE를 사용한다.
• 프로그래밍 언어에서 흔히 쓰이는 IF/ELSE 문과 비슷하다.
• 일반적인 CASE문과 CASE 표현식으로 쓸 수 있다. 두 방법 모두 결과는 같다.

-- 기본 문법
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE some_other_result
END

SELECT a,
CASE 
WHEN a = 1 THEN 'one'
WHEN a = 2 THEN 'two'
ELSE 'other' AS label
END
FROM test;

-- CASE 표현식
CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE some_other_result
END

SELECT a,
CASE a 
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;

-- 일반 CASE 문
SELECT customer_id,
CASE
	WHEN (CUSTOMER_ID <= 100) THEN 'Premium'
	WHEN (CUSTOMER_ID BETWEEN 100 AND 200) THEN 'Plus'
    ELSE 'Normal'
END AS customer_class
FROM customer

-- CASE 표현식
SELECT customer_id,
CASE customer_id
    WHEN 2 THEN 'Winner'
    WHEN 5 THEN 'Second Place'
    ELSE 'Normal'
END AS raffle_results
FROM customer

SELECT 
SUM(CASE rental_rate
    WHEN 0.99 THEN 1
    ELSE 0
END) AS bargains,
SUM(CASE rental_rate
    WHEN 2.99 THEN 1
    ELSE 0
END) AS regular,
SUM(CASE rental_rate
    WHEN 4.99 THEN 1
    ELSE 0
END) AS premium
FROM film;

CASE 연습문제

--- 영화 테이블에서 등급별 영화 개수 구하기
SELECT 
SUM(CASE rating 
    WHEN 'R' THEN 1 
    ELSE 0 
END) AS r,
SUM(CASE rating 
    WHEN 'PG' THEN 1 
    ELSE 0 
END) AS pg,
SUM(CASE rating 
    WHEN 'PG-13' THEN 1 
    ELSE 0 
END) AS pg13
FROM film

COALESCE

• 더 큰 덩어리로 합치다라는 뜻
• 처음으로 NULL이 아닌 값을 만나면 그 값을 리턴하는 함수
• 따라서 NULL값을 가질 수 있는 데이터를 쿼리할 때 유용하다.

SELECT COALESCE (1, 2) -> 1
SELECT COALESCE (NULL, 2, 3) -> 2

-- 할인율 컬럼이 NULL이 가능한 경우
SELECT item, (price - COALESCE(discount, 0))
AS final FROM table

CAST

• CAST 연산자는 데이터 유형을 바꿔준다.

-- CAST 함수 문법
SELECT CAST('5' AS INTEGER)
SELECT CAST(date AS TIMESTAMP) FROM table

-- PostgreSQL CAST 연산자 문법
SELECT '5'::INTEGER

-- inventory_id 컬럼을 정수에서 문자열로 바꾸고 길이 재기
SELECT CHAR_LENGTH(CAST(inventory_id AS VARCHAR))
FROM rental

NULLIF

• NULLIF 함수는 두 개의 인자를 받고 양쪽이 같으면 NULL을 반환하고 아니면 첫 번째 인자를 반환하는 함수이다.
• NULL 값이 에러의 원인이 되거나 원하지 않는 결과가 나오는 경우에 유용하게 사용될 수 있다.
• A부서 총합과 B부서 총합을 나눠서 부서별 비율을 구하는 쿼리인데, B부서 인원이 0명이 되면 0으로 나누기 에러가 난다.

SELECT (
SUM(CASE WHEN department = 'A' THEN 1 ELSE 0 END) /
SUM(CASE WHEN department = 'B' THEN 1 ELSE 0 END)
) AS department_ratio
FROM depts

• NULLIF를 사용 할 경우 B부서 인원이 0이 되면 NULL을 반환하기 때문에 NULL로 나누기를 하면 값이 NULL로 바뀌고 에러가 나지 않는다.

SELECT (
SUM(CASE WHEN department = 'A' THEN 1 ELSE 0 END) /
NULLIF(SUM(CASE WHEN department = 'B' THEN 1 ELSE 0 END), 0)
) AS department_ratio
FROM depts

VIEW

• 뷰(View)란 SQL에서 하나 이상의 테이블(또는 다른 뷰)에서 원하는 모든 데이터를 선택하여, 그들을 사용자 정의하여 나타낸 것 이다.
• 일반적인 테이블처럼 조작할 수 있지만 실존하지 않는 가상의 테이블이다.
• 뷰는 단순히 쿼리를 저장한 것이고 실제로 데이터를 물리적으로 저장하는 것은 아니다.
• 뷰를 통해서 데이터의 검색, 삭제, 삽입, 수정 등의 작업 시 참조 무결성이나 개체 무결성의 원칙에 위배되는 작업은 수행되지 않는다.
• WITH CHECK OPTION을 사용하여 뷰에 대한 삽입 및 수정 작업에 대한 제약 조건을 설정할 수 있다.
• 뷰를 활용하면 원본 데이터를 조회하는 대신 데이터의 일부분만 보여줄 수 있기 때문에 권한 관리와 보안에 장점이 있다.
• 또한 데이터의 독립성 강화, 다양한 구조의 테이블 사용효과, 작업의 단순화, 데이터의 무결성 강화 등의 장점이 있다.

-- VIEW 생성
CREATE VIEW view_name AS SELECT clause;

-- VIEW 생성 또는 수정
-- view_name이란 뷰가 이미 있으면 수정하고 없으면 생성한다.
CREATE OR REPLACE VIEW view_name AS SELECT clause;

-- VIEW 이름 변경
ALTER VIEW view_name RENAME to renamed_view_name

-- ALTER로 VIEW 수정하기
ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- VIEW 삭제
DROP VIEW view_name;

CREATE VIEW customer_info AS
SELECT first_name, last_name, address
FROM customer
INNER JOIN address
ON customer.address_id = address.address_id

SELECT * FROM customer_info

-- SELECT에 district 컬럼 추가
CREATE OR REPLACE VIEW customer_info AS
SELECT first_name, last_name, address, district
FROM customer
INNER JOIN address
ON customer.address_id = address.address_id

-- 백만원 이상의 잔액을 소유하고 있는 학생의 정보를 조회하는 VIEW 생성
CREATE VIEW 고액계좌_보유자 AS
(SELECT A.학생이름, A.전화번호, B.계좌번호, B.잔액
FROM 학생 A NATURAL JOIN 계좌 B
WHERE B.잔액 >= 1000000);

-- 위에서 생성한 VIEW 조회
SELECT * FROM 고액계좌_보유자;

-- VIEW에 WHERE절 적용 가능
SELECT * FROM 고액계좌_보유자
WHERE 성별 = '여';

-- WITH CHECK OPTION 사용
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition
WITH CHECK OPTION;

CREATE OR REPLACE VIEW usa_city AS 
SELECT
	city_id,
	city,
	country_id
FROM
	city
WHERE
	country_id = 103
ORDER BY
	city WITH CHECK OPTION;

-- WITH CHECK OPTION으로 인해 밑의 쿼리는 에러 발생함
UPDATE usa_city
SET country_id = 102
WHERE city_id = 135;