[TIL]241204_코드카타, 서브쿼리 연습문제, 개인과제 2,4,5

내일배움캠프/TIL

[TIL]241204_코드카타, 서브쿼리 연습문제, 개인과제 2,4,5

dydatablog 2024. 12. 4. 20:47

728x90

SELECT ai.ANIMAL_ID,
    ai.NAME
FROM ANIMAL_INS ai
JOIN ANIMAL_OUTS ao ON ai.ANIMAL_ID = ao.ANIMAL_ID 
ORDER BY DATEDIFF (ao.DATETIME, ai.DATETIME) DESC
LIMIT 2;

-- 날짜를 그냥 ao.DATETIME - ai.DATETIME 으로 하면 정수형으로 결과값이 나와버림.
-- DATEDIFF(날짜1 - 날짜2) : 날짜1에서 날짜2를 빼주는 함수 를 이용!!

36. 보호소에서 중성화한 동물

SELECT ai.ANIMAL_ID, ai.ANIMAL_TYPE, ai.NAME
FROM ANIMAL_INS ai
JOIN ANIMAL_OUTS ao ON ai.ANIMAL_ID = ao.ANIMAL_ID 
WHERE ai.SEX_UPON_INTAKE LIKE 'Intact%'AND ao.SEX_UPON_OUTCOME LIKE 'Spayed%' 
    OR ai.SEX_UPON_INTAKE LIKE 'Intact%'AND ao.SEX_UPON_OUTCOME LIKE 'Neutered%';

37. 조건에 맞는 도서와 저자 리스트 출력하기

SELECT b.BOOK_ID, 
    a.AUTHOR_NAME, 
    DATE_FORMAT(b.published_date, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK b
JOIN AUTHOR a on b.AUTHOR_ID = a.AUTHOR_ID 
WHERE category = '경제'
ORDER BY published_date;

38. 조건별로 분류하여 주문상태 출력하기

SELECT ORDER_ID,
    PRODUCT_ID,
    DATE_FORMAT(OUT_DATE,'%Y-%m-%d')'OUT_DATE',
    CASE WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
        WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
        WHEN OUT_DATE IS NULL THEN '출고미정'
        END AS '출고여부'
FROM FOOD_ORDER
ORDER BY ORDER_ID;

39. 성분으로 구분한 아이스크림 총 주문량

SELECT INGREDIENT_TYPE,
SUM(TOTAL_ORDER) 'TOTAL_ORDER'
FROM FIRST_HALF f
JOIN ICECREAM_INFO i on f.FLAVOR = i.FLAVOR 
GROUP BY INGREDIENT_TYPE;

40. 루시와 엘라 찾기

SELECT 
    ANIMAL_ID,
    NAME,
    SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty') 
ORDER BY ANIMAL_ID;

SQL 서브쿼리 연습문제 :

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

SELECT FOOD_TYPE, 
        REST_ID,
        REST_NAME,
        FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN
    (
    SELECT FOOD_TYPE, MAX(FAVORITES) 
    FROM REST_INFO
    GROUP BY FOOD_TYPE
    ) 		-- 서브쿼리 결과값
ORDER BY FOOD_TYPE DESC;

[서브쿼리 결과값]

[전체쿼리 결과값]

개인과제

문제2

payments 테이블에서 각 결제 방식(payment_type)별 결제 금액의 합계와 해당 결제 방식이 전체 결제 금액에서 차지하는 비율을 계산하세요.

결과 컬럼: payment_type, total_payment_value, payment_percentage

-- percentage의 소숫점 값이 살짝 다름 : 각 행의 %를 더했기 때문
SELECT 
    payment_type,
    SUM(payment_value) AS 'total_payment_value',
    SUM(ROUND(p.payment_value / total.total_payment * 100, 2))AS payment_percentage
FROM
    payments p,
    (SELECT SUM(payment_value) AS total_payment FROM payments) total
GROUP BY payment_type;

-- 정답 : sum을 
SELECT 
    p.payment_type,	-- 3.다음을 출력
    SUM(p.payment_value) AS total_payment_value,	-- 4.다음을 출력
    ROUND(SUM(p.payment_value) / (SELECT SUM(payment_value) FROM payments) * 100, 2) AS payment_percentage
    	-- 5.(서브쿼리)payments에서 전체합을 계산
        -- 6. 외부쿼리(payment_type으로 그룹화된)payment_value의 합 / 전체합(서브쿼리 결과값) * 100 을 소수점 둘째자리까지
        -- 7. Percentage라고 지정후 출력
FROM 
    payments p	-- 1. payments테이블을
GROUP BY 
    p.payment_type;	-- 2. payment_type을 기준으로 그룹화

문제4 -

동일한 결제수단(payment_type)에서 이루어진 다른 결제의 평균 금액보다 높은 결제들 중에서, 해당 결제가 해당 결제수단의 총 결제 금액 대비 20% 이상을 차지하는 주문을 조회하세요. payment_ratio는 결제 금액이 총 결제 금액에서 차지하는 비율을 소수점 둘째 자리까지 계산해주세요.

결과 컬럼: order_id, payment_type, payment_value, payment_ratio

(문제2, 서브쿼리, window함수 참고)

1. 동일한 결제수단(payment_type)에서 이루어진 다른 결제의 평균 금액보다 높은 결제들 : 서브쿼리 + window함수로 계산

SELECT order_id ,
	payment_type ,
	payment_value,
	avg_type
FROM (
	SELECT order_id ,
		payment_type ,
		payment_value ,
		ROUND( AVG(payment_value) OVER(PARTITION BY payment_type), 2) AS avg_type
	FROM payments p 
) AS sub
WHERE payment_value > avg_type;

2. 해당 결제수단의 총 결제 금액

SELECT order_id ,
	payment_type ,
	payment_value,
	sum_type
FROM (
	SELECT order_id ,
		payment_type ,
		payment_value ,
		ROUND( AVG(payment_value) OVER(PARTITION BY payment_type), 2) AS avg_type,
		ROUND( SUM(payment_value) OVER(PARTITION BY payment_type), 2) AS sum_type -- 추가
	FROM payments p 
) AS sub
WHERE payment_value > avg_type;

3. 해당 결제가 해당 결제수단의 총 결제 금액 대비 20% 이상을 차지하는 주문 (payment_ratio는 결제 금액이 총 결제 금액에서 차지하는 비율을 소수점 둘째 자리)

=> 정답

-- 작성 쿼리
SELECT order_id ,
	payment_type ,
	payment_value ,
	ROUND(payment_value / sum_type * 100 ,2) AS 'payment_ratio' --추가
FROM (
	SELECT order_id ,
		payment_type ,
		payment_value ,
		ROUND( AVG(payment_value) OVER(PARTITION BY payment_type), 2) AS avg_type,
		ROUND( SUM(payment_value) OVER(PARTITION BY payment_type), 2) AS sum_type
	FROM payments p 
) AS sub
WHERE payment_value > avg_type
AND payment_value > sum_type * 0.2; --추가

-- 정답
WITH payment_summary AS (
    SELECT 
        payment_type,
        AVG(payment_value) AS avg_payment_value,
        SUM(payment_value) AS total_payment_value
    FROM payments
    GROUP BY payment_type
)
SELECT 
    p.order_id,
    p.payment_type,
    p.payment_value,
    ROUND(p.payment_value / ps.total_payment_value * 100, 2) AS payment_ratio
FROM payments p
JOIN payment_summary ps ON p.payment_type = ps.payment_type
WHERE p.payment_value > ps.avg_payment_value -- 조건1 체크!
AND p.payment_value / ps.total_payment_value >= 0.2; -- 조건2 체크!

문제5.

orders 테이블에서 월별(년-월) 주문 건수를 계산하되, 주문이 없는 달도 0건으로 포함하고, 지난달 대비 주문 건수 증감율(growth_rate)을 계산하세요. 결과는 년-월(month) 순서대로 정렬하며, 증감율은 소수점 둘째 자리까지 반올림 해주세요.

결과 컬럼: month, cnt_orders, growth_rate

-- <중간과정> 
-- 주문 월별 주문 개수 : 서브쿼리로 테이블화
-- 증감율 (이번달의 값 - 지난달의 값)/ 지난달의 값 * 100 : window함수 사용


SELECT 
	sub.month ,
	sub.cnt_orders,
	LAG(sub.cnt_orders)OVER () '이전값',
	ROUND((sub.cnt_orders - LAG(sub.cnt_orders)OVER ())/ LAG(sub.cnt_orders)OVER () *100 ,2 )AS growth_rate
FROM 
(
	SELECT 
		DATE_FORMAT(order_purchase_timestamp , '%Y-%m') month ,
		COUNT(*) cnt_orders
	FROM orders o
	GROUP BY DATE_FORMAT(order_purchase_timestamp , '%Y-%m') 
	ORDER BY 1
) AS sub

--> order값이 없는 달이 나오지 않음 (2016-11, 2016-12 등..)

튜터님 피드백: INTERVAL함수로 원하는 날짜 값을 테이블로 만든 후에 두 테이블을 JOIN해서 작성

https://okky.kr/questions/1162257

mysql 오늘부터 7일간 날짜 만들기 | OKKY Q&A

mysql 을 사용해서 오늘부터 7일전까지 날짜를 더미 데이터로 만들수 있을까요 ????

okky.kr

>> 미해결...! 내일 마저 작성해보기...

-- 중간 과정1 : 누락월 생성

WITH RECURSIVE all_months AS (
    SELECT DATE_FORMAT(MIN(order_purchase_timestamp), '%Y-%m') AS months
    FROM orders
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH), '%Y-%m')
    FROM all_months
    WHERE DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH) <= (
        SELECT DATE_FORMAT(MAX(order_purchase_timestamp), '%Y-%m-01') FROM orders
    )
)
SELECT months FROM all_months ORDER BY months; -- 누락월 채워진 것 확인!

-- 중간 과정2 : 월별 주문 건수 집계
WITH RECURSIVE all_months AS (
    SELECT DATE_FORMAT(MIN(order_purchase_timestamp), '%Y-%m') AS months
    FROM orders
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH), '%Y-%m')
    FROM all_months
    WHERE DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH) <= (
        SELECT DATE_FORMAT(MAX(order_purchase_timestamp), '%Y-%m-01') FROM orders
    )
)
SELECT 
    m.months,
    COALESCE(COUNT(o.order_id), 0) AS cnt_orders
FROM all_months m
LEFT JOIN orders o ON DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') = m.months
GROUP BY 1
ORDER BY 1;

-- 중간 과정3 : 지난 달 주문량 계산
WITH RECURSIVE all_months AS (
    SELECT DATE_FORMAT(MIN(order_purchase_timestamp), '%Y-%m') AS months
    FROM orders
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH), '%Y-%m')
    FROM all_months
    WHERE DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH) <= (
        SELECT DATE_FORMAT(MAX(order_purchase_timestamp), '%Y-%m-01') FROM orders
    )
),
monthly_orders AS (
    SELECT 
        m.months,
        COALESCE(COUNT(o.order_id), 0) AS cur_orders
    FROM all_months m
    LEFT JOIN orders o ON DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') = m.months
    GROUP BY m.months
)
SELECT 
    months,
    cur_orders,
    LAG(cur_orders) OVER (ORDER BY months) AS prev_orders
FROM monthly_orders;

중간 과정4 :월별 주문 증감률 계산 *증감율 = (현재값−과거값)/과거값×100

WITH RECURSIVE all_months AS (
    SELECT DATE_FORMAT(MIN(order_purchase_timestamp), '%Y-%m') AS months
    FROM orders
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH), '%Y-%m')
    FROM all_months
    WHERE DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH) <= (
        SELECT DATE_FORMAT(MAX(order_purchase_timestamp), '%Y-%m-01') FROM orders
    )
),
monthly_orders AS (
    SELECT 
        m.months,
        COALESCE(COUNT(o.order_id), 0) AS cur_orders
    FROM all_months m
    LEFT JOIN orders o ON DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') = m.months
    GROUP BY m.months
)
SELECT 
    months,
    cur_orders,
    prev_orders,
    ROUND(
        CASE 
            WHEN prev_orders IS NULL THEN NULL
            WHEN prev_orders = 0 THEN cur_orders * 100
            ELSE (cur_orders - prev_orders) / prev_orders * 100
        END, 
        2
    ) AS growth_rate
FROM (
    SELECT 
        months,
        cur_orders,
        LAG(cur_orders) OVER (ORDER BY months) AS prev_orders
    FROM monthly_orders
) sub
ORDER BY 1;

-- 정답
WITH RECURSIVE all_months AS (
    SELECT DATE_FORMAT(MIN(order_purchase_timestamp), '%Y-%m') AS months
    FROM orders
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH), '%Y-%m')
    FROM all_months
    WHERE DATE_ADD(CONCAT(months, '-01'), INTERVAL 1 MONTH) <= (
        SELECT DATE_FORMAT(MAX(order_purchase_timestamp), '%Y-%m-01') FROM orders
    )
),
monthly_orders AS (
    SELECT 
        m.months,
        COALESCE(COUNT(o.order_id), 0) AS cur_orders
    FROM all_months m
    LEFT JOIN orders o ON DATE_FORMAT(o.order_purchase_timestamp, '%Y-%m') = m.months
    GROUP BY m.months
)
SELECT 
    months,
    cur_orders,
    prev_orders,
    ROUND(
        CASE 
            WHEN prev_orders IS NULL THEN NULL
            WHEN prev_orders = 0 THEN cur_orders * 100
            ELSE (cur_orders - prev_orders) / prev_orders * 100 -- 전월 값이 0이면 계산이 불가하기 때문에 만드시 0일때 계산식을 설정
        END, 
        2
    ) AS growth_rate
FROM (
    SELECT 
        months,
        cur_orders,
        LAG(cur_orders) OVER (ORDER BY months) AS prev_orders
    FROM monthly_orders
) sub
ORDER BY 1;

728x90

'내일배움캠프 > TIL' 카테고리의 다른 글

[TIL]코드카타 (1)	2024.12.06
[TIL]241205_코드카타, 개인과제 문제6 (2)	2024.12.05
[TIL]241203_코드카타,SQL,그룹과제,개인과제1,3 (1)	2024.12.03
[TIL]241202_JOIN,서브쿼리,팀 프로젝트 주제정하기 (1)	2024.12.02
[TIL]241129_코드카타 (2)	2024.11.29

현재글[TIL]241204_코드카타, 서브쿼리 연습문제, 개인과제 2,4,5

그냥 하는거지

해봐야지 어쩌겠어라는 마음으로 살아가는 사람의 필기노트와 일기 그 어딘가..

그냥 하는거지