[데이터 분석을 위한 SQL 레시피] 20강 - 여러 개의 데이터셋 비교하기

 

• 데이터의 순위를 비교하는 경우

인기 기사 순위 등을 화면에 출력해도 변화가 거의 없다면 자주 방문하는 사용자에게는 흥미없는 정보 → 순위 집계기간과 조건을 적절하게 변경해야함.

집계기간 또는 출력 조건을 변경했을 때 과거 로직과 비교해서 어떤 차이가 있었는지 확실하게 확인해야함 → 로직의 결과로 출력된 순위의 유사도를 수치화해서, 새로운 로직과 과거의 로직 변화를 정량적으로 설명할 수 있어야함.

 

• 추가된 마스터 데이터 추출하기

** 두 개의 마스터 테이블에서 한쪽에만 존재하는 레코드를 추출할 때는 **외부 결합 (OUTER JOIN)**을 사용.

→ 새로운 마스터 테이블에만 존재하는 레코드를 추출할 때는 새로운 테이블을 기준으로 오래된 테이블을 LEFT OUTER JOIN 하고, 오래된 테이블의 컬럼이 NULL인 레코드를 추출하면 됨.

#20-1 추가된 마스터 데이터를 추출하는 쿼리

SELECT
	new_mst.*
FROM
	mst_products_20170101 AS new_mst
	LEFT OUTER JOIN
		mst_products_20161201 AS old_mst
	ON
		new_mst.product_id = old_mst.product_id
WHERE
	old_mst.product_id IS NULL
; 

 

• 제거된 마스터 데이터 추출하기

오래된 테이블에만 있는 레코드 추출하면 됨.

위의 코드에서 LEFT → RIGHT OUTER JOIN

 

• 갱신된 마스터 데이터 추출하기

오래된 테이블과 새로운 테이블에 모두 존재하고, 특정 컬럼의 값이 다른 레코드를 추출하면됨. → 타임스탬프 값이 다른 레코드 추출

#20-3 변경된 마스터 데이터를 추출하는 쿼리

FROM
	mst_products_20170101 AS new_mst
JOIN
	mst_products_20161201 AS old_mst
ON
	new_mst.product_id = old_mst.product_id
WHERE
--갱신 시점이 다른 레코드만 추출하기
	new_mst.updated_at <> old_mst.updated_at

 

• 변경된 마스터 데이터 모두 추출하기

추가, 제거, 갱신된 레코드 모두 추출

→ FULL OUTER JOIN

• 오래된 테이블 값이 NULL이면 추가
• 새로운 테이블의 값이 NULL이면 삭제
• 이외의 경우(타임스탬프가 다른 경우)는 갱신 ← outer join의 경우 한쪽에만 데이터가 있을 때, 다른 쪽이 null이 되므로 new.stamp <> old.stamp연산자 만으로 제대로 비교가 되지 않음.

→ 한쪽에만 NULL이 있는 레코드 확인할 때는 IS DISTINCT FROM 연산자 사용 (WHERE 절에 ← 오래된 값, 새로운 값 모두 한쪽에 null 있는거 찾는거니까.)

# IS DISTINCT FROM 이해 -> null 포함한 비교 가능하게 해주기

SELECT *
FROM temp_table
WHERE address IS DISTINCT FROM '서울';

-- 아래는 IS DISTINCT FROM 와 동일한 쿼리

SELECT *
FROM temp_table
WHERE address != '서울' OR address IS NULL;

 

IS DISTINCT FROM = null 포함한 not equal
만약 A와 B에 대해 <> 또는 ≠ 비교 연산을 수행하는 경우, 둘 중 하나라도 null이면, 결과 값은 반드시 null

cf. IS NOT DISTINCT FROM = null 포함한 equal

 

*null = null인걸 true로 보고 안고 감. (원래는 null인데)

 

- 두 순위의 유사도 계산하기

방문 횟수, 방문자 수, 페이지 뷰 를 어떻게 조합해야 가장 적합한 점수 부여? → 순위들의 유사도를 계산해서 어떤 순위가 효율적인지 정량적으로 평가.

 

• 지표들의 순위 작성하기

방문자 수 = 브라우저를 꺼도 사라지지 않는 쿠키의 유니크 수 (한 명의 사용자가 1일에 3회 사이트를 방문해도 1회로 집계) → count(distinct long_session) AS access_users (쿠키)
방문 횟수 = 브라우저를 껐을 때 사라지는 쿠키의 유니크 수 (한 명의 사용자가 1일에 3회 사이트를 방문하면 3회로 집계) → count(distinct short_session) AS access_count ) (로그인/ 창 닫았다 키기)
페이지 뷰 = 페이지를 출력한 로그의 줄 수

#20-5 3개 지표 기반 순위작성

..
SELECT 'access_user' AS type, path, RANK() OVER (ORDER BY access_users DESC) AS rank FROM path_stat
UNION ALL SELECT 'access_user' AS type, path, RANK() OVER (ORDER BY access_count DESC) AS rank FROM path_stat
UNION ALL SELECT 'access_user' AS type, path, RANK() OVER (ORDER BY page_view DESC) AS rank FROM path_stat

 

• 경로별 순위들의 차이 계산하기

경로와 순위 조합을 만들고, 순위 차이 계산

ex_ 결과 창에서 ‘/top’ → 방문 횟수 1위, 방문자 수 3위

→ 순위의 차이(diff)는 양쪽 순위 차이의 제곱 ⇒ POWER(r1.rank - r2.rank, 2) AS diff

카테시안 곱처럼 같은 테이블 자가복제 되면 하나에 대해 쫘르륵 해당하는게 옆에 붙음. 그렇게 하나에 3개씩 붙는거에 path가 같은 조건을 걸어줬으니까 그것만 남는거임.

 

• 스피어만 상관계수 계산하기

→ 순위의 유사도 계산. → 두 순위의 유사도를 수치로 표현할 수 있으면, 서로 다른 점수를 기반으로 만든 순위가 어떤 유사성을 갖는지 확인할 수 있음. → 이상적인 순위 자동 생성.

ex) 과목의 유사성 측정할 때 → 수학성적이 높은 학생은 영어 성적이 높을까? 국어성적이 높은 학생은 영어성적이 높을까?

→ 두 개의 순위가 완전히 일치할 경우 1.0 ↔ 완전히 일치하지 않을 경우 -1.0

 

 spearman = 1 - 6*sum(diff) / N3 - N ( N: 순위 페어 수)

 

→ 1- ( 6.0 * SUM(diff) / (POWER(COUNT(1),3) - COUNT(1))) AS spearman