[oracle/mysql]입양 시각 구하기(2)

쉬울 줄 알았으나 굉장히 오랜 시간을 들인 문제

특히 mysql 코드를 공들여 쉽게 설명하였으니 @set 함수를 사용하는 것에 어려움을 느꼈다면 꼭 끝까지 읽었으면 하는 바람이다.

문제

결과

당연히 시간별로 GROUP BY 해서 COUNT로 하면 나올 줄 알았더니 그렇게 하면 데이터가 없는 시간은 출력되지 않는다.

SELECT to_char(datetime, 'HH24') datetime, count(animal_id) count
from animal_outs
CONNECT BY LEVEL =24
group by to_char(datetime, 'HH24')
order by datetime

 

1) ORACLE

SELECT B.HOUR, NVL(COUNT, 0) COUNT
FROM(
    SELECT to_char(datetime, 'HH24') datetime, count(animal_id) count
    from animal_outs
    CONNECT BY LEVEL =24
    group by to_char(datetime, 'HH24')
    order by datetime
    ) A,
    (SELECT LEVEL-1 HOUR
    FROM DUAL
    CONNECT BY LEVEL <=24
    ) B
WHERE A.DATETIME(+) = B.HOUR
ORDER BY B.HOUR

24줄짜리 시간 데이터를 만들고 B라고 이름지었다.

그리고 기존 코드와 조인했는데 여기서 중요한 것은 B를 기준으로 조인해야 한다는 것이다.

 

A를 기준으로 조인 :

B의 HOUR가 A의 row 수에 맞게 들어가기 때문에 7~19시까지의 HOUR만 나온다

B를 기준으로 조인 :

A의 데이터들이 B의 row 수에 맞게 들어가져 0~23시를 표현할 수 있다.

 

그렇게 되면 원래 A의 row보다 늘어나기 때문에 23개의 row 중 7~19시까지만 유효한 데이터가 들어가고  나머지 데이터는 null이다.

그래서 NVL(COUNT, 0)으로 NULL일 때 0을 출력하는 것

 

 

2)mysql 

set @time :=-1;
 
select (@time := @time+1) as time,
        (select count(hour(datetime)) 
         from animal_outs
         where hour(datetime)= @time)  count
from animal_outs
where @time < 23
 

set으로 선언하는 문장에서는 =, := 모두 대입으로 쓰인다

 

그러나 set을 제외한 명령문에서는 

:= 이항연산자     = 비교연산자 이다.

 

@hour := 10 -> hour라는 변수에 10을 대입하겠다

@hour = -1 -> hour라는 변수는 -1인가 아닌가를 확인한다

 

코드를 조금씩 뜯어서 확인해보자.

set @time :=-1;
 
select (@time := @time+1) as time
from animal_outs
where @time < 23

 -1이라는 값을 가진 time라는 변수를 만들었다.

왜 -1인가? select문에서 한 시간씩 늘어나는 time이란 컬럼을 만들것인데 0시부터 시작해야 하기 때문이다.

 

(@time := @time+1) -> @time+1을 다시 @time에 대입. time은 계속 1씩 커지는 값으로 갱신된다.

 

@time < 23  -> 23시까지 필요하므로 23보다 1만큼 작을 때까지 반복해준다.

만약 where절이 없다면 엄청나게 많은 row들이 출력되어버린다.

 

그럼 다시 전체 코드를 보자.

set @time :=-1;
 
select (@time := @time+1) as time, 
        (select count(hour(datetime)) 
         from animal_outs
         where hour(datetime)= @time)  count
from animal_outs
where @time <23