[Programmers] GROUP BY

ANIMAL_INS  테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. 
ANIMAL_INS  테이블 구조는 다음과 같으며, 
ANIMAL_ID,  ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE 는 각각
동물의 아이디,    생물 종,            보호 시작일,       보호 시작 시 상태,        이름,     성별 및 중성화 여부를 나타냅니다.

 

1) 고양이와 개는 몇 마리 있을까 동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

SELECT ANIMAL_TYPE, COUNT(*) FROM ANIMAL_INS  GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE ASC;

---

2) 동명 동물 수 찾기 동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.

SELECT NAME, COUNT(NAME)  FROM ANIMAL_INS  GROUP BY NAME HAVING COUNT(NAME) > 1 ORDER BY NAME ASC;

---

 

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. 
ANIMAL_OUTS 테이블 구조는 다음과 같으며, 
ANIMAL_ID,  ANIMAL_TYPE, DATETIME,  NAME,  SEX_UPON_OUTCOME는 각각 동물의
   아이디,          생물 종,       입양일,     이름,      성별 및 중성화 여부를 나타냅니다.

 

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

SELECT HOUR, COUNT(*) AS COUNT FROM (SELECT TO_CHAR(DATETIME,'HH24') AS hour FROM ANIMAL_OUTS)  CONNECT by level <= 10; GROUP BY HOUR HAVING HOUR BETWEEN 9 AND 19 ORDER BY HOUR ;

---

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

SELECT LV.HOUR, NVL(COUNT, 0) AS COUNT FROM (SELECT TO_CHAR(DATETIME, 'HH24') AS hour, COUNT(*) AS count       FROM ANIMAL_OUTS       GROUP BY TO_CHAR(DATETIME, 'HH24')       ORDER BY hour) OG,       (SELECT LEVEL - 1 AS hour FROM DUAL CONNECT BY LEVEL <= 24 ) LV WHERE LV.HOUR = OG.HOUR (+) ORDER BY LV.HOUR;

---

SQL을 잘 사용하지 않은 지 오래되어 4번 문제를 풀 때는 구글링이 필요했다.

 

4번 문제의 경우 필요한 문법을 4가지 정도로 나누어 볼 수 있을 것 같다.

 

① NVL(COUNT, 0)

: 해당 문제는 0시나 1시같이 입양이 이루어지지 않은 시간대도 포함하여 COUNT(*) 함수를 써야하기 때문에, 

  COUNT 개수가 NULL 이라면 0으로 대체해주는 NVL 함수를 SELECT절에 사용했다.

 

② TO_CHAR(DATETIME, 'HH24') 

: ORACLE에서 시간을 HH24 포맷으로 바꾸어준다.

--날짜 포맷
TO_CHAR(SYSDATE,'YYYYMMDD'),--20200607
TO_CHAR(SYSDATE,'YYYY-MM-DD'),--2020-06-07
TO_CHAR(SYSDATE,'YYYY/MM/DD'),--2020/06/07
TO_CHAR(SYSDATE,'YYYY'),--2020
TO_CHAR(SYSDATE,'YY'),--20
TO_CHAR(SYSDATE,'MM'),--06
TO_CHAR(SYSDATE,'MON'),--JUN
TO_CHAR(SYSDATE,'D'),--1 (주중의 일을 1~7로 표시(일요일 = 1)
TO_CHAR(SYSDATE,'DD'),--07
TO_CHAR(SYSDATE,'DDD'),--159 (1년기준 일 수)
TO_CHAR(SYSDATE,'DAY'),--SUNDAY (요일 표시)
TO_CHAR(SYSDATE,'DY'), --SUN (요일을 한자리로 표시)
TO_CHAR(SYSDATE,'WW'), -- 23 (일년 기준 주를 00 ~ 53 형태로 표시)
TO_CHAR(SYSDATE,'W'), -- 1 (한달 기준 몇번째 주)
TO_CHAR(SYSDATE,'DL'),--Sunday, June 07, 2020

--사간 포맷
TO_CHAR(SYSDATE,'AM'),--AM
TO_CHAR(SYSDATE,'PM'),--AM
TO_CHAR(SYSDATE,'HH'), --11 (시간을 0 ~ 12 형태로 표시)
TO_CHAR(SYSDATE,'HH24'), --11 (시간을 0 ~ 24 형태로 표시)
TO_CHAR(SYSDATE,'MI'), --15	 (분을 00 ~ 59 형태로 표시)
TO_CHAR(SYSDATE,'SS'), --51	 (초을 01 ~ 59 형태로 표시)

--세기 포맷
TO_CHAR(SYSDATE,'CC'), --21 (세기)
TO_CHAR(SYSDATE,'BC') --AD

(출처 : https://hyeon-joo.tistory.com/3 )

 

③ SELECT LEVEL - 1 AS hour FROM DUAL CONNECT BY LEVEL <= 24

: 참고한 블로그의 설명에 따르면 CONNECT BY절과 LEVEL 칼럼은 아래와 같다.

  CONNECT BY 절의 결과에는 LEVEL이라는 칼럼이 있으며, 이는 계층의 깊이를 의미한다.

  LEVEL 함수를 이용하면  순차적 목록을 수월하게 만들 수 있다.

  0부터 24까지의 숫자를 순서대로 출력하려면 SELECT 문에 LEVEL-1을 작성하고,

  CONNECT BY절에는 LEVEL<=24라는 조건을 걸어주어야 한다.

 

실제로 LEVEL - 1 이 아닌 LEVEL만으로 테스트 한 결과, 출력이 1부터 24까지 되고있는 것을 확인 할 수 있다.

LEVEL의 시작은 1부터이기에, 문제처럼 0부터 24개의 숫자를 사용하고 싶으면 SELECT문에 -1을 이용해서 0으로 시작점을 맞춰줘야 한다.

 

④ LV.HOUR = OG.HOUR (+)

: FROM절에서 두 가지 서브쿼리를 사용했기 때문에, WHERE문의 조인 조건이 없는 경우 해당하는 모든 값이 표출되게 된다. (크로스 조인)

따라서 크로스 조인 상태인 FROM 절 아래에서, WHERE 절로 두 가지 서브쿼리 LV와 OG의 공통 요인인 HOUR를 조건으로 LEFT OUTER JOIN을 실행해줘야 한다.

 

LV의 결과값에 0부터 24까지의 시간(HOUR)가 있고,

OG에는 실제로 입양 기록이 있는 07시부터 19시까지의 시간(HOUR)가 있기 때문에 0부터 24까지의 시간을 모두 출력하기 위해 사용한다.

 

 

 

(참고한 블로그)

[프로그래머스] SQL 입양 시각 구하기(2) - Oracle

https://hyeon-joo.tistory.com/3