22.10.14.
데이터리안 강사님들의 SQL 중급 수업 마지막 날!

을 22.10.27.에 정리!

---

JOIN

• 실무에서, 데이터들은 하나의 테이블에 담겨있지 않다. 여러 테이블로 나누어져 저장되어 있다!
• 그런 여러 테이블간의 관계를 통해 연결하여 한번에 다룰 수 있는 방법이 바로 JOIN이다.
• 즉, 여러 테이블들을 좌우로 합쳐서 하나의 데이터처럼 활용하는 것이 바로 JOIN이다.

JOIN에는 INNER JOIN, LEFT, RIGHT,FULL OUTER JOIN이 있다.

INNER JOIN

INNER JOIN은 합쳐지는 두 테이블에 모두 존재하는 key에 대해서만 합쳐지는 것을 말한다.

다음과 같이 사용한다.

SELECT *
From users
	Inner join orders on users.id = orders.id

• INNER JOIN 을 쓴 후, JOIN할 테이블의 이름, ON, 결합의 기준이 될 key를 작성해준다.
• 양쪽의 key는 이름이 다를 수도 있고, 이름이 같아도 내용이 같으면 key가 아니므로 주의한다.
• 꼭 두개가 아니라 여러개의 테이블을 JOIN할 수도 있다.
• JOIN하는 테이블 간의 관계를 ERD를 통해 도식화할 수도 있다.
• 조건 역시 AND나 OR를 이용하여 여러 개를 줄 수 있다.

예제는 다음과 같다.

해커랭크(Average population of Each continent)

""" Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) 
and their respective average city populations (CITY.Population) rounded down to the nearest integer. """


SELECT COUNTRY.Continent, FLOOR(AVG(CITY.Population))
FROM CITY
    INNER JOIN COUNTRY ON CITY.CountryCode = COUNTRY.Code
GROUP BY COUNTRY.Continent

OUTER JOIN

LEFT, RIGHTJOIN은 각각 왼쪽의 테이블, 오른쪽의 테이블에 있는 key를 모두 표시하게 결합하는 것을 말한다.

보통 RIGHT JOIN은 사용하지 않고, LEFT JOIN을 많이 이용한다.

예제.
LEETCODE(183. customer who never order)

SELECT Customers.name as "Customers"
FROM Customers
   LEFT JOIN Orders On Customers.id = Orders.customerid
WHERE Orders.id is NULL

is NULL을 까먹어서 푸는데 한참 걸렸던 문제...
그리고 JOIN절에서도 AS를 사용할 수 있다!

SELF JOIN

• 하나의 테이블을 스스로 JOIN시켜서 사용해야하는 경우도 있다.
• 그런 경우 그냥, 다른 테이블을 JOIN 시킬 때와 마찬가지로 JOIN해주면 된다!
• 만약 테이블의 행끼리 비교해야 하는 경우에도, 이러한 SELF JOIN 을 사용할 수 있다.

예제는 다음과 같다.
LEETCODE 181번

SELECT Employee.name as Employee
FROM Employee
    INNER JOIN Employee as manager on Employee.managerId = manager.id
WHERE Employee.salary > manager.salary

LEETCODE 197번
틀린 풀이

SELECT Weather.id as id
FROM Weather
    INNER JOIN Weather as yesterday ON Weather.id = (Yesterday.id + 1)
WHERE Weather.temperature > yesterday.temperature

id가 날짜순으로 되어있지 않기 때문에 틀린 풀이.

정답 풀이

SELECT Weather.id
FROM Weather
    INNER JOIN Weather as yesterday ON Weather.recordDate = DATE_ADD(Yesterday.recordDate, INTERVAL 1 DAY)
WHERE Weather.temperature > yesterday.temperature

여기서는 datetime을 다루는 함수를 사용했다.

MySQL DATE_ADD 함수

DATE_ADD(기준날짜, INTERVAL)로 사용한다!

DATE_ADD(NOW(), INTERVAL 1 DAY)
DATE_ADD(NOW(), INTERVAL 1 SECOND)
DATE_ADD(NOW(), INTERVAL - 1 MINUTE)
DATE_ADD(NOW(), INTERVAL 1 HOUR)
DATE_ADD(NOW(), INTERVAL 1 YEAR)
등과 같이 사용한다!

UNION

JOIN이 테이블을 좌우로 합치는 거였다면, UNION은 테이블을 위아래로 합친다.

• 이때, JOIN은 key조차도 하나가 사라지지 않고 두개 데이터가 다 나오게 합쳐졌지만, UNION은 중복되는 데이터들은 제거하고 합쳐진다! UNION ALL을 사용한다면, 중복을 제거하지 않고 합쳐줄 수 있다.
• SELECT는 DISTINCT를 안써주면 ALL이 생략되어있다고 볼 수 있지만, UNION은 ALL을 안써주면 DISTINCT가 생략되어 있다고 볼 수 있다.

예시.

Product table에서 price가 5이하 또는 200이상인 상품들만 출력하세요 

UNION을 사용하지 않고

SELECT *
FROM Products
WHERE Price<= 5 or Price >=200

UNION을 사용하여

SELECT *
FROM Products
WHERE Price<= 5

UNION

SELECT *
FROM Products
WHERE Price >=200

예제.
해커랭크(Symmetric pairs)

Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.

SELECT DISTINCT p1.X , p1.Y
FROM Functions as p1
    INNER JOIN Functions as p2 ON p1.Y = p2.X
WHERE (p1.X = p2.Y) AND (p1.X < p1.Y) 

UNION

SELECT X, Y
FROM Functions
WHERE X = Y
GROUP BY X, Y
HAVING COUNT(*) = 2

ORDER BY X

y = x 위의 점과 그렇지 않은 점에 대해 처리 방식이 달라지므로, 따로따로 처리하여 UNION으로 합쳐준다!

이렇게 UNION으로 합쳐준 테이블을 정렬할 때 주의해야할 점이 있는데, 바로 첫번째 테이블에는 OREDER BY를 써주지 않고, 맨 마지막에만 ORDER BY를 써주어 전체 테이블만 정렬해 줄 수 있다는 것이다!

FULL OUTER JOIN

• 양쪽 테이블의 모든 key가 다 나오게 결합하는 FULL OUTER JOIN은 MySQL에서 제공하지 않는 기능이다.
• 해당 기능을 UNION, LEFT, RIGHT JOIN을 사용하여 구현할 수 있다.

	LEFT JOIN 한 테이블
UNION
	RIGHT JOIN 한 테이블

이렇게 구현하면 된다!

---

완전 SQL 이야기는 아니지만, 실무에서 데이터분석가로 일할때 도움이 될만한 내용도 많이 가르쳐주셨다. 이날은 매출분석 (AARRR)에 대해 배웠다.

매출분석(AARRR)

서비스 성장 분석 방법론

• A(Acquisition, 획득): 광고 등의 방법으로 새로운 사용자를 얻어오는 단계
• A(Activation, 활성화): 회원 가입, 튜토리얼 등 사용자를 활성화 시키는 단계
• R(Retention, 리텐션): 지속적으로 서비스를 사용하게 만드는 단계
• R(Revenue, 매출): 매출을 만드는 단계
• R(Referral, 추천): 다른 사용자에게 제품을 추천하는 단계

중 Revenue 과정의 이야기!

매출액의 증가를 어떻게 해석할 것인가?

해석을 위해 먼저 알고가야 할 두가지 개념이 있다.

• ARPU = 전체 매출 per 유저
• ARPPU = 전체 매출 per paying user(결제 유저)

의 두 가지 수치를 매출 분석에 있어 사용할 것이다.
최근 매출액이 증가하고 있다면?

• 전체 유저가 증가하고 있을 수도있고,
• 결제 유저의 비율이 증가하고 있을 수도 있고,
• 결제유저의 인당 결제액이 증가하고 있을 수도 있으니!

이러한 수치들을 측정하여 분석한다면, 매출 증대를 위한 전략을 짜는데 활용할 수 있을 것이다.

SQL을 통해 이러한 분석을 위한 쿼리를 짤 수 있다.

SELECT DATE(order_purchase_timestamp) dt
    , ROUND(SUM(p.payment_value), 2) revenue_daily
      , COUNT(DISTINCT o.order_id) Puser
    , ROUND(SUM(p.payment_value) / COUNT(DISTINCT customer_id),2) ARPPU
FROM olist_orders_dataset o
    INNER JOIN olist_order_payments_dataset p ON o.order_id = p.order_id
WHERE o.order_purchase_timestamp BETWEEN '2017-05-01 00:00:00' AND '2017-11-19 23:59:59'
GROUP BY dt
ORDER BY dt

해당 쿼리는, 쇼핑몰의 order와 payments 테이블을 활용해 일별 결제 유저 수, 매출액, ARPPU를 계산하여 보는 쿼리이다.

음 쿼리 짤때, 계속 헷갈리거나 틀리는 부분이 있다면, count에 DISTINCT를 걸어주는 것! 이거 좀 생각하면서 짜야겠다.