TIL

[TIL] WEEK 3 THUR -DUAL, 사용자 변수, 시간대 별 통계량, RECURSIVE

ollive 2024. 7. 11. 20:37

1. ORDER BY 수식작성

문제를 풀다가 ORDER BY에 수식을 작성할 수 있다는 것을 알게 되었다. 아니였으면 서브쿼리를 이용해야 했는데 수식덕분에 한 쿼리에 식을 담을 수 있다.

SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS I, ANIMAL_OUTS O
WHERE I.ANIMAL_ID=O.ANIMAL_ID
ORDER BY O.DATETIME-I.DATETIME DESC LIMIT 2

문제링크 : 오랜기간 보호한 동물(2)

 

2. DUAL TABLE

  • 오라클에서 기본적으로 제공하는 칼럼1개와 데이터 1개가 들어있는 기본 더미테이블이다. 
  • DUAL이라는 기능은 SQL내에서 평션이나 계산식을 수행해보기위해 사용할 수 있다.
  • 함수의 쓰임이나 출력되는 데이터를 확인해 보려고 할 때 테이블을 생성하지 않고도 결과값을 알 수 있다. 
SELECT 1+2+3+4,
       CONCAT('DATA'," ",'KNOWS'," ",'EVERYTHING')
FROM DUAL

 

위의 식은 FROM을 사용하지 않아도 작동가능하다. FROM DUAL 이 생략된 구문으로 인식하기 때문이다.

SELECT 1+2+3+4,
       CONCAT('DATA'," ",'KNOWS'," ",'EVERYTHING')

 

3. 시간대 별 통계량 구하기

기존의 수식을 활용하면, DATETIME형식내 있는 레코트만 반환이 된다. 이 데이터 원본은 7에서부터 19까지의 시간 데이터를 가지고 있어서 문제에서 제시하는 0에서 24의 데이터를 추출할 수 없다.

SELECT HOUR(DATETIME), COUNT(*)
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 00 AND 23
GROUP BY 1
ORDER BY 1

 

따라서 먼저 0에서 23에 해당하는 숫자열 데이터를 만들고, 여기에 시간과 시간별 통계량 데이터를 추가 시키는 방법으로 시간별 통계량을 구할 수 있다.

WITH HOURS AS (SELECT @N := @N +1 'HH'
               FROM ANIMAL_OUTS, (SELECT @N := -1 FROM DUAL) N
               LIMIT 24)
               
SELECT HH, COUNT(ANIMAL_ID)
FROM HOURS LEFT JOIN ANIMAL_OUTS ON HOUR(DATETIME) = HH
GROUP BY 1
ORDER BY 1

 

위 식을 만드는 방법은 다음과 같다. 우선 시간을 순차적으로 출력해주는 테이블을 만들어야한다.

 

SELECT @ 변수명 := 대입값

  • SELECT @N:=0 FROM DUAL : DUAL TABLE이 N이라는 변수값과 그 변수의 값이 0으로 초기화된다. (사진1)
  • @는 사용자변수를 선언할때 사용하는 기호로 변수명과 @사이에 공백이 있으면 안 된다.
  • :=는 할당연산자이다. 위의 식에서는 사용자변수N에 0을 넣는 의미로 사용되었다. 식에 =이 들어간다면, :=을 사용하여 할당연산자를 구분해 주어야한다. (관련 정보: https://dev.mysql.com/doc/refman/5.7/en/assignment-operators.html)

 

그 다음 이 TABLE과 이를 사용할 TABLE과 합쳐준다. 이때 다시 한번 변수(HH)를 만들어주는데 @N := @N+1 을 사용하여 N의 값이 0부터 1씩 증가하여 추출되는 식으로 지정한다. 한없이 커지므로 LIMIT를 통해 통제해주어야 한다.(사진2)

SELECT @N := @N +1 AS HH
FROM ANIMAL_OUTS , (SELECT @N:=0 FROM DUAL) N
LIMIT 23

 

사진 1) SELECT @N:=0 FROM DUAL
사진 2) @N := @N+1

 

이때 구하려는 값이 0부터 23이기 때문에 서브쿼리의 @N을 -1로 지정하고 그로부터 24를 더해주어 23을 추출하는 값을로 수정해준다. 결과가 0부터 시작하는 것을 알 수 있다.(사진 3)

SELECT @N := @N +1 'HH'
FROM ANIMAL_OUTS, (SELECT @N := -1 FROM DUAL) N
LIMIT 24

사진 3) @N := -1, LIMIT = 24

이렇게 해주면 시간을 0에서 23시로 만들어주는 작업이 끝났다. 이 테이블과 구해야하는 값이 있는 테이블을 조인해서 만들어주면 시간대별 통계량을 구할 수 있다.

SELECT HH, COUNT(ANIMAL_ID)
FROM (SELECT @N := @N +1 'HH'
	  FROM ANIMAL_OUTS, (SELECT @N := -1 FROM DUAL) N	
      LIMIT 24) HOURS
      LEFT JOIN ANIMAL_OUTS 
      ON HOUR(DATETIME) = HH
GROUP BY HH
ORDER BY HH

 

조인보다 WITH을 사용하여 합치면 가독성이 더 좋아진다.

WITH HOURS AS (SELECT @N := @N +1 'HH'
               FROM ANIMAL_OUTS, (SELECT @N := -1 FROM DUAL) N
               LIMIT 24)
               
SELECT HH, COUNT(ANIMAL_ID)
FROM HOURS 
	 LEFT JOIN ANIMAL_OUTS 
     ON HOUR(DATETIME) = HH
GROUP BY 1
ORDER BY 1

3.번과 관련한 정보 출처 :https://devlogjoy.tistory.com/23

 

[MySQL] 시간대별 방문 통계 구하기

방법 1 SELECT Hour(access_date) AS hh , count(access_date) AS cnt FROM access_log WHERE access_date BETWEEN '2022-01-01 00:00:00' AND date_format(now(),'%Y-%m-%d 23:59:59') GROUP BY hh ORDER BY hh ASC; access_date 에서 Hour() 로 시간값(0,1,2,3...)

devlogjoy.tistory.com

 

4. 시간대 별 통계량 구하기2 - RECURSIVE

재귀 쿼리를 사용하여 구할 수도 있다. 재귀 쿼리의 구조는 다음과 같다.

WITH RECURSIVE 재귀쿼리이름 (n)  #사용자변수명을 n으로 지정
AS (
      SELECT 1                  # 초기 쿼리(anchor member)
      UNION ALL			# UNION ALL 또는 UNION DISTINCT로만 결합
      SELECT n + 1 		# 재귀 쿼리(recursive member)
      FROM 재귀쿼리이름          # WITH에서 지정한 이름과 동일해야 반복수행이 가능
      WHERE n < 3		# 종료 조건 지정
    )

 

재귀 쿼리 문은 WITH RECURSIVE를 통해 사용할 수 있다.

내부구조는 크게 anchor member(초기 쿼리)와 recursive member(재귀쿼리)로 나눠진다.

  • 초기 쿼리:
    • 기본 결과(초기 행)을 형성한다.
    • 위에 코드에서는 n = 1을 형성하고 그 다음은 n =2로 계속 이어진다.
  • 재귀 쿼리:
    • '재귀쿼리이름'을 참조하여 결과 도출한다.
    • UNION ALL 또는 UNION  DISTINCT와 사용된다.
    • 위 코드에서는 n = 1+1이 되고, 그 다음엔 n = 2이므로 n = 2+1이 되어 계속 반복한다. 

초기쿼리를 실행하여 기본 결과를 만든 후, 이 결과를 재귀쿼리에서 반복하여 실행하여 결과가 출력된다.

결과는 종료 조건이 충족될 때까지(또는 빈 결과값 반환될 때) 반복되므로 조건을 필수로 제시해 주어야한다.

 

조건까지 충족되면 union을 통해 두 결과를 결합하여 연산이 끝나게 된다. 


<재귀쿼리 작성 시, 주의해야할 점>

  • 집계함수, group, order, limit, distinct는 재귀 쿼리에 들어갈 수 없다.
  • 연산자에 distinct는 가능하다
  • 재귀쿼리의 이름은 WITH절 하나에 한번만 참조가능하는 점이다.
  • 재귀 쿼리의 제한 요소들은 초기 쿼리에서는 상관없이 작성가능하다.

 

위의 방법에 따라 풀이를 작성하면 다음과 같다.

WITH RECURSIVE HOURS (HH) AS (	SELECT 0
                                UNION ALL
                                SELECT HH + 1
                                FROM HOURS
                                WHERE HH < 23)

 

WITH RECURSIVE HOURS (HH) AS (SELECT 0
		  	      UNION ALL
                              SELECT HH + 1
                              FROM HOURS
                              WHERE HH < 23)

SELECT HH, COUNT(HOUR(O.DATETIME)) AS COUNT 
FROM ANIMAL_OUTS O 
	 RIGHT OUTER JOIN HOURS H
     ON HOUR(O.DATETIME) = H.HH
GROUP BY 1
ORDER BY 1

4.번과 관련한 정보 출처: https://www.mysqltutorial.org/mysql-basics/mysql-recursive-cte/

 

5. 시간대 별 통계량 구하기 - 미친 DUAL

DUAL과 UNION만을 활용하여 시간대를 만들 수도 있다.

DUAL에 값은 0, 변수명은 HOUR로 넣으면 결과는 다음과 같다.

SELECT 0 HOUR
FROM DUAL

 

WITH을 이용해서 시간대 구문을 만들고, 조인을 통해 시간대별 입양건을 더하면 된다.

WITH HOURS AS (SELECT 0 HOUR
                FROM DUAL
                UNION 
                SELECT 1 HOUR
                FROM DUAL
                UNION 
                SELECT 2 HOUR
                FROM DUAL
                UNION 
                SELECT 3 HOUR
                FROM DUAL
                UNION 
                SELECT 4 HOUR
                FROM DUAL
                UNION 
                SELECT 5 HOUR
                FROM DUAL
                UNION 
                SELECT 6 HOUR
                FROM DUAL
                UNION 
                SELECT 7 HOUR
                FROM DUAL
                UNION 
                SELECT 8 HOUR
                FROM DUAL
                UNION 
                SELECT 9 HOUR
                FROM DUAL
                UNION 
                SELECT 10 HOUR
                FROM DUAL
                UNION 
                SELECT 11 HOUR
                FROM DUAL
                UNION 
                SELECT 12 HOUR
                FROM DUAL
                UNION 
                SELECT 13 HOUR
                FROM DUAL
                UNION 
                SELECT 14 HOUR
                FROM DUAL
                UNION 
                SELECT 15 HOUR
                FROM DUAL
                UNION 
                SELECT 16 HOUR
                FROM DUAL
                UNION 
                SELECT 17 HOUR
                FROM DUAL
                UNION 
                SELECT 18 HOUR
                FROM DUAL
                UNION 
                SELECT 19 HOUR
                FROM DUAL
                UNION 
                SELECT 20 HOUR
                FROM DUAL
                UNION 
                SELECT 21 HOUR
                FROM DUAL
                UNION 
                SELECT 22 HOUR
                FROM DUAL
                UNION 
                SELECT 23 HOUR
                FROM DUAL
                )
                
SELECT 
    HOUR
    ,COUNT(ANIMAL_ID)
FROM 
    HOURS HS 
    LEFT JOIN ANIMAL_OUTS AO
    ON HS.HOUR = HOUR(AO.DATETIME)
GROUP BY 
    HOUR