[SQL] 코드카타 8일차

ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요. (링크)

SELECT
    *
FROM
    (
    SELECT
        date_format(sales_date, '%Y-%m-%d') as 'sales_date'
        , product_id
        , user_id
        , sales_amount
    FROM
        ONLINE_SALE
    WHERE
        (year(sales_date) = 2022)
        and (month(sales_date) = 3)
    UNION
    SELECT
        date_format(sales_date, '%Y-%m-%d') as 'sales_date'
        , product_id
        , null as 'user_id'
        , sales_amount
    FROM
        OFFLINE_SALE
    WHERE
        (year(sales_date) = 2022)
        and (month(sales_date) = 3)
    ) aa
ORDER BY
    sales_date
    , product_id
    , user_id
;

사실 틀렸을 줄 알고 일단 정답체크 해보고 천천히 다시 봐야겠다 싶었는데 바로 정답이어서 오히려 놀랐다
UNION한 걸 전체 인라인뷰로 묶은 뒤 SELECT가 되는구나

---

USED_GOODS_BOARD와 USED_GOODS_REPLY 테이블에서 2022년 10월에 작성된 게시글 제목, 게시글 ID, 댓글 ID, 댓글 작성자 ID, 댓글 내용, 댓글 작성일을 조회하는 SQL문을 작성해주세요. 결과는 댓글 작성일을 기준으로 오름차순 정렬해주시고, 댓글 작성일이 같다면 게시글 제목을 기준으로 오름차순 정렬해주세요. (링크)

SELECT
    b.title
    , b.board_id
    , r.reply_id
    , r.writer_id
    , r.contents
    , date_format(r.created_date, '%Y-%m-%d') as 'created_date'
FROM
    USED_GOODS_BOARD b
    INNER JOIN USED_GOODS_REPLY r ON b.board_id = r.board_id
WHERE
    (year(b.created_date) = 2022)
    and (month(b.created_date) = 10)
ORDER BY
    r.created_date
    , b.title
;

---

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

초안

SELECT
    hour(datetime) as 'hour'
    , count(hour(datetime)) as 'count'
FROM
    animal_outs
GROUP BY
    1
ORDER BY
    1
;

의도했던 대로 작성은 되었으나, 시간대에 데이터가 존재하지 않는 시간대는 아예 결과에서 나타나지 않았다
문제에서 원하는 바는 0시부터 23시까지 모든 시간대에 체크하고, 없다면 0을 반환하는 것
python이었다면 loop문으로 쉽게 됐을 것 같은데, sql은 어떻게 처리해야 하는지 우선 감이 오지 않았다

null값일 때 원하는 값을 반환해주는 함수는 여럿 있다
IFNULL(), COALESCE() 등 (참고)
하지만 문제는 여전히 hour 칼럼에 0~23을 넣을 수 없다는 것이었다.

위 내용을 정리하다가 든 생각
그냥 case-end로 강제로 0, 1, ..., 23을 출력하게 만들어버리고
이걸 서브쿼리나 조인으로 고정값으로 넣어버리면 해결되지 않을까?

SELECT
    CASE
        WHEN 1 then '0'
        WHEN 1 then '1'
        WHEN 1 then '2'
        WHEN 1 then '3'
        WHEN 1 then '4'
        WHEN 1 then '5'
        WHEN 1 then '6'
        WHEN 1 then '7'
        WHEN 1 then '8'
        WHEN 1 then '9'
        WHEN 1 then '10'
        WHEN 1 then '11'
        WHEN 1 then '12'
    END as 'hour'
FROM
    animal_outs

어림도 없었다.
when 조건을 12번만 돌게 제한을 둘 수는 없는걸까...
SQL 프로시저?를 활용하면 loop를 돌릴 수는 있는 것 같은데(참고), 일단 문제에서 요구하는 내용은 아닐 것 같아서 넘어가기로 했다

재귀 cte? recursive cte

검색을 해보니 재귀쿼리? 재귀cte?라는 것을 활용해 임시 테이블에 원하는 숫자 시퀀스를 형성할 수 있다고 한다 (참고1) (참고2)

WITH recursive cte 
    as  (
        select 1 as 'n'
        union all
        select n+1
        FROM cte 
        where n < 10
        )
SELECT n
FROM cte

그런데 솔직히, 어떤 방식으로 구동되는 것인지는 정확하게 모르겠다
그러던 와중 UNION을 통해 다소 어거지로 만들 수 있는 방법을 확인했다

# 1부터 10까지 숫자 테이블
select * from (select 1 x union 
               select 2 union 
               select 3 union 
               select 4 union 
               select 5 union 
               select 6 union 
               select 7 union 
               select 8 union 
               select 9 union 
               select 10) A

(참고로 Oracle이라면 DUAL 테이블을 사용해 보다 쉽게 된다고 한다)
이렇게 반복 노가다를 요구하는 코드라면 정답이 아닐 가능성은 높겠지만,
일단은 정답 싸인을 한 번 받고 나서 다른 사람들 해답을 보고 싶기에...
일단은 트라이 해보기로 했다

그 결과 나온 대환장의 멀티버스

SELECT  
    aa.hour
    , count(o.n_hour) as 'count'
FROM (
	# 0~23까지 숫자 나열 용도
    select * from (select 0 hour union 
                   select 1 union
                   select 2 union 
                   select 3 union 
                   select 4 union 
                   select 5 union 
                   select 6 union 
                   select 7 union 
                   select 8 union 
                   select 9 union  
                   select 10 union 
                   select 11 union
                   select 12 union
                   select 13 union 
                   select 14 union 
                   select 15 union 
                   select 16 union
                   select 17 union 
                   select 18 union 
                   select 19 union 
                   select 20 union
                   select 21 union 
                   select 22 union
                   select 23) A
    ) aa
    LEFT OUTER JOIN 
    (
    # datetime의 hour만 추출
    SELECT
        hour(datetime) as 'n_hour'
    FROM
        animal_outs
    ) o
        ON aa.hour = o.n_hour
GROUP BY
    aa.hour
;

위 코드를 GROUP BY 하기 전에 SELECT *로 보면 아래 캡쳐와 같이 나타난다

사실 이 이미지를 봤다면, count() 하면 끝나겠구나!를 바로 알 수 있다

물론 이렇게 풀어놓고도 만족스럽지는 않다...
뭔가 내가 모르는 더 좋은 방법이 있을 것 같은 느낌
일단 일차적으로 정답을 완성은 했으니, 이제 다른 사람들 코드를 보면서 내가 놓쳤던 부분을 확인해보도록 하........려고 했으나
대부분이 recursive cte 재귀쿼리를 활용한 답안을 제시하고 있었다
내가 한 것처럼 UNION 하드코딩을 한 케이스도 있었으나, 굉장히 비효율적이라는 의견도 보였고.
결국 재귀쿼리를 공부를 해야 하는 것인가... (참고)

sql에서 @를 통한 변수 정의

그러던 중 부트캠프 다른 분이 작성한 코드 하나가 인상 깊어서 가져와봤다

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

SQL에서 @를 사용해 변수, 즉 임시 데이터를 만들고 사용할 수 있다고 한다 (참고)
또한 :=는 대입 연산자라고 하던데, 변수에 연산을 적용하여 그 변수를 정의할 때 사용하는 듯 하다

SELECT @RNUM:= @RNUM +1 AS RNUM, T1.* FROM abc T1,  (SELECT @RNUM := 0) R;

(출처)
이 경우에는 @RNUM이 최초에 0이고, SELECT문에서 행을 불러올 때마다 기존 RNUM 값에 +1을 더해 출력한다는 의미라고 한다

그렇다면 돌아와서, 다시 위의 부트캠프에서 본 코드를 분석해보자면...
최초의 @hour는 -1로 설정하고
행을 불러올 때마다 @hour가 1씩 커지며 (즉 첫번째 행에서는 0이 된다)
이 때 그 @hour와 같은 hour(datetime)을 count()하고
@hour가 23보다 작은 경우(마지막 행에서 불러온 @hour=23인 상태)에 이 loop를 반복하는 것

굉장히 python의 while-loop문과 흡사하고, 그렇기에 위의 재귀쿼리 보다는 이해가 수월했다

다만 아직 생소한 개념들이라, 이걸 문제에 즉각적으로 잘 활용할 수 있을지는...