[Programmers | SQL | GROUP BY 문제 풀이] 입양 시각 구하기(1) - Solution with 날짜 형식 변환 & COUNT()

KDT 실무형 스프링 백엔드 엔지니어 양성과정 6기 | SQL CODEKATA

⌚ 입양 시각 구하기(1)

🏷 관련 주제 : 날짜 형식 변환 HOUR() TRUNC() TO_CHAR()
COUNT() GROUP BY HAVING ORDER BY

---

✔ (Oracle) Solution with TO_CHAR(), TRUNC(), COUNT(), GROUP BY, HAVING, ORDER BY

SELECT TRUNC(TO_CHAR(DATETIME, 'HH24')) "HOUR"
       , COUNT(*) "COUNT"
FROM ANIMAL_OUTS
GROUP BY TRUNC(TO_CHAR(DATETIME, 'HH24'))
HAVING TRUNC(TO_CHAR(DATETIME, 'HH24')) BETWEEN 9 AND 19
ORDER BY HOUR;

실행 결과

---

✔ (MySQL) Solution with HOUR(), COUNT(), GROUP BY, HAVING, ORDER BY

SELECT HOUR(DATETIME) "HOUR"
       , COUNT(*) "COUNT"
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19
ORDER BY HOUR;

실행 결과

---

💥 오늘 만난 문제 & 나의 시도 💦 & 해결 방법 👍

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

---

오늘은 문제를 풀면서 점점 더 좋은 함수를 찾아서 풀이가 조금 지저분할 수 있으니 양해바랍니다.

1. ANIMAL_OUTS 테이블에서 DATETIME 칼럼을 조회해보자.

• Oracle & MySQL

  SELECT DATETIME
  FROM ANIMAL_OUTS;

  실행 결과

  

2. DATETIME칼럼의 날짜 형식이 시간만 출력되게 조회하자.

시간만 출력한 칼럼의 별칭을 "HOUR"라고 주자.

처음에 MySQL에서 DATE_FORMAT()을 이용했는데 더 좋은 방법을 찾았다.
EXTRACT(HOUR FROM 칼럼명)을 이용해 DATETIME에서 시간만 추출하였다.
그런데... 더 좋은 방법을 찾았다. HOUR(칼럼명)😍
그런데 Oracle에서는
EXTRACT를 사용하려면 CAST(DATETIME AS TIMESTAMP)에
EXTRACT()를 사용해야 실행이 되었다.(불편🤨)
차라리 TRUNC(TO_CHAR(DATETIME, 'HH24'))가 나은 것 같다.

• Oracle
  • EXTRACT() 사용하기

    (EXTRACT()를 사용하고 싶었는데 왜인지 DATETIME타입에 EXTRACT를 바로 사용할 때, 에러가 뜬다.)

    EXTRACT(HOUR FROM DATETIME) 실행 결과

    SELECT EXTRACT(HOUR FROM DATETIME) "HOUR"
    FROM ANIMAL_OUTS;

     SQL 실행 중 오류가 발생하였습니다.
    ORA-30076: invalid extract field for extract source

    
    

    SELECT EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) "HOUR"
    FROM ANIMAL_OUTS;

    실행 결과

    

  ---

  • TRUNC() & TO_CHAR() 사용하기

    TO_CHAR()만 사용하면 한자리 숫자는 앞에 0이 붙어서 출력됨.
    LTRIM(TO_CHAR(DATETIME, 'HH24'), '0')도 사용해 보았는데 TRUNC()가 더 짧아서 좋은 듯^^

    
    

    SELECT TRUNC(TO_CHAR(DATETIME, 'HH24')) "HOUR"
    FROM ANIMAL_OUTS;

---

• MySQL
  • HOUR() 사용하기 ( 👍초간단 👍)

    SELECT HOUR(DATETIME) "HOUR"
    FROM ANIMAL_OUTS;

  •  EXTRACT() 사용하기
    (👍이것도 DATE_FORMAT을 이용한 방법보다는 나았음.)

    SELECT EXTRACT(HOUR FROM DATETIME) "HOUR"
    FROM ANIMAL_OUTS;

    실행 결과

    DATETIME의 시간을 잘 추출하였으나 7 ~ 19 사이의 데이터를 가진 인스턴스들이 모두 조회되고 있다.

    

  ---

  • (👎비추천) DATE_FORMAT() 사용하기

    2 - 1. DATETIME칼럼에 DATE_FORMAT을 이용하여 시간을 조회하자.

    SELECT DATETIME
           , DATE_FORMAT(DATETIME, '%H') "HOUR"
    FROM ANIMAL_OUTS;

    실행 결과

    HOUR칼럼의 값이 한자리 수이면 문제의 예시와 달리 한자리 수로 출력되지 않고 앞에 0이 붙어서 출력된다.

    

    ---

    2 - 2. HOUR칼럼의 값 중 한자리 수인 값의 앞에 0이 출력되지 않도록 조회하자.

    CAST() 또는 CONVERT()를 이용해 데이터 타입을 변경할 수 있다.
    - CAST(변환 대상 AS 변환할 타입)
    - CONVERT(변환 대상, 변환할 타입)

    
    
    • CAST() 이용하기

      SELECT DATETIME
             , CAST(DATE_FORMAT(DATETIME, '%H') AS UNSIGNED) "HOUR"
      FROM ANIMAL_OUTS;

    • CONVERT() 이용하기

      SELECT DATETIME
             , CONVERT(DATE_FORMAT(DATETIME, '%H'), UNSIGNED) "HOUR"
      FROM ANIMAL_OUTS;

      실행 결과

      HOUR칼럼의 데이터가 잘 출력되었지만 7 ~ 19사이의 인스턴스들이 모두 조회되고 있다.

      

---

3. HOUR칼럼으로 GROUP BY하여 데이터별 인스턴스 수를 COUNT()함수로 조회하자.

COUNT(*) 칼럼은 "COUNT"라고 별칭을 주자

• Oracle

  SELECT TRUNC(TO_CHAR(DATETIME, 'HH24')) "HOUR"
         , COUNT(*) "COUNT"
  FROM ANIMAL_OUTS
  GROUP BY TRUNC(TO_CHAR(DATETIME, 'HH24'));

  실행 결과

  

---

• MySQL

  SELECT HOUR(DATETIME) "HOUR"
         , COUNT(*) "COUNT"
  FROM ANIMAL_OUTS
  GROUP BY HOUR;

  실행 결과

  HOUR칼럼의 데이터들이 7 ~19인 인스턴스들이 모두 조회되고 있다.

  

---

3. HOUR의 데이터가 9  ~ 19 사이인 인스턴스만 조회하자.

조건을 WHERE절에 줄 수도 있고 HAVING절에 줄 수도 있다.
MySQL의 경우 HAVING절에서는 별칭으로 조건을 줄 수 있음.👍

• Oracle
  (HAVING절의 조건을 그대로 WHERE절에 써도 같은 결과를 얻을 수 있다.)

  SELECT TRUNC(TO_CHAR(DATETIME, 'HH24')) "HOUR"
         , COUNT(*) "COUNT"
  FROM ANIMAL_OUTS
  GROUP BY TRUNC(TO_CHAR(DATETIME, 'HH24'))
  HAVING TRUNC(TO_CHAR(DATETIME, 'HH24')) BETWEEN 9 AND 19;

  실행 결과

  

  
  

---

• MySQL
  • HAVING절에 조건 주기
    (👍  별칭 사용 가능)

    SELECT HOUR(DATETIME) "HOUR"
           , COUNT(*) "COUNT"
    FROM ANIMAL_OUTS
    GROUP BY HOUR
    HAVING HOUR BETWEEN 9 AND 19;

    실행 결과

    HOUR 칼럼의 데이터가 9 ~19 사이인 인스턴스만 조회되었다.
    하지만 HOUR칼럼을 기준으로 정렬되어 있지 않다.

    

    
    

  ---

  • WHERE절 조건 주기 (👎 별칭 사용 불가)
    WHERE절에 별칭을 사용하면 어떻게 될까?

    SELECT EXTRACT(HOUR FROM DATETIME) "HOUR"
    , COUNT(*) "COUNT"
    FROM ANIMAL_OUTS
    WHERE HOUR BETWEEN 9 AND 19
    GROUP BY HOUR;

     SQL 실행 중 오류가 발생하였습니다.
    Unknown column 'HOUR' in 'where clause'

    SELECT HOUR(DATETIME) "HOUR"
           , COUNT(*) "COUNT"
    FROM ANIMAL_OUTS
    WHERE HOUR(DATETIME) BETWEEN 9 AND 19
    GROUP BY HOUR;

    실행 결과

    HOUR칼럼의 데이터가 9 ~19 사이인 인스턴스만 조회되었다.
    하지만 HOUR칼럼을 기준으로 정렬되어 있지 않다.

    

---

3. HOUR칼럼을 기준으로 오름차순 정렬하자.

• Oracle
  (별칭으로 정렬을 해 보았는데 된다.)

  SELECT TRUNC(TO_CHAR(DATETIME, 'HH24')) "HOUR"
         , COUNT(*) "COUNT"
  FROM ANIMAL_OUTS
  GROUP BY TRUNC(TO_CHAR(DATETIME, 'HH24'))
  HAVING TRUNC(TO_CHAR(DATETIME, 'HH24')) BETWEEN 9 AND 19
  ORDER BY HOUR;

  실행 결과

  

---

• MySQL

  SELECT HOUR(DATETIME) "HOUR"
         , COUNT(*) "COUNT"
  FROM ANIMAL_OUTS
  GROUP BY HOUR
  HAVING HOUR BETWEEN 9 AND 19
  ORDER BY HOUR;

  실행 결과

  

---

💬 무엇을 새롭게 알았는지

• Oracle
  • 날짜 형식 변경 : TO_CHAR()
    • 24시간 : HH24
•  MySQL
  • 데이터 타입 변환
    • CAST()
    • CONVERT()
  • SIGNED와 UNSIGNED의 차이를 알게 되었다.
  • 날짜 또는 시간 형식 지정 함수 DATE_FORMAT() 지정자
    • 24시간 : %H 또는 %k
    • 12시간 : %h 또는 %I

EXTRACT() 함수에 대해 알게 되었다.
LTRIM(), TRUNC()함수를 사용해 보았다.

---

📚 References(참고 자료)

• Mysql CAST/CONVERT 문자, 숫자, 날짜 형 변환하기
• [MySQL] DATE_FORMAT: 날짜/시간 표기 방식 지정하기
• Oracle - Date
• MySQL EXTRACT()
• 데이트 타임 칼럼에서 시간만 추출하기 + GROUB BY 피하기
• [오라클] TRIM, LTRIM, RTRIM 함수 사용방법 (문자, 공백, 0, 제거)