[TIL] 54일차 TIL(20230421) - SQL, Tableau 개념 정리

STARTERS/TIL

[TIL] 54일차 TIL(20230421) - SQL, Tableau 개념 정리

라딘 2023. 4. 24. 00:34

[TIL] 54일차 TIL(20230421) - SQL, Tableau 개념 정리

📗 Tableau 개념 정리

✅ 태블로 파일 형태

- tde, hyper: 데이터 추출 파일
- tds: 태블로에서 데이터를 수정한 내용, 계산된 필드, 그룹 등(데이터 소스 파일)
- tdsx: tds + tde(hyper)
- twb: 시각화 + 데이터 수정 내용(tds)
- twbx: 시각화 + 데이터 수정 내용(tds) + tde(hyper)

✅ MoM/MTD 함수

1) MoM: 전월대비 증감률

(sum(if datediff(‘month’,[Order Date],today())=1 then [Profit] END)
/sum(if datediff(‘month’,[Order Date],today())=2 then [Profit] END)) -1

이후 서식 누르고 퍼센테이지로 변환 + 소수점 자리 변경

1) MTD: 현재 월의 첫째날부터 기준일(오늘)까지의 누적값

DATEDIFF('month', [Order Date], [Date Parameter]) = 0 AND
DATEDIFF('day', [Order Date], [Date Parameter]) >= 0

계산된 필드로 만들고 F/T필터로 걸어준 뒤 원하는 측정값을 넣으면 자동으로 계산

✅ WINDOW 함수
- WINDOW_AVG(SUM([Sales]),-1,1) : 지난 달, 이번 달, 다음 달의 값 평균
- WINDOW_SUM(SUM([Sales]),-1,1) : 지난 달, 이번 달, 다음 달의 값 합계
- LOOKUP(SUM([Sales]), -1) : 1칸 위에 있는 행의 값 가져오기
- RUNNING_SUM(SUM([Sales])): 누계
- PREVIOUS_VALUE(0): 이전 값을 가져옴, SUM([Sales]) + PREVIOUS_VALUE(0)는 누계를 구할 수 있음
- RANK(SUM([Sales])): 등수

✅ DATE 함수

1. DATEADD(date_part, interval, date): date에 date_part 단위의 interval을 더함
ex) DATEADD('year', 2, #2021–08–02#)=2023–08–02 12:00:00AM

2. DATEDIFF(date_part, date1, date2)
date1(시작일)과 date2(종료일) 사이의 간격을 date_part 단위로 구함
ex) DATEDIFF('week', #2021–08–04#,#2021–08–07#)=0

3. DATEPART(date_part,date)
date의 date_part를 정수형으로 반환
DATENAME('month', #2021–08–04#) = 8

4. DATEPARSE(date_format, [date_string])
date_string(문자열)을 date_format의 형태(날짜형)로 반환
ex) DATEPARSE('MMMM dd, YY', 'August 04, 21')

5. DATERUNC(date_part, date)
date 기준 date가 속한 date_part 의 첫째 날 반환
ex) DATRUNC('quarter',#2021–08–04#)= 2021–07–01
ex) DATRUNC('month',#2021–08–04#)= 2021–08–01

✅ 태블로 기능 우선순위

📗 SQL 개념 정리

✅ 집계의 종류

- 전체 집계: 집계함수 OVER ()

- 그룹 집계: 집계함수 OVER(PARTITION BY <컬럼이름>)

- 윈도우프레임 집계: 집계함수 OVER(ROWS BETWEEN start AND end)

✅ 윈도우 프레임 지정

- ROWS BETWEEN start AND end

- start와 end에 올 수 있는 요소들은 다음과 같다.

CURRENT ROW: 현재 행
n PRECEDING: n행 앞
n FOLLOWING: n행 뒤
UNBOUNDED PRECEDING: 이전 행 전부
UNBOUNDED FOLLOWING: 이후 행 전부

✅ 집계함수

- SUM(SALES) OVER(PARTITION BY ~ ORDER BY ~)
- LAG(AMOUNT, 3) OVER(PARTITION BY ~ ORDER BY ~)
- COUNT(AMOUNT) OVER(PARTITION BY ~ ORDER BY ~)
- rank() over(partition by ~ order by ~)/ dense_rank() / row_number()
- mode() within group (order by column): 최빈값
- percentile_cont(백분위) within group (order by column): 데이터의 개수가 짝수일 경우 백분위수로 둘의 평균을 반환
- percentile_disc(백분위) within group (order by column): 데이터의 개수가 홀수일 경우 백분위수로 둘 중 작은 값을 반환

SELECT ORDER_YEAR, ORDER_MONTH, ORDER_DAY,
sum(SALES) over() --전체의 합을 모든 row에 동일하게 보여줌, 비율을 구할 때 유용
FROM CTE_ORDER_DETAILS;

SELECT ORDER_YEAR, ORDER_MONTH, ORDER_DAY, 
	sum(SALES) over(ORDER BY order_year)  --년도별 합을 구하고, 누적
FROM CTE_ORDER_DETAILS;

SELECT ORDER_YEAR, ORDER_MONTH, ORDER_DAY, 
	sum(SALES) over(PARTITION BY order_year ORDER BY order_year, order_month)  -- 동일한 partition 안에서 윈도우 함수를 적용
FROM CTE_ORDER_DETAILS;

SELECT *, lag(amount, 3) over(ORDER BY order_year, order_month, order_day) 3전일매출
FROM cte_pre_amount

-- 5일 이동평균
SELECT *,
	CASE WHEN count(amount) over(ORDER BY order_year, order_month, order_day ROWS BETWEEN 4 PRECEDING AND CURRENT row) = 5
	THEN avg(amount) over(ORDER BY order_year, order_month, order_day ROWS BETWEEN 4 PRECEDING AND CURRENT row)
	END AS moving_avg
FROM cte_amount;

✅ SQL 날짜함수

- date_part('part', ORDER_DATE): integer 형식
- date_trunc('part', 날짜/시간): timestamp, 해당 part의 첫번째 시간을 출력
- to_char(날짜/시간, 'part'): 문자열 형식으로 변환

✅ SQL 고객분석

🐣 ABC분석

① 매출액이 많은 순으로 정렬하고, 총 매출액 대비 고객별 매출액의 비율을 구한다.
② 구성비율이 높은 고객부터 정렬하여 순서대로 누적 비율을 구한다.
③ 세로축에 매출액 누적 비율을, 가로축에 고객을 기입하고 그래프를 그린다.
④ 세로축의 70%와 90% 지점에 속하는 고객을 각각 A, B그룹으로 정의하고, 이 외 그룹을 C그룹으로 정의한다.

🐣 z차트
- 월별매출, 매출누계, 이동년계를 이용해 Z형태의 차트를 그린다.
- 매출누계: 해당 월 매출에 이전 월까지 매출 누계
- 이동년계: 해당 월의 매출에 과거 11개월의 매출을 합한 값(최근 1년차 누적 합계)

-- 월별매출, 매출누계, 이동연계
WITH ym_sales AS (
SELECT to_char(o.order_date, 'yyyy-mm') order_ym, 
	sum(od.quantity * od.unit_price * (1-od.discount)) sales
FROM orders o, order_details od
WHERE o.order_id = od.order_id
GROUP BY order_ym
ORDER BY 1
), ym_sales_moving_sum AS (
SELECT *,
	round(sum(sales) over(ORDER BY order_ym ROWS BETWEEN 10 PRECEDING AND CURRENT row)::NUMERIC, 2) 이동연계
FROM ym_sales
)
SELECT order_ym,
	sales 월별매출, sum(sales) over(ORDER BY order_ym) 매출누계, 이동연계
FROM ym_sales_moving_sum
WHERE order_ym >= '1997-06'

🐣 RFM 분석
- Recency: 얼마나 최근에 구매했는가? max(max(order_date)) over() - max(order_date)
- Frequency: 얼마나 빈번하게 구매했는가? count(distinct order_id)
- Monetary: 얼마나 많은 금액을 지불했는가? sum(amount)
- 각 지표를 5단계로 나누어 1점부터 5점까지 부여 ex) ntile(5) over(ORDER BY recency desc) recency_score
- case when으로 구체적인 범위를 정하고 점수 부여

SELECT customer_id, max(max(order_date)) over() - max(order_date) recency,
	count(DISTINCT order_id) frequency, sum(amount) monetary
FROM cte_customers
GROUP BY customer_id
ORDER BY recency DESC

🐣 Decil 분석
1. 고객의 총 매출액 기준으로 정렬하고, 상위부터 10%씩 나누어 10개의 그룹을 할당한다.
2. decil별 매출합계, decil별 구성비, decil별 구성비 누계를 확인한다.
- ntile(나눌 group 수) over(partition by ~ order by ~)

-- 고객별 매출액을 기준으로 decil 생성
customers_amount_decil AS (
SELECT customer_id, sum(amount) amount, ntile(10) over(ORDER BY sum(amount) desc) decil
FROM cte_customers
GROUP BY customer_id
ORDER BY 2 DESC
),
-- decil별 매출합계, 구성비, 구성비 누계
customers_amount_decil2 AS (
SELECT decil,
	sum(amount) amount,
	sum(amount) / sum(sum(amount)) over() * 100 amount_rate
FROM customers_amount_decil
GROUP BY decil
)
SELECT *,
	sum(amount_rate) over(ORDER BY decil) cummulative_amount
FROM customers_amount_decil2

소감 및 정리

최종 평가를 위해 그동안 배운 sql과 태블로 이론을 정리했다. 정리하면서 보니 중간중간 자주 사용하지 않아 잊어버렸던 기능이나 함수들이 있다는 것을 깨달았다. 예를 들어 태블로의 window 함수는 프로젝트 중에도 거의 사용하지 않아서 잊어버리고 있었는데 정리하면서 복기할 수 있었다. 전반적인 현황과 추세를 살펴볼 필요가 있는 실무에서는 유용하게 사용할 수 있는 함수라고 생각해 꼭 기억해둬야겠다! 최종 평가 시험은 생각보다 어려웠고, 제대로 풀지 못한 문제들이 있어서 아쉬움이 남았다. 하지만 아쉬움을 뒤로하고 해커톤에 집중해야만 좋은 결과를 얻을 수 있을 것이다. 해커톤에서 좋은 성과를 얻어갈 수 있었으면 좋겠다! 🐰