TIL_[SQL+미니 프로젝트] 회고 및 SQL RECURSIVE 문법

김희정·2023년 12월 21일

TIL

목록 보기

17/57

오늘 한일

미니 프로젝트 마무리 단계
SQL 코드카타 3문제
csv vsc에 넣어보려고 시도했으나 ~~처참히 실패~~
데이터분석 5주차 복습

진행과정

프로젝트를 위한 데이터는 어제 대부분 정리를 마쳤기 때문에, 마지막으로 발표를 위한 정제과정을 가졌다. 개인적으로 나 자신에게 아쉬웠던 점을 꼽자면, 판다스를 활용해서 데이터 분석을 하기가 아직은 어렵다고 느꼈다. 언제 한번 날 잡아서 판다스만 집중적으로 풀어보는 시간을 가지던가 해봐야 할 것 같다😢
데이터 분석 강의 5주차를 재재재탕했다. 재재재탕을 해도 결국은 스스로 배웠던 걸 활용을 해봐야 하는데, 아직 그럴 시간이 안나 못하고 있다는게 아쉽다. 주말에라도 날을 잡고 혼자서 공부를 해봐야할 것 같다.

코드카타

오프라인/온라인 판매 데이터 통합하기

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

(정답)
SELECT
    date_format(SALES_DATE,'%Y-%m-%d') AS SALES_DATE,
    product_id,
    NULL AS USER_ID,    
    sales_amount
FROM
    OFFLINE_SALE
WHERE
   date_format(SALES_DATE,'%Y-%m')='2022-03'

UNION ALL

SELECT
    date_format(SALES_DATE,'%Y-%m-%d') AS SALES_DATE,
    PRODUCT_ID,
    USER_ID,    
    SALES_AMOUNT
FROM
    ONLINE_SALE
WHERE
    date_format(SALES_DATE,'%Y-%m')='2022-03'
ORDER BY 
    SALES_DATE, 
    PRODUCT_ID, 
    USER_ID

UNION 은 조회할 두 테이블의 컬럼수가 같아야 한다는 것을 처음 알게되었다.
만약 같지 않다면, NULL as (대체 컬럼명) 으로 숫자를 맞출 수 있다고 한다.

입양 시각 구하기(2)

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

웬일로 쉬운 문제냐! 하고 신나게 풀었는데 결과는 오답.
뭐가 문제인지 확인해보니 결과값에 count로 집계되는게 없더라도 hour 컬럼이 나와야 한단다.

또 새로운 문법을 알게되었다.

RECURSIVE (+ WITH) 재귀쿼리

재귀(recursion)는 어떠한 것을 정의할 때 자기 자신을 참조하는 것을 뜻한다.
자기언급과도 관련된 재귀는 언어학에서 논리학에 이르기까지 다양한 분야에서 연구되는 주제로,
특히 컴퓨터 과학과 수학에서, 재귀는 함수가 자신의 정의에 의해 정의될 때의 개념을 가리킨다. (출처_wiki)

with recursive 쿼리문을 작성하고 내부에 union을 통해 재귀를 구성하는 것이 포인트.

(문법 예)
WITH RECURSIVE cte_count 
AS ( 
    -- Non-Recursive 문장( 첫번째 루프에서만 실행됨 )
    SELECT 1 AS n
    UNION ALL
    -- Recursive 문장(읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
    SELECT n + 1 AS num 
    FROM cte_count
    WHERE n < 3 
)

SELECT * FROM test;

특징
1. 메모리 상에 가상의 테이블을 저장한다.
2. 반드시 union 을 사용해야 한다.
3. 반드시 비반복문(Non-Recursive)도 최소한 1개 요구된다. 처음 한번만 실행
4. SubQuery에서 바깥의 가상의 테이블을 참조하는 문장(반복문)이 반드시 필요하다
5. 반복되는 문장은 반드시 정지조건(Termination condition)이 요구된다
6. 가상의 테이블을 구성하면서 그 자신(가상의 테이블)을 참조하여 값을 결정할 때 유용하다

(정답)
with recursive rc as (
    select 0 as hour
    union all
    select hour+1 from rc where hour<23
)

select 
    rc.hour, 
    count(hour(A.datetime)) as count
from rc
left join animal_ins as A
on rc.hour = hour(A.datetime) -- 재귀숫자와 datetime시간숫자가 같은것 만
group by rc.hour -- 집계함수를 썼으니 그룹핑, 안그러면 다 더해서 레코드 하나만 반환