[MySQL] SQL 복습

1. RANK()

• 즐겨찾기가 가장 많은 식당 정보 출력하기

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES, 
      RANK() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS RA
      FROM REST_INFO) T
WHERE RA <= 1
ORDER BY FOOD_TYPE DESC

 

2. NULL처리

a. IFNULL

b. NULLIF

 

3. UNION

 

4. DATE_FORMAT

// 1
SELECT DATE_FORMAT(T.SALES_DATE, '%Y-%m-%d') AS SALES_DATE, T.PRODUCT_ID, T.USER_ID, T.SALES_AMOUNT
FROM (
    SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM ONLINE_SALE
    WHERE SALES_DATE LIKE '2022-03%'
    UNION ALL
    SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
    FROM OFFLINE_SALE
    WHERE SALES_DATE LIKE '2022-03%'
    ) AS T
ORDER BY T.SALES_DATE ASC, T.PRODUCT_ID ASC, T.USER_ID ASC

// 2
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'
UNION ALL
SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE '2022-03%'
ORDER BY SALES_DATE ASC, PRODUCT_ID ASC, USER_ID ASC

 

5. EXISTS, IN

-- 1
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID

--2
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION NOT IN ('Aged')
ORDER BY ANIMAL_ID

 

6. DATETIME의 YEAR, MONTH, DATE

-- 코드를 입력하세요
SELECT COUNT(*) AS USERS
FROM USER_INFO
WHERE YEAR(JOINED)='2021' AND AGE BETWEEN 20 AND 29

 

7. DISTINCT

-- 코드를 입력하세요
SELECT COUNT(DISTINCT(NAME))
FROM ANIMAL_INS

 

8. AS

- SELECT절의 AS는 GROUP BY, ORDER BY절에서는 사용 가능 but WHERE절에서는 사용 불가 

SELECT HOUR(DATETIME) AS HOUR,COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 19
GROUP BY HOUR
ORDER BY HOUR ASC

SELECT HOUR(DATETIME) AS HOUR,COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19
ORDER BY HOUR ASC

 

9. 재귀 쿼리: WITH RECURSIVE

- WITH: 메모리 상에 가상 테이블을 만드는데에 사용

https://wakestand.tistory.com/455

MySQL WITH 절 사용법 및 예제(가상 테이블)

WITH RECURSIVE 테이블명 AS(
	SELECT 초기값 AS 컬럼명
    UNION ALL
    SELECT 계산식 FROM 테이블명 WHERE 제어식
)

https://hyunmin1906.tistory.com/149

[MySQL] WITH 재귀(RECURSIVE) 쿼리 계층구조

--프로그래머스 입양 시각 구하기
-- 1
WITH RECURSIVE H AS(
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM H WHERE HOUR < 23
)

SELECT H.HOUR, IFNULL(T.COUNT, 0) AS COUNT
FROM H LEFT JOIN (SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
        FROM ANIMAL_OUTS 
        GROUP BY HOUR) AS T
        ON H.HOUR = T.HOUR
ORDER BY HOUR

-- 2
SELECT H.HOUR, COUNT(T.ANIMAL_ID) AS COUNT
FROM H LEFT JOIN ANIMAL_OUTS AS T
ON H.HOUR = HOUR(T.DATETIME)
GROUP BY H.HOUR
ORDER BY H.HOUR

 

10. FLOOR

SELECT FLOOR(PRICE/10000)*10000 AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP/10000
ORDER BY PRICE_GROUP/10000 ASC

 

11. 반올림, 버림

두 번째 파라미터가 양수 a -> 소수점 아래 a자리까지 표시. 소수점 아래 a+1자리에서 반올림

두 번째 파라미터가 음수 a -> a자리에서 반올림.

- ROUND(반올림 할 수 또는 컬럼, 반올림 할 자리 수)

- TRUNCATE(버림 할 수 또는 컬럼, 버림 할 자리 수)

 

12. GROUP BY문제: 식품분류별 가장 비싼 식품의 정보 조회하기

-- 코드를 입력하세요
SELECT F.CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT F INNER JOIN
    (SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY) M
ON F.PRICE = M.MAX_PRICE AND F.CATEGORY = M.CATEGORY
ORDER BY PRICE DESC

 

13. 프로그래머스 GROUP BY 문제

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

-- 코드를 입력하세요
SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, 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 GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR ASC, MONTH ASC, GENDER ASC

 

14. MYSQL 정규식: REGEXP

-- 코드를 입력하세요
SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID=O.ANIMAL_ID
WHERE SEX_UPON_INTAKE LIKE 'Intact%'
    AND SEX_UPON_OUTCOME REGEXP 'Spayed+|Neutered+'
ORDER BY I.ANIMAL_ID

 

15. 프로그래머스 JOIN 문제

• 상품을 구매한 회원 비율 구하기

-- 코드를 입력하세요
WITH MAY AS(
    SELECT *
    FROM USER_INFO
    WHERE YEAR(JOINED)=2021
)

SELECT YEAR(SALES_DATE) AS YEAR, 
    MONTH(SALES_DATE) AS MONTH,
    COUNT(DISTINCT(O.USER_ID)) AS PUCHASED_USERS,
    ROUND(COUNT(DISTINCT(O.USER_ID))/(SELECT COUNT(*) FROM MAY), 1) AS PUCHASED_RATIO
FROM MAY M INNER JOIN ONLINE_SALE O
ON M.USER_ID=O.USER_ID
GROUP BY YEAR, MONTH
ORDER BY 1, 2

 

16. JOIN 여러개

- APPOINTMENT와 PATIENT, APPOINTMENT와 DOCTOR가 관계가 있는 상태임

SELECT APNT_NO, PT_NAME, A.PT_NO, A.MCDP_CD, DR_NAME, APNT_YMD
FROM APPOINTMENT A 
    JOIN PATIENT P
    JOIN DOCTOR D
    ON A.MDDR_ID=D.DR_ID
    AND A.PT_NO=P.PT_NO
WHERE A.MCDP_CD='CS'
    AND DATE_FORMAT(APNT_YMD, '%Y-%m-%d')='2022-04-13' 
    AND APNT_CNCL_YN='N'
ORDER BY APNT_YMD

 

17. 문자열

https://needjarvis.tistory.com/64

[MySQL] 문자열 추출하기, SUBSTRING, SUBSTR

 

18. IF, CASE

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=rorean&logNo=221594169204