240817 - SQL(DATE_FORMAT, NTILE, ROUND, MAX, 둘 이상 WHERE, 특정 값 먼저 ORDER BY, DISTINCT)

robert-min commented 4 weeks ago

DATE_FORMAT

날짜 포맷 변경 :
- 년-월-일 : "%Y-%m-%d
- yyyy-MM-dd HH:mm:ss >> DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s')

-- 코드를 입력하세요
SELECT
MEMBER_ID,
MEMBER_NAME,
GENDER,
DATE_FORMAT(DATE_OF_BIRTH, "%Y-%m-%d") AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 3
    AND GENDER = "W"
    AND TLNO IS NOT NULL
ORDER BY MEMBER_ID ASC;

robert-min commented 4 weeks ago

NTILE

대장균 개체의 크기를 내름차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류합니다.

NTILE 함수의 주요 특징: N개 그룹으로 분할: 지정된 N 값에 따라 데이터를 가능한 한 균등하게 나눕니다. 정렬 순서: ORDER BY 절에 따라 데이터를 정렬한 후 그룹을 나눕니다. 윈도우 프레임: 다른 윈도우 함수들과 마찬가지로 OVER 절과 함께 사용되며, 데이터셋에 대해 특정 그룹(윈도우)을 정의합니다. 예를 들어, NTILE(4) OVER (ORDER BY SIZE DESC)는 데이터셋을 4개의 그룹으로 나누고, SIZE 값이 큰 순서대로 각 행에 1부터 4까지의 그룹 번호를 할당합니다.

-- 코드를 작성해주세요
SELECT
    ID,
    CASE
        WHEN NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) = 1 THEN 'CRITICAL'
        WHEN NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) = 2 THEN 'HIGH'
        WHEN NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) = 3 THEN 'MEDIUM'
        ELSE "LOW"
    END AS COLONY_NAME
FROM ECOLI_DATA
ORDER BY ID ASC

robert-min commented 4 weeks ago

ROUND

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

-- 코드를 입력하세요
SELECT
    A.REST_ID,
    A.REST_NAME,
    A.FOOD_TYPE,
    A.FAVORITES,
    A.ADDRESS,
    B.SCORE
FROM REST_INFO AS A
JOIN (
    SELECT
        REST_ID,
        ROUND(AVG(REVIEW_SCORE), 2) AS SCORE
    FROM REST_REVIEW
    GROUP BY REST_ID
) AS B
ON A.REST_ID = B.REST_ID
WHERE ADDRESS LIKE "서울%"
ORDER BY SCORE DESC, FAVORITES DESC

# SELECT
#     REST_ID,
#     ROUND(AVG(REVIEW_SCORE), 3) AS SCORE
# FROM REST_REVIEW
# GROUP BY REST_ID

robert-min commented 4 weeks ago

MAX

-- 코드를 입력하세요
SELECT
    *
FROM FOOD_PRODUCT
WHERE PRICE = (
    SELECT MAX(PRICE) FROM FOOD_PRODUCT
)

robert-min commented 4 weeks ago

둘 이상 WHERE

-- 코드를 작성해주세요
SELECT
    A.ID,
    B.FISH_NAME,
    A.LENGTH
FROM FISH_INFO AS A
INNER JOIN FISH_NAME_INFO AS B
ON A.FISH_TYPE = B.FISH_TYPE
WHERE (A.FISH_TYPE, A.LENGTH) IN (
    SELECT FISH_TYPE, MAX(LENGTH) FROM FISH_INFO
    GROUP BY FISH_TYPE
)
ORDER BY A.ID

robert-min commented 4 weeks ago

특정 값 먼저 ORDER BY

-- 코드를 입력하세요
SELECT
    ANIMAL_TYPE,
    COUNT(ANIMAL_TYPE) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY
    CASE
        WHEN ANIMAL_TYPE = "Cat" THEN 0
        ELSE 1
    END

robert-min commented 4 weeks ago

DISTINCT

집계 후 개수 count 시 중복 제거해야 함!!!

SELECT
    YEAR(O.SALES_DATE) AS YEAR,
    MONTH(O.SALES_DATE) AS MONTH,
    U.GENDER,
    COUNT(DISTINCT O.USER_ID) AS USERS
FROM ONLINE_SALE AS O
LEFT JOIN USER_INFO AS U
    ON O.USER_ID = U.USER_ID
WHERE U.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR ASC, MONTH ASC, GENDER ASC

robert-min / dev-blog