[SQL] 코드카타 4일차

오늘부터는 어려웠던 문제, 주목할 포인트가 있는 문제는 소제목을 달기로 했다. 빨리 찾아볼 수 있도록.

동일한 회원의 동일 상품 재구매 데이터

ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요. (링크)

일단 초안..이라고 하기엔 몇번의 시행착오를 거친 결과는 이렇다

SELECT
    user_id
    , product_id
FROM (
    SELECT
        user_id
        , product_id
        , dense_rank() over (partition by user_id order by product_id) as 'ranking'
    FROM
        online_sale
    ) aa
WHERE ranking <= 1
GROUP BY
    1
HAVING
    count(product_id) > 1
ORDER BY
    user_id
    , product_id desc
;

사실 틀린 이유는 알겠는데, ranking <= 1이라는 게 rank가 1번인 애들만 걸러내는 것이지, 이게 중복값을 따지는 게 아니니까...
그래도 대략 방향은 잡은 것 같고, 조금 더 다듬으면 해볼만하지 않을까?

는 그냥 방향 잘못 잡았던 것 같다
도저히 안 풀어져서 결국 답안을 확인했고, 생각보다 너무 간단한 코드에 경악...

SELECT
    user_id
    , product_id
FROM
    online_sale
GROUP BY
    1
    , 2
HAVING
    count(*) >= 2
ORDER BY
    user_id
    , product_id desc
;

2개 이상 칼럼에 대한 GROUP BY

내가 2개 이상 칼럼에 대한 group by를 잘 모르고 있던 것 같다
참고글
group by를 여러 칼럼에 대해 적용하면 상위 칼럼부터 시작해 하위 칼럼들이 하위 기준으로 group화를 수행한다
예컨데, group by를 년도로만 하면 2015, 2016, 2017 이런 식으로 이루어진다면,
년도, 월을 적었을 때는 2015-1, 2015-2, 2015-3, ... , 2015-11, 2015-12, 2016-1 ...
이렇게 년도별 아래 추가로 월별로 또 묶을 수 있다는 것이다

이를 통해 해당 문제에서는
group by 1, 2를 사용하는데
먼저 user_id로 묶고, 그 유저마다 product_id로 다시 한 번 묶는 것이다
여기에 having count(*) > 1을 조건으로 적어준다면 유저마다 제품별 구매개수 > 1이라는 조건으로 적용되는 것

---

가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.
(링크)

SELECT
    datetime    as 'intake_time'
FROM
    animal_ins
ORDER BY
    datetime desc
LIMIT 1
;

역시 LIMIT가 편하다
oracle이라면 서브쿼리로 만들고, row_number() 붙여줘서 처리하면 될 듯

---

중고 거래 게시물 3건 이상 사용자 정보 조회

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요. 이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요. 결과는 회원 ID를 기준으로 내림차순 정렬해주세요. (링크)

어려웠다...

전화번호 포맷 변경하기 (not format())

concat()까지는 떠올렸는데, 전화번호를 단순히 date_format() 함수처럼 포맷을 지정해주는 함수로 할 수 있을 거라고 생각했었다
결국 안 풀려서 다른분들 풀이를 확인하고 아래와 같이 코드를 작성했다
(주석 처리한 것은 처음 내가 시도했던 코드)

SELECT
    b.writer_id
    , u.nickname
    #, concat(u.city, ' ', u.street_address1, ' ', u.street_address2) as '전체주소'
    , concat_ws(' ', u.city, u.street_address1, u.street_address2) as '전체주소'
    #, format(u.TLNO, '0##-####-####') as '전화번호'
    , concat_ws('-', substr(u.tlno,1,3), substr(u.tlno,4,4), substr(u.tlno,8,4) ) as '전화번호'
FROM
    USED_GOODS_BOARD b
    INNER JOIN USED_GOODS_USER U
        ON b.writer_id = u.user_id
GROUP BY
    b.writer_id
HAVING
    count(b.writer_id) >= 3    
ORDER BY 
    b.writer_id desc
;

2가지를 기억해야 할 것인데
(1) cancat() 함수의 강화판 concat_ws() 함수. ws는 with seperator의 약자로, 구분자를 함수 자체에 내장시킨다는 의미다.
위 코드에서 볼 수 있다싶이 ' '(공백)이나 '-'와 같은 구분자를 index 0에 넣어주면 그걸 구분자로 사용하게 된다

(2) subsrt() 함수는 n번째 글자부터 m개의 문자를 슬라이스 해준다. n번부터 m번까지가 아니다.
또한 mysql이기 때문인지 별도로 format() 함수가 사용되지 않으므로, concat를 통해 '0##-####-####' 형태를 구현할 수 있다는 발상을 기억할 필요가 있다.

---

CAR_RENTAL_COMPANY_CAR 테이블에서 '네비게이션' 옵션이 포함된 자동차 리스트를 출력하는 SQL문을 작성해주세요. 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요. (링크)

SELECT
    *
FROM
    CAR_RENTAL_COMPANY_CAR 
WHERE
    options like '%네비게이션%'
ORDER BY
    car_id desc
;

---

USED_GOODS_BOARD 테이블에서 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회하는 SQL문을 작성해주세요. 거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력해주시고, 결과는 게시글 ID를 기준으로 내림차순 정렬해주세요. (링크)

SELECT 
    board_id
    , writer_id
    , title
    , price
    , CASE
        WHEN status = 'sale' THEN '판매중'
        WHEN status = 'reserved' THEN '예약중'
        WHEN status = 'done' THEN '거래완료'        
        END as 'new_STATUS'
FROM 
    used_goods_board
WHERE
    (date_format(created_date, '%Y-%m-%d') = '2022-10-05')
ORDER BY
    board_id desc
;

---

PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요. (링크)

SELECT
    a.apnt_no
    , p.pt_name
    , p.pt_no
    , a.mcdp_cd
    , d.dr_name
    , a.apnt_ymd
FROM
    patient p
    INNER JOIN appointment a
        ON p.pt_no = a.pt_no
    INNER JOIN doctor d
        ON d.dr_id = a.mddr_id
WHERE
    (date_format(a.apnt_ymd, '%Y-%m-%d') = '2022-04-13')
    and (a.APNT_CNCL_YN = 'N')
    and (a.MCDP_CD = 'CS')
ORDER BY
    a.apnt_ymd
;

3개 테이블을 JOIN해서 푸는 건 처음이었던 것 같다
p와 d를 바로 연결할 수가 없어서
p - a - d 순서로 JOIN을 연결했다

---

차량의 대여 가능 여부 조회

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요. (링크)

초안은 아래와 같았다
'2022-10-16'가 start_date와 end_date 사이에 있으면 대여중으로 처리하면 되지 않나?라는 아이디어였지만,
같은 차가 여러번 대여가 된 기록이 전부 있다는 점이 문제

SELECT
    *
FROM (
    SELECT
        car_id
        , if('2022-10-16' between start_date and end_date, '대여중', '대여 가능') as 'availability'
    FROM
        car_rental_company_rental_history
    ORDER BY
        car_id
    ) aa
WHERE
    availability = '대여중'
GROUP BY 
    car_id
ORDER BY 
    car_id
;

즉 관건은 2022-10-16에 '대여중'인 차량과 '대여 가능'인 차량을 구분하는 방법을 고안해야 한다는 점!

그렇게 어거지로 완성한 코드는 아래와 같다

SELECT
    car_id
    , AVAILABILITY
FROM (
    SELECT
        car_id
        , AVAILABILITY
        , row_number() over (partition by car_id order by AVAILABILITY desc) as 'rownum'
    FROM (
        SELECT
            car_id
            , if('2022-10-16' between start_date and end_date, '대여중', '대여 가능') as 'AVAILABILITY'
        FROM
            car_rental_company_rental_history
        ) aa
    ) bb
WHERE 
    rownum = 1
ORDER BY 
    CAR_ID desc
;

서브쿼리 aa를 통해 start_date와 end_date 사이에 '2022-10-16'이 있다면 '대여중'과 '대여 가능'을 출력한다
다만 아직까지는 car_id별로 여러 개의 값이 나오기 때문에 불완전

서브쿼리 bb를 통해 car_id를 partition으로 대여중, 대여 가능 출력을 모아버리고 row_number를 부여한다
이 때 order by desc를 통해 '대여중'이 row_number=1로 정렬되도록 조정한다
이렇게 한 이유는, 본 쿼리에서 조건을 달아서 맨 첫번째 값만 취할 생각이고, 이 때 '대여중'이 있는 차량은 '대여중'만 남기고, 그렇지 않은 차량은 '대여 가능'이 출력되게 하고자 하기 위함

마지막으로 본 쿼리에서 WHERE절을 통해 rownum=1만 남기고 쳐내면, car_id마다 1개 row만 남게 되고, 이 때 '대여중'이 우선적으로 남게 설정했기에 문제에서 의도한 바를 달성할 수 있다

다만... 이렇게 푸는 게 맞을까? 더 간단한 방법은 없을까?를 고민해봐야 할 차례.

이번에는 JOIN을 활용한 코드를 짜봤다

SELECT
#    distinct a.car_id as 'orgn_id'
#    , aa.car_id as 'new_id'
    distinct a.car_id
    , if(aa.car_id is null, '대여 가능', '대여중') as AVAILABILITY
FROM
    CAR_RENTAL_COMPANY_RENTAL_HISTORY a
    LEFT OUTER JOIN 
    (
    SELECT
        car_id
        , '대여중' as 'AVA_text'
    FROM
        CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE
        ('2022-10-16' between date_format(start_date, '%Y-%m-%d') and date_format(end_date, '%Y-%m-%d'))
    ) aa
        ON a.car_id = aa.car_id
ORDER BY 
    a.car_id desc
;

위 코드에서 주석처리한 부분은 아래 이미지와 같게 나온다

말하자면, 대여중인 차량으로 조건을 걸어버린 다음
본래 테이블과 대여중 차량만 남은 테이블을 LEFT JOIN을 한 상황
이렇게 하면 대여중 차량은 양쪽 다 car_id가 있지만,
대여 가능한 차량은 왼쪽에만 car_id가 있고 오른쪽에선 null이 된다
그 뒤 마지막으로 오른쪽 car_id를 참고하면서 null이면 '대여 가능', null이 아니라면 '대여중'을 출력하도록 하는 것

---

년, 월, 일별 상품을 구매한 회원수

USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요. (링크)

우선 이게 초안.
group by를 년, 월, 성별에 한 번에 적용하는 건 맞는 것 같고,
왜 답이 틀렸을까...

SELECT
    year
    , month
    , gender
    , count(*) as 'users'
FROM (
    SELECT
        year(o.sales_date) as 'year'
        , month(o.sales_date) as 'month'
        , u.gender
    FROM
        USER_INFO u
        INNER JOIN ONLINE_SALE o
            ON u.user_id = o.user_id
    WHERE
        u.gender is not null
    ) aa
GROUP BY
    1, 2, 3
ORDER BY 
    1, 2, 3
;

데이터의 중복 가능을 고려하자

이유는 데이터에 대한 내 이해 부족으로 잘못된 함수와 인자를 사용한 것.
구매 내역에는 같은 회원이 여러번 구매한 기록이 있을 수 있다
그렇기에 count(*)로 계산을 하면 동일한 회원을 여러번 세기 때문에 결과가 다른 것

SELECT
    year(o.sales_date) as 'year'
    , month(o.sales_date) as 'month'
    , u.gender
    , count(distinct u.user_id) as 'users'
FROM
    USER_INFO u
    INNER JOIN ONLINE_SALE o
        ON u.user_id = o.user_id
WHERE
    u.gender is not null
GROUP BY
    1, 2, 3
ORDER BY 
    1, 2, 3
;

---

REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요. (링크)

초안

SELECT
    r.review_id
    , i.rest_name
    , i.food_type
    , i.favorites
    , i.address
    , round(avg(r.review_score), 2) as 'avg_re'
FROM
    rest_info i
    INNER JOIN rest_review r
        ON i.rest_id = r.rest_id
WHERE
    (i.address like '서울%')
GROUP BY
    r.review_id
ORDER BY
    avg_re desc
    , i.favorites desc
;

'리뷰 평균 점수'를 어떻게 해야 하는지 고민이 된다

SELECT
    i.rest_id
    , i.rest_name
    , i.food_type
  # , sum(i.favorites) as 'favorites'
    , i.favorites
    , i.address
    , round(avg(r.review_score), 2) as 'avg_re'
FROM
    rest_info i
    INNER JOIN rest_review r
        ON i.rest_id = r.rest_id
WHERE
    (i.address like '서울%')
GROUP BY
    i.rest_name
ORDER BY
    avg_re desc
    , i.favorites desc
;

GROUP BY로 rest_name을 걸고,
조회할 칼럼도 문제에서 제시된 대로 정확하게 수정

마지막으로 favorites는 sum()을 사용했을 때는 틀렸다가 빼니까 해결됐는데...
데이터를 확인해보면 각 식당마다 좋아요수는 동일하게 나타나있음을 알 수 있다

즉 굳이 sum을 사용하지 않아도 이미 총합이 집계되어 있는듯

---

자동차 장기/단기 대여 구분하기

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요. (링크)

SELECT
    history_id
    , car_id
    , date_format(start_date, '%Y-%m-%d') as 'start_date'
    , date_format(end_date, '%Y-%m-%d') as 'end_date'
#    , if(TIMESTAMPDIFF(day, start_date, end_date+1) >= 30, '장기 대여', '단기 대여') as 'rent_type'
    , if(datediff(end_date+1, start_date) >= 30, '장기 대여', '단기 대여') as 'rent_type'
FROM
    CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE
    (year(start_date) = 2022)
    and (month(start_date) = 9)
ORDER BY 
    history_id desc
;

날짜 차이 계산

처음엔 end_date를 그대로 입력했었는데,
생각해보니까 start_date 다음날 바로 반납한다고 생각하면 '2일 대여'지만, end_date - start_date = 1이 된다
그렇기에 현실에 맞춰서 식을 맞추려면 end_date+1로 작성해야 맞게 된다.

여담으로 mysql에서 날짜 차이를 계산하는 방식은 2가지가 있는데,
datediff와 timestampdiff 함수이다.

DATEDIFF(날짜1, 날짜2);

TIMESTAMPDIFF(단위, 날짜1, 날짜2);

datediff 함수는 단순히 날짜1 - 날짜2를 계산해 일수를 출력하는데, 이때 반드시 나중의 날짜가 날짜1에 입력되어야 한다

timestampdiff는 두 날짜 데이터를 '단위'로 얼마나 차이나는지 계산해주는데, 이를 통해 일 외에도 시, 분, 초, 월, 년 등의 단위로 차이를 계산할 수 있다.

(참고)