20. 온보딩 SQL 5일차

문제풀이

1. 재구매가 일어난 상품과 회원 리스트 구하기

HAVING

• WHERE 절에서는 집계함수를 사용할 수 없음.
• HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용됨.
• HAVING 절은 GROUP BY 절과 함께 사용됨.

아래 문제에서는 USER_ID, PRODUCT_ID로 묶었을 때 묶인 USER_ID의 개수가 2 이상인 데이터만 뽑는 것.

SELECT USER_ID, PRODUCT_ID 
FROM ONLINE_SALE 
GROUP BY USER_ID, PRODUCT_ID 
HAVING COUNT(USER_ID) >= 2 
ORDER BY USER_ID, PRODUCT_ID DESC

2. 자동차 평균 대여 기간 구하기

위에서 새로 배운 HAVING 절을 사용하여 SELECT 된 AVERAGE_DURATION에 조건을 걸어주었고 DATEDIFF 함수를 써서 기간을 구함.

DATEDIFF

날짜 간의 차이를 구하는 함수를 검색하다가 DATEDIFF 함수를 발견했는데, 보통 블로그나 사이트에 나와있는 DATEDIFF 함수는 매개변수를 3개 받음.

DATEDIFF(interval, date1, date2)

하지만 프로그래머스에 제출했을 때 Incorrect parameter count in the call to native function 'DATEDIFF'라는 오류가 떴고 확인해보니 위의 DATEDIFF는 MSSQL 함수였고 우리가 채점하는건 MySQL이어서 다른 함수였음.

MySQL의 DATEDIFF 함수는 date1, date2를 받아서 두 날짜 간의 차이를 반환해주는 함수.

SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID 
HAVING AVERAGE_DURATION >= 7 
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

3. 헤비 유저가 소유한 장소

새로 배운 문법이나 함수는 없지만 subquery 사용법이 아직은 익숙치 않아서인지 풀기 어려운 문제였음.

1. HOST_ID로 묶었을 때 HOST_ID가 2회 이상 나오는 데이터로 이루어진 테이블 생성
2. 기존 테이블을 1번에서 생성한 테이블과 병합
3. 형식에 맞게 데이터 출력

SELECT P.ID, P.NAME, P.HOST_ID 
FROM PLACES P 
JOIN (
	SELECT P2.ID, P2.NAME, P2.HOST_ID 
    FROM PLACES P2 
    GROUP BY P2.HOST_ID 
    HAVING COUNT(*) > 1
) B ON P.HOST_ID = B.HOST_ID 
ORDER BY P.ID

4. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

subquery도 그렇고 IN 절을 처음 보는 문제였던 것 같음.

1. 주어진 기간 동안 대여된 차들을 CAR_ID로 묶었을 때 HISTORY_ID가 5회 이상인 테이블 생성.
2. MONTH(START_DATE): START_DATE의 월만 뽑아오기
3. CAR_ID가 새로 생성한 테이블에 존재하는 데이터에 대해서 월, CAR_ID로 묶었을 때 HISTORY_ID가 1 이상인 데이터들 출력.

WITH CARS AS (
    SELECT * 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31' 
    GROUP BY CAR_ID 
    HAVING COUNT(HISTORY_ID) >= 5
)
SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(HISTORY_ID) AS RECORDS 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H 
WHERE  CAR_ID IN (SELECT CAR_ID FROM CARS) 
GROUP BY MONTH(START_DATE), CAR_ID 
HAVING COUNT(HISTORY_ID) > 0 
ORDER BY MONTH, CAR_ID DESC

5. 루시와 엘라 찾기

사실 이 문제는 LIKE 절을 사용해서 풀 수 있긴 한데 찾는 이름이 6개나 돼서 or 같은 방법이 없나 검색하다가 정규표현식을 찾았음.

REGEXP

• . - 문자 하나 나타냄
• * - 앞에 나온 문자의 0개 이상 반복을 나타냄
• ^ - 문자열의 처음을 나타냄
• $ - 문자열의 끝을 나타냄
• [.] - 괄호 안의 문자열 일치를 확인함
• {.} - 반복을 나타냄
• | - or를 나타냄

예제)

• WHERE NAME REGEXP '가': '가'를 포함한 모든 레코드
• WHERE NAME REGEXP '가|나|다|라': 가 or 나 or 다 or 라가 포함된 모든 레코드
• WHERE NAME REGEXP '[가-힇]': 한글이 포함된 모든 레코드
• WHERE NAME REGEXP '^[가-힇]+$' 한글로만 구성된 모든 레코드

MySQL Regular Expressions

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE 
FROM ANIMAL_INS 
WHERE NAME REGEXP '^(Lucy|Ella|Pickle|Rogan|Sabrina|Mitty)$' 
ORDER BY ANIMAL_ID