지난 포스팅([SQL] CRUD 기초 개념 정리)에서 SELECT에 대해서 간단히 알아보았는데 이번 포스팅에서는 더 다양한 활용법에 대해서 정리하고자 한다.

💰 거래내역 DB 필터링하기

아래 이미지와 같이 거래내역이 담긴 aggregate-function 이라는 이름의 데이터베이스 내 transaction 테이블이 있다고 하자. 특정 기간의 거래액 총합과 평균, 거래 최대 금액, 최소 금액 등의 데이터가 필요하다면 쿼리를 어떻게 작성해야할까? 데이터를 필터링할 수 있는 함수들을 활용하면 한 줄의 쿼리로 간단하게 확인할 수 있다.

실습 DB 정보

우선 위 예시 DB의 created_at 컬럼은 TIMESTAMPTZ 타입으로 생성되었다.

TIMESTAMPTZ 타입

2022-05-08 19:56:49 +09

TIMESTAMPTZ는 위와 같이 날짜 + 시간 + TZ로 구성되어 있으며 여기서 TZ란 TIME ZONE의 약자로, 영국을 기준으로 한 시간차를 의미한다. 예를 들어 런던이 자정(00시) 일때, 서울은 오전 9시, 뉴욕은 저녁7시 이다. 이 TIMESTAMPTZ를 활용해 아래에서 기간별 거래액 등을 알아볼 것이다. 우선은 집계함수에 대해서 먼저 정리하였다.

집계함수

Functions

• SUM : 총 합
• AVG : 평균
• COUNT : 횟수
• MAX : 최대
• MIN : 최소
• DISTINCT : 중복 제거

가장 대표적인 집계 함수 목록이다.

SELECT function(column) FROM table

모든 집계함수 사용법은 위와 같이 동일하며, SELECT문 바로 옆에 사용할 함수() FROM 테이블명으로 쿼리를 작성한다. () 안에는 총합이나 평균 등을 구할 컬럼의 이름을 적는다.

#SUM() 예시 : 총 합 구하기

집계함수 중 대표적으로 SUM() 함수를 사용해 transaction 테이블의 총 합을 알아보는 예시를 작성했다. 쿼리는 아래와 같다.

SELECT SUM(column) FROM table

SELECT SUM(합계를 구하고자하는 컬럼) FROM 테이블명 으로 작성한 쿼리를 실행하면 아래와 같이 합계가 담긴 SUM 컬럼 생긴 것을 확인할 수 있다.

#AVG() 예시 : 평균 값 구하기

SELECT AVG(amount) FROM transactions

그런데, 위에서 설명한대로 SELECT function(column) FROM table 형식에 맞추어 AVG 함수를 사용했는데 아래와 같이 결과 값이 나왔다. 소숫점이 너무 길어 마음에 들지 않는다. 이럴 때는 어떻게 해야할까?

#ROUND() 예시 : 소수점 반올림

SELECT ROUND(AVG(amount),2) FROM transactions

ROUND() 함수로 소수점을 반올림하고 싶은 데이터를 ()안에 감싸고 ,n 을 넣어 n자리에 소수점 이하 몇번째 자리 이하에서 반올림하고 싶은지 숫자를 넣어주면 된다.

결과값으로 소수점 2번째 자리 이하에서 반올림된 평균 amount를 확인할 수 있다.

#COUNT() 예시 : 횟수 구하기

또 만약 transaction 테이블에서 특별히 google과 거래한 횟수에 대해서만 조회하고 싶다면 WHERE절을 함께 사용해 아래와 같이 활용할 수 있다.

EXTRACT 함수

SELECT * FROM table WHERE EXTRACT(PROPERTY FROM column) = number

Extract 는 발췌, 추출물 이라는 뜻이다. 즉 위에서 설명했던 timestamptz(yyyy-mm-dd hh:mm:ss+tz) 타입에서 내가 원하는 특정 속성(년도, 월 등)을 숫자로 추출할 수 있는 함수이다.

TIMESTAMPTZ 속성

• YEAR : 년도
• MONTH : 월
• DAY : 일
• HOUR : 시
• MINUTE : 분
• SECOND : 초
• TIMEZONE_HOUR : time zone의 시(hour)

#EXTRACT() 예시 : 데이터 추출

SELECT * FROM transactions 
	WHERE 
		EXTRACT(YEAR FROM created_at) = 2021 
		AND 
		EXTRACT(MONTH FROM created_at) = 12

timestamptz 타입으로 생성된 created_at 컬럼에서 YEAR 속성인 년도가 2021인 데이터, 그리고 동시에 MONTH 타입인 월이 12인 데이터를 추출하는 쿼리를 작성했다. 이후 실행한 결과는 아래와 같이 2021년도 12월의 데이터만 추출된 것을 확인할 수 있다.

여기서 더 나아가 2021년 12월의 거래금액 총합을 알고 싶다면 아래와 같이 쿼리를 작성하고 실행할 수 있다.

SELECT SUM(amount) FROM transactions 
	WHERE 
		EXTRACT(YEAR FROM created_at) = 2021 
		AND 
		EXTRACT(MONTH FROM created_at) = 12

TO_CHAR 함수

SELECT * FROM table WHERE TO_CHAR(column, 'form') = 'form';

TO_CHAR() 함수는 특정 값을 다양한 형식의 문자로 바꾸어주는 역할을 한다. 예를 들어서 Timestamptz의 YYYY-MM-DD hh:mm:ss +tz 형식을 'YYYY-MM' 등 다양한 형식의 문자열로 바꾸어 해당 값과 일치하는 데이터만 추출을 할 수도 있는 것이다.

TO_CHAR 형식

• YYYY-MM-DD - 2022-05-08
• YYYY/MM/DD - 2022/05/08
• HH24:MM:SS - 23:59:55
• TZH - time zone 시

#TO_CHAR() 예시 : 문자열 변환

SELECT * FROM transactions WHERE TO_CHAR(created_at, 'YYYY-MM) = '2021-11';

위와 같은 쿼리를 작성 후 실행하면 아래와 같이 2021-11에 해당하는 데이터만 조회되는 것을 확인할 수 있다.

🙏 Reference

• 인프런 홍팍 sql 입문 강의