한 번 더 풀어볼 프로그래머스 문제 모음 [SQL]

- https://school.programmers.co.kr/learn/courses/30/lessons/131532

- 레벨 : 4

distinct 유의,한 번 더 생각해볼 만한 문제

SELECT YEAR(a.sales_date) AS year, MONTH(a.sales_date) AS month, b.gender, COUNT(DISTINCT(a.user_id)) AS users
    FROM online_sale AS a
    INNER JOIN user_info AS b
    ON a.user_id = b.user_id
    WHERE b.gender IS NOT NULL
    GROUP BY year, month, gender
    ORDER BY 1, 2, 3

 

 

- https://school.programmers.co.kr/learn/courses/30/lessons/164670

- 레벨 : 3

CONCAT 사용법

SELECT user_id, nickname,
    CONCAT(city, " ", street_address1, " ", street_address2) AS 전체주소,
    # substr(str, index, length): str의 index부터 length 만큼 추출
    CONCAT(substr(tlno, 1, 3), "-", substr(tlno, 4, 4), "-", substr(tlno, 8, 4)) AS "전화번호"
    FROM used_goods_user
    WHERE user_id IN (
        SELECT writer_id
            FROM used_goods_board
            GROUP BY writer_id
            HAVING COUNT(writer_id) >= 3
    )
    ORDER BY 1 DESC

 

- https://school.programmers.co.kr/learn/courses/30/lessons/131124

- 레벨 : 4

여러가지 고려사항이 많음

WITH c_table AS(
    SELECT member_id, COUNT(member_id) as cnt
        FROM rest_review
        GROUP BY member_id
)

SELECT A.member_name, B.review_text, B.review_date
    FROM member_profile AS A
    INNER JOIN (
        SELECT member_id, review_text, DATE_FORMAT(review_date, "%Y-%m-%d") as review_date
            FROM rest_review
            WHERE (member_id) IN (
                SELECT member_id
                    FROM c_table
                    WHERE cnt = (
                        SELECT MAX(cnt) as max_count
                            FROM c_table
                    )
            )
    ) AS B
    ON A.member_id = B.member_id
    ORDER BY 3 ASC, 2 ASC

 

 

- https://school.programmers.co.kr/learn/courses/30/lessons/59413

- 레벨 : 4

WITH 절은 이러한 서브 쿼리의 단점을 해결하기 위해 서브 쿼리를 마치 함수처럼 이름을 가지도록 사용하는 방법이다.

WITH
WITH_SUB AS (
	... -- 서브쿼리 작성
)
SELECT * FROM WITH_SUB; -- WITH절을 사용한 메인 쿼리 예시

WITH절을 가지고 자신이 자신을 호출하는 Recursive 한 방식을 사용할 수 있다. 이를 Recursive WITH절이라고 한다.
Recursive WITH 절을 사용하려면 UNION ALL을 가지고 초기값을 정해주는 초기 서브 쿼리와, 이후에 행위를 작성하는 Recurisive 서브 쿼리로 나뉜다. 단 항상 재귀함수에서 그랬듯이 무한루프에 빠지지 않도록 종료조건을 조건절로 표현을 해야한다.

WITH
SUM(NUM, RESULT) AS(
	-- 초기값을 설정해주는 초기 서브쿼리이다. 즉 NUM=1, RESULT=1 이다.
	SELECT 1,1 FROM DUAL 
	UNION ALL
	-- 이후 행위를 정의한 Recurisive 서브쿼리이다. NUM의 값은 1씩 증가시키며, RESULT 값에는 계속 (NUM + 1)을 더한다.
	SELECT NUM+1, (NUM+1) + RESULT FROM SUM WHERE NUM < 9-- 종료조건으로 NUM < 9를 작성했다.
)
SELECT NUM ,RESULT FROM SUM;

ref) https://camel-context.tistory.com/16

 

 WITH 절과 JOIN 및 SUBQUERY를 적절히 섞어 풀면 된다.

WITH RECURSIVE time AS(
    SELECT 0 AS hour
    UNION
    SELECT hour + 1 
        FROM time
        WHERE hour < 23
)
SELECT t.hour, COUNT(a.animal_id)
    FROM time AS t
    LEFT OUTER JOIN animal_outs AS a
    ON t.hour = HOUR(a.datetime)
    GROUP BY t.hour
    ORDER BY t.hour

 

- https://school.programmers.co.kr/learn/courses/30/lessons/62284

- 레벨 : 4

서브쿼리 두 개로도 풀 수 있지만, 아래 풀이가 더 빠름

SELECT DISTINCT(cart_id) FROM cart_products
    WHERE cart_id IN 
        (SELECT cart_id FROM cart_products WHERE name = 'Milk')
    AND name = 'Yogurt'
    ORDER BY cart_id

 

- https://school.programmers.co.kr/learn/courses/30/lessons/131113

- 레벨 : 3

WHEN CASE 연습용, 프로그래머스는 DATE 타입을 DATE_FORMAT을 사용하여 변환하지 않으면 틀렸다고 하니 꼭 변환하자

SELECT order_id, product_id, DATE_FORMAT(out_date, "%Y-%m-%d"),
    (CASE WHEN out_date <= '2022-05-01' THEN '출고완료'
         WHEN out_date IS NULL THEN '출고미정'
         ELSE '출고대기'
     END) AS '출고여부'
    FROM food_order
    ORDER BY order_id;