일반적으로 사용하는 SELECT, FROM, ORDER BY 등의 구문이 주로 데이터 조회를 위해 사용된다면, PL/SQL은 IF 같은 조건문, FOR이나 WHILE 같은 반복문 등을 사용하여 특정 조건에 맞게 자료를 저장하거나 함수, 프로시저, 패키지(프로시저의 모음), 트리거 등을 생성할 때 사용한다.
2. 기본 구조
PL/SQL 블록
DECLARE
-- 변수 선언, 서브 프로그램
BEGIN
-- 실행 구문
EXCEPTION when others then
-- 예외처리 (필수는 아님)
null;
END;
DECLARE: 변수, 서브 프로그램들을 정의
BEGIN: 실질적으로 수행하고자 하는 질의, 제어문을 입력
EXCEPTION: 예외처리를 수행할 때 사용(필수X)
END: 블록 종료
- DECLARE은 익명 블록으로, 아래의 내용을 수행하는 블록이며 수행한 결과를 바탕으로 함수, 프로시저, 패키지 및 트리거 등을 만들 수 있다. 만든 함수, 프로시저, 패키지 및 트리거는 스키마에 물리적으로 저장된다.
- DECLARE은 중첩으로 사용할 수 있으며, 가장 바깥쪽에 선언된 변수는 global 변수로, 그 하위 블록에서는 local 변수로 사용할 수 있다.
3. 자주 사용하는 Oracle 명령어
dbms_output.put_line('문자열'): print 기능. 로그 등을 찍을 때도 사용.
데이터베이스를 정의하는 언어, 데이터를 생성하고 수정, 삭제하는 등 데이터의 전체적인 모양을 결정
대표 질의: CREATE, ALTER, DROP, TRUNCATE
직접 DB 및 테이블을 만들면서 진짜 많이 썼다!
1. 데이터 베이스 생성하기
문법: CREATE schema 데이터베이스명; 또는 CREATE database 데이터베이스명;
-- test라는 이름의 데이터베이스 생성
CREATE schema test;
-- 또는
CREATE database test;
2. 테이블 생성하기
문법: CREATE TABLE 테이블명;
-- test_table1 테이블 생성
CREATE TABLE test_table1;
위와 같이 쉽게 생성할 수 있다.
3. 테이블의 결과로 새로운 테이블 생성하기
문법: CREATE TABLE 데이터베이스명.테이블명 쿼리;
-- test_table1 테이블의 일부 조건을 건 결과를 test 스키마의 test_table2라는 테이블로 저장
CREATE TABLE test.test_table2
SELECT id, name, age
FROM test
WHERE joined_at > '2023-09-01'
역대 올림픽 정보 데이터셋은 역대 올림픽 경기와 관련된 데이터가 들어있는 테이블로 이루어져 있습니다.
athletes테이블에는 역대 올림픽 참가 선수의 이름이 들어 있습니다.
events테이블에는 종목과 경기 이름이 들어 있습니다.
games테이블에는 올림픽 개최 연도, 개최 도시와 시즌 정보가 기록되어 있습니다.
records테이블에는 역대 올림픽 참가 선수들의 신체 정보와 획득한 메달 정보가 기록되어 있습니다. 이 테이블은 다른 테이블과 매핑할 수 있는 ID 정보도 가지고 있습니다.
teams테이블에는 국가 정보가 기록되어 있습니다.
* 조건 *
2000년 이후의 메달 수상 기록만 고려했을 때, 메달을 수상한 올림픽 참가 선수 중 2개 이상의 국적으로 메달을 수상한 기록이 있는 선수의 이름을 조회하는 쿼리를 작성해주세요. 조회된 선수의 이름은 오름차순으로 정렬되어 있어야 합니다.
1. 조건에 필요한 모든 컬럼을 가져오기 위해 record를 기준으로 athletes(선수 이름), games(개최 연도)를 JOIN
2. 메달을 수상한 기록이 있음 -> medal is not null / 2000년 이후의 메달 수상 기록 -> year >= 2000
⭐3. 2개 이상의 국적으로 메달을 수상한 기록이 있는 선수!
3번의 그룹핑을 정말 많이 헤맸다. 처음에 grouping을 선수의 이름을 기준으로 했는데 선수 이름을 기준으로 하면 총 record가 14명이 나온다 (이걸 해결하느라 정말 오래 걸렸음)
소스 코드:
SELECT name
FROM records as r
JOIN athletes as a ON r.athlete_id = a.id
JOIN games as g ON r.game_id = g.id
WHERE medal is not null
AND year >= 2000
GROUP BY a.id
HAVING COUNT(distinct team_id) >= 2
ORDER BY name
풀이 과정
처음 쿼리는 이랬다. (name으로 그룹핑)
근데 자꾸 정답 레코드의 개수 차이가 남
SELECT name
FROM records as r
JOIN athletes as a ON r.athlete_id = a.id
JOIN games as g ON r.game_id = g.id
WHERE medal is not null
AND year >= 2000
GROUP BY name
HAVING COUNT(distinct team_id) >= 2
ORDER BY name
(좌)name으로 그룹핑 했을 때 결과 / (우)athletes의 id로 그룹핑 했을 때 결과
Chen Jing 선수가 id로 그룹핑 했을 때는 빠지게된다. 그래서 Chen Jing을 검색하여 찾아봤음
SELECT a.id, name, game_id, team_id, medal
FROM records as r
JOIN athletes as a ON r.athlete_id = a.id
JOIN games as g ON r.game_id = g.id
WHERE name = 'Chen Jing'
AND medal is not null
AND year >= 2000
2000년 이후의 기록을 봤을때 그는 동명이인이었다... unique key로 잡아줘야하는 것의 중요성을 다시 한 번 알게되었다.
같이 스터디 하는 분들과 함께 매주 세 문제씩 SQL 코딩 테스트를 하고 있다. 많이 풀고 다른 사람들의 코드를 보면서 더 잘하는 사람들의 논리를 받아들이는 과정에서 쿼리 실력이 예전에 비해 정말 많이 늘었는데, 그와 동시에 생기는 고민이 바로 쿼리 효율 Query Efficiency 에 대한 부분이었다. ('쿼리 최적화' 라는 표현도 많이 쓰이는 것 같다.)
이전에 회사를 다닐 때도 사수분께서 간단하게 언급한 적 있었지만, 그 때는 쿼리 효율은 무슨... 원하는 내용 추출이나 가능하면 다행이었다.
그래서 SQL 코딩 테스트를 봤을 때와 조사하며 나온 내용들을 바탕으로 쿼리 효율에 대한 글을 작성해보고자 한다!
효율적인 쿼리를 날리기 위해서 기억해야 할 것은 CPU 사용량이다.
간단하게 설명하자면 DB가 데이터를 찾기 위해 뒤져야하는 테이블의 범위가 넓을 수록 많은 CPU를 사용하게 되므로, 조건을 잘 설정해주어 테이블 범위를 줄이는 것이 좋다.
CPU 시간에 영향을 끼치는 것은 아래 세 가지 종류가 있다.
테이블 크기 (검색해야하는 row 수가 많을 수록 느려짐)
JOIN
Aggregation
따라서 위 세 가지를 줄이는 과정에서 흔히 접할 수 있는 케이스에 대해 설명할 예정이다.
1. 테이블의 크기를 줄입시다: 조건의 설정
1-1. LIKE 검색 조건
WHERE 절에 LIKE와 와일드 카드 문자(%)를 함께 사용할 때
예를 들어 찾고 싶은 내용이 'abc' 라는 것을 명시적으로 알고있으면, 'a%' 를 사용하지 않고 'abc'로 정확하게 검색해 주는 것이 좋다.
가상의 이벤트 테이블에서 장바구니에서 삭제한 목록들을 검색하고 싶을 때,
SELECT id, event_value
FROM t1
WHERE coll LIKE '장바구니%'
와
SELECT id, event_value
FROM t1
WHERE coll = '장바구니 삭제'
라고 날리는 것은 차이가 있다.
전자의 경우 '장바구니 담기'인지, '장바구니 삭제'인지, '장바구니 클릭'인지 '장바구니'로 시작하는 모든 rows를 찾기 때문에 훨씬 많은 수의 row를 가진 테이블을 반환한다. 따라서 찾아야 할 내용이 무엇인지 알고있다면 정확하게 명시해 주는 것이 좋다. 반면 '장바구니'를 통한 모든 이벤트 값을 보고 싶은거라면, LIKE '장%' 보다는 LIKE '장바구니%'가 훨씬 효율적이다.
요지는 범위를 지엽적으로 주어 검색하는 row의 수를 줄이는 것이다.
1-2. 적절한 LIMIT의 사용
본 쿼리를 돌리기 전 테스트 쿼리를 돌릴 때 아주 아주 유용하게 사용할 수 있다.
SELECT COUNT(*)
FROM (SELECT event_value
FROM event_log
LIMIT 100) A
이런식으로 인라인 뷰에 LIMIT을 걸어줘서 본쿼리에서 하고 싶은 동작들을 작은 규모로 실행해 볼 수 있다.
주의할 점은 서브 쿼리로 만든 테이블은 원래 테이블에서 잘라온 것이므로, 쿼리가 잘 돌아가는지 로직만 확인 하는 수준으로 사용하고 실제 쿼리를 돌릴 때는 (당연히) 원래 테이블에서 돌려야한다. 안그러면 결과가 무지 달라질 수 있음
2. JOIN 효율적으로 하기: JOIN의 조건, OUTER JOIN의 필요성
개인적으로 쿼리 날리고 나서 항상 후회하는(?) 부분 중 하나인데, JOIN 조건을 하나만 주지 않는 것이다.
SELECT Name
FROM Students as s
JOIN Friends as f ON s.ID = f.ID
JOIN Packages as p1 ON s.ID = p1.ID
JOIN Packages as p2 ON f.Friend_ID = p2.ID
WHERE p1.Salary < p2.Salary
ORDER BY p2.Salary
처음 풀었던 쿼리는 위와 같았다. WHERE 절에 조건 주기
그런데 JOIN 조건을 명확히 주면 쿼리 효율이 더 좋아진다.
SELECT Name
FROM Students as s
JOIN Friends as f ON s.ID = f.ID
JOIN Packages as p1 ON s.ID = p1.ID
JOIN Packages as p2 ON f.Friend_ID = p2.ID
AND p1.Salary < p2.Salary
ORDER BY p2.Salary
바뀐 부분이라곤 ON 절의 조건을 AND로 묶어서 WHERE 절의 조건을 넣어준 것 뿐이다.
하지만 이 사소한 변화가
JOIN 함 -> 모든 테이블을 JOIN 한 테이블 -> 조건을 검색 -> 검색한 조건을 ordering
뭔가를 잘 알지 않아도 바로 적용할 수 있는거라 JOIN을 할 때 늘 신경써야 한다는 생각이 들었다.
2-2. OUTER JOIN의 적절한 사용
OUTER JOIN의 특성상 교집합이 아닌 부분은 NULL 값으로 채워지고 결국 이는 row의 개수를 늘린다. 만약 교집합의 정보만 필요하다면 INNER JOIN을 사용한 방법을 권장한다.
3. EXPLAIN 함수 사용하기
EXPLAIN 함수는 실행하는 쿼리에 대해서 쿼리 플랜을 보여준다. 어떤 테이블에서 얼만큼의 row가 반환되는지 여러 가지 요소들을 보여준다.
아래 연도별 매출액을 구하는 쿼리를 돌렸을 때
이렇게 어떤 테이블에 몇 개의 row가 반환되었는지 보여준다. 만약 그만큼 필요하지 않은데 너무 많은 row가 나온 테이블들은 조건을 수정하여 더 작은 규모의 실행을 해 보는 것을 추천한다.
이외에도 개인적으로 하려는 노력은
1. with문보다는 subquery 쓰기
같은 결과를 내어야 한다면 with문으로 테이블이 들어갈 자리를 하나 만들어 주는 것보다는 서브 쿼리를 사용한 질의가 (아주 조금) 더 효율적이라고 한다. 데이터가 크면 클수록 효율적이라고 한다. 다만 서브 쿼리 또한 연산 시간이 많이 걸릴 수 있으므로, 정말 필요한 때 사용해야 한다.
2. 연산 줄이기
많은 양을 검색해야할 때 해당 쿼리에 연산이 포함되어 있다면 연산과 검색을 동시에 해야하기 때문에 실행 시간이 오래 걸릴 수 있다. 만약 연산을 해야하는 테이블이 있다면 미리 가공을 한 뒤 가공한 결과를 가진 테이블과 join 하는 등의 방식으로 검색+연산에 의한 CPU 저하를 예방해야한다.
3. JOIN 조건 잘 생각하기
JOIN을 할 때 정말 필요한 테이블만 조인하는 습관이 필요하다. 이를 잘 하기 위해서는 스키마에 대한 이해가 필수적이다. (프로젝트를 할 때 스키마 정의서를 꼭 작성하기를 권장하는 이유가 이 때문이다.)
더 많은 방법이 있지만 지금의 수준에서 시도해 볼 수 있는 방법들은 이렇게 있는 것 같다.
Query analphabetically orderedlist of all names inOCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses).
OCCUPATIONS 테이블에서 1. 알파벳 순으로 2. 각 직업의 첫 알파벳 글자를 따와서 아래와 같은 형태로 쿼리하라. For example:AnActorName(A),ADoctorName(D),AProfessorName(P), andASingerName(S). -- 1번 쿼리
Query the number of ocurrences of each occupation inOCCUPATIONS. Sort the occurrences inascending order, and output them in the following format:
There are a total of [occupation_count] [occupation]s.
각 직업이 몇 개나 있는 지 숫자를 세어 위와 같은 형태로 나타나야 하며, 숫자 순서대로 내림차순 정렬 -- 2번 쿼리
Sample output
Ashely(P) Christeen(P) Jane(A) Jenny(D) Julia(A) Ketty(P) Maria(A) Meera(S) Priya(S) Samantha(D) There are a total of 2 doctors. There are a total of 2 singers. There are a total of 3 actors. There are a total of 3 professors.
STEP 1
두 쿼리문의 형태가 다르기 때문에 쿼리를 나누어 출력해야 한다고 생각했다.
우선 1번 쿼리를 짜기 전 occupations table에 대해 살펴보았다.
=> 이름 출력, SUBSTR 통해서 첫 문자 가져오는 것으로 확인, CONCAT을 통해 '(', ')'을 붙여주면 될 것이라고 판단.
STEP 2: 쿼리1 완성
-- Name 컬럼을 가져오고, CONCAT 함수로 문자열 연결, SUBSTR으로 Occupation 컬럼의 첫 번째 위치에서
-- 한 개의 문자만 가지고 옴
-- SUBSTR(문자열 또는 컬럼, 가져올 위치, length)
SELECT Name, CONCAT('(',SUBSTR(Occupation, 1,1),')') as occupation
FROM OCCUPATIONS
;
OUT:
Ashley (P)
Samantha (A)
Julia (D)
Britney (P)
Maria (P)
STEP 3: 쿼리 2 작성
-- 문자열 합치기: CONCAT 함수 사용(문자열 연결)
SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', Occupation,'s.')
FROM OCCUPATIONS
GROUP BY Occupation -- count 집계 함수를 세기 위해 occupation으로 그룹핑
ORDER BY COUNT(Occupation)
;
OUT:
There are a total of 3 Doctors.
There are a total of 4 Actors.
There are a total of 4 Singers.
There are a total of 7 Professors.
STEP 4: 연결
SELECT Name, CONCAT('(',SUBSTR(Occupation, 1,1),')') as occupation
FROM OCCUPATIONS
ORDER BY 1
;
SELECT CONCAT('There are a total of ', COUNT(Occupation),' ', Occupation,'s.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation)
;
OUT: Wrong Answer
Aamina (D)
Ashley (P)
Belvet (P)
Britney (P)
Christeen (S)
Eve (A)
Jane (S)
Jennifer (A)
Jenny (S)
Julia (D)
Ketty (A)
Kristeen (S)
Maria (P)
Meera (P)
Naomi (P)
Priya (D)
Priyanka (P)
Samantha (A)
There are a total of 3 Doctors.
There are a total of 4 Actors.
There are a total of 4 Singers.
There are a total of 7 Professors.
뭐가 틀렸지?
1) 1번 쿼리에서 Name과 직업 사이에 공백이 있음
2) 2번 쿼리에서 직업이 대문자로 되어있음
STEP 5: 수정
-- Name 컬럼을 CONCAT 안에 넣어줘서 공백을 지움
SELECT CONCAT(Name,'(',SUBSTR(Occupation, 1,1),')') as occupation
FROM OCCUPATIONS
ORDER BY 1
;
-- Occupation에 lower 함수를 적용하여 소문자로 나타내 주었다
SELECT CONCAT('There are a total of ', COUNT(Occupation),' ', lower(Occupation),'s.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation)
;
OUT: 정답
Aamina(D)
Ashley(P)
Belvet(P)
Britney(P)
Christeen(S)
Eve(A)
Jane(S)
Jennifer(A)
Jenny(S)
Julia(D)
Ketty(A)
Kristeen(S)
Maria(P)
Meera(P)
Naomi(P)
Priya(D)
Priyanka(P)
Samantha(A)
There are a total of 3 doctors.
There are a total of 4 actors. {-이하 생략-}
WITH 문과 WITH RECURSIVE는 테이블을 직접 만들 수 없는 상황에서 쿼리 안에 가상의 테이블을 만들어 JOIN 등을 쉽게 하도록 한다.
현업에서 WITH 문을 자주 썼는데, CTE(Common Table Expression)이라고 부르는 것은 오늘 처음 알았다.
가상의 테이블은 남발하면 쿼리의 작동 속도를 늦춰 권장하지 않는 방법이라고 적는 글도 왕왕 보았지만, 전에 있던 회사에서는 테이블이 이미 너무 많은 상황 + 스키마 정리가 되어있지 않아서 WITH문을 사용한 임시 테이블 생성 및 SQL을 통한 TABLE 전처리 (Python Pandas가 아닌 SQL로도 할 수 있다는 것을 처음 알았다.)를 곧잘 사용했다.
주로, 엄청 많이 쓰일 것 같지는 않지만 가상의 테이블이 필요할 때 (자주 사용하면 아예 테이블로 만드는 것이 낫다)
개인적으로 가장 정석적이고 가장 합리적이라고 생각했던 쿼리를 가져와서 약간 고쳤다. (By 류연식 님)
윈도 함수 ROW_NUMBER PARTITION OVER을 + LIMIT로 순위를 가져와서 숫자만 있는 테이블을 생성하는 것까지는 생각이 닿았지만, 왠지 실제 쿼리를 돌려보니 순위 값이 모두 1로만 나와서 (ROW_NUMBER는 순위 중복 허용을 안 하는데 왜 그렇게 나왔는 지는 모르겠다 ㅜ) 어떻게 타개할까 보던 중 가장 내가 해석 가능한, 사용 가능한 수준의 쿼리를 발견했다.
원문에서는 WITH RECURSIVE를 사용했지만, ROW_NUMBER를 매기는 마당에 재귀 할 것이 없다고 생각해서 WITH문으로 바꿔버림.
WITH TIMETABLE AS #TIMETABLE이라는 가상의 테이블 생성
(
SELECT ROW_NUMBER() OVER(ORDER BY DATETIME)-1 AS HOUR #윈도 함수를 통해 순위(숫자) 생성, 시작값이 1이므로 -1을 붙여줘서 0시~23시를 표현할 수 있게 되었다!
FROM ANIMAL_OUTS
LIMIT 24 #값은 24개까지만
)
위의 쿼리를 돌리면 HOUR 이라는 컬럼에 0 ~ 23까지의 값을 가진 테이블이 생성된다.
이후는 쉽다. LEFT JOIN으로 시간대를 붙여주면 끝.
값이 없는 시간대는 자동으로 0이 출력된다.
SELECT HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM TIMETABLE
LEFT JOIN ANIMAL_OUTS
ON DATE_FORMAT(DATETIME, '%H') = TIMETABLE.HOUR
GROUP BY HOUR
ORDER BY HOUR
여기서 원래는 HOUR(DATETIME) 컬럼을 통해 JOIN으로 붙여줄 생각이었지만, ON에서 DATE_FORMAT을 사용해 시간대를 떼어낸 쿼리가 더 효율적이라는 판단이 들어 ON 절에서 만져줬다.
RECURSIVE를 사용할 만큼 복잡한 케이스는 아니게 되었지만, SQL에서 반복문처럼 사용할 수 있는 함수를 알게 된 만큼 다른 케이스에서 사용할 수 있었으면 좋겠다. (진지하게 노가다로 0부터 23까지 원 컬럼 테이블을 만들까도 생각했음)
