PostgreSQL 문법 정리4 - 조건식과 Procedure 정리

CASECASE

• 특정 조건이 충족되었을 때 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

VIEWS

• View는 SQL에서 하나 이상의 테이블(또는 다른 뷰)에서 원하는 모든 데이터를 선택하여, 그들을 사용자 정의하여 나타낸 것 이다.
• 뷰는 저장된 쿼리이며 가상의 테이블로 접근할 수 있다.
• 뷰는 단순히 쿼리를 저장한 것이고 실제로 데이터를 물리적으로 저장하는 것은 아니다.
• 뷰를 통해 SQL 이용의 편의성을 높일 수 있다.
• 뷰를 통해 원본 데이터를 조회하는 대신 데이터의 일부분만 보여주게 할 수 있기 때문에 권한관리와 보안에도 장점이 있다.

-- 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

-- 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

• 특정 조건이 충족되었을 때 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

VIEWS

• View는 SQL에서 하나 이상의 테이블(또는 다른 뷰)에서 원하는 모든 데이터를 선택하여, 그들을 사용자 정의하여 나타낸 것 이다.
• 뷰는 저장된 쿼리이며 가상의 테이블로 접근할 수 있다.
• 뷰는 단순히 쿼리를 저장한 것이고 실제로 데이터를 물리적으로 저장하는 것은 아니다.
• 뷰를 통해 SQL 이용의 편의성을 높일 수 있다.
• 뷰를 통해 원본 데이터를 조회하는 대신 데이터의 일부분만 보여주게 할 수 있기 때문에 권한관리와 보안에도 장점이 있다.

-- 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

-- 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