[TIL]241203_코드카타,SQL,그룹과제,개인과제1,3

31. 오랜 기간 보호한 동물(1)

--작성한 코드 >> 오답
SELECT ai.NAME, ai.DATETIME
FROM ANIMAL_INS AS ai LEFT JOIN ANIMAL_OUTS AS ao
ON ai.ANIMAL_ID = ao.ANIMAL_ID
WHERE ai.ANIMAL_ID NOT IN (ao.ANIMAL_ID)
ORDER BY ai.DATETIME;

--정답1
SELECT AI.NAME, AI.DATETIME
FROM ANIMAL_INS AI LEFT JOIN ANIMAL_OUTS AO
ON AI.ANIMAL_ID=AO.ANIMAL_ID
WHERE AO.ANIMAL_ID IS NULL -- 추가 설명 참조
ORDER BY AI.DATETIME
LIMIT 3;

--정답2
SELECT NAME, DATETIME
FROM ANIMAL_INS 
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY DATETIME 
LIMIT 3;

 

[LEFT JOIN 추가설명]

1. LEFT JOIN을 했을 때의 결과 값

SELECT *
FROM ANIMAL_INS AI 
LEFT JOIN ANIMAL_OUTS AO
ON AI.ANIMAL_ID=AO.ANIMAL_ID
ORDER BY AI.DATETIME;

 

실행결과:

2. WHERE절로 AO.ANIMAL_ID가 NULL인 값만 추출 + ANIMAL_INS의 NAME과 DATETIME만

-- 실행쿼리
SELECT AI.NAME, AI.DATETIME
FROM ANIMAL_INS AI LEFT JOIN ANIMAL_OUTS AO
ON AI.ANIMAL_ID=AO.ANIMAL_ID
WHERE AO.ANIMAL_ID IS NULL

2. 실행결과

3. ORDER BY와 LIMIT으로 정렬 및 가장 오래 보호소에 있었던 3마리 추출 = 정답과 동일

 

 

32. 카테고리 별 도서 판매량 집계하기

SELECT CATEGORY, 
    SUM(sales) TOTAL_SALES
FROM BOOK AS b JOIN BOOK_SALES AS bs
ON b.BOOK_ID = bs.BOOK_ID
WHERE sales_date LIKE '2022-01%'
GROUP BY CATEGORY
ORDER BY CATEGORY;

 

33. 상품 별 오프라인 매출 구하기

SELECT PRODUCT_CODE,
    SUM(PRICE * sales_amount) AS SALES
FROM PRODUCT AS p
LEFT JOIN OFFLINE_SALE AS o
ON p.PRODUCT_ID = o.PRODUCT_ID 
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE;

 

34. 있었는데요 없었습니다.

SELECT ai.ANIMAL_ID, ai.NAME
FROM ANIMAL_INS AS ai
LEFT JOIN ANIMAL_OUTS AS ao
ON ai.ANIMAL_ID= ao.ANIMAL_ID
WHERE ao.DATETIME < ai.DATETIME
ORDER BY ai.DATETIME;

 

 

---

WINDOW 함수

-- 윈도우 함수 기본문법

SELECT 윈도우함수(컬럼1) OVER (
    [PARTITION BY 컬럼2] -- 그룹화
    [ORDER BY 컬럼3 ASC|DESC] -- 정렬
    [ROWS|RANGE BETWEEN A AND B] -- 계산 범위
) AS 결과
FROM 테이블;
-- []은 생략가능

 

 

RO는 데이터 행 순서 기준 (앞뒤 한명씩)

RANGE 는 데이터 '값' 기준 (값 +-1인 모든 행)

 

 

---

그룹과제

 

SELECT user_id,
       COUNT(CASE WHEN event_type = 'cart' THEN product_id END) AS cart_count,
       COUNT(CASE WHEN event_type = 'purchase' THEN product_id END) AS purchase_count,
       COUNT(CASE WHEN event_type = 'cart' THEN product_id END) - COUNT(CASE WHEN event_type = 'purchase' THEN product_id END) yet_purchased
FROM `2019_dev` d 
GROUP BY 1
ORDER BY 1;

-- 컬럼값으로 user_id, 
-- 유저별 cart 가 발생한 수, 
-- 유저별 purchase 가 발생한 수, 
-- 유저별 cart 가 발생한 수에서 purchase 가 발생한 수를 뺸 (장바구니에 넣었지만 구매는 하지 않은)값을 구한다.

 

문제점: 이벤트 값 중에 remove_from_cart 값이 존재하므로, cart가 발생한 후에 remove_from_cart가 발생한 수는 빼야한다.

-> 방대한 주제보다 한가지 주제에 집중해서 분석, 내일 오전 일찍 전반적인 방향성 확인.

 

---

[개인과제]

[문제1]

예상 배송일보다 실제 배송을 늦게 받은 고객들 중에서, 가장 많은 주문을 한 고객의 ID와 총 주문 수를 조회하세요.

• 결과 컬럼: customer_id, total_orders

-- 문제1
SELECT customer_id, 
	COUNT(*) AS 'total_orders' 
FROM orders o 
WHERE DATE(order_estimated_delivery_date) < DATE(order_delivered_customer_date)
GROUP BY customer_id 
LIMIT 1;

 

[문제3]

배송된(delivered) 주문을 기준으로 고유 고객 수, 총 주문 수, 총 결제 금액, 그리고 고객 1명당 평균 결제액을 계산하세요.

• 결과 컬럼: cnt_users, cnt_orders, sum_payment, arppu

-- 문제3
--작성쿼리
SELECT 
	COUNT(o.order_id) AS 'cnt_users' , 
	COUNT(o.customer_id) AS 'cnt_orders', 
	SUM(payment_value) AS 'sum_payment',
	AVG(payment_value) AS 'arppu' 
FROM orders o
JOIN payments p 
ON o.order_id = p.order_id 
WHERE order_status = 'delivered'
GROUP BY order_status;

-- 정답
SELECT 
    COUNT(DISTINCT o.customer_id) AS cnt_users,
    COUNT(o.order_id) AS cnt_orders,
    SUM(p.payment_value) AS sum_payment,
    CASE
        WHEN COUNT(DISTINCT o.customer_id) = 0 THEN 0
        ELSE SUM(p.payment_value) / COUNT(DISTINCT o.customer_id)
    END AS arppu 
FROM orders o
INNER JOIN payments p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered';