SQL, 날짜 추출해서 월별 데이터 구하기

테이블 컬럼에 날짜 값이 일별(yyyy-mm-dd) 형태로 들어가 있더라도, 월별(yyyy-mm)로 데이터를 뽑아낼 수 있습니다. substr(), as, group by 등을 함께 사용하면 됩니다. 날짜 데이터를 substr()를 통해 원하는 형태로 추출하고 그 추출한 값을 기준으로 데이터를 조회하는 방식을 쓰면 됩니다.

 

이런 방식을 쓰면 날짜 컬럼에 2023-01-15, 2022-01-01 … 처럼 저장되어 있어도 2023년 1월 판매량, 2022년 1월 판매량 … 같이 분류하여 쿼리 할 수 있습니다.

날짜별 데이터 구하기

예를 들어, 아래와 같은 orders 테이블이 있습니다. 주문 날짜와 함께 주문 수량이 저장되어 있습니다.

 

 

1) 일별 판매 수량 구하기

이때 단순하게 날짜별 판매 수량의 합계를 구하는 건 고민할 것이 없습니다. orderdate를 기준으로 묶어서 조회하면 됩니다.

select orderdate, sum(quantity) from orders
group by orderdate;

 

 

2) 월별 판매 수량 구하기

그러나 컬럼에 저장된 형태인 일별 데이터가 아니라 월별로 데이터를 조회할 때는 orderdate 컬럼의 값을 substr 함수로 가공하는 작업을 거쳐야 합니다.

 

아래의 쿼리는 orderdate의 값을 1번째부터 7번째까지(yyyy-mm) 잘라서 조회하고 여기에 MONTH라는 별칭을 붙입니다.

SELECT SUBSTR(orderdate, 1, 7) AS MONTH FROM orders;

 

 

위 쿼리를 사용할 수 있다면 이제 가공한 MONTH 컬럼을 기준으로 데이터를 조회하면 됩니다.

 

orders 테이블에서 월별 판매합계를 구하는 쿼리는 아래와 같습니다. 과거 데이터부터 정렬되도록 내림차순으로 정렬했습니다.

SELECT SUBSTR(orderdate, 1, 7) AS MONTH, sum(quantity) AS sales
FROM orders
GROUP BY MONTH
ORDER BY MONTH desc;

 

3) 연도별 판매 수량 구하기

연도별 판매량도 마찬가지 방법으로 구하시면 됩니다. 이번엔 orderdate 컬럼의 1번째부터 4번째까지를 잘라서 추출합니다. 별칭은 YEAR 등으로 변경하면 될 거 같습니다.

SELECT SUBSTR(orderdate, 1, 4) AS YEAR, sum(quantity) AS sales
FROM orders
GROUP BY YEAR
ORDER BY YEAR desc;