search

GreatAlgorithm-Study / AlgorithmStudy

🌟알고리즘 대장정🌟
6 stars 4 forks source link

[9주차_SQL] SQL 풀이 모음 #123

Closed Jewan1120 closed 1 week ago

Jewan1120 commented 2 weeks ago

문제 리스트

## 문제: [문제명](링크)

### 설명

### 코드

### 아이디어
baexxbin commented 2 weeks ago

문제: 오프라인/온라인 판매 데이터 통합하기

설명

코드


아이디어

UNION과 UNION ALL


필터링과 인덱스


서브쿼리에 별칭 붙이기

Jewan1120 commented 2 weeks ago

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

설명

코드

WITH CTE AS (
    SELECT
        *
    FROM
        CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE
        START_DATE <= '2022-11-30'
        AND END_DATE >= '2022-11-01'
)

SELECT
    A.CAR_ID
    , A.CAR_TYPE
    , FLOOR(30 * (A.DAILY_FEE * (1 - C.DISCOUNT_RATE / 100))) FEE
FROM
    CAR_RENTAL_COMPANY_CAR A
    LEFT JOIN CTE B
        ON A.CAR_ID = B.CAR_ID 
    INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN C
        ON A.CAR_TYPE = C.CAR_TYPE
WHERE
    A.CAR_TYPE IN ('SUV', '세단')
    AND B.CAR_ID IS NULL
    AND C.DURATION_TYPE LIKE '30%'
    AND FLOOR(30 * (A.DAILY_FEE * (1 - C.DISCOUNT_RATE / 100))) BETWEEN 500000 AND 2000000
ORDER BY
    FEE DESC
    , A.CAR_TYPE
    , A.CAR_ID

아이디어

KodaHye commented 2 weeks ago

문제: 입양 시각 구하기(2)

설명

코드

WITH RECURSIVE TMP AS (
    (SELECT 0 HOUR)
    UNION ALL
    (SELECT HOUR + 1 FROM TMP WHERE HOUR < 23)
)

SELECT A.HOUR, IFNULL(B.COUNT, 0) `COUNT`
FROM TMP A LEFT JOIN (
    SELECT HOUR(DATETIME) `HOUR`, COUNT(HOUR(DATETIME)) `COUNT`
    FROM ANIMAL_OUTS
    GROUP BY HOUR(DATETIME)
) B ON A.HOUR = B.HOUR

아이디어


RECURSIVE WITH절 간단 설명

  • RECURVICE WITH절은 초기 설정값과 RECURSIVE 할 쿼리를 UNION ALL로 엮는 쿼리와 같음
  • 필요 요소: 초기 서브 쿼리, RECURSIVE 서브 쿼리, 기저조건
WITH RECURSIVE WITH CTE AS ( # 재귀쿼리 세팅
   SELECT 0 AS HOUR # 초기값 설정
   UNION ALL # 위 쿼리와 아래 쿼리의 값을 계산
   SELECT HOUR + 1 FROM CTE # 반복 실행
   WHERE HOUR < 23 # 반복을 멈추는 용도
)

프로시저와 변수(@)를 사용해서 풀이할 수 있음!

icegosimperson commented 2 weeks ago

문제: 분기별 분화된 대장균의 개체 수 구하기

설명

코드

1) 처음에 작성한 코드

 SELECT 
        CASE
            WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 1 AND 3 THEN '1Q'
            WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 4 AND 6 THEN '2Q'
            WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 7 AND 9 THEN '3Q'
            WHEN MONTH(DIFFERENTIATION_DATE) BETWEEN 10 AND 12 THEN '4Q'
        END AS QUARTER, -- CASE문 닫고 컬럼명 지정
        COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER;

2) 다른 사람의 풀이를 참고 : QUARTER 함수, CONCAT 함수 사용

SELECT CONCAT(QUARTER(DIFFERENTIATION_DATE), 'Q') AS QUARTER, COUNT(ID) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER;

아이디어

1) 처음에 작성한 코드

2) QUARTER 함수, CONCAT 함수를 사용한 코드 (1) QUARTER 함수 : 주어진 날짜에서 몇 분기인지 숫자(1, 2, 3, 4)로 반환하는 함수

(2) CONCAT 함수 : 여러 문자열을 하나의 문자열로 연결해주는 함수

yeongleej commented 2 weeks ago

문제: 상품을 구매한 회원 비율 구하기

설명

코드

  1. 서브쿼리 활용

    SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH
   , COUNT(DISTINCT B.USER_ID) AS PURCHASED_USERS
   , ROUND(COUNT(DISTINCT B.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021), 1) AS PURCHASED_RATIO
FROM USER_INFO A
JOIN ONLINE_SALE B
ON A.USER_ID = B.USER_ID
WHERE YEAR(JOINED) = 2021
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY YEAR, MONTH

  2. 가상테이블 활용

    WITH TOTAL_USERS_2021 AS (
SELECT COUNT(DISTINCT USER_ID) AS T_COUNT
FROM USER_INFO
WHERE YEAR(JOINED) = 2021
)
SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH
   , COUNT(DISTINCT B.USER_ID) AS PURCHASED_USERS
   , ROUND(COUNT(DISTINCT B.USER_ID) / (SELECT * FROM TOTAL_USERS_2021), 1) AS PURCHASED_RATIO
FROM USER_INFO A
JOIN ONLINE_SALE B
ON A.USER_ID = B.USER_ID
WHERE YEAR(JOINED) = 2021
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY YEAR, MONTH

  3. LIKE 활용

    SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH
   , COUNT(DISTINCT B.USER_ID) AS PURCHASED_USERS
   , ROUND(COUNT(DISTINCT B.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE JOINED LIKE "2021%"), 1) AS PURCHASED_RATIO
FROM USER_INFO A
JOIN ONLINE_SALE B
ON A.USER_ID = B.USER_ID
WHERE JOINED LIKE "2021%"
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY YEAR, MONTH

아이디어

총 개수를 구하거나 값을 구할 때는 JOIN된 쿼리, GROUP BY쿼리와 분리된 서브쿼리를 활용하기,,!

yeahdy commented 2 weeks ago

문제: 특정 형질을 가지는 대장균 찾기

설명

코드

SELECT
    COUNT(ID) COUNT
FROM ECOLI_DATA
WHERE (GENOTYPE & 2 != 2) AND GENOTYPE & 5 > 0

아이디어

ID GENOTYPE GENOTYPE 별 이진수 5의 이진수 GENOTYPE & 5
1 8 1000 101 0
2 15 1111 101 5
3 1 1 101 1
4 13 1101 101 5
KodaHye commented 2 weeks ago

문제: 오프라인/온라인 판매 데이터 통합하기

설명

코드

WITH TMP AS (
    SELECT SALES_DATE, PRODUCT_ID, NULL `USER_ID`, SALES_AMOUNT
    FROM OFFLINE_SALE 
    WHERE SALES_DATE LIKE '2022-03%'
    UNION ALL
    SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM ONLINE_SALE 
    WHERE SALES_DATE LIKE '2022-03%'
)

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') `SALES_DATE`, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM TMP
WHERE SALES_DATE LIKE '2022-03%'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

아이디어


UNION과 UNION ALL

yeahdy commented 2 weeks ago

문제: 우유와 요거트가 담긴 장바구니

설명

코드

SELECT cp.CART_ID
FROM CART_PRODUCTS cp
INNER JOIN CART_PRODUCTS sub
ON cp.CART_ID = sub.CART_ID
WHERE cp.NAME = 'Milk' AND sub.NAME = 'Yogurt'
GROUP BY cp.CART_ID, sub.CART_ID
ORDER BY cp.CART_ID

아이디어

한 테이블에서 서로 다른 조합만들기 문제

icegosimperson commented 2 weeks ago

문제: 물고기 종류 별 대어 찾기

설명

코드

SELECT ID, FISH_NAME, LENGTH
FROM FISH_INFO A JOIN FISH_NAME_INFO B
ON A.FISH_TYPE = B.FISH_TYPE
WHERE (A.FISH_TYPE, LENGTH) IN( 
    SELECT FISH_TYPE, MAX(LENGTH)
    FROM FISH_INFO
    GROUP BY FISH_TYPE
)
ORDER BY ID;

아이디어

기초적인 질문이긴한데 혹시 다들 서브쿼리 사용하는 문제 푸실 때 메인쿼리랑 서브쿼리 중에 뭐 먼저 작성해보시나요..?! 2주차에 지영님이 푸셨던 문제 입니다

baexxbin commented 2 weeks ago

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

설명

코드

-- 코드를 입력하세요
-- 가장 많은 리뷰 적은 회원 찾기

SELECT m.MEMBER_NAME, r.REVIEW_TEXT, DATE_FORMAT(r.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE m
JOIN REST_REVIEW r ON m.MEMBER_ID = r.MEMBER_ID
WHERE m.MEMBER_ID = (
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY COUNT(MEMBER_ID)
    DESC LIMIT 1
)
ORDER BY REVIEW_DATE, r.REVIEW_TEXT;

아이디어

COUNT(*)가 아닌 COUNT(MEMBER_ID)를 사용했습니다!!
8주차에 다혜님이 푼 문제입니다!!

Jewan1120 commented 1 week ago

문제: 조건에 맞는 사용자 정보 조회하기

설명

코드

SELECT
    B.USER_ID
    , B.NICKNAME
    , CONCAT(B.CITY, ' ', B.STREET_ADDRESS1, ' ', B.STREET_ADDRESS2) `전체주소`
    , REGEXP_REPLACE(B.TLNO, '(.{3})(.{4})(.{4})', '$1-$2-$3') `전화번호`
FROM
    USED_GOODS_BOARD A
    INNER JOIN USED_GOODS_USER B
        ON A.WRITER_ID = B.USER_ID
GROUP BY
    B.USER_ID
HAVING
    COUNT(B.USER_ID) >= 3
ORDER BY
    B.USER_ID DESC

아이디어

2주차 수빈님이 풀이하신 문제였습니다!

yeongleej commented 1 week ago

문제: 대장균의 크기에 따라 분류하기 2

설명

코드

SELECT ID,
       (CASE 
            WHEN (ROWNUM / CNT) <= 0.25 THEN "CRITICAL"
            WHEN (ROWNUM / CNT) <= 0.5 THEN "HIGH"
            WHEN (ROWNUM / CNT) <= 0.75 THEN "MEDIUM"
            ELSE "LOW"
        END) AS COLONY_NAME
FROM (SELECT ID, SIZE_OF_COLONY,
       ROW_NUMBER() OVER(ORDER BY SIZE_OF_COLONY DESC) AS ROWNUM
       FROM ECOLI_DATA) R,
     (SELECT COUNT(*) AS CNT FROM ECOLI_DATA) C
ORDER BY ID

아이디어

2) ROW_NUMBER() 활용 : 중복값에 대해서도 순차적인 순위를 표시

3주차 예진님이 풀이하신 문제입니다!