[SQL KIT] (2024) 입양 시각 구하기(2)(설명/코드/정답)

 

문제 링크 : https://school.programmers.co.kr/learn/courses/30/lessons/59413

[목차]

1. 정상 코드
   - 방법 1. 번호 붙이기 함수 ROW_NUMBER() 사용
   - 방법 2. 재귀 CTE 사용
   - 방법 3. 변수(@num)를 사용( 반복문과 유사한 효과 사용)
2. 0~23 정수 테이블 만드는 방법
3.  Advanced. CTE, SET

 

 

 

 

정상 코드.

 

방법 1. 번호 붙이기 함수  ROW_NUMBER() 사용

SELECT HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM ( SELECT (ROW_NUMBER() OVER () - 1) AS HOUR
       FROM ANIMAL_OUTS
       LIMIT 24) AS 24NUM_TABLE 
     LEFT JOIN ANIMAL_OUTS ON HOUR = HOUR(DATETIME)
GROUP BY HOUR
ORDER BY HOUR

 

 

방법 2. 재귀 CTE 사용 

WITH RECURSIVE 재귀CTE사용시간표시 AS (
  SELECT 0 AS HOUR
  UNION
  SELECT HOUR + 1
  FROM 재귀CTE사용시간표시
  WHERE HOUR < 23
)

SELECT HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM 재귀CTE사용시간표시 
     LEFT JOIN ANIMAL_OUTS ON HOUR = HOUR(DATETIME)
GROUP BY HOUR
ORDER BY HOUR;

 

 

방법 3. 변수(@num)를 사용( 반복문과 유사한 효과 사용)

SET @num = 0;
SELECT (@num := @num + 1) -1  AS HOUR,
       (SELECT COUNT(DATETIME)
        FROM ANIMAL_OUTS
        WHERE @num = HOUR(DATETIME)) AS COUNT
FROM ANIMAL_OUTS
WHERE @num < 24
ORDER BY HOUR

 

 

 

 

내부 동작 순서 (방법 1)

 

1. FROM 절

(SELECT (ROW_NUMBER() OVER () - 1) AS HOUR 

FROM ANIMAL_OUTS 

LIMIT 24) AS 24NUM_TABLE: 

• ANIMAL_OUTS 테이블에서 24개의 행을 선택하고,
• 각 행에 대해 윈도우 함수인 ROW_NUMBER()를 사용하여 0부터 23까지의 순차적인 값을 생성합니다.
• (결과 HOUR이라는 이름의 컬럼에 24개의 행으로 구성된 서브쿼리(인라인 뷰) 생성)

 

LEFT JOIN ANIMAL_OUTS ON HOUR = HOUR(DATETIME):

• 서브쿼리(인라인 뷰) 로 생성한 24NUM_TABLE 테이블의  HOUR컬럼의 값과
• ANIMAL_OUTS 테이블의 DATETIME에서 추출한 시간이 일치하는 경우,
• LEFT JOIN 하여 해당 시간대에 속하는 ANIMAL_OUTS의 모든 행을 가져옵니다.
• 만약 일치하는 데이터가 없으면 NULL 값으로 채웁니다.

 

2. GROUP BY 절 

JOIN을 통해 가져온 결과를, HOUR을 기준으로 그룹화합니다.

 

3. SELECT 절

각 그룹에서 HOUR을 선택하고, 해당 그룹의 ANIMAL_ID의 수를 COUNT 합니다. 

 

4. ORDER BY 절

결과를 HOUR을 기준으로 오름차순 정렬합니다.

 

 

 

코드 실행 결과.

HOUR	COUNT
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	3
8	1
9	1
10	2
11	13
12	10
13	14
14	9
15	7
16	10
17	12
18	16
19	2
20	0
21	0
22	0
23	0

 

 

 

 

 

0~23 정수 테이블 만드는 방법

---

MySQL

SELECT 0

코드 실행 결과.

SELECT 1

 

코드 실행 결과.

 

SELECT 0 AS HOUR
UNION 
SELECT 1

컬럼 별칭은 가장 처음 SELECT 절에 한 번만 붙여준다.

 

코드 실행 결과.

 

 

SELECT 0 AS HOUR
UNION 
SELECT 1 
UNION 
SELECT 2 
UNION 
SELECT 3 
UNION 
SELECT 4
UNION 
SELECT 5 
UNION 
SELECT 6 
UNION 
SELECT 7 
UNION 
SELECT 8 
UNION 
SELECT 9
UNION 
SELECT 10 
UNION 
SELECT 11 
UNION 
SELECT 12 
UNION 
SELECT 13 
UNION 
SELECT 14
UNION 
SELECT 15 
UNION 
SELECT 16 
UNION 
SELECT 17 
UNION 
SELECT 18 
UNION 
SELECT 19
UNION 
SELECT 20 
UNION 
SELECT 21 
UNION 
SELECT 22 
UNION 
SELECT 23;

 

코드 실행 결과.

 

 

Advanced. CTE, SET

---

WITH RECURSIVE 재귀CTE사용시간표시 AS (
  SELECT 0 AS HOUR
  UNION
  SELECT HOUR + 1
  FROM 재귀CTE사용시간표시
  WHERE HOUR < 23
)

SELECT HOUR FROM 재귀CTE사용시간표시;

CTE 사용 방법

WITH RECURSIVE를 사용하여 0부터 23까지의 시간대를 생성하는 방법은 간단하지만 일반적으로 성능이 좋지 않다.

 

코드 실행 결과.

 

 

SET @hour = 0;

SELECT (@hour := @hour + 1) - 1 AS HOUR
FROM INFORMATION_SCHEMA.TABLES 또는 테이블명
LIMIT 24;

변수를 사용하여 값을 증가시키는 표현식

 :=  변수에 값을 할당하는 대입 연산자

INFORMATION_SCHEMA.TABLES

 

사용자 정의 변수 @hour을 0으로 초기화 
@hour := @hour + 1은 @hour 변수에 현재 값에 1을 더한 값을 할당
(@hour := @hour + 1) - 1은 할당 후에 증가한 값을 1 감소시켜서 현재 행의 HOUR 값 얻음 (0~23)

 

 

코드 실행 결과.

 

아래 코드도 위 코드와 같은 실행 결과 출력

SET @hour = -1;

SELECT @hour := @hour + 1 AS HOUR
FROM INFORMATION_SCHEMA.TABLES
LIMIT 24;