Today I Learned

SQLD 문제를 풀던 중 누적합을 구하는 문제를 만나 해결방법에 대해 공부해보았다.

누적합 구하기

MySQL 누적합

SUM() OVER()

SUM(컬럼명) OVER(ORDER BY 정렬키)

• 위와 같은 형태로 사용해, 해당 컬럼을 계속 더해간다.

• ORDER BY : 누적합을 계산할 때 어떤 열을 기준으로 정렬할 지 지정한다. 누적합은 정렬 순서에 따라 더해지는 순서가 달라지기 때문에 중요하다.

• PARTITION BY : 선택사항. 결과를 파티션으로 나누는데 사용한다. 파티션별로 누적합이 계산된다.

• ROW BETWEEN : 선택사항. 어떤 행 범위를 기반으로 누적합을 계산할 지 지정한다. 현재행부터 이전 N행까지의 합을 계산할 수 있다.

DATETIME -> DATE 형변환

• CAST(변환하려는 데이터 AS 변환 후 형식)
  CAST(day AS DATE)로 DATETIME 형식의 day컬럼을 DATE형식으로 바꿀 수 있다.

• DATE_FORMAT(컬럼명, 형식)
  DATE_FORMAT(day, '%Y-%m-%d')를 이용해 형식을 지정해서 바꿀 수도 있다. 참고로 %Y는 1999처럼 4자리 연도, %y는 99처럼 2자리 연도, %m은 2자리 숫자 월, %d는 2자리 숫자 일 형식을 의미한다.

• 자세한 형식 표는 Contributor9에서 확인 가능하다.

문제

매출 표가 위와같이 testorder 테이블에서 주어질 때, 10월 매출만 날짜별로 정렬한 뒤 매출이 누적되어 보이게 날짜순으로 정렬해서 가져와라

• 풀이 과정

1. 우선 DATETIME 형식의 day를 DATE로 변환해야 한다.
   DATE_FORMAT(컬럼명, 형식) 함수를 사용해서 변환한다.

2. 해당 컬럼을 날짜별로 그룹화 하고 HAVING 조건으로 10월만 뽑아내야 한다.

3. 해당 쿼리를 서브쿼리로 만들어 FROM 구에 넣고 누적합 테이블을 메인쿼리에서 SELECT로 지정한다.
   누적합은 SUM(컬럼명) OVER()를 사용한다.

• 완성된 쿼리

SELECT d AS 날짜,
	SUM(s) OVER(ORDER BY d) AS 누적매출
FROM (
    SELECT DATE_FORMAT(day, '%Y-%m-%d') AS d, SUM(sales) AS s
	FROM testorder
    GROUP BY d
    HAVING d < '2023-11-01' && d >= '2023-10-01') AS subquery;

• 결과