PostgreSQL 강의 정리 (3)

CASE

특정 조건이 충족되었을 때 SQL 코드를 실행하기 위해 CASE를 사용함(C++와 같은 다른 프로그래밍 언어의 IF/ELSE와 비슷)
CASE는 두 경우로 사용할 수 있음, 일반 CASE와 CASE표현으로 나뉨
두 방법 모두 동일한 결과를 불러올 수 있음

ex) CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE some_other_result END

CASE를 쓴 후 WHEN 특정 조건 다음, THEN 원하는 결과를 적음
다른 조건으로도 적용하려면, 목록으로 만들어 쓰면 됨
ELSE는 위 모든 조건이 불충족되면 어떤 결과를 도출할지 쓰는 것임

ex) SELECT * FROM test;

일반 CASE 구문

ex) SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test; -> 다른 열로 CASE를 활용하고 싶을 때

SELECT a 다음에 쉼표를 쓰고, CASE구문을 넣은 뒤 다른 열로 결괏값을 도출했다는 것을 봐주면 됨
새 열은 디폴트로 CASE라 불림. AS로 이름을 바꿀 수 있음

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

Else 'other' 뒤에 AS label하면 열 이름이 label로 바뀜(보편적인 방법)
이미 존재하는 열을 일반 CASE명령으로 특정 조건에 따라 결괏값을 갖게 되는데 새 열에 나타남
END를 명시해 SQL을 계속 이어나가겠다는 뜻 여기서 FROM test로 이어감, ORDER BY로 이어가도 가능 -> 세로열을 명령에 있어 대안이 되는 보편적인 경우(결괏값을 뭘 원하는지에 따라 자체 조건 로직을 정하면 됨)

CASE 표현 구문은 WHEN 구문의 결과로써 우선 표현을 평가하고 각 결괏값을 비교함
ex) WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE some_other_result END
CASE 바로 전 표현을 평가하고, 조건 대신 WHEN 구문을 바로 써서 값을 적으면 됨

ex) SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' AS label END FROM test;
-> 수동으로 조건을 확인하는 과정을 생략할 수 있음(활용도가 높은 CASE표현으로 알아두면 좋음, 어떤 것이 동일한지 대량으로 확인해야 하는 경우)

CASE 표현 구문은 값이 동일한지를 확인하는 데 중점을 두고 있기 때문(단일 세로단처럼 간단한 경우)
일반 CASE 구문은 다양한 조건을 확인할 수 있도록 해줌
일반 구문은 WHEN 다음에 개별적이고 고유한 특정 조건을 대량으로 확인 요청할 수 있도록 해줌
CASE 표현 구문은 한 표현을 평가하고, 여러분이 가능한 값을 목록화함(해당 표현에 일치하는지 여부를 둠, 그런 표현은 주로 단순한 열에 관한 명령임)

• SQL 자체는 빈 곳과 들여쓰기에 별로 영향을 받지 않는다는 것을 알아두는 것이 좋음(키워드에 서로 영향없을때)

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

WHEN 키워드 뒤 일반 구문에 조건을 적음, 이 조건들을 활용해 다양한 유연함 발휘 가능.
조건이 만족하면 어떤 결괏값이 도출되어야 할지 결정해야 함
얼마나 많은 조건을 CHECK 해야 하는지 목록화함
WHEN 조건이 참일 때, 어떻게 하고 싶은지 결정하고 ELSE 구문으로 넘어감
명시한 조건들에 다 해당 않을 시, ELSE 구문이 적용되는 데 'Premium', 'Plus'가 도출되지 않았다는 것 => ELSE 조건 뒤에 적으면 됨
ELSE 다음에 적은 대로 행하라는 명령

조건을 다양하게 적용할 수 있지만, CASE 구문에서는 동일한지 확인하는 경우가 많음

ex2) 고객 아이디 기반으로 무작위 추첨(동질성만 확인하면 되니 CASE 표현 사용)
SELECT customer_id,
CASE customer_id
WHEN 2 THEN 'Winner'
WHEN 5 THEN 'Second Place'
ELSE 'Normal'
END AS raffle_results
FROM customer

일반적 구문보다 유연함이 떨어지지만 시간이 덜 걸림
고객 아이디는 한 번만 적고, 값으로 동일성 확인만 하면 됨

CASE 구문의 결과로 오퍼레이션을 수행할 수 있음의 예시

ex3)
SELECT
CASE rental_rate:
WHEN 0.99 THEN 1
ELSE 0
END
FROM film

ex4) SELECT
SUM(CASE rental_rate
WHEN 0.99 THEN 1
ELSE 0
END) AS number_of_bargains
FROM film

ex5) 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
FROM film ------------------> 유용한 점은 전체 SUM이 다른 행 명령으로 여겨지기 때문에 쉼표도 필요 없음

ex5) 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 열 결과에 대한 기능을 활용할 수 있어서 유용함
CASE 구문은 WHEN, THEN 1, ELSE 0과 함께 잘 쓰임(무언가 더하거나 결괏값을 쓸 때)

COALESCE

무한한 수의 argument를 쓸 수 있는 기능으로, NULL이 아닌 첫 argument를 도출함
모든 argument가 NULL이면 COALESCE 기능에서도 NULL로 나옴

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

NULL 값을 가진 표를 쿼리 할 때 유용함, 그걸로 어떤 오퍼레이션을 수행하려 하는데, NULL을 처리할 수 없을때 유용함
NULL이 있으면 다른 값으로 바꾸면 됨

ex) SELECT item,(price - COALESCE(discount,0)) AS fianl FROM table

NULL 값이 있는 표를 보면 이 기능을 생각하면 됨(본 표를 바꾸지 않으면서 오퍼레이션을 수행하고 싶을 때)

CAST

CAST 오퍼레이터는 데이터 유형을 바꿔줌(string -> integer)

• 모든 데이터 유형이 모든 데이터 유형으로 CAST 될 수 없음(데이터를 바꿔야 하는 상황에만 가능)

ex) '5'라는 문자열이 있는데 integer로 바꾸면 문자열 혹은 숫자5가 정수5로 변환될 수 있음, 하지만 '5'가 아닌 'five'라면 문자열을 숫자로 인식하지 못하고 integer로 처리하지 못함 --> 여기서 CAST 함수 사용 가능

CAST 오퍼레이션은 보통 두 가지 방법으로 사용함(CAST 함수를 통해서)

1. 일반 SQL 구문

ex) SELECT CAST('5' AS INTEGER) -> SELECT 다음 사용할 CAST를 쓰고 데이터 유형을 씀

2. CAST 오퍼레이터

ex) SELECT '5'::INTEGER -> CAST와 AS 키워드를 쓰지 않고, 프로그램이 특별한 오퍼레이터로 2개의 콜론으로 표현함(하나의 샘플이나 사례로 선택하지 않음, 단독 사례가 아니라 쿼리를 열 이름과 함께 SELECT함)

ex2) 날짜 열이 문자열 입력 칸으로 셋업되었으면 SELECT CAST(date AS TIMESTAMP)FROM table이라 할 수 있음

ex3) SELECT CAST('five' AS INTEGER)
이런 상황에선 CAST 기능으로 할 수 있는것이 없음. 바꾸려는 대상이 합리적이던가 알맞은 데이터 유형을 골라야 함
이 변화를 빠르게 진행할 수 있게 프로그램에 특별 오퍼레이터를 만들어 두었음(PostgreSQL만 가능함)

ex4) 재고 아이디의 글자 수를 알아야 하는 상황 -> 각 재고 아이디에 몇 개의 수가 들어가냐고 물어야함

SELECT CHAR_LENGTH(CAST(inventory_id AS VARCHAR)) FROM rental; -> 각 재고 아이디의 실제 글자 수가 세어져 도출됨

NULLIF

두 개의 값을 넣어서 두 값이 같으면 NULL 아니면 첫 번째 값이 도출됨
ex) NULLIF(10,10) -> Returns NULL
ex2) NULLIF(10,12) -> Returns 10

NULL 값이 에러의 원인이 되거나 원하지 않는 결과가 나오는 경우에 유용하게 사용될 수 있음

ex) INSERT INTO depts(
first_name,
department
)
VALUES
('Vinton','A'),
('Lauren','A'),
('Claire','B');

ex2) SELECT (
SUM(CASE WHEN department = 'A' THEN 1 ELSE 0 END) ----> A부서에 있는 총합 2
SUM(CASE WHEN department = 'B' THEN 1 ELSE 0 END) ----> B부서에 있는 총합 1
) -----> 부서에 몇명이 있는지 알 수 있는 예시

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

위 예시에서 B부서에 직원이 없을 경우

ex3) DELETE FROM depts WHERE deparment = 'B' ---> B 부서 직원 삭제

SELECT (
SUM(CASE WHEN department = 'A' THEN 1 ELSE 0 END)/
SUM(CASE WHEN department = 'B' THEN 1 ELSE 0 END) ----> B가 0이기 때문에 에러가 발생함
) AS deparment_ratio
FROM depts

위 예시에서 에러가 아닌 NULL이 되도록 같은 조건을 NULLIF로 바꿔보는 예시(에러가 나면 상황이 불명확하게 보이기 때문)
합이 0이면 NULL이 나오게 하고 싶음

ex4) SELECT (
SUM(CASE WHEN department = 'A' THEN 1 ELSE 0 END)/
NULLIF(SUM(CASE WHEN department = 'B' THEN 1 ELSE 0 END),0) ---> 둘이 같아서 SUM이 0이 되면 NULL이 나오는 것
) AS deparment_ratio
FROM depts

뭔가를 NULL로 나누면 NULL이 도출됨(0으로 나눠 에러가 나오는 것과는 다름)
NULL IF를 많이 쓰는 경우는 뭔가를 확인하는 경우임
데이터베이스 값의 일치 여부에 따라 0,1을 도출할 수 있음

VIEWS

처음부터 같은 쿼리를 계속해 수행해서 모든 문장이 맞는지 확인하고 수기로 다시 작성하는 대신 VIEW를 만들어도 됨
간단한 명령으로 재빨리 쿼리를 보여주는 것임(표가 이미 있듯이)

예를 들어 특정 데이터베이스로 프로젝트를 하는데 보고서 작성 중 항상 두 표가 JOIN되는 상황 발생
항상 본 표의 열을 SELECT하는 쿼리 작성 후 두 개가 JOIN되도록 해야 함
같은 쿼리를 계속 수기 작성하는 대신, VIEW로 변환 가능함

VIEW는 데이터베이스 오브젝트로 저장된 쿼리임, 프로그램에서 시각화한 표로 볼 수 있음
시각화한 표라 부르는 이유는 VIEW가 실제로 데이터를 물리적으로 다른 곳에 저장하지 않기 때문
이미 존재하는 표를 전체 선택하듯 쉽게 실행하고 간단하게 쿼리를 저장함 -> 복잡한 쿼리를 계속 반복해 쓴다면 어떤 표인 것처럼 변환함
---> SELECT * FROM view

이미 존재하는 VIEW를 바꾸거나 업데이트 할 수도 있음

고객에게 이름과 주소 정보를 끊임없이 물어보는 프로젝트를 진행

ex) SELECT first_name,last_name,address FROM customer
INNER JOIN address
ON customer.address_id = address.address_id

이런 종류의 쿼리는 복잡해질 수 있기 때문에 view로 본 쿼리를 저장해두는 것이 좋음

view 혹은 기존 쿼리 정보를 바꾸는 일은 CREATE OR REPLACE를 명령을 하면 됨

ex2) CREATE OR REPLACE VIEW customer_info AS
SELECT first_name,last_name,address,district FROM customer ---> district 추가
INNER JOIN address
ON customer.address_id = address.address_id

DROP VIEW -> view를 없앰(표도 마찬가지)

에러를 방지하기 위해 IF EXISTS도 추가

고객 정보가 존재하면 해당 view를 드랍하라는 명령

view의 이름을 바꾸고 싶으면 ALTER VIEW 원래 이름에 RENAME TO 새 이름 하면 됨

불러오기와 내보내기

모든 외부 데이터 파일이 통하지는 않음

포맷이나 매크로나 데이터 유형 등이 다른 경우 불러오기 명령에 방해가 될 수 있음

SQL에서 쓸 수 없는 파일이면, 파일을 수정하거나 가지고 있는 데이터 파일에 잘 들어맞도록 기존 표를 변경해야 함

ex : https://www.postgresql.org/docs/12/sql-copy.html

복사 명령은 그래픽 유저 인터페이스를 통해 불러오기 위해 SQL 코드를 실행할 때 사용자를 내부에서 불러옴

불러올 파일의 주소가 100퍼센트 정확해야 한다는 점도 중요함(.tar 파일을 복원할 때와 비슷함)

불러오기는 표로 만들어지지 않음(csv 혹은 excel 파일을 표로 만들려 함)

프로그램의 복사 명령과 PG 어드민의 불러오기 기능은 표가 이미 생성되었다는 가정하에 작동함

표준 PG 어드민과 프로그램에서 표를 즉시 만들어주는 자동 기능은 없음

<엑셀에서 .csv 파일을 만들었다고 가정함>

ex)

CREATE TABLE simple(
a INTEGER,
b INTEGER,
c INTEGER,
)

PostgreSQL with Python>

# coding: utf-8

# # Using PostgreSQL in Python (with Psycopg2)
# 
# ### Psycopg2
# 
# A library that allows Python to connect to an existing PostgreSQL database to utilize SQL functionality.
# 
# #### Documentation
#  * http://initd.org/psycopg/docs/install.html
# 

# In[ ]:

# After installing with pip install psycopg2
import psycopg2 as pg2

# In[ ]:

# Create a connection with PostgreSQL
# 'password' is whatever password you set, we set password in the install video
conn = pg2.connect(database='postgres', user='postgres',password='password')

# In[ ]:

# Establish connection and start cursor to be ready to query
cur = conn.cursor()

# In[ ]:

# Pass in a PostgreSQL query as a string
cur.execute("SELECT * FROM payment")

# In[ ]:

# Return a tuple of the first row as Python objects
cur.fetchone()

# In[ ]:

# Return N number of rows
cur.fetchmany(10)

# In[ ]:

# Return All rows at once
cur.fetchall()

# In[ ]:

# To save and index results, assign it to a variable
data = cur.fetchmany(10)

# **Inserting Information**

# In[2]:

query1 = '''
        CREATE TABLE new_table (
            userid integer
            , tmstmp timestamp
            , type varchar(10)
        );
        '''

# In[ ]:

cur.execute(query1)

# In[ ]:

# commit the changes to the database
cur.commit()

# In[ ]:

# Don't forget to close the connection!
# killing the kernel or shutting down juptyer will also close it
conn.close()ㅁ

강사는 Jupyter 노트북 에디터로 실행하는데 각 셀을 다른 코드로 실행하고 있음 → 셀을 실행하면서 동시에 결과를 볼 수 있음

커서는 조작용 구조임(데이터베이스 레코드를 돌아다닐 수 있도록 운행하는 거라고 보면 됨)

커서 실행 명령을 내리고 괄호에 문자열을 넣음(데이터베이스나 쿼리에서 궁금한 걸 넣으면 됨)

예시로 표 전체 선택 명령을 넣을 수 있음

Review

python

• import psycopg2 as pg2 # 연결 명령

• conn = pg2.connect(database='dvdrental', user='postgres', password=secret) # 연결하는 데이터베이스 이름과 연결하는 사용자 이름 추가

• cur = conn.cursor() # 연결 커서를 정비함(이걸 통해 SQL 쿼리를 실행할 수 있음)

• cur.execute('SELECT * FROM payment') # 쿼리를 커서 실행하면 페치 기능 사용 가능(여러 줄과 한 줄 페치 가능)

• cur.fetchall() # 전체도 가능

• cur.fetchone() # 한 줄 페치, 튜플과 페킹도 함께 사용 가능

• data = cur.fetchmany(10)

• data[0] # 인덱스를 적용하고 튜플, 페킹해서 인덱스 하면 특정 데이터가 나옴, 사용자에게 어떤 유형의 데이터를 사용할지가 달려있음, 어떤 파이썬 psycopg 포맷을 쓸 지도

• conn.close()

• add = 'DROP TABLE' # 이런 식의 실수를 하면 데이터베이스를 망칠 수 있음

• command = 'SELECT * FROM payment' # 실행 명령 후에 명령을 적는데 명령은 전체 선택으로 적음, 다른 문자열도 추가 가능(다른 오브젝트일 수도 있음)

• cur.execute(command)

cur.fetchall() - 여러 줄이 있으니 주의해서 사용해야 함, 안 그러면 끊임없는 결괏값이 도출될 수도 있음

중요한 것은 불러오기, 연결하기, 커서 만들기, SQL 쿼리 실행임

• 파이썬 프린팅 포매팅과 문자열 포매팅을 피하는질 확인해줘야 함(SQL 쿼리를 실행할 때)

전체 SQL 쿼리를 한 줄에 쓰는 게 안전함

• Python, PostgreSQL, psycopg2에 대한 공식 문서 내용 : https://wiki.postgresql.org/wiki/Psycopg2_Tutorial