[day12] with recursive 재귀문 만들기

Q. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회

---

데이터에 포함된 시간에 대해서만 뽑는 게 아니라, 0~23시 모든 시간대별 통계를 알아야 한다.

hour

0 1 2 . . . 23

이런 테이블을 만들어준 다음 left join 시키자.

그럼 저 테이블은 어떻게 만들까?

---

방법1. 사용자 정의 변수 (SET / SELECT)

 

🔶 SET, SELECT 특징

SET	SELECT
한 번에 하나의 변수만 처리	한 번에 여러 변수 처리 가능
SET @변수이름 := 대입값	SELECT @변수이름 := 대입값 FROM 테이블

 

🔶 예시

SELECT @n := @n + 1 as hour
FROM animal_outs, (SELECT @n := -1 FROM DUAL) a
LIMIT 24

"서브쿼리의 결과값이 animal_outs의 모든 행과 짝을 이루게 하고,

그 값은 변수 -1부터 시작하는 @n에 1씩 더한 값이다.

그러한 값을 24행까지만 나타내라"

 

🔶 알아야 할 것

1️⃣ FROM절에 animal_outs 테이블을 넣어야 하는 이유 ⭐⭐
animal_outs를 이용해서 여러 개의 행을 만들 수 있기 때문에
(',(콤마)' 로 연결해서 서브쿼리와 카티션곱으로 조인됨)

> 포함 안 시켰을 때 결과

hour

0

‼️ 여기서 주의
만약 animal_outs의 행 개수가 24개 미만이면 23시까지 구할 수가 없다.
이 문제에서는 운 좋게 24개 이상이라 가능했지만 아니었으면 못 풀었음.

2️⃣ DUAL 테이블은 아무리 더미라도 하나의 서브쿼리이기 때문에 반드시 별칭을 주어야 한다.
3️⃣ mysql 에서는 'FROM DUAL' 생략 가능하다. (Oracle 에서는 적어야 함)
* DUAL이란? function 이나 계산식을 테이블 생성 없이 실행해보려는 용도로만 사용하는 테이블

 

🔶 문제 풀이

SELECT nn as HOUR,
        ifnull(count,0) as COUNT
FROM
(
	SELECT *
	FROM
		(
		SELECT @n := @n + 1 as nn
		FROM animal_outs, (SELECT @n := -1) a
		LIMIT 24
		) A
	LEFT JOIN 
		(
		SELECT HOUR(datetime) as hour,
       	COUNT(*) AS count
		FROM animal_outs o
		GROUP BY 1
		) B
	ON A.nn = B.hour
) C

"1. 0부터 시작하는 24개 행을 만들고 (테이블 A)

2. animal_outs 테이블에서 datetime의 시간과 시간대별 건수를 구하고 (테이블 B)

3. A와 B를 LEFT JOIN 시킨 다음

4. 0~23에 해당하는 시간대별 건수를 나타내라. null값은 0으로 나타내라."

 

 

방법2. WITH (RECURSIVE)

 

🔶 기본 구조

WITH RECURSIVE 테이블명 AS (
Non_RECURSIVE QUREY
        UNION ALL
        RECURSIVE QUREY
        )
        
SELECT 칼럼명
FROM 테이블명

 

🔶 알아야 할 것

1️⃣ 가상의 테이블을 만들 때 사용
2️⃣ RECURSIVE 여부에 따라 있으면 재귀, 없으면 비재귀로 나뉜다.
* 재귀란? 재참조하여 반복적으로 작동한다. 한마디로 반복문.
3️⃣ UNION 이랑 짝꿍?
4️⃣ 뒤에 본격적인 SELECT할 때 사용하기 위해서 칼럼명에 꼭 별칭 부여해주자.

 

🔶 예시

WITH RECURSIVE hours AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1 FROM hours WHERE hour < 23
)

SELECT h.hour AS HOUR
FROM hours h

"hours 라는 이름을 가진 테이블을 새로 만들거야.

어떻게 만들거냐면(어떻게 생겨먹었냐면),

hour

0

여기 밑으로

hour 값을 0부터 시작해서 1씩 계속 더해주는 일을 무한반복하는 테이블을 합치는거야. (수직결합)

대신 hour가 22일 때까지만 하고 멈춰야 돼.

 

자 이제

새로 만든 hours 테이블에서 hour 컬럼을 출력해. 칼럼명은 HOUR로 해줘.

"

 

🔶 문제 풀이

WITH RECURSIVE hours AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1 FROM hours WHERE hour < 23)

SELECT A.HOUR,
        IFNULL(B.cnt,0) COUNT
FROM
(SELECT h.hour AS HOUR
FROM hours h) A
LEFT JOIN
(SELECT HOUR(DATETIME) AS H,
        COUNT(*) AS cnt
FROM ANIMAL_OUTS O
GROUP BY 1) B
ON A.HOUR = B.H
ORDER BY 1

"hours 테이블에서 hour열만 뽑은 테이블 A랑

animal_outs 테이블에서 시간대별 입양건수을 뽑은 테이블 B를

LEFT JOIN해서

모든 시간대별 입양건수를 구해줘.

입양건수가 NULL값이면 0으로 출력해주고 시간대별 오름차순 정렬하렴."