[BE] SQL 데이터 통계 조회

SQL(Structured Query Language)이란?

SQL은 'Structured Query Language'의 약자로, 데이터베이스에서 데이터를 추출하고 조작하는 데에 사용하는 데이터 처리 언어이다. 쉽게 말해 데이터베이스에 저장된 정보를 쉽게 찾고 정리하는 데에 도움을 주는 도구이다.

기본적인 SQL 문법

1. SELECT
: 조회에 사용하는 기본 문법

//조회
SELECT * FROM 테이블;
SELECT * FROM tablea;

//조건 조회
SELECT * FROM 테이블 WHERE 조건;
SELECT * FROM tablea WHERE id = "testid";

//조회 데이터 정렬(오름차순)
SELECT * FROM 테이블 WHERE 조건 ORDER BY 컬럼명 ASC;
SELECT * FROM tablea ORDER BY createdAt ASC;

//조회 데이터 정렬(내림차순)
SELECT * FROM 테이블 WHERE 조건 ORDER BY 컬럼명 DESC;
SELECT * FROM tablea ORDER BY createdAt DESC;
SELECT * FROM tablea ORDER BY createdAt DESC LIMIT 1000;

통계에 유용했던 SQL 문법

1. GROUP BY
GROUP BY 절은 특정 칼럼을 기준으로 집계 함수를 사용하여 건수(COUNT), 합계(SUM), 평균(AVG) 등 집 계성 데이터를 추출할 때 사용한다. 

SELECT SUM(VALUE) FROM 테이블 이름 GROUP BY 컬럼명
SELECT SUM(VALUE) AS sumdata FROM testa GROUP BY testid

- 자주 사용하는 집계 함수 (SUM, COUNT, AVG)

SELECT SUM(VALUE) AS sumData, COUNT(*), AVG(VALUE) AS avgData FROM 테이블이름 GROUP BY 컬럼명

- 일간, 주간, 월간 통계

// 일간통계
SELECT DATE(`날짜컬럼`) AS `date`,
       sum(`value`)
FROM test_table
GROUP BY `date`;

// 주간통계
SELECT DATE_FORMAT(DATE_SUB(`날짜컬럼`, INTERVAL (DAYOFWEEK(`날짜컬럼`)-1) DAY), '%Y/%m/%d') as start,
       DATE_FORMAT(DATE_SUB(`날짜컬럼`, INTERVAL (DAYOFWEEK(`날짜컬럼`)-7) DAY), '%Y/%m/%d') as end,
       DATE_FORMAT(`날짜컬럼`, '%Y%U') AS `week`,
       sum(value) AS value 
FROM test_table
GROUP BY week;

// 월간통계
SELECT MONTH(`날짜컬럼`) AS `month`,
       sum(`value`)
FROM test_table
GROUP BY `month`;

INTERVAL (DAYOFWEEK(날짜컬럼)-1) DAY는 MySQL에서 사용되는 표현식으로, 날짜(regist_date)에서 해당 주의 첫 번째 날이 아닌 날짜의 간격을 계산한다.

• DAYOFWEEK( 날짜컬럼 ): 이 함수는 주어진 날짜가 무슨 요일인지를 반환한다. 일요일부터 시작하여 1부터 7까지의 값을 반환한다. 예를 들어, 일요일은 1, 월요일은 2, ..., 토요일은 7
• (DAYOFWEEK( 날짜컬럼 ) - 1): 이 부분은 위에서 얻은 요일 값에서 1을 뺀 값이다. 이렇게 하면 일요일은 0, 월요일은 1, ..., 토요일은 6
• INTERVAL (DAYOFWEEK( 날짜컬럼 ) - 1) DAY: 이것은 날짜(regist_date)에서 위에서 계산한 요일 간격만큼의 일자를 나타낸다. 즉, 주의 시작일(일요일)로부터 몇 일이나 지났는지를 나타내는 간격이다.

따라서 INTERVAL (DAYOFWEEK( 날짜컬럼 )-1) DAY 표현식은 주어진 날짜로부터 해당 주의 시작일(일요일)까지의 일자 간격을 계산한다.