[내일배움캠프_데이터분석] 2주차 화요일 TIL _ SubQuery

A. 어떤 문제가 있었는지

: 금일까지 제출해야하는 SQL 과제가 있었으나 문제가 어려웠다.

 

B. 내가 시도해본 것

: SQL 강의를 모두 듣고 숙제 및 온보딩 주차 강의를 따라가며 실습하였다.

 

C. 어떻게 해결했는지

: SQL 구조와 문제의 문장 간 관계에 집중하였다. 어떤 데이터가 필요하고, 그 위에 어떤 방식으로 데이터를 솎아내면 되는지에 집중해 공부하였다.

 

D. 무엇을 새롭게 알았는지

: SubQuery, Join, Pivot, Format 생성 및 변경 하는 방법을 알았다.

- SubQuery와 Join을 활용하여 복잡한 연산 수행이 가능하다는 것을 배웠다.

- 예상치 못한 데이터 값에 대한 분석 및 적절한 처리 방법을 습득하였다.

 

Q. 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

SELECT restaurant_name,
       price_group,
       case when age<30 then '~20대'
       		when age<40	then '30대'
       		when age<50 then '40대'
       		ELSE '50대 이상'
       		END age_group
FROM
(
SELECT restaurant_name,
	   age,
       case when price>=5000 then '5,000원'
       		when price>=10000 then '10,000원'
       		when price>=30000 then '30,0000원'
       	    ELSE '30,000원 초과' END price_group     
FROM 
(
SELECT f.restaurant_name,
       avg(f.price) price,
       avg(c.age) age
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
GROUP BY f.restaurant_name
) base
) avg_price
ORDER BY restaurant_name

 

Q. 음식 타입별, 연령별 주문건수 Pivot View 만들기

SELECT cuisine_type,
	   sum(case when age between 10 and 19 then 1 else 0 end) as '10대',
	   sum(case when age between 20 and 29 then 1 else 0 end) as '20대',
	   sum(case when age between 30 and 39 then 1 else 0 end) as '30대',
	   sum(case when age between 40 and 49 then 1 else 0 end) as '40대',
	   sum(case when age between 50 and 59 then 1 else 0 end) as '50대',
	   count(1) cnt_order
FROM food_orders f INNER JOIN customers c ON f.customer_id =c.customer_id
where age BETWEEN 10 and 59
GROUP BY 1
ORDER BY 1

※  형식을 외우면 쉽지만, 이론적으로 왜 이렇게 코드를 작성하여야 하는지 모르겠다.

 

[과제 중 어려웠던 문제]

Q. 아래 조건을 만족시키는 코드를 생성하라

조건

1. user 테이블에서 서버 번호가 2 이상인 데이터와 payment 테이블에서 결제 수단 'Card' 인 경우Join 2. 게임계정ID별로 게임캐릭터ID를 중복값 없이 세어 'actorcnt' 로 컬럼명 명시 3. 게임계정ID별 결제금액의 합 출력하고 'sumamount'로 컬럼명 명시 4. 최종적으로 'actorcnt'가 2 이상인 경우만 추출, 'sumamount' 의 내림차순 정렬

select game_account_id,
       count(distinct game_actor_id) as actorcnt,
       sum(pay_amount) as sumanount
from 
(
select u.serverno, u.game_account_id, p.pay_type, p.pay_amount, u.game_actor_id
from users u left join payment p on u.game_account_id = p.game_account_id 
)a
where serverno >=2
	and pay_type = 'CARD'
group by game_account_id
having actorcnt >=2
order by sumanount desc

▶ 조건이 많고, Join 구문에 익숙하지 않아 코드의 흐름을 따라가는 것이 큰 어려움으로 다가옴.

    그러나 반복적인 강의 실습을 통해 SQL의 구조에 따라 어떻게 작성하는 것이 좋은지 감을 잡아가고 있음.

 

번외

[예상과 많이 다른 튜터와 코드 비교]

조건

1. 각 서버(serverno)별로 결제한 사용자의 수를 계산하세요. 2. 서버 번호와 해당 서버에서 결제한 사용자 수를 반환해야 합니다. 3. 사용자 수는 중복 제거한 상태로 계산해주세요. 결과값은 아래와 같아야합니다.

select serverno, count(distinct game_account_id) as 'users_with_payment'
from 
(
select p.game_account_id, u.serverno
from users u inner join payment p on u.game_account_id = p.game_account_id
) a
group by serverno

SELECT serverno, COUNT(DISTINCT(u.game_account_id))
FROM USERS u
JOIN PAYMENT p ON u.game_account_id = p.game_account_id
GROUP BY u.serverno

한 눈에 봐도 튜터의 코드와 비교됨. 좀 더 간결하게 작성하는 방법을 생각하자.