입양 시각 구하기(2) #WITH RECURSIVE , SET함수

• 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

 

WITH RECURSIVE 구문 사용

WITH RECURSIVE TIME AS(
SELECT 0 AS h
UNION ALL
SELECT h+1 FROM TIME 
WHERE h < 23)

SELECT h, COUNT(HOUR(DATETIME)) AS 'COUNT'
FROM TIME LEFT OUTER JOIN ANIMAL_OUTS
ON h=HOUR(DATETIME)
GROUP BY h
ORDER BY h;

① WITH RECURSIVE TIME AS(

# 가상 테이블명 TIME 생성

 

② SELECT 0 AS h
    UNION ALL
    SELECT h+1 FROM TIME 

    WHERE h < 23)

# 0 초기값 설정

# 0 이라는 h 에서 1을 더해준다

# h 가 23이 되기 전까지, 즉 h=22  이고 +1 을 해주기 때문에 0부터 23까지의 값을 구해준다

 

③ SELECT h, COUNT(HOUR(DATETIME)) AS 'COUNT'
     FROM TIME LEFT OUTER JOIN ANIMAL_OUTS
     ON TIME.h=HOUR(ANIMAL_OUTS.DATETIME)

# 가상의 테이블 TIME 과 animal_outs 테이블을 LEFT 조인 해준다(0~23까지의 값 전부)

# join 은 TIME 의 h 값과 animal_outs 의 datetime 값 

# datetime 을 카운트 해준다

* ON h=hour(datetime) 으로 바꾸어 주어도 됨

: 각각의 테이블을 꼭 호출해주어야 하는줄 알았는데, 컬럼명이 1개이면 가능한 듯 ?

 

 

SET 함수 사용

SET @hour =-1;
SELECT (@hour := @hour+1) AS HOUR,
        (SELECT COUNT(*) 
         FROM ANIMAL_OUTS 
         WHERE HOUR(DATETIME) = @hour) AS COUNT 
FROM ANIMAL_OUTS
WHERE @hour < 23
ORDER BY HOUR;

 

① SET @hour = -1;

# hour 라는 변수에 -1을 넣어준다

 

② SELECT (@hour := @hour+1) AS HOUR,

# hour 라는 변수에 +1 을 대입해준다.

>> -1 := -1 + 1

>> 0 := 0 + 1

>> 1 := 1 + 1

>> 반복

 

③ WHERE @hour < 23

# 변수 hour 가 22까지 반복해준다

>> 22 := 22+1

 

④ WHERE HOUR(DATETIME) = @hour) AS COUNT 

# hour 변수와 시간이 같을 때 카운트 해준다.

 

 

 

---

WITH RECURSIVE

WITH RECURSIVE 테이블명 AS(
SELECT 초기값 AS 컬럼별명1 # 비반복문
UNION ALL                 # 두 테이블 연결
SELECT 컬럼별명1 계산식 FROM 테이블명 
WHERE 제어문              # 정지조건
)

 

 

SET 함수

SET @변수명 = 변수값 ;       # 변수 선언 및 값 대입
SELECT @변수명 := 대입값    # 대입값 입력

SET 이외의 명령문에서는 = 가 비교연산자로 취급되기 때문에

:= 를 사용하여 값을 대입해준다.

 

단일쿼리문이 아닌, 여러 쿼리문을 수행할 때에는 세미콜론(;) 을 넣어 구분해주어야 한다

 

---

참고:

https://school.programmers.co.kr/learn/courses/30/lessons/59413#qna

https://velog.io/@majaeh43/SQL-WITH-RECURSIVE

https://velog.io/@kimmjieun/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4-%EC%9E%85%EC%96%91-%EC%8B%9C%EA%B0%81-%EA%B5%AC%ED%95%98%EA%B8%B02