[프로그래머스 ORACLE SQL]입양 시각 구하기(2)★FULL OUTER JOIN★CONNECT BY LEVEL절 실습!!!!★VER3.0

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

 

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

 

 

VER 3.0

 

 

SELECT A.HOUR AS HOUR  , COUNT(B.ANIMAL_TYPE) AS COUNT FROM 

(SELECT LEVEL -1 AS HOUR FROM DUAL 
CONNECT BY LEVEL <=24 ) A
LEFT JOIN ANIMAL_OUTS B ON 
TO_CHAR(DATETIME , 'HH24') = A.HOUR 
 GROUP BY HOUR
 ORDER BY A.HOUR

==> LEFT JOIN ==> CONNECT BY LEVEL

 

SELECT A.HOUR , COUNT(B.ANIMAL_ID) AS COUNT FROM
(SELECT LEVEL-1 AS HOUR FROM DUAL
CONNECT BY LEVEL<=24) A
FULL OUTER JOIN ANIMAL_OUTS B
ON A.HOUR = TO_CHAR(DATETIME , 'HH24')
GROUP BY A.HOUR
ORDER BY HOUR

 

 

 

 

 

VER 1.0

1. CONNECT BY LEVEL 절 연습!!!

 

SELECT LEVEL AS NO FROM DUAL
CONNECT BY LEVEL <=10

 

2. 2023년 12월까지의 열 생성해봐라! 

SELECT '2023 년 '||LPAD(LEVEL,2,0)||'월' AS YEAR
FROM DUAL
CONNECT BY LEVEL <=12

==> LPAD(변수, 2칸, 남은칸숫자(0)넣기) 

 

3. 특정 구간의 날짜 찾기

 

SELECT TO_DATE('20221001', 'YYYYMMDD') + (LEVEL-1) AS DT
   FROM DUAL
CONNECT BY LEVEL <= (TO_DATE('20221011', 'YYYYMMDD') - TO_DATE('20221001', 'YYYYMMDD')) +1

 

 

4.  최종실습

 

SELECT A.HOUR AS HOUR, COUNT(B.ANIMAL_TYPE) AS COUNT FROM 
(SELECT DISTINCT (LEVEL-1) AS HOUR FROM ANIMAL_OUTS
      CONNECT BY LEVEL <= 24
       ) A
  LEFT JOIN ANIMAL_OUTS B
    ON TO_CHAR(DATETIME,'HH24') = A.HOUR
 GROUP BY HOUR
 ORDER BY HOUR

==> CONNECT BY LEVEL <= 24를 통해 1부터 24까지의 LEVEL 변수 생성

==> A 테이블에 0부터 23까지의 열 이 생성되었다. 

 

==>ANIMAL_OUTS(B) 테이블을 A테이블에 LEFT JOIN한다. ==> DATETIME의 24시간제(HH24) 와 A테이블(0~23)이 일치하는 것들을 머리를 집어넣는다.

 

 

==> COUNT(ANIMAL_OUTS(B)) ==> GROUP 으로 HOUR간 정렬이 완료가 된다. COUNT가 가능해진다.

 

 

 

VER 2.0  FROM 절 서브쿼리문 활용

SELECT A.HOUR , NVL(B.COUNT , 0) AS COUNT FROM
(SELECT LEVEL-1 AS HOUR FROM DUAL
CONNECT BY LEVEL<=24) A FULL OUTER JOIN (
SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) AS HOUR , COUNT(*) AS COUNT  FROM ANIMAL_OUTS
GROUP BY TO_NUMBER(TO_CHAR(DATETIME, 'HH24'))
) B ON A.HOUR = B.HOUR 
ORDER BY HOUR

 

 

===================================

-- 코드를 입력하세요
-- SELECT TO_NUMBER(TO_CHAR(DATETIME , 'HH')) AS HOUR, COUNT(*) FROM ANIMAL_OUTS
-- GROUP BY TO_CHAR(DATETIME , 'HH')
-- ORDER BY HOUR
-- SELECT  TO_CHAR(DATETIME , 'HH24') AS HOUR , COUNT(*) AS COUNT FROM ANIMAL_OUTS
-- GROUP BY TO_CHAR(DATETIME , 'HH24')
-- ORDER BY HOUR

-- SELECT A.HOUR AS HOUR, COUNT(B.ANIMAL_TYPE) AS COUNT FROM 
-- (SELECT DISTINCT (LEVEL-1) AS HOUR FROM ANIMAL_OUTS
--       CONNECT BY LEVEL <= 24
--        ) A
--   LEFT JOIN ANIMAL_OUTS B
--     ON TO_CHAR(DATETIME,'HH24') = A.HOUR
--  GROUP BY HOUR
--  ORDER BY HOUR
-- SELECT LEVEL AS NO FROM DUAL
-- CONNECT BY LEVEL <=10
-- SELECT '2023 년 ' ||LPAD(LEVEL, 2, 0)||'월' AS NO 
-- FROM DUAL 
-- CONNECT BY LEVEL <=12

-- SELECT '2023 년 '||LPAD(LEVEL,2,0)||'월' AS YEAR
-- FROM DUAL
-- CONNECT BY LEVEL <=12

-- SELECT TO_DATE('20221001', 'YYYYMMDD') + (LEVEL-1) AS DT
--    FROM DUAL
-- CONNECT BY LEVEL <= (TO_DATE('20221011', 'YYYYMMDD') - TO_DATE('20221001', 'YYYYMMDD')) +1

SELECT A.HOUR , NVL(B.COUNT , 0) AS COUNT FROM
(SELECT LEVEL-1 AS HOUR FROM DUAL
CONNECT BY LEVEL<=24) A FULL OUTER JOIN (
SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) AS HOUR , COUNT(*) AS COUNT  FROM ANIMAL_OUTS
GROUP BY TO_NUMBER(TO_CHAR(DATETIME, 'HH24'))
) B ON A.HOUR = B.HOUR 
ORDER BY HOUR