[STARTERS 4기 TIL] SQL 심화 #2 - 기초 복습, 매출 지표 분석 (230411)

 

📖 오늘 내가 배운 것

 

1. 발표 및 피드백

2. 기초 복습

3. 주요 지표 추출 방안

4. 과제

 

---

1. 발표 및 피드백

 

피드백 - 전체 데이터에 대한 인사이트 찾는 것도 좋지만 초기 단계에서는 테이블-컬럼 EDA 했을면 좋을 거 같다!

 

2. 데이터 분석을 위한 쿼리 기초 복습

2.1 기본 select 구문

DISTINCT : 중복 추출되는행 제거 

* : 테이블 내 모든 컬럼 추출

alias : 다른 이름 부여 

테이블명 : 칼럼 추출 대상 테이블

WHERE : 검색 조건 앞에 명시하는 키워드

조건 - 컬럼, 표현식, 상수, 비교 연산자, 논리 연산자, 부정 연산자

ORDER BY : 정렬! ASC or DESC

 

2.2 칼럼 연산자

- 산술 연산자 : 숫자형 자료인 경우 연산 (+,-,*,/)

- 합성 연산자 : 문자형 자료인 경우 합성 (||, concat) + null과 합성 시  null

- coalesce : 인자로 주어진 컬럼들 중 null이 아닌 첫번째 값 반

- NULLIF(표현식1,표현식2) : 표현식1=표현식2면 NULL

 

# nullif! categroy_name이 beverages라면? 얘는 null로 만듷어라!

select *, NULLIF(CATEGORY_NAME,'Beverages')
from categories c ;

 

2.3 날짜/시간형 데이터 다루기

1. 데이터 타입

timestamp : 날짜와 시간 - YYYY-MM-DD HH:MI:SS.MS TIMEZONE

date : 날짜 (시간 미포함) - YYYY-MM-DD

time : 시간 (날짜 미포함) - HH:MI:SS.MS [TIMEZONE]

interval : 날짜 차이 - 1days, 1mon, 1year

 

2. 현재 날짜, 시간 가져오기

select now();
select current_timestamp ; -- timezone 포함
select localtimestamp ; -- timezone 포함하지 않음
select current_date ;
select current_time ; -- timezone 포함
select localtime ; -- timezone 포함하지 않음

 

3.  단일 행 함수 > 날짜 함수

now( )	현재 날짜와 시각 출력
extract( ‘part’ from 날짜/시간타입)	날짜/시간 데이터에서 part를 반환
date_part(‘part’, 날짜/시간타입)	날짜/시간 데이터에서 part를 반환
date_trunc(‘part’, 날짜/시간타입)	날짜/시간 데이터에서 part 이하 초기화하여 반환
to_char(날짜/시간데이터, ‘part’)	날짜/시간 데이터에서 part를 문자열로 반환

 

2.4 자료형 변환

cast(변환대상 as 자료형)

or
변환대상::자료형

 

2.5 다중 행 함수

다중 행을 인자로 받아들여 한 개의 값으로 결과를 반환하는 함수

집계함수 (전체에 레코드에 대한 집계, 소그룹에 대한 집계)

 

1. 집계함수 종류

GROUP BY 했을 때 주로 사용

COUNT (*)	NULL값을 포함한 행의 수 반환
COUNT  (DISTINCT )	표현식의 행의 개수 반환 (NULL 제외)
SUM	표현식의 합계 반환 (NULL 제외)
AVG	표현식의 평균 반환 (NULL 제외)
MAX	표현식의 최대값 반환 (문자, 날짜 타입 사용 가능)
MIN	표현식의 최소값 반환 (문자, 날짜 타입 사용 가능)
STDDEV	표현식의 표준편차 반환 (NULL 제외)
VARIANCE	표현식의 분산 반환 (NULL 제외)

 

2. GROUP BY , HAVING 절

GROUP BY 쓰지 않구 집계를 하게 되면? 전체 레코드에 대해 집계

 

3. 쿼리의 실행 순서

5 SELECT

1 FROM & JOIN

2 WHERE

3 GROUP BY

4 HAVING

6 ORDER BY

7 LIMIT

 

3. 데이터 분석을 위한 쿼리 실습 - 구매지표 추출

3.1 구매지표

1.매출액 (일자별, 월별, 분기별)

2.구매자수, 구매건수 (일자별, 월별, 분기별)

3.인당 매출액 (월별, 분기별)

4.건당 구매금액 (월별, 분기별)

 

1. 일별 구매지표 추출하기

일별 매출액

일별 주문 건수

일별 구매자 수

 

들어가기 앞서

필요한 데이터는?

어떤 테이블에 존재?

어떻게 조인?

 

일별 매출액 구하기 위해 필요한 테이블?

orders, orderdetails

 

orders : order_date

order_details : unitprice, quantity, discount

 

step 1. 필요한 데이터 불러오기

step 2. 검증하

 

 

postgresql과 태블로 연결

sql 추출 후 연결

 

- 일별 주문 건수 

ORDERS만 있어도 된다!

select order_date
	 , count(order_id)	  
from orders
group by 1
order by 1;

 

 

 

 

 

- 일별 구매자 수 변화

select order_date
	 , count(distinct order_id) cnt
from orders
group by 1
order by 1;

 

- 일별 매출액, 구매 건수, 구매자 수

매출액은? orders + order_details

구매 건수는? orders

구매자 수는? orders

select o.order_date
	 , sum(od.unit_price * od.quantity * (1-od.discount)) as amount
	 , count(distinct o.order_id)
	 , count(distinct o.customer_id) 
from order_details od
	 left join orders o ON od.order_id = o.order_id
group by 1 
order by 1

 

 

# PostgreSQL/DBeaver에 태블로 연결하는법

1. 파일 다운로드

2. C:\Program Files\Tableau\Drivers 에 압축 해제하지 않고 그대로 넣기

3. 태블로 재부팅

4. 첫 번째 이미지처럼 dbeaver 열어서 edit connection

5. 태블로 입력창에 나오는 정보들 2번째 이미지(edit connection에 나오는 정보들) 참고해서 입력하면 됩니다 (패스워드는 본인이 설정한거)

 

DB를 바로 올려서 바로 태블로에서 분석

CSV로 EXPORT 후 태블로에 넣고 분석

등등의 방법이 존재, 아직 뭐가 효율적인 방법인지 찾지 못했다!

 

SQL-태블로 각각의 장점을 명확히 한 후 구분지어 효율적인 방안을 마련해야 한다

 

4. 개인 과제

 

쿼리

----과제
-- 월별 매출액 + 주문 건수 + 구매자 수
select to_char( o.order_date, 'YYYY-mm')as month
	 , round(sum(od.unit_price * od.quantity * (1-od.discount))) as amount
	 , count(distinct o.order_id) as order_cnt
	 , count(distinct o.customer_id) as customer_cnt
from order_details od
	 left join orders o ON od.order_id = o.order_id
group by 1 
order by 1

-- 월별+인당 평균 매출액 건당 평균 구매 금액
select to_char(o.order_date, 'YYYY-mm') as month
	 , round(SUM(od.unit_price * od.quantity * (1-od.discount)) / count(distinct o.customer_id)) as c_sales
	 , round(SUM(od.unit_price * od.quantity * (1-od.discount)) / count(distinct o.order_id)) as o_sales
from order_details od
	 left join orders o ON od.order_id = o.order_id
group by 1 
order by 1

-- 분기별 매출액, 주문건수, 구매자 수 추출
select to_char(order_date , 'YYYY-q')
	 , round(sum(od.unit_price * od.quantity * (1-od.discount))) as amount
	 , count(distinct o.order_id) cnt_or
	 , count(distinct o.customer_id) cnt_cu
from order_details od
	 left join orders o ON od.order_id = o.order_id
group by 1 
order by 1

-- 분기별+인당 평균 매출액 건당 평균 구매 금액
select to_char(o.order_date, 'YYYY-q')
	 , round(SUM(od.unit_price * od.quantity * (1-od.discount)) / count(distinct o.customer_id)) as c_sales 
	 , round(SUM(od.unit_price * od.quantity * (1-od.discount)) / count(distinct o.order_id)) as o_sales
from order_details od
	 left join orders o ON od.order_id = o.order_id
group by 1 
order by 1

---

💪🏻 좋았던 점, 앞으로 개선해야 할 점 (추가로 배워야 할 점)

 

📌 이전에 학습했었던 SQL 기초 복습 후 시간대에 따른 주요 지표 확인하는 기법을 학습했다!

과제는 SQL을 통해 데이터를 추출하고 태블로로 시각화를 수행했다.

과제 수행 중 SQL에서 굳이 전처리 하지 않아도 태블로에서 충분히 수행할 수 있는 작업들이 존재한다고 느꼈으며,

이 부분에 대해 SQL을 어느 정도로 활용해야 할지 감을 잡지 못한 상황이다.

더 많은 경험을 통해 각 분석 방안들의 장/단을 확실히 한 후 상황별 사용 방안을 확립하는 게 좋을 거 같다

 

 

#유데미, #유데미코리아, #유데미부트캠프, #취업부트캠프, #부트캠프후기, #스타터스부트캠프, #데이터시각화 #데이터분석 #태블로