WITH절 = 가상테이블
with절 sql에서 쿼리를 작성할 때 하나의 서브쿼 또는 임시 테이블처럼 활용할 수 있는 기능이다. = 매크로처럼 사용이 가능하다.
- 장점
1) 코드의 가독성을 높여준다.
2) 서브쿼리를 사용하면 가독성이 떨어진다 -> 어떤 목적으로 사용되었는지 알기 어렵다.
3) with절을 여러번 사용하여 가독성 문제를 해소할 수 있다.
4) SQL의 성능을 개선할 수 있다.
- 사용
WITH 가상테이블명 AS (
WITH절로 저장하고 싶은 SQL쿼리문)
SELECT 속성1, 속성2, 가상테이블명
FROM 테이블 JOIN 가상테이블 A.속성 = B.속성;
가상테이블을 활용하여 본절을 작성할 수 있다.
WITH Salesemployees AS (
SELECT FirstName, LastName, JobTitle, Salary
FROM employees a
JOIN departments b ON a.DepartmentID = b.DepartmentID
WHERE DepartmentName = 'Sales')
SELECT FirstName, LastName, JobTitle, Salary
FROM Salesemployees;WITH RECURSIVE(재귀 쿼리)
RECURSIVE 여부에 따라 재귀, 비재귀 두 가지 방법으로 사용한다.
WITH RECURSIVE구문은 가상 테이블을 생성하면서 가상 테이블 자신의 값을 참조하여 값을 결정할 때 사용한다.
1) 메모리 상에 가상 테이블 저장
2) 반드시 UNION사용
3) 반드시 비반복문도 최소 1개 요구, 한번만 실행
4) 바깥의 가상 테이블을 참조하는 문장(반복문)이 반드시 필요하다.
5) 반복되는 문장은 반드시 정지조건이 요구된다.
EX. 0~23의 값을 갖는 임시 테이블 생성
WITH RECURSIVE cte (n) AS
(
SELECT 0 // 재귀 초기값
UNION ALL
SELECT n + 1 // 재귀 FROM cte WHERE n < 23 // 재귀 정지 조건
)초기값 1부터 23까지 데이터를 갖는 가상 테이블 생성한다.
LV.4 입양 시각 구하기(2)
ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
WITH RECURSIVE cte (n) AS
(
SELECT 0
UNION ALL
SELECT n+1 FROM cte WHERE n<23
)
SELECT n AS HOUR, COUNT(ANIMAL_ID)
FROM cte LEFT JOIN ANIMAL_OUTS ON n=HOUR(DATETIME)
GROUP BY n
ORDER BY n;LV.4 저자 별 카테고리 별 매출액 집계하기
- 테이블이 3개가 주어졌을 때
그냥 JOIN할 수 있는 컬럼이 있다면 A,B,C로 지정하여 JOIN하면 된다.
- GROUP BY
저자 별, 카테고리 별 ~별이 들어가 있으면 그룹별로 묶어 준 뒤 집계함수 SUM을 사용하면 된다.
SELECT A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(A.PRICE*C.SALES) AS TOTAL_SALES
FROM BOOK AS A
JOIN AUTHOR AS B ON A.AUTHOR_ID=B.AUTHOR_ID
JOIN BOOK_SALES C ON A.BOOK_ID=C.BOOK_ID
WHERE YEAR(C.SALES_DATE) = 2022 AND MONTH(C.SALES_DATE)=1
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY A.AUTHOR_ID, A.CATEGORY DESC;
