[TIL] 46일차 TIL(20230411) - SQL 쿼리문 기초, 구매지표 추출

 

 

[TIL] 46일차 TIL(20230411) - SQL 쿼리문 기초, 구매지표 추출

 

 

 

📗 SQL 쿼리문 기초

 

✅ 쿼리문 구조

SELECT [ALL┃DISTINCT] 컬럼이름[,] | *
FROM [dbname.]테이블이름[,]
[WHERE 검색조건(들)]
[GROUP BY 속성이름]
[HAVING 검색조건(들)]
[ORDER BY 속성이름 [ASC┃DESC]]

 

✅ 기초 쿼리문 작성 예제

 

1. customers 테이블의 country 고유값 개수를 country_cnt로 추출

SELECT count(DISTINCT country) country_cnt FROM customers;

SELECT count(DISTINCT(country)) country_cnt FROM customers;

 

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

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

 

 3. customers 테이블의 company_name 컬럼을 오름차순 정렬하여 상위 3개만 추출

SELECT company_name FROM customers
ORDER BY 1
LIMIT 3;

 

4. order_details의 주문금액 계산하여 tot이라는 컬럼으로 추가

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

 

✅ 문자열 다루기 쿼리문 작성 예제

 

1. concat함수를 사용하여 customers 테이블에서 하나의 문자열 주소로 만들고, 공백으로 각 컬럼을 구분

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

SELECT concat_ws(' ', address, city, region, postal_code, country) full_adress
FROM customers;

 * concat_ws('구분자', 붙일 컬럼명)

 

2. null값을 대체하고 || 사용하여 하나의 문자열 주소로 만들기

SELECT COALESCE(address,'') || ' ' || COALESCE(city,'') || ' ' || COALESCE(region,'') || ' ' || COALESCE(postal_code,'') || ' ' || COALESCE(country,'') full_adress
FROM customers;

 

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

 

-- category_name이 Beverages라면 null을 반환

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

 

 

✅ 날짜/시간형 데이터 쿼리

 

1. 현재 날짜, 시간을 나타내는 함수

SELECT now(); -- +0900 표준시간보다 9시간 빠르다는 것을 의미(대한민국 시간)
SELECT localtimestamp;
SELECT current_time;
SELECT current_date;
SELECT localtime;

 

2. 자료형 변환 함수

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

-- now에서 현재 날짜만 가져오기
SELECT cast(now() AS date);
SELECT now()::date;

-- now에서 현재 시간만 가져오기
SELECT cast(now() AS time);
SELECT now()::time;

 

 3. 날짜/시간 추출 함수

 1) extract('part' from 날짜/시간), date_part('part', 날짜/시간)

- integer 형식으로 출력

SELECT extract('year' from now()), date_part('year', now());
SELECT extract('month' from now()), date_part('month', now());
SELECT extract('day' from now()), date_part('day', now());
SELECT extract('quarter' from now()), date_part('quarter', now());
SELECT extract('hour' from now()), date_part('hour', now());
SELECT extract('dow' from now()), date_part('dow', now()); --DAY OF week: 요일

 

2) date_trunc('part', 날짜/시간)

- timestamp 형식으로 출력 -> 요일 추출 불가

- 해당 part의 첫번째 시간을 출력

SELECT date_trunc('year', now()) year1, date_trunc('month', now()) month1, date_trunc('day', now()) day1,
date_trunc('quarter', now()) quarter1, date_trunc('hour', now()) hour1;

 

3) to_char(날짜/시간, 'part')

- 문자열 형식으로 변환

- 사용자가 서식을 다양하게 지정할 수 있음

SELECT TO_CHAR(now(), 'YYYY'), TO_CHAR(now(), 'MM'), TO_CHAR(now(), 'DD'), TO_CHAR(now(), 'HH'), TO_CHAR(now(), 'HH24'),
TO_CHAR(now(), 'MI'), TO_CHAR(now(), 'SS'), TO_CHAR(now(), 'YYYY-MM'), TO_CHAR(now(), 'MON'), TO_CHAR(now(), 'mon'),
TO_CHAR(now(), 'day'), TO_CHAR(now(), 'DAY');

 

✅ 쿼리 실행 순서

FROM(JOIN) > WHERE > GROUP BY > HAVING > SELECT(집계) > ORDER BY

* SELECT가 나중에 이루어지므로, WHERE, HAVING에서 집계 결과의 별칭을 사용할 수 없다.

 

 

📗 구매지표 추출

 

✅ 일자별 매출액

SELECT o.order_Date, sum(od.unit_price * od.quantity * (1-od.discount)) 일별매출액
FROM ORDERS O, order_details od
WHERE o.order_id = od.order_id
GROUP BY O.order_date
ORDER BY o.order_Date

 

- 새로운 테이블로 저장하기/ 검증하기

-- 새로운 테이블로 만들기
CREATE TABLE Sales_by_date AS (
SELECT o.order_Date, sum(od.unit_price * od.quantity * (1-od.discount)) 일별매출액
FROM ORDERS O, order_details od
WHERE o.order_id = od.order_id
GROUP BY O.order_date
ORDER BY o.order_Date
)

-- 검증하기
SELECT sum(일별매출액) from
(SELECT o.order_Date, sum(od.unit_price * od.quantity * (1-od.discount)) 일별매출액
FROM ORDERS O, order_details od
WHERE o.order_id = od.order_id
GROUP BY O.order_date
ORDER BY o.order_Date) a;

 

✅ 일자별 주문 건수

SELECT order_date, count(order_id) 일별주문건수
FROM orders
GROUP BY order_Date
ORDER BY order_date;

 

✅ 일자별 고객 수

-- 하루에 주문한 고객이 몇 명인지만 계산하려는 것이므로 여러 번 구매했더라도 1명으로 집계
SELECT order_date, count(DISTINCT customer_id) 일별고객수 FROM orders
GROUP BY order_date
ORDER BY order_date;

 

✅ 일자별 매출액, 주문 건수, 고객 수 한번에 구하기

SELECT o.order_Date, sum(od.unit_price * od.quantity * (1-od.discount)) 일별매출액,
count(DISTINCT o.order_id) 일별주문건수, count(DISTINCT o.customer_id) 일별고객수
FROM ORDERS O, order_details od
WHERE o.order_id = od.order_id
GROUP BY O.order_date
ORDER BY o.order_Date;

 

 

📗 과제

- 월별/분기별 매출액, 구매 건수, 고객 수, 인당 평균 매출액, 건당 구매 금액 구하기

- 각 구매지표들 사이의 상관관계 파악 및 시각화

 

-- 월별 인당 평균 매출액, 건당 구매 금액
SELECT to_char(o.order_date, 'YYYY-MM') 주문년월,
	sum(od.unit_price * od.quantity * (1-od.discount))  월별매출액,
	count(DISTINCT o.order_id) 월별주문건수, count(DISTINCT o.customer_id) 월별고객수,
	sum(od.unit_price * od.quantity * (1-od.discount)) / count(DISTINCT o.order_id)  건당구매금액,
	sum(od.unit_price * od.quantity * (1-od.discount)) / count(DISTINCT o.customer_id)  인당평균매출액
FROM orders o, order_details od
WHERE o.order_id = od.order_id
GROUP BY to_char(o.order_date, 'YYYY-MM')
ORDER BY 주문년월;


-- 분기별 인당 평균 매출액, 건당 구매 금액
SELECT to_char(o.order_date, 'YYYY-QUARTER') 주문분기,
	sum(od.unit_price * od.quantity * (1-od.discount))  분기별매출액,
	count(DISTINCT o.order_id) 분기별주문건수, count(DISTINCT o.customer_id) 분기별고객수,
	sum(od.unit_price * od.quantity * (1-od.discount)) / count(DISTINCT o.order_id)  건당구매금액,
	sum(od.unit_price * od.quantity * (1-od.discount)) / count(DISTINCT o.customer_id)  인당평균매출액
FROM orders o, order_details od
WHERE o.order_id = od.order_id
GROUP BY to_char(o.order_date, 'YYYY-QUARTER')
ORDER BY 주문분기;

 

 

 

 

---

 

소감 및 정리

오늘은 이전에 온라인 강의를 통해 다뤘던 sql 쿼리문의 기초적인 부분들에 대해 한번씩 다뤄보고 다시 감을 익혀보는 시간을 가졌다. 대부분 아는 함수들이었지만 MySQL과 PostgreSQL의 함수들 중 일부 다른 것들이 있어서, MySQL에서 하던 방식으로 적어 결과가 나오지 않았던 것들이 있었다. 아마 해커톤을 진행할 때 PostgreSQL을 사용하여 데이터를 추출하게 될 것 같아 여기에 적응할 필요가 있을 것 같다. 또 SQL과 태블로를 연결해봤는데, SQL에서 추출하고 테이블을 생성하면 태블로에서 바로 사용할 수 있어 굉장히 편리했다! 해커톤때도 유용하게 사용할 수 있을 것 같다.