SQL ) LV4. 입양 시각 구하기(2)
관련 개념
- WITH 문
- 대량의 데이터를 다룰 때, 임의의 결과 저장할 때마다 테이블을 생성하거나 저장하기가 번거롭다.
- 이때, 임시 결과를 정의하고, 임시 테이블을 만들어 해당 테이블에 값을 저장할 수 있다.
- 동일한 쿼리를 자주 사용할 때, 반복적인 사용이 가능하도록 하기 위해 WITH절을 사용한다.
WITH 임시테이블명 AS
(
SELECT 조회하려는 칼럼
FROM 테이블명
...
)
SELECT * FROM 임시테이블명;
- WITH RECURSIVE 문
- 재귀 쿼리
- 반드시 UNION을 사용한다.
- 비 반복문도 최소한 1개가 요구 된다.(처음 한 번 실행)
- 반복문은 정지조건이 필요하다.
WITH RECURSIVE 임시테이블명 AS
(
-- 첫 번째 루프에서만 실행(비반복문)
SELECT 1 AS N
UNION ALL
-- 반복문
SELECT N + 1 AS NUM
FROM 임시테이블명
WHERE N < 3 -- 종료조건
)
접근법
- WITH RECURSIVE 문을 통해 0~23까지의 값을 갖는 HOUR칼럼을 보여주는 임시테이블을 생성했다.
- 이후, 해당 임시 테이블과 ANIMAL_OUTS테이블에서 시간만추출한 인라인뷰를 임시테이블 기준으로 LEFT JOIN한 뒤, 임시테이블의 HOUR칼럼 기준으로 그룹화 했다.
- 이때, 인라인뷰의 DATETIME칼럼을 COUNT하여 각 시간별 입양 횟수를 조회했다.
코드
WITH RECURSIVE CTE AS
(
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR + 1
FROM CTE
WHERE HOUR < 23
)
SELECT HOUR, COUNT(DATETIME) COUNT
FROM CTE C LEFT JOIN (SELECT HOUR(DATETIME) DATETIME FROM ANIMAL_OUTS) A ON C.HOUR = A.DATETIME
GROUP BY HOUR
ORDER BY HOUR;
SQL ) LV4. 그룹별 조건에 맞는 식당 목록 출력하기
접근법
- WHERE절에서 서브쿼리로 REST_REVIEW테이블을 MEMBER_ID 기준으로 그룹화했을 때 행의 개수를 COUNT한 값 기준으로 내림차순하고, 가장 첫 번째 행의 MEMBER_ID를 반환했다.
- 이때, 반환된 값과 동일한 MEMBER_ID를 갖는 회원의 이름, 리뷰 내용, 작성 날짜를 출력했다.
코드
SELECT M.MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') REVIEW_DATE
FROM MEMBER_PROFILE M JOIN REST_REVIEW R ON M.MEMBER_ID = R.MEMBER_ID
WHERE M.MEMBER_ID = (SELECT MEMBER_ID FROM REST_REVIEW
GROUP BY MEMBER_ID ORDER BY COUNT(*) DESC LIMIT 1)
ORDER BY 3, 2;
SQL ) LV4. 년, 월, 성별 별 상품 구매 회원 수 구하기
접근법
- 년, 월, 성별을 기준으로 그룹화한 뒤에 레코드 수(회원 수)를 카운트했다.
- 이때, 주의할 것은 COUNT()를 해버리면 같은년월에 여러번 구매한 회원이 중복으로 집계 될 수 있기 때문에 COUNT할 때 DISTINCT키워드를 추가해야한다.
코드
SELECT YEAR(SALES_DATE) YEAR, MONTH(SALES_DATE) MONTH, GENDER, COUNT(DISTINCT O.USER_ID) USERS
FROM ONLINE_SALE O JOIN USER_INFO U ON O.USER_ID = U.USER_ID
WHERE GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER;
SQL ) LV4. 주문량이 많은 아이스크림들 조회하기
접근법
- 각 테이블에서 FLAVOR기준 그룹화 후, TOTAL_ORDER의 합계와 FLAVOR 칼럼을 가진 인라인뷰를 생성한 뒤, FLAVOR 기준 JOIN연산 했다.
- 총 주문량이 큰 상위 3개의 레코드를 조회해야하므로
- ORDER BY절에서 인라인뷰의 TOTAL_ORDER를 합산한 값 기준 내림차순 정렬 후 LIMIT연산으로 상위 3개의 레코드만 조회하도록 했다.
코드
SELECT F.FLAVOR
FROM (SELECT FLAVOR, SUM(TOTAL_ORDER) TOTAL_ORDER FROM FIRST_HALF GROUP BY 1) F
JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) TOTAL_ORDER FROM JULY GROUP BY 1) J
ON F.FLAVOR = J.FLAVOR
ORDER BY F.TOTAL_ORDER + J.TOTAL_ORDER DESC
LIMIT 3;
