[231222] SQL: 코드카타 72~74

1. WITH RECURSIVE절을 활용한 문제 

 입양 시각 구하기(2) https://school.programmers.co.kr/learn/courses/30/lessons/59413

 

1) 어떤 문제가 있었나

시간대별 입양수를 구하는 문제에서, 입양이 발생하지 않은 시간대를 포함한 전체 시간대를 추출하는 방법을 모름

 

2) 내가 시도해본 건 무엇인가

IF절을 활용해 값이 NULL일 경우 0이 기입되도록 쿼리를 작성하였으나, 애초에 항목이 없어서 NULL값도 없어 조건절 적용 불가

 

3) 어떻게 해결했나

구글링을 통해 WITH RECURSIVE 재귀 함수로 없는 칼럼을 새로 생성하는 방법을 공부하고, 이후 문법은 스스로 짜봄

with recursive A as (select 0 as h
                     union all 
                     select h+1
                       from A
                      where h < 23),
      B as (SELECT DATE_FORMAT(DATETIME,'%H') HOUR, COUNT(*) CNT
            FROM ANIMAL_OUTS
            GROUP BY 1) 
SELECT h HOURS, IF(CNT IS NULL, 0, CNT) COUNT
  FROM A 
  LEFT JOIN B ON A.h = B.HOUR

 

4) 무엇을 새롭게 알았나

- WITH RECURSIVE : 메모리 상에 가상의 테이블을 저장한 후 원하는 조건으로 값을 생성하는 재귀 함수 

 * 재귀(RECURSION): 어떤 것을 정의할 때 자기 자신을 참조하는 것을 의미 

└ 기본 구조 

 > Anchor member : 가상의 테이블 저장. 이때 처음 한 번 실행되는 비반복문(Non-recursive)이 최소한 1개 요구됨

 > UNION ALL : SELECT 뒤에 반드시 UNION 사용 

 > Recursive member: 가상의 테이블인 Anchor member를 참조하는 문장(반복문) 필수. 

 > Termionation condition: 설정한 조건이 멈추는, 정지조건이 요구됨

└ 0~5를 구하는 쿼리 예시  

with recursive A as (
                      select 0 as B -- 재귀 초깃값
                 	  union all
                   	  select B + 1      -- 재귀 조건 
                      from A  
                  	  where B < 23 -- 재귀 정지 조건
                	  )

select * 
  from A

 

 > 결과는 

 

* 출처: https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

 

 

2. 대여 가능한 [특정 기간]을 추출하는 법  

특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 https://school.programmers.co.kr/learn/courses/30/lessons/157339

 

1) 어떤 문제가 있었나

- 2022-11-01 ~ 2022-11-30 사이에 대여 가능하다는 조건을 충족하기 위해서,  대여 시작일과 종료일에 2022-11 이 포함되지 않는 조건을 설정하였음 

 

2) 내가 시도해본 건 무엇인가

[오답]
With  car_2211 as  -- 2022년 11월 대여기록 있는 차만 추출 
                (SELECT car_id
                  from CAR_RENTAL_COMPANY_RENTAL_HISTORY   
                  LEFT join CAR_RENTAL_COMPANY_CAR using (car_id)
                  where car_type in ('세단', 'SUV')
                        and (DATE_FORMAT(start_date, '%Y-%m') IN ('2022-11') 
                             or DATE_FORMAT(end_date, '%Y-%m') IN ('2022-11')) ) ,
      car_price as  -- 조건에 맞는 차의 30일치 대여비용 추출
                    (select *, daily_fee * 30 30days_fee
                      from CAR_RENTAL_COMPANY_CAR
                     where car_type in ('세단', 'SUV') 
                           and (CAR_ID) not in (select * from  car_2211)), 
      car_discount as  -- 세단, SUV 30일 이상 대출 시 할인율만 추출
                    (SELECT car_type, discount_rate
                      FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                      where car_type in ('세단', 'SUV') 
                            and duration_type = '30일 이상')
select *   -- 최종 할인율 적용 및 가격 조건에 맞도록 추가 
from (select car_id, car_type, round((DAILY_FEE*(1-(discount_rate/100)))*30,0) FEE
  from car_price
  inner join car_discount using (car_type))a
 WHERE FEE BETWEEN 500000 AND 1999999  
 ORDER BY 3 DESC, 2, 1 DESC

 

3) 어떻게 해결했나

- 최근 날짜를 max 함수를 써서 구할 때, where 에 넣으면 오류가 났는데 gorup by having에 넣으니 제대로 추출됨

└ car_id별 최근 날짜를 구해야 하므로 max는 having으로 넣어야 했음  

- 2022-11-01 ~ 2022-11-30 사이에 대여 가능하다는 조건

 └ 대여 시작일은 11/30 이전 and 대여 종료일은 11/1 이전이어야 함 (11/30에 대여 시작해서 30일 빌려도 조건 충족) 

 └ 초반에 문제를 대여 시작일이 2022-11-01 이고, 대여 종료일이 2022-11-30으로 잘못 이해한 것을 깨달음  

with car_2211 as 
                (SELECT car_id
                  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                 group by 1
                    having max(start_date) <= '2022-11-30'
                        AND max(end_date) <= '2022-11-01'), 
      car_discount as  
                    (SELECT car_type, ROUND(discount_rate/100,2) discount_rate
                      FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
                     WHERE duration_type = '30일 이상')                  
                  
SELECT car_id, car_type, round((DAILY_FEE*(1-discount_rate))*30,0) FEE
  FROM car_2211 
 INNER JOIN CAR_RENTAL_COMPANY_CAR USING (car_id)
 inner join car_discount USING (car_type)
 WHERE car_type IN ('SUV', '세단') 
      AND round((DAILY_FEE*(1-discount_rate))*30,0) BETWEEN 500000 AND 1999999
 ORDER BY 3 DESC, 2, 1 DESC

 

+) 다른 사람 풀이

--- 3개의 테이블을 모두 합친 후, where과 group by 의 having으로 조건 처리
SELECT a.CAR_ID, a.CAR_TYPE,ROUND((a.DAILY_FEE *((100-c.DISCOUNT_RATE)*0.01))*30) as FEE
  from CAR_RENTAL_COMPANY_CAR a
  left join CAR_RENTAL_COMPANY_RENTAL_HISTORY b on a.CAR_ID=b.CAR_ID
  left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN c on a.CAR_TYPE=c.CAR_TYPE 
 where a.CAR_TYPE in ("세단","SUV")
      and c.DURATION_TYPE like "%30일%"
 group by a.CAR_ID
       having max(b.end_date)<="2022-11-01"
              and FEE between 500000 and 2000000
 order by FEE desc, CAR_TYPE, CAR_ID

 

4) 무엇을 새롭게 알았나

- WHRE절과 HAVING절의 차이점 정리가 필요함 

  └ WHERE은 기본적인 조건절로, 모든 필드에 조건을 걸 수 있음. 테이블 자체에 쿼리 수행

  └ HAVING은 group by로 그룹화된 필드에 거는 조건절로, 특정 필드에만 조건을 걸 수 있음 

- LEFT JOIN 시, TABLE_B 같이 기준이 되는 칼럼(PROD_ID) 값이 중복되면, 결과치에도 중복이 발생함

 └ SELECT절 DISTINCT 나 GROUP BY절 HAVING 을 활용해 중복 제거 필요 

* 출처:  https://for-my-wealthy-life.tistory.com/5

- 티스토리에 코드 블록 설정하는 법을 알게됨