SQL 실습 ① 거래액 데이터 분석

※ PostgreSQL 프로그램과 Dbeaver 툴로 연습한 기록

 

→ 기본적인 SQL 구문은 일단 패스, 실행하면서 익혀보자!

 

2017년부터 2021년 3월까지의 전자상거래 추정거래액 (단위 : 백만 원)

데이터 출처 : https://kosis.kr/ (온라인 쇼핑몰 거래액)

 

1) 데이터 탐색

- Dbeaver tool로 PostgreSQL과 연결하여 작업하였다.

- 국가 통계포털 Kosis의 온라인 쇼핑몰거래액 테이블 이름 gmv_trend 

 

 

 

 

 

 

 

STEP 1) 모든 컬럼 추출하기

select *
from gmv_trend

 

 *  는 '모든 컬럼'을 의미한다.

 

 gmv_trend 테이블의 모든 컬럼을 보여주라는 코드다!

 

 

 

 

 

 

 

STEP 2) 특정 컬럼 추출하기

select category, yyyy, mm
from gmv_trend

→ gmv_trend 테이블의 컬럼 category, yyyy, mm 추출하기

 

 

STEP 3) 중복값 없이 특정 컬럼 추출하기

select distinct category
from gmv_trend

→ 컬럼명 앞에 'distinct' 를 붙이면, 같은 이름(=같은 값)을 배제한다. (중복배제)

 

 

 

2) 특정 연도의 매출 탐색

2-1) 조건이 하나일 때 More Example

a) 숫자열 (between, 대소비교 =, !=, >, <, >=, <=, <>)

select *
from gmv_trend
where yyyy = 2021

 

→ 'where' 은 조건문!!

where yyyy >= 2019   → 2019년도 이상의 데이터 추출

where yyyy != 2021  → 2021년이 아닌 년도의 데이터 추출 

where yyyy <> 2021  → '!=' 연산과 같다. '같지않음'을 뜻하는 연산자

 

select *
from gmv_trend
where yyyy between 2018 and 2020

between 2018 and 2020 → 2018년 이상 2020년 이하의 데이터 추출하라는 의미

 

 

b) 문자열 (like, in, not in)

select *
from gmv_trend
where category in ('컴퓨터 및 주변기기', '생활용품')

where category in ('컴퓨터 및 주변기기', '생활용품')

→ category 컬럼의 값이 '컴퓨터 및 주변기기', '생활용품' 인 것들을 추출

 

where category not in ('컴퓨터 및 주변기기', '생활용품')

→ category 컬럼의 값이 '컴퓨터 및 주변기기', '생활용품' 인 것이 아닌 것들을 추출

 

where category like '%패션%'

→ category 컬럼의 값에 '패션'이라는 글자가 들어가는 값들을 추출

★ '%'기호는 어떠한 글자를 의미하는데, '~~~패션' = '%패션' 과 같고, '패션~~~' = '패션%' 과 같이 표현할 수 있다.

 

 

2-2) 조건이 여러개일 때

a) and 조건

select *
from gmv_trend
where category = '컴퓨터 및 주변기기'
and yyyy = 2020

 

b) or 조건

select *
from gmv_trend
where gmv > 1000000 or gmv < 50000

 

c) and, or 조건 혼용 --> ( )로 확실히 분리하자! 

select *
from gmv_trend
where gmv > 1000000 or gmv < 50000 and yyyy = 2021 -- X

→ 의미가 달라질 수 있기 때문이다. 되도록이면 괄호를 이용하여 확실하게 구분하자.

select *
from gmv_trend
where (gmv > 1000000 or gmv < 50000) and yyyy = 2021 -- O

 

 

3) 카테고리별 매출 분석

More Example) 카테고리별, 연도별 매출

select category, yyyy, sum(gmv)
from gmv_trend
group by category, yyyy

→ Select 구문에 집계함수 'sum'을 사용할 수 있다.  ex) sum(컬럼명)

→ 집계함수는 거의 통상적으로 셀렉트 구문의 맨 뒤에 쓴다!

 

★ Select 구문에 집계함수를 사용하면, Group by를 해주어야 한다. (집계함수를 사용한 컬럼 빼고)

→  '그룹'으로 묶어서 통계를 내는 것이라 생각!!

 

→ sum 집계함수를 사용함에 있어서 추출한 데이터 테이블의 컬럼명이 'sum'으로 변한 것을 볼 수 있다.

★ 추출한 데이터 테이블의 컬럼명을 바꿔주는 방법

ex)  sum(gmv) as total_gmv   ex) sum(gmv) total_gmv

ex) sum(gmv) "total_gmv"

 

 

 

 

 

 

select category, yyyy, sum(gmv)
from gmv_trend
group by 1, 2

→ 'group by' 구문에 컬럼의 순서를 적어주어도 된다. (컬럼명이 조잡하거나, 중간에 수정을 할 때 간단하다)

 

 

More Example) 전체 총합

select sum(gmv) as gmv, min(yyyy), max(yyyy), avg(gmv)
from gmv_trend

→ 이런 경우(전체 총합을 구할 때)는 group by를 쓰지 않는다. (어차피 집계된 데이터테이블의 행은 하나다.)

 

cf) 집계함수의 종류

sum / min / max / avg      → 이정도만 알아도 실무에 거의 지장이 없다!

(합 / 최솟값 / 최댓값 / 평균값)

 

 

More Example)  groupby + where 절 예시

select category, yyyy, sum(gmv) as gmv
from gmv_trend
where category = '컴퓨터 및 주변기기'
group by 1, 2

★ from -> where -> group by 순으로 와야한다! 

 

 

4)매출이 높은 주요 카테고리만 확인하기

select category, sum(gmv) as gmv
from gmv_trend
group by 1
having sum(gmv) >= 50000000

→ having절 : "집계 후" 데이터를 필터링

 

 

--More Example) where절이랑 같이 쓰기

select category, sum(gmv) as gmv
from gmv_trend
where yyyy = 2020 --where절: "집계 전"데이터를 필터링
group by 1
having sum(gmv) >= 10000000
-- 따라서, where절에는 집계함수가 올 수 없고, having절에는 집계함수만 올 수 있다.

→ where절 : "집계 후" 데이터를 필터링

★ 따라서, where절에는 집계함수가 올 수 없고, having절에는 집계함수만 올 수 있다!

 

 

5) 매출이 높은 순으로 카테고리 정렬하기 (order by)

select category, sum(gmv) as gmv
from gmv_trend
group by 1
order by gmv desc

→ asc : 오름차순, desc : 내림차순

★ order by 에 쓰이는 컬럼은 select 절에 있는 컬럼만 가능하다

 

 

복합 Example

select category, yyyy, sum(gmv)
from gmv_trend
group by 1,2
order by 1 asc, 3 desc

→ category 명은 오름차순, sum(gmv)는 내림차순으로 추출

 

--[추가 예제 1] 복수의 컬럼으로 정렬

select yyyy, mm, sum(gmv) as gmv
from gmv_trend
group by 1,2
order by 3 desc