[Database] DBA급 개발자로 - #2 Intermediate SQL

이전 포스팅에서 relational model에 대해서 살펴봤는데요, 이번 포스팅에서는 RDBMS 중급 난이도 SQL의 사용법과 특징을 살펴보고자 합니다. 

 

Aggregation Functions

데이터베이스에서 aggregate 함수는 여러 개의 데이터 값을 입력으로 받아 하나의 결과 값을 내놓는 함수입니다. 이러한 함수는 데이터베이스에서 대량의 데이터를 다루는 데 있어서 매우 유용합니다. 일반적으로 데이터베이스에서는 이러한 함수를 이용하여 평균, 최댓값, 최솟값, 합계 등을 계산할 수 있습니다. 예를 들어, 고객 데이터베이스에서 고객들의 연령대별 평균 소득을 계산하기 위해서는 소득이라는 열을 이용하여 aggregate 함수인 평균 함수(AVG)를 적용할 수 있습니다.

Aggregation 함수의 예시입니다. 더욱 다양한 Aggregation 함수의 사용법(MySQL)은 다음 링크를 참고해주세요. 

• AVG(col) : 명시된 컬럼의 평균값을 반환합니다. 
• MIN(col) : 명시된 컬럼의 최솟값을 반환합니다. 
• MAX(col) : 명시된 컬럼의 최댓값을 반환합니다. 
• SUM(col) : 명시된 컬럼의 총합을 반환합니다. 
• COUNT(col) : 명시된 컬럼의 총개수를 반환합니다. 

추가로 COUNT, SUM, AVG 함수는 DISTINCT 키워드와 함께 사용할 수 있습니다. DISTINCT 키워드를 사용하게 되면 명시된 컬럼에서 중복을 제거해서 집합 함수를 적용합니다. 

사용법은 다음과 같습니다. student 테이블은 id(primary key), grade(성적), level(학년) 컬럼으로 구성됐습니다. 

student table

여기서 각 학년별로 평균 성적을 구하려면 어떻게 해야 할까요? 다음과 같은 쿼리를 작성하면 됩니다. 

select avg(grade), level
from student
group by level;

그럼 다음과 같은 결과가 나옵니다. 

학년별 평균 성적

Aggregation 함수를 사용할 때 주의할 점이 있습니다. SELECT 절에 명시된 컬럼 중 Aggregation 함수가 적용되지 않은 컬럼(아래의 예시에서는 level)은 반드시 GROUP BY 절에 위치해야 합니다(group by level). 만약 GROUP BY에 명시하지 않는다면 다음과 같은 에러가 발생합니다. 참고로 sql_mode는 MySQL의 시스템 변수입니다. 

non-aggregated 컬럼이 group by에 명시되지 않는 경우 발생하는 에러

그럼 sql_mode의 only_full_group_by를 끄면 어떻게 될까요? MySQL에서는 현재 세션의 sql_mode를 변경할 수 있습니다. 일단 현재 세션의 sql_mode를 확인해줍니다.

sql_mode

sql_mode의 ONLY_FULL_GROUP_BY를 제외한 값으로 sql_mode를 설정해줍니다.

sql_mode 설정

다시 sql_mode를 확인해보면 변경된 것을 확인할 수 있습니다.

ONLY_FULL_GROUP_BY 제거

그럼 이번에는 다음과 같이 질의를 수행하겠습니다. 

select avg(grade), level
from student;

ONLY_FULL_GROUP_BY 설정 해제 후 결과

분명 학년(level)은 1, 2, 3이 있었는데 level 1만 표시되고 있습니다. MySQL은 이러한 불분명한 동작을 방지하기 위해 ONLY_FULL_GROUP_BY를 기본 설정값으로 사용합니다. 

다음으로는 Aggregation 함수에 사용될 데이터를 필터링할 수 있는 HAVING 절에 대해 알아보겠습니다. 예를 들어 평균 60점 이상의 결과만 보겠다 하면 다음과 같이 질의를 수행할 수 있습니다. HAVING 절에 명시할 수 있는 컬럼은 GROUP BY 절에 명시된 컬럼 또는 Aggregation 함수에 사용된 컬럼입니다(SQL 표준). 

평균이 60점 이상만 질의

HAVING 절에 명시할 수 있는 컬럼은 DBMS별로 스펙이 상이합니다. 예를 들어 MySQL은 다음과 같은 쿼리가 가능합니다. id 컬럼은 GROUP BY 절에 명시되지 않았고 Aggregation 함수에도 사용되지 않았는데 HAVING 절에서 사용할 수 있습니다. 

MySQL의 경우 HAVING 절에 명시할 수 있는 컬럼

 

Window Functions

데이터베이스에서 window 함수는 기본적으로 특정한 범위 내의 데이터를 다루는 함수입니다. 이러한 함수는 전체 데이터 집합이 아닌 일부의 데이터만을 처리하는 데 사용됩니다. 예를 들어, 고객 데이터베이스에서 고객들의 연령대별 소득 분포를 계산하기 위해서는 소득이라는 열을 이용하여 window 함수를 적용하여 각 연령대별로 소득을 분류할 수 있습니다. 또한 window 함수는 데이터의 순서를 고려하여 처리할 수 있는 기능을 가지고 있습니다. 예를 들어, 주가 데이터베이스에서 종목의 최근 3일간의 주가 지수를 계산하기 위해서는 window 함수를 이용하여 데이터의 순서를 고려하여 주가 지수를 계산할 수 있습니다. Window function은 아래와 같은 형태로 작성합니다. 

SELECT ... FUNCTION_NAME(...) OVER (...)

FUNCTION_NAME은 Aggregation 또는 Window function에 사용되는 함수들이며 OVER에서 연관된 레코드를 명시합니다. 

위와 동일한 student 테이블을 예제로 사용하겠습니다.

student table

Student 테이블에서 각 레코드가 위치한 행 number를 알고 싶으면 다음과 같이 쿼리 할 수 있습니다.

row_number()

Student 테이블에서 각 학년별로 성적(grade)의 순위를 매기고 싶으면 다음과 같이 쿼리 할 수 있습니다. 여기서 확인할 것은 OVER 키워드에는 PARTITION BY 또는 ORDER BY 키워드를 사용할 수 있다는 점입니다. PARTITION BY는 window function을 적용할 그룹을 지정합니다(여기서는 학년별로 성적 순서를 매기기 때문에 학년, 즉 level을 PARTITION BY 대상으로 지정했습니다). ORDER BY는 window function이 적용된 각 그룹(학년별)에서 정렬에 사용될 컬럼을 명시할 수 있습니다. 

rank()

CTE

데이터베이스의 CTE는 Common Table Expression의 약자로, 데이터베이스 쿼리에서 사용되는 임시 뷰를 정의하는 문법입니다. CTE는 데이터베이스 쿼리의 구조를 단순화하고, 읽기 쉽게 만드는 기능을 가지고 있습니다. 예를 들어, 고객 데이터베이스에서 고객들의 연령대별 소득을 조회하기 위해서는 소득이라는 열을 이용하여 CTE를 정의하고, 이를 이용하여 연령대별 소득을 계산할 수 있습니다. CTE는 복잡한 쿼리를 이용하여 정의한 뷰와 비슷한 기능을 가지고 있지만, 임시 뷰로서 데이터베이스에 저장되지 않고 쿼리 실행 시점에만 존재합니다. 따라서 CTE는 쿼리 작성과 실행에 대한 효율을 높이는데 유용한 도구입니다.

CTE 예제

마무리 

이번 포스팅에서는 중급 레벨의 SQL에 대해서 살펴봤습니다. 현업에서는 제 경험상 Window 함수와 Common Table Expression을 활용할 일이 없어서 상당히 흥미로운 주제였습니다. 다음 포스팅에서는 DBMS가 디스크에 데이터를 어떤 형태로 저장하는지 살펴보겠습니다.

 

Reference 

https://stackoverflow.com/questions/11169550/is-there-a-performance-difference-between-cte-sub-query-tempora ry-table-or-ta