데이트 타임 칼럼에서 시간만 추출하기 + GROUB BY 피하기

#SQL 문을 연습하자 #Programmers

---

코딩테스트 연습 - 입양 시각 구하기(1)

> DATETIME 칼럼에서 시간만 추출해서 그 시간에 해당하는 레코드의 수와 함께 리턴하는 문제

ORACLE

SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, COUNT(ANIMAL_ID)
FROM ANIMAL_OUTS
GROUP BY TO_CHAR(DATETIME, 'HH24')
HAVING TO_CHAR(DATETIME, 'HH24') > 8 AND TO_CHAR(DATETIME, 'HH24') < 20
ORDER BY HOUR;

 

몰랐는 데, ORACLE에서 표현식의 ALIAS는 GROUP BY 에도, WHERE 절에도 사용할 수 없었다. 
사용하게 되면 invalid identifier 에러가 발생한다.
그렇지만 ORDER BY 에선 사용할 수 있다 ㅎㅎ..

이래서 ORACLE 이 싫다..

너무 표현식을 중복해서 작성하게 되어서 이게 맞나.. 싶어서 찾아보던 중
ORACLE에서 GROUP BY 보다 WHERE 절을 쓰는 게 더 낫나고 권고해서
PARTITION BY와 WHERE 절로 한번 바꾸어 보았다.
(window 함수가 GROUP BY 보다 낫다고 알고 있는 데, 사람마다 다르게 말해서 좀 더 찾아봐야 한다)

SELECT 
  DISTINCT(TO_CHAR(DATETIME, 'HH24')) AS HOUR, 
  (COUNT(ANIMAL_ID) OVER (PARTITION BY TO_CHAR(DATETIME, 'HH24'))) AS COUNT
FROM 
  ANIMAL_OUTS
WHERE TO_CHAR(DATETIME, 'HH24') > 8 AND TO_CHAR(DATETIME, 'HH24') < 20
ORDER BY HOUR;

추가로 DATE 타입에서 숫자 값을 얻을 때 TO_CHAR 보다 EXTRACT 가 더 낫다고 해서,
TO_CHAR을 EXTRACT로 바꿔볼까 했는데, DATETIME 타입은 HOUR, MINUTE, SECOND를 추출할 수 없다고 한다.

If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.

 

MYSQL

SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID)
FROM ANIMAL_OUTS
GROUP BY HOUR 
HAVING HOUR > 8 AND HOUR < 20
ORDER BY HOUR;

 

MYSQL는 SQL 문이 깔끔하게 나와서 좋았다. 시간을 추출하기 위해선 HOUR 함수를 쓰면 된다.

MYSQL 도 GROUP BY는 느리며, WHERE 절과 INDEX를 사용하는 걸 권고한다.

The most general way to satisfy a 
GROUP BY
 clause is to scan the whole table and create a new temporary table where all rows from each group are consecutive, and then use this temporary table to discover groups and apply aggregate functions (if any)

INDEX 를 설정할 순 없으니 PARTITION BY와 WHERE를 사용해보았다.

SELECT 
    DISTINCT(HOUR(DATETIME)) AS HOUR, 
    COUNT(ANIMAL_ID) OVER (PARTITION BY HOUR(DATETIME))
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) > 8 AND HOUR(DATETIME) < 20
ORDER BY HOUR;

 

---

결론

• 시간을 추출하는 방법은 MYSQL 에선 HOUR, ORACLE 에선 TO_CHAR 또는 EXTRACT
• EXTRACT 가 TO_CHAR 보다 낫지만, DATETIME, DATE 타입은 시분초를 추출할 수 없음
• MYSQL 은 ALIAS 를 WHERE에서 사용할 수 없음(invalid identifier)
• ORACLE 은 ALIAS를 GROUP BY, WHERE에서 사용할 수 없음(invalid identifier)
•  GROUP BY는 느리다. WHERE + INDEX + PARTITION BY 등을 사용해보자.

 

끝!