[스타터스 TIL] 46일차.SQL 실전 트레이닝 (2) - 컬럼 연산자, 날짜/시간형 데이터, 다중 행 함수

웅진X유데미 STARTERS/TIL (Today I Learned)

[스타터스 TIL] 46일차.SQL 실전 트레이닝 (2) - 컬럼 연산자, 날짜/시간형 데이터, 다중 행 함수

라밍쏭 2023. 4. 11. 18:09

728x90

1. 데이터 추출

1.1 컬럼 추출

✔ customer 테이블의 country, city 데이터를 유니크하게 추출하고 country 오름차순, city 내림차순으로 정렬하기

SELECT DISTINCT country, city
FROM customers
ORDER BY country, city DESC;

✔ customer의 company_name 컬럼을 오름차순 정렬하여 상위 3개만 추출

SELECT company_name
FROM customers
ORDER BY company_name 
LIMIT 3;

1.2 컬럼 연산자

1) 산술 연산자

order_detail의 주문금액 계산하여 tot라는 컬럼으로 추가하기

SELECT *
	 , unit_price * quantity * (1-discount) AS tot
FROM order_details;

2) 합성 연산자 (concat 함수)

- customer에서 주소를 하나의 문자열로 만들기
- address, city, region, postal_code, country 합성
- 공백 (' ')으로 각 컬럼 구분

SELECT concat(address, ' ', city, ' ', region, ' ', postal_code, ' ', country) AS full_address
FROM customers;

3) 합성 연산자 (||)

- customer에서 주소를 하나의 문자열로 만들기
- address, city, region, postal_code, country 합성
- 공백 (' ')으로 각 컬럼 구분

SELECT COALESCE(address, '')||' '||COALESCE(city, '') ||' '|| coalesce(region, '') ||' '|| COALESCE(postal_code, '') ||' '|| COALESCE(country, '') AS full_address
FROM customers;

4) COALESCE 함수
인자로 주어진 컬럼들 중 null이 아닌 첫번째 값을 반환하는 함수

SELECT COALESCE(homepage, '')
	 , COALESCE(fax, '')
	 , COALESCE(phone, '')
FROM suppliers;

5) NULLIF 함수

단일행 함수 > NULL 관련 함수 > 특정 값을 NULL 처리하기

SELECT *
     , NULLIF(category_name, 'Beverages')
FROM categories;

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

1) timestamp

SELECT now();  -- 2023-04-11 10:19:44.084 +0900

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

SELECT current_timestamp;  -- 2023-04-11 10:19:44.084 +0900
SELECT localtimestamp ;  -- 2023-04-11 10:19:39.561
SELECT current_date;  -- 2023-04-11
SELECT current_time;  -- 10:19:28 +0900
SELECT localtime; -- 10:19:19

[+] 자료형 변환

- cast(변환대상 as 자료형)
- 변환대상::자료형

3) now에서 현재 날짜만 가져오기

SELECT cast(now() AS date);  -- 2023-04-11
SELECT now()::date;  -- 2023-04-11

4) now에서 현재 시간만 가져오기

SELECT cast(now() AS time);  -- 10:22:58
SELECT now()::time;  -- 10:22:58

5) EXTRACT('part' FROM 날짜/시간)

→ integer 형으로 출력

SELECT EXTRACT('year' FROM now());  -- 2023
SELECT EXTRACT('month' FROM now()); -- 4
SELECT EXTRACT('day' FROM now());   -- 11
SELECT EXTRACT('quarter' FROM now()); -- 2
SELECT EXTRACT('hour' FROM now()); -- 10
SELECT EXTRACT('minute' FROM now()); -- 27
SELECT EXTRACT('second' FROM now()); -- 7.667219
SELECT EXTRACT('dow' FROM now());  -- 2 (일요일 : 0 ~ 토요일 : 6)

6) DATE_PART('part', 날짜/시간)

→ integer 형으로 출력

SELECT date_part('year', now());  -- 2023
SELECT date_part('month', now()); -- 4
SELECT date_part('day' , now());   -- 11
SELECT date_part('quarter', now()); -- 2
SELECT date_part('hour', now()); -- 10
SELECT date_part('minute', now()); -- 27
SELECT date_part('second', now()); -- 7.667219
SELECT date_part('dow', now());  -- 2 (일요일 : 0 ~ 토요일 : 6)

7) DATE_TRUNC('part', 날짜/시간)

→ timestamp 형으로 출력
→ 요일은 추출할 수 없음

SELECT date_trunc ('year', now());  -- 2023-01-01 00:00:00.000 +0900
SELECT date_trunc('month', now()); -- 2023-04-01 00:00:00.000 +0900
SELECT date_trunc('day' , now());   -- 2023-04-11 00:00:00.000 +0900
SELECT date_trunc('quarter', now()); -- 2023-04-01 00:00:00.000 +0900
SELECT date_trunc('hour', now()); -- 2023-04-11 10:00:00.000 +0900
SELECT date_trunc('minute', now()); -- 2023-04-11 10:30:00.000 +0900
SELECT date_trunc('second', now()); -- 2023-04-11 10:30:26.000 +0900

8) TO_CHAR(날짜/시간, 'part')

→ 문자형으로 출력

SELECT to_char(now(), 'YYYY'); -- 2023
SELECT to_char(now(), 'MM'); -- 04
SELECT to_char(now(), 'DD'); -- 11
SELECT to_char(now(), 'HH'); -- 10
SELECT to_char(now(), 'HH24'); -- 10
SELECT to_char(now(), 'MI'); -- 33
SELECT to_char(now(), 'SS'); -- 50
SELECT to_char(now(), 'YYYY-MM'); -- 2023-04
SELECT to_char(now(), 'YYYYMM');  -- 202304
SELECT to_char(now(), 'MMDD');    -- 0411
SELECT to_char(now(), 'HH24:MI:SS'); -- 10:34:11

SELECT to_char(now(), 'mon'); -- apr
SELECT to_char(now(), 'MON'); -- APR
SELECT to_char(now(), 'day'); -- tuesday  
SELECT to_char(now(), 'DAY'); -- TUESDAY  
SELECT to_char(now(), 'quarter'); -- 2uarter
SELECT to_char(now(), 'QUARTER'); -- 2UARTER

9) 예제 - 2023년 4월 9일은 무슨 요일일까?

SELECT EXTRACT('dow' from '2023-04-09'::date); -- 0
SELECT date_part('dow', '2023-04-09'::date);  -- 0
SELECT to_char('2023-04-09'::date, 'day'); -- sunday

10) 예제 - orders 테이블에서 order_date의 '연도-월' 출력

SELECT order_date
	 , to_char(order_date, 'YYYY-MM') AS year_month
FROM orders;

11) 예제 - orders 테이블에서 order_date의 '연도-분기' 출력

SELECT order_date
	 , to_char(order_date, 'YYYY-QUARTER') AS year_quarter
FROM orders;

12) 예제 - 다른 형식으로 날짜 출력

SELECT order_date
	 , to_char(order_date, 'YYYY년 mm월 dd일') AS order_date2
FROM orders;

1.4 다중 행 함수

- 다중 행을 인자로 받아들여 한 개의 값으로 결과를 반환하는 함수
- 집계함수 (전체에 레코드에 대한 집계, 소그룹에 대한 집계)

1) GROUP BY, HAVING 절

✔ 전체 고객 수

SELECT count(*) AS customer_cnt
FROM customers;

✔ 유니크한 국가 수

SELECT count(DISTINCT country) AS country_cnt
FROM customers;

✔ 국가 별 고객 수

SELECT country
	 , count(customer_id) AS customer_cnt
FROM customers
GROUP BY country
ORDER BY customer_cnt desc;

✔ USA 고객 수

- where절 사용

SELECT country
	 , count(customer_id) AS customer_cnt
FROM customers
WHERE country = 'USA'
GROUP BY country;

- having절 사용

SELECT country
	 , count(customer_id) AS customer_cnt
FROM customers
GROUP BY country
HAVING country = 'USA';

✔ 고객 수가 10 이상인 국가

SELECT country
	 , count(customer_id) AS customer_cnt
FROM customers
GROUP BY country
HAVING count(customer_id) >= 10;

✅ 쿼리의 실행 순서

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT

1.5 구매지표 추출

- 매출액 (일자별, 월별, 분기별)
- 구매자수, 구매건수 (일자별, 월별, 분기별)

- 인당 매출액 (월별, 분기별)
- 건당 구매금액 (월별, 분기별)

1) 총 매출액, 총 주문건수, 총 상세주문건수, 총 주문수량

전체적인 집계를 통해 이후 분석 시 올바르게 쿼리를 짰는지 확인하는데에 도움이 됨

-- 총 매출액 (1265793.038653364)
SELECT sum(unit_price * quantity * (1-discount)) AS total_sales
FROM order_details;

-- 총 주문건수 (830)
SELECT count(order_id) AS order_cnt
FROM orders;

-- 총 상세주문건수 (2155)
SELECT count(order_id) AS order_cnt
FROM order_details;

-- 총 주문수량 (51317)
SELECT sum(quantity)
FROM order_details;

2) 일별 구매지표 추출

일별 매출액, 주문건수, 구매자수의 변화를 시각화하기

✔ 일별 매출액

① 데이터 추출

SELECT o.order_date
	 , sum(od.unit_price * od.quantity * (1-od.discount)) AS total_sales
FROM orders o
	INNER JOIN order_details od
		ON o.order_id = od.order_id
GROUP BY o.order_date
ORDER BY o.order_date;

② 검증하기

-- 검증하기 (서브쿼리 활용) → 1265793.038653364

SELECT sum(total_sales)
FROM (
	SELECT o.order_date
	 , sum(od.unit_price * od.quantity * (1-od.discount)) AS total_sales
	FROM orders o
		INNER JOIN order_details od
			ON o.order_id = od.order_id
	GROUP BY o.order_date
	ORDER BY o.order_date
) AS a;

→ 1)에서 확인한 총 매출액과 값이 같음을 확인할 수 있다.

③ 시각화하기 (Tableau 활용)

[+] 1997년 일별 매출액

SELECT o.order_date
	 , sum(od.unit_price * od.quantity * (1-od.discount)) AS total_sales
FROM orders o
	INNER JOIN order_details od
		ON o.order_id = od.order_id
WHERE to_char(order_date, 'YYYY') = '1997'
GROUP BY o.order_date
ORDER BY o.order_date;

✔ 일별 주문건수

① 데이터 추출

SELECT order_date
	 , count(order_id)
FROM orders
GROUP BY order_date
ORDER BY order_date;

② 검증하기

-- 일별 주문건수 검증하기 (830)
SELECT sum(order_cnt)
FROM (
	SELECT order_date
		 , count(order_id) AS order_cnt
	FROM orders
	GROUP BY order_date
	ORDER BY order_date
)AS b;

③ 시각화하기 (Tableau 활용)

✔ 일별 구매자수

① 데이터 추출

SELECT order_date
	 , count(DISTINCT customer_id) AS cust_cnt
FROM orders
GROUP BY order_date
ORDER BY order_date;

② 검증하기

-- 일별 구매자수 검증하기 (823)
SELECT sum(cust_cnt)
FROM (
	SELECT order_date
		 , count(DISTINCT customer_id) AS cust_cnt
	FROM orders
	GROUP BY order_date
	ORDER BY order_date
) AS c;

③ 시각화하기 (Tableau 활용)

✅ 하나의 쿼리로 일별 매출액, 구매건수, 구매자수 출력

① 데이터 추출

SELECT o.order_date
     , sum(od.unit_price * od.quantity * (1-od.discount)) AS total_sales
     , count(DISTINCT o.order_id) AS order_cnt
     , count(DISTINCT o.customer_id) AS cust_cnt
FROM orders o 
    INNER JOIN order_details od 
        ON o.order_id = od.order_id
GROUP BY o.order_date
ORDER BY o.order_date;

② 검증하기

SELECT sum(total_sales)
	 , sum(order_cnt)
	 , sum(cust_cnt)
FROM (
	SELECT o.order_date
		 , sum(od.unit_price * od.quantity * (1-od.discount)) AS total_sales
		 , count(DISTINCT o.order_id) AS order_cnt
		 , count(DISTINCT o.customer_id) AS cust_cnt
	FROM orders o 
		INNER JOIN order_details od 
			ON	o.order_id = od.order_id
	GROUP BY o.order_date
	ORDER BY o.order_date
)AS d;

③ 시각화 (Tableau 활용)

2. 개인 과제

northwind 데이터 셋을 활용하여 다음과 같은 데이터를 추출하고 시각화, 추가로 상관관계 분석까지 진행하는 과제가 주어졌다.

SQL을 활용하여 데이터를 추출하였으며, Tableau를 활용하여 시각화를 하였다.

2.1 월 별 매출액 분석

1) 월 별 매출액, 주문건수, 구매자 수 추출

SELECT to_char(o.order_date, 'YYYY-MM') AS order_month
	 , round(sum(od.unit_price * od.quantity * (1-od.discount))::NUMERIC, 2) AS total_sales
	 , count(DISTINCT o.order_id) AS order_cnt
	 , count(DISTINCT o.customer_id) AS customer_cnt
FROM orders o 
	INNER JOIN order_details od 
		ON o.order_id = od.order_id 
GROUP BY to_char(o.order_date, 'YYYY-MM')
ORDER BY to_char(o.order_date, 'YYYY-MM');

2) 월 별 인당 평균 매출액, 건당 평균 구매 금액 추출

SELECT to_char(o.order_date, 'YYYY-MM') AS order_month
	 , round(sum(od.unit_price * od.quantity * (1-od.discount))::NUMERIC, 2) AS avg_sales
	 , round((sum(od.unit_price * od.quantity * (1-od.discount)) / count(DISTINCT o.customer_id))::NUMERIC, 2) AS avg_sales_per_cust
	 , round((sum(od.unit_price * od.quantity * (1-od.discount)) / count(DISTINCT o.order_id))::NUMERIC, 2) AS avg_sales_per_unit
FROM orders o 
	INNER JOIN order_details od 
		ON o.order_id = od.order_id 
GROUP BY to_char(o.order_date, 'YYYY-MM')
ORDER BY to_char(o.order_date, 'YYYY-MM');

3) 월 별 평균 매출액 - 주문수, 고객수 상관관계

SELECT corr(total_sales, order_cnt) AS sales_order_corr
	 , corr(total_sales, customer_cnt) AS sales_cust_corr
	 , corr(order_cnt, customer_cnt) AS order_cust_corr
FROM month_table;

2.2 분기 별 매출액 분석

1) 분기 별 매출액, 주문건수, 구매자 수 추출

SELECT to_char(o.order_date, 'YYYY-QUARTER') AS order_quarter
	 , round(sum(od.unit_price * od.quantity * (1-od.discount))::NUMERIC, 2) AS total_sales
	 , count(DISTINCT o.order_id) AS order_cnt
	 , count(DISTINCT o.customer_id) AS customer_cnt
FROM orders o 
	INNER JOIN order_details od 
		ON o.order_id = od.order_id 
GROUP BY to_char(o.order_date, 'YYYY-QUARTER')
ORDER BY to_char(o.order_date, 'YYYY-QUARTER');

2) 분기 별 인당 평균 매출액, 건당 평균 구매 금액 추출

SELECT to_char(o.order_date, 'YYYY-QUARTER') AS order_month
	 , round(sum(od.unit_price * od.quantity * (1-od.discount))::NUMERIC, 2) AS avg_sales
	 , round((sum(od.unit_price * od.quantity * (1-od.discount)) / count(DISTINCT o.customer_id))::NUMERIC, 2) AS avg_sales_per_cust
	 , round((sum(od.unit_price * od.quantity * (1-od.discount)) / count(DISTINCT o.order_id))::NUMERIC, 2) AS avg_sales_per_unit
FROM orders o 
	INNER JOIN order_details od 
		ON o.order_id = od.order_id 
GROUP BY to_char(o.order_date, 'YYYY-QUARTER')
ORDER BY to_char(o.order_date, 'YYYY-QUARTER');

3) 분기 별 평균 매출액 - 주문수, 고객수 상관관계

SELECT corr(total_sales, order_cnt) AS sales_order_corr
	 , corr(total_sales, customer_cnt) AS sales_cust_corr
	 , corr(order_cnt, customer_cnt) AS order_cust_corr
FROM quarter_table;

2.3 시각화 및 정리

💡 회고

오늘부터 본격적인 SQL 쿼리 짜기를 했다.

오랜만에 SQL을 해서 그런가 나름 재밌었다. 태블로는 나에겐 너무 어려운 존재라 할 때마다 몰라서 헤맸는데, 다행히 SQL은 어느정도 알고 있어서 오늘은 편하게 했다.

오늘 과제도 쿼리 짜는 것은 수월하게 했는데 이를 시각화하는데에 많은 노력을 투자했다...!

이 데이터를 어떤 식으로 시각화하는 것이 좋을지, 시각화를 통해 어떤 것을 말해주어야 할지에 대한 고민이 많았다.

그래도 이렇게 성장하는 거지 않을까😎

오늘 아주 우중충한 날이었는데, 잘 버티고, 과제를 잘 마무리한 나 자신에게 박수,,👏👏

728x90

'웅진X유데미 STARTERS > TIL (Today I Learned)' 카테고리의 다른 글

[스타터스 TIL] 48일차.SQL 실전 트레이닝 (4) - 제품/카테고리 매출 지표 분석, ABC 분석 (0)	2023.04.13
[스타터스 TIL] 47일차.SQL 실전 트레이닝 (3) - CTE, 윈도우 함수 (0)	2023.04.13
[스타터스 TIL] 45일차.SQL 실전 트레이닝 (1) - 데이터 분석, 테이블파악 (0)	2023.04.10
[스타터스 TIL] 44일차.태블로 실전 트레이닝 (20) - 태블로 자격증 공부 (Test 1, Test 2) (1)	2023.04.08
[스타터스 TIL] 43일차.태블로 실전 트레이닝 (19) - 태블로 자격증 공부 (Test 4, Test 5) (0)	2023.04.07

현재글[스타터스 TIL] 46일차.SQL 실전 트레이닝 (2) - 컬럼 연산자, 날짜/시간형 데이터, 다중 행 함수

ARAMDAUN

Cos Pro 2급, 코딩테스트, 파이썬 코딩테스트, Python, 취업부트캠프, 유데미, 데이터드리븐, MySQL, 태블로, 유데미코리아, COS PRO 파이썬, 부트캠프후기, 데이터시각화, 파이썬, SQL, 데이터분석, 백준, 스타터스부트캠프, 유데미큐레이션, 유데미부트캠프,

Today :
Yesterday :

« 2025/03 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31