PlanB의 백엔드 엔지니어링

SELECT 절에는 statement가 들어갈 수 있다. 단지 테이블의 특정 컬럼만이 아니라, 어떤 리터럴 값이나 식을 포함할 수 있다는 것이다. 아래는 이미 우리에게 익숙한 SELECT절의 예다.

그리고 SELECT는 아래처럼 사용할 수도 있다.

한 row에, 'Hello', 20, 9라는 결과가 출력될 것이다.

응용

스키마가 동일한 두 테이블을 결합하기 위해, UNION을 사용하곤 한다.

한 row에 대해서, 레코드가 어느 테이블 소속인지를 알고 싶은 경우, literal select를 사용한다.

row마다, 레코드의 소속을 나타내는 'app' 또는 'web'이 origin이라는 이름으로 항상 포함될 것이다.

집계 함수와 조건(LIMIT, WHERE 등)을 함께 사용하고 싶을 때가 있다. '소득'을 다루는 테이블에서 '상위 1만 명의 소득의 평균'을 집계하거나, '집값'을 다루는 테이블에서 '대전광역시에서 가장 비싼 원룸' 등을 집계하는 일이다. 일차원적으로 생각하면 '굉장히 쉽게 쿼리할 수 있지 않을까' 싶겠지만, 결과를 보면 갸우뚱하게 된다.

아무튼 결과는 나오지만, 사실 이는 원하는 결과가 아닐 가능성이 높다. 위 쿼리는 'tbl_people_incomes에서 AVG(income)을 계산한 결과를 income으로 내림차순 정렬한 후 LIMIT(10000)'을 의미한다. 이미 SELECT AVG(income) FROM tbl_people_incomes로 인해 만들어진 하나의 row를 정렬하고 잘라내는, 효과 없는 쿼리다. 집계 함수와 조건식을 함께 사용하려면, FROM의 뒷부분을 써먹어야 한다.

SELECT FROM SELECT절에는 nested(내부) SELECT를 소괄호로 감싸고 aliasing해줘야 한다. MySQL이 아닌 다른 SQL 엔진에서도 동일하게 적용되는지는 모르겠다.

GROUP BY [expr](, expr...) 쿼리는 SELECT 문의 질의 결과를 expr을 기준으로 묶어 새로운 row들을 반환하는 역할을 한다. 이는 보통 집계 함수와 함께 쓰이는데, 예를 들면 아래와 같다.

tbl_2nd_grade라는 테이블이 2학년 학생들의 정보를 관리하고, 여기에는 '반'과 '키', '몸무게'가 각각 class, height, weight라는 이름의 컬럼에 들어가 있다고 가정한다. 위 쿼리는 GROUP BY class로 인해 row들이 class를 기준으로 묶여 class마다 '반별 학생들의 키와 몸무게 평균'을 결과로 반환하게 된다. GROUP BY 없이 어플리케이션 레벨에서 이러한 요구사항을 만족시키려면, class를 key로 갖는 dictionary에 학생 수와 함께 키, 몸무게를 누적시킨 후 평균을 계산하는 로직을 작성해야할 것이다.

GROUP BY가 포함된 SELECT 문은 그룹마다 하나의 행을 반환한다. 예를 들어 class가 1, 2, 3만 있다면 3개의 행이 반환될 것이고, 1부터 10까지 있다면 10개의 행이 반환된다. GROUP BY가 포함된 쿼리를 작성할 때 인지하면 좋은 것은, GROUP BY 절에 명시한 expression이 아니라면 SELECT 절에 metric을 명시할 때 집계 함수 등을 통해 row가 하나만 등장하도록 만들어야 한다는 것이다. 예를 들면,

위의 예제는 유효한 쿼리지만, 아래의 예제는 유효하지 않다. grade를 기준으로 GROUP BY 되었기에 height와 weight는 AVG 함수에 전달되어 알아서 집계되어 group마다 하나의 값만을 가질텐데, student_number는 group에 따라 여러 개의 값을 가질 수 있기 때문이다. 논리적으로 맞지 않는 것이다.

GROUP BY 써먹기

사실 웬만하면 GROUP BY는 유용하다. 내 경우에는 수만~수십만 row가 넘는 데이터를 특별한 기준으로 나누어 집계하는 쿼리를 최적화할 때 GROUP BY의 유용함을 느낄 수 있었다. 내가 처했던 상황을 조금 간단한 예로 표현한다면, 10만 개가 넘는 학생별 시험 성적 데이터가 row마다 학번, 수학 점수, 과학 점수, ... 형태로 이루어져 있다고 칠 때, 과목별로 5점 단위마다 학생 수를 count해야 하는 일이었다. '수학 0~4점에 몇명, 5~9점에 몇명, ..., 95~99점에 몇명, 100점에 몇명' 하는 식의 데이터를 만들어야 하는 것이다. 처음 했던 것은, 과목을 기준으로 GROUP BY하고 score에 대해 CASE WHEN을 사용하는 것이었다.

결과는 적당히 잘 나오지만, 쿼리 시간은 SELECT절에 포함시키는 metric의 수에 비례하여 늘어난다는 것을 생각하면 확실히 좋기만 한 쿼리는 아니다. 만점이 더 높다면 그만큼 쿼리 시간이 더 늘어날 것이고, 피겨 스케이팅처럼 만점이 따로 없는 경우를 생각한다면 그렇게 유연한 쿼리도 아니다. GROUP BY에 단지 column name이 아니라 expression을 사용할 수 있다는 것을 응용하면 더 나은 쿼리를 작성할 수 있다.

subject와 score를 5로 나눠 소수점을 버리는 floor(score / 5)를 기준으로 group이 만들어질테니, 수학 0~4점은 math, 0, 13, 수학 5~9점은 math, 1, 3, ..., 수학 100점은 math, 100, 17 이런 식으로 각 row가 구성될 것이다. 이러한 쿼리가 빠른 이유는, row들을 iteration하는 for문 안에 수많은 if 절이 있는 것과 dictionary에 값을 누적하는 것 중 무엇이 빠른지를 생각하면 된다.

추가적으로, GROUP BY는 결과를 '분산'시키는 것에도 유용하다. 예를 들어 SELECT AVG(score) FROM tbl_score_2018_02_mid;로 모든 학생의 전체 과목에 대한 평균 점수를 집계할 수 있을텐데, 이걸 학생 단위로 결과를 분산시키기 위해 SELECT AVG(score) FROM tbl_score_2018_02_mid GROUP BY student_number;처럼 표현할수도 있다는 것이다.