1주차 - SQL로 피봇테이블 만들기, 다중컬럼 서브쿼리, 조인

데이터리안 SQL 데이터 분석 캠프 실전반 2기 1주차 수업에서는 피봇테이블 만드는 법과 기본 함수들에 대해 공부했다.

내가 새로 알게된 내용과 기존에 알고 있었지만 기록하고 싶은 내용 위주로 남겨두고자 한다.

** 본 내용은 캠프에서 배운 내용을 바탕으로 쿼리를 재구성한 것이며, 수업에서 사용하는 쿼리와 동일하지 않습니다.

 

 

 

1. 피봇 테이블 만들기

 

<와인 색별 와인 속성의 평균 구하기>

solvesql 내 'Wines' 테이블을 활용했다. 아마 구글링하면 나오는 데이터인 것 같긴 하지만, 재가공된 것일 수도 있으므로 저작권상 테이블 스키마는 첨부하지 않겠다.

SELECT color,
      ROUND(AVG(CASE WHEN color = 'white' THEN fixed_acidity ELSE fixed_acidity END),2) AS m_fixed_acidity,
      ROUND(AVG(CASE WHEN color = 'white' THEN volatile_acidity ELSE volatile_acidity END),2) AS m_volatile_acidity,
      ROUND(AVG(CASE WHEN color = 'white' THEN citric_acid ELSE citric_acid END),2) AS m_citric_acid,
      ROUND(AVG(CASE WHEN color = 'white' THEN residual_sugar ELSE residual_sugar END),2) AS m_residual_suger
FROM wines
GROUP BY color

** 참고

fixed_acidity(고정산) : 와인의 산도를 제어함

volatile_acidity(휘발산) : 와인의 향에 연관이 많음

citric_acid(구연산) : 와인의 신선함을 유지시켜주는 역할을 하며, 산성화에 연관을 미침

residual_sugar(잔여 설탕) : 와인의 단맛을 올려줌. 포도의 감미에서 오는 잔류 설탕

 

와인에 대해 무지하고 먹어본 적도 별로 없지만... 궁금해서 이것 저것 찾아봤다.

• 레드가 화이트에 비해 고정산이 조금 높다. 고정산에 대한 정보는 찾기 힘들었는데,, 그냥 기본적으로 고정되어 있는 산을 뜻하는 거 아닌가 추측해본다.
• 레드가 화이트에 비해 휘발산이 높다. 휘발산은 날카롭고 자극적인 식초 향을 내게 하는 속성이라고 한다. 따라서, 레드가 화이트에 비해 코를 찌르는 향이 더 많이 난다고 볼 수 있다.
• 레드와 화이트 둘 다 구연산은 비슷한 수치를 보인다. 구연산은 우리가 감귤류라고 부르는 과일, 즉 라임같은 과일에 많은 산이라고 한다.
• 화이트가 레드에 비해 잔류 설탕이 엄청 많다. 와인을 많이 먹어보지 않아서 내 경험상으로도 진짜 그런지는 모르겠지만 위 데이터로만 보면 화이트가 레드보다 달다고 볼 수 있다.

 

 

<연도별 책 평점 수준 구하기>

solvesql 내 'books' 테이블을 활용했다.

SELECT year,
      COUNT(CASE WHEN user_rating >= 4.8 THEN user_rating END) '상',
      COUNT(CASE WHEN user_rating >= 4.2 and user_rating < 4.8 THEN user_rating END) '중',
      COUNT(CASE WHEN user_rating >= 3.5 and user_rating < 4.2 THEN user_rating END) '하'
FROM books
GROUP BY year
ORDER BY year

• 내 나름대로의 범위를 정해 평점을 '상', '중', '하'로 구분한 후, 연도별로 그 개수를 집계했다.
• 대략적으로 살펴보면, 해가 지날수록 '상' 수준의 책의 수가 증가한다. 독자들의 취향을 저격한 좋은 퀄리티의 수준의 책이 많이 출판되었다고 볼 수 있을지도 모르겠다.
• 반면, '중' 수준의 책의 개수는, 초반 년도에는 30 후반 40 초반 언저리였지만 점차 감소해 2019년에는 18개밖에 없다. 이 또한 '상' 수준의 책이 많아지면서 '중' 수준의 책이 감소했다고 볼 수 있다.
• '하' 수준의 책은 많지는 않지만 항상 1개 이상이었던 것에 비해 2018, 2019년에는 0개인 것을 확인할 수 있다.

 

 

 

2. 다중컬럼 서브쿼리

 

<도시별로 최대 이윤을 남긴 도시의 데이터만 추출하기>

solvesql 내 'US E-commerce Records 2020' 테이블을 활용했다.

SELECT *
FROM records
WHERE (city, profit) IN (
                        SELECT city, MAX(profit) 
                        FROM records
                        GROUP BY city
                        )

** 우측 컬럼은 생략함

 

위와 같이 WHERE 절에 컬럼 N개, 로우 N개인 서브쿼리를 사용할 수 있다.

 

 

 

3. 두 테이블의 컬럼의 값이 다른 것을 기준으로 조인하기( ON 테이블1의 컬럼 != 테이블2의 컬럼)

 

캠프에서 풀었던 '멘토링 짝꿍 리스트' 문제는 저작권 때문에 문제 내용과 정답 쿼리를 올릴 수 없기 때문에, 다른 테이블을 활용해 쿼리를 작성해보았다.

solvesql 내 'E-commerce Data' 테이블을 활용했다.

SELECT *
FROM orders o
INNER JOIN order_details d
ON o.order_id != d.order_id
LIMIT 100

 

보통 조인을 수행할 때, ON 절에 '테이블1의 컬럼 = 테이블2의 컬럼'이라고 작성한다.

하지만 위 쿼리처럼 '테이블1의 컬럼 != 테이블2의 컬럼' 라고 작성할 수도 있다. 이렇게 작성할 경우, orders 테이블의 order_id와 order_details 테이블의 order_id가 같지 않은 모든 경우의 수가 추출된다.

 

 

 

 

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반' 을 수강하며 작성한 내용입니다.

SQL 데이터 분석 캠프에서 제공하는 수업 자료의 저작권은 데이터리안에게 있으며, 자료의 무단 복제 및 배포, 상업적 이용을 금지합니다.