[MySQL]LV 4 문제 모음

Intro

SQL 문제들은 많이 없기 때문에 LV 1 ~ LV 5 까지 문제들을 레벨별로 모아서 한번에 정리하겠다.

문제 1 - with 심화, right join

• 입양 시각 구하기(2)

• with, union, union all, recursive, right join 이렇게 다양한 함수들을 많이 사용한 문제

  • with : CTE(common table expression)을 생성하는 문법

    WITH [RECURSIVE] TABLE명 AS (
        SELECT - # 비반복문. 무조건 필수
        [UNION ALL] # RECURSIVE 사용 시 필수. 다음에 이어붙어야 할 때 사용
        SELECT - 
        [WHERE -] # RECURSIVE 사용 시 필수. 정지 조건 필요할 때 사용
    )
    

  • union 은 결합하며 중복제거, union all 은 결합하며 중복제거X

  • right join 은 오른쪽 테이블의 모든 값을 가져와서 남는게 없도록 함

코드

-- 코드를 입력하세요
# 테이블을 만들어서 0~23을 추가한다. 그리고 right join 을 활용하겠다.
with recursive newTable as (
    select 0 as hour 
    union all
    select hour+1 from newTable 
    where hour < 23
)

select b.hour, count(a.animal_id) as count 
from animal_outs a right join newTable b on (hour(a.datetime)=b.hour) 
group by hour order by hour;

풀이

with recursive cts as (...) 코드로 임시로 테이블을 만든다.

• 테이블을 만들어서 0~23 을 추가하고, 이것을 join 에 사용하게 된다.

select b.hour, count(a.animal_id) as count from animal_outs a right join newTable b on (hour(a.datetime)=b.hour)

• right join을 활용해서 newTable의 hour(0~23)을 살린다.

count(*) 로 안하고 count(a.animal_id) 로 하는이유는??

• count(*) 로 하면 모든 부분을 따지므로 b.hour 부분도 카운트가 되어서 최소 1개 이상이 되기 때문이다.
• 따라서 a쪽 테이블만 개수 세줘야 정상 출력 된다.

문제 2 - 서브쿼리, limit, order by에 집계함수

• 그룹별 조건에 맞는 식당 목록 출력하기

코드

select a.member_name, b.review_text, date_format(b.review_date,'%Y-%m-%d') as review_date from member_profile a join rest_review b on (a.member_id=b.member_id) 
where (a.member_id) = (select a.member_id from member_profile a join rest_review b on (a.member_id=b.member_id) 
group by a.member_id order by count(*) desc limit 1) 
order by b.review_date, b.review_text;

-- 최상위 값 구하면 되는거라 limit 1 -> 처음에 max함수만 생각해서 못 풀었다.
# select a.member_id, count(*) as count from member_profile a join rest_review b on (a.member_id=b.member_id) 
# group by a.member_id order by count desc limit 1;

풀이

문제에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL을 구하라고 하였다.

서브쿼리를 통해서 해당 회원을 구했는데, 이때 max 함수가 아니라 limit 으로 구하면 된다.

order by 에는 집계함수도 올 수 있다. 따라서 그룹별 count(*) 로 정렬을 했다.

• 또는 인라인뷰에 윈도우함수로 랭크 매기고, 메인에서 1등 랭크인지 필터링 하면된다.

문제 3 - count(distinct a.user_id)

• 년, 월, 성별 별 상품 구매 회원 수 구하기

코드

-- 회원 user_info, 상품판매 online_sale
-- count(distinct a.user_id) 가 핵심

select year(sales_date) as year, month(sales_date) as month, gender, count(distinct a.user_id) as users 
from user_info a join online_sale b on (a.user_id=b.user_id) 
group by year, month, gender having gender is not null 
order by year, month, gender;

풀이

위 테이블은 join하면 id부분이 중복이 발생한다.

이를 제거하기 위해 distinct를 사용!

• count 함수 안에서 사용했다는게 특징
• 이 방식은 각 회원이 특정 기간에 여러 번 구매하더라도, 그 회원은 한 번만 카운트 되도록 하기 위함
  • 문제의 이러한 요구사항 때문에 중복 제거를 사용하게 됨.
  • 1:N 관계이므로 전체 행이 중복일 수는 없음. N:N이면 모를까. 그래도 distinct를 쓴 이유가 위의 이유임.

문제 4 - union all

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

코드

-- hint : union all
-- 문제를 읽어보면 join을 생각하는게 아니라 합집합 형태가 필요!

select date_format(a.sales_date,'%Y-%m-%d') as sales_date, a.product_id, a.user_id, a.sales_amount 
from 
(select sales_date, product_id, user_id, sales_amount from online_sale where year(sales_date)=2022 and month(sales_date)=3 
 union all
 select sales_date, product_id, null, sales_amount from offline_sale where year(sales_date)=2022 and month(sales_date)=3 
 ) a
order by a.sales_date, a.product_id, a.user_id;

풀이

ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성하라고 했다.

• 두 테이블의 합집합을 구해서 출력하면 되는 문제이다.
• OFFLINE_SALE에는 user_id 컬럼이 없으므로 null로 출력해서 구한다. (문제에서도 null로 표기하라 했음)

문제 5 - timestamp<->date 차이

• 취소되지 않은 진료 예약 조회하기

코드

-- timestamp 타입은 date 타입이랑 다르므로 date로 바꿔서 비교 필수

select a.apnt_no, b.pt_name, b.pt_no, a.mcdp_cd, c.dr_name, a.apnt_ymd from appointment a join patient b on (a.pt_no=b.pt_no) join doctor c on (a.mddr_id=c.dr_id) 
where a.mcdp_cd='cs' and a.apnt_cncl_yn='N' and date(a.apnt_ymd) = '2022-04-13' 
order by a.apnt_ymd;

풀이

join3개를 했고, date() 함수를 사용했다.

컬럼이 테이블 전부 사용하다 보니(출력) 중복 걱정도 없다.

문제 6 - join, group by, order by 심화

• 주문량이 많은 아이스크림들 조회하기

코드

-- first_half 와 july 테이블은 1:N으로 july 쪽은 sum함수로 구해줘야 한다.
-- 이를 위해 group by 를 활용
select b.flavor from first_half a join july b on (a.flavor=b.flavor) 
group by b.flavor 
order by (sum(b.total_order)+a.total_order) desc 
limit 3;

풀이

7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성하는 문제이다.

• 7월 아이스크림 총 주문량은 july 테이블이며 그룹핑을 사용해서 sum 함수를 써야 구해진다.
• 상반기의 아이스크림 총 주문량은 first_half 테이블이며 바로 해당 컬럼을 사용하면 된다.
• 이 둘을 더해서 정렬해야 상위 3개의 맛을 조회할 수 있다.
  • order by 가 이런 집계함수 뿐만아니라 연산하는 컬럼도 적용된다.

문제 7 - with(CTE), 스칼라 서브쿼리

• 자동차 대여 기록 별 대여 금액 구하기

코드

with use_date as (
select history_id, car_id, (datediff(end_date,start_date))+1 as using_date 
    from car_rental_company_rental_history 
)

select b.history_id, 
round((case 
 when b.using_date>=7 and b.using_date<30 then b.using_date*a.daily_fee*(1-(select discount_rate from car_rental_company_discount_plan where car_type=a.car_type and duration_type like '7%')*0.01) 
 when b.using_date>=30 and b.using_date<90 then b.using_date*a.daily_fee*(1-(select discount_rate from car_rental_company_discount_plan where car_type=a.car_type and duration_type like '30%')*0.01) 
 when b.using_date>=90 then b.using_date*a.daily_fee*(1-(select discount_rate from car_rental_company_discount_plan where car_type=a.car_type and duration_type like '90%')*0.01) 
 else b.using_date*a.daily_fee 
end),0) as fee
from car_rental_company_car a 
join use_date b on (b.car_id=a.car_id)
where a.car_type='트럭' 
order by fee desc, b.history_id desc;

풀이

문제 : 대여 기록 별로 대여 금액(컬럼명: FEE) 를 구하자

• 대여 기록 별 (그룹 history_id) -> 굳이 그룹 안해도 풀림
• fee 를 구하기 위해 사용 날짜를 따로 with로 구해두자.(계산 편리)
• 마지막 plan 테이블은 case when 에서 할인율로 해결하자.
  • case when 조건문의 경우 날짜로 한다는걸 생각.
• 참고) with절에서 SELECT *를 사용할 경우에는 중복된 컬럼명을 명시적으로 처리하지 않으므로 직접 명시를 해줘야 join으로 인한 중복 컬럼 에러를 마주치지 않을 수 있다.

문제 8 - group by, having

• 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

코드

with use_date as (
select history_id, car_id, start_date, end_date 
    from car_rental_company_rental_history 
    group by car_id having max(end_date)<'2022-11-01'
)

select a.car_id, a.car_type, 
round((
   30*a.daily_fee*(1-(select discount_rate from car_rental_company_discount_plan where duration_type like '30%' and car_type=a.car_type )*0.01)
      ),0) as fee
from car_rental_company_car a join use_date b on (a.car_id=b.car_id)
where a.car_type in ('세단', 'SUV') 
group by a.car_id having fee >= 500000 and fee < 2000000 
order by fee desc, a.car_type, a.car_id desc;

풀이

• history엔 동일한 car_id가 여러개 이므로 join 하면 중복으로 여러개 생기고, 대여 가능 여부 판단이 불가.
  • 이를 해결하는 핵심코드 : group by car_id having max(end_date)<'2022-11-01'

문제 9 - bit 비교 자세히, any 사용

• FrontEnd 개발자 찾기
• 언어별 개발자 분류하기

코드

-- bit 비교? 1001 & 1000 -> 1000
-- 조인 결과 중복이 발생하므로 distinct 필요한 쿼리
-- 참고로 category가 front end 인 튜플이 여러개 임
select distinct a.id, a.email, a.first_name, a.last_name from developers a, skillcodes b 
where a.skill_code & b.code = b.code and b.category='Front End' 
order by a.id;

-- sum(CODE) 로 서브쿼리 만들어 비교하는 아이디어를 사용한 사람! 이런 풀이도 기억해두자.
-- 참고로 category가 front end 인 튜플이 여러개 임
select ID, EMAIL, FIRST_NAME, LAST_NAME
from DEVELOPERS
where SKILL_CODE&(select sum(CODE) from SKILLCODES where CATEGORY = 'Front End')
order by ID

-- 아니면 in? any? 연산자 사용해서 하는방식
-- 참고로 category가 front end 인 튜플이 여러개 임
0 < any (select skill_code & code
from skillcodes
where category = 'front end')

-- GRADE 구하기 -> with 3 -> sum(bit) 활용
with skill1 as (
select sum(b.code) as fecode from skillcodes b where b.category like 'F%'
), skill2 as (
select b.code as pycode from skillcodes b where b.name like 'Python' 
), skill3 as (
select b.code as cscode from skillcodes b where b.name like 'C#' 
), devs as (
select 
(case 
 when a.skill_code&b.fecode and a.skill_code&c.pycode then 'A' 
 when a.skill_code&d.cscode then 'B' 
 when a.skill_code&b.fecode then 'C' 
 end 
) as grade, a.id, a.email 
from developers a, skill1 b, skill2 c, skill3 d 
)

select grade, id, email 
from devs
where grade is not null
order by grade, id;

-- 서브쿼리 활용 -> 별칭 사용 위해 having 에서 not null
select 
(case 
 when skill_code & (select code from skillcodes where name = 'python') and 
0 < any (select skill_code & code from skillcodes where category = 'front end') then 'A'
 when skill_code & (select code from skillcodes where name = 'C#') then 'B'
 when 0 < any (select skill_code & code from skillcodes where category = 'front end') then 'C'
end) as grade, id, email
from developers
group by grade, id, email
having grade is not null
order by grade asc, id asc;

-- 참고로 any -> exists 둘이 비슷하다~
when 0 < any (select skill_code & code from skillcodes where category = 'front end') then 'C'
when exists (select 1 from skillcodes where skill_code & code and category = 'front end') then 'C'

풀이

코드 참고~! 주석 참고~!

문제 10 - with(CTE) recursive

• 특정 세대의 대장균 찾기

코드

-- self join
select c.id from ecoli_data a join ecoli_data b on (b.parent_id=a.id) join ecoli_data c on (c.parent_id=b.id) 
where a.parent_id is null 
order by c.id;

-- self join -> recursive 풀이
WITH RECURSIVE CTE
AS
(
    SELECT ID, PARENT_ID, 1 AS DEPTH
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT CHILD.ID, CHILD.PARENT_ID, CTE.DEPTH + 1
    FROM ECOLI_DATA CHILD
    INNER JOIN CTE
        ON CHILD.PARENT_ID = CTE.ID
)
SELECT ID FROM CTE WHERE DEPTH = 3;

풀이

recursive 한 풀이와 아닌 풀이 둘 다 익혀두자.

특히 recursive 한 풀이의 의미를 이해하기 위해 아래 문법을 참고

with recursive test as (
    -- non recursive
    select 1 as hour
    union all
    -- recursive
    select hour + 1
    from test
    -- terminate
    where hour < 5
)
select * from rc;
-- 출력??
/*
1
2
3
4
*/