QUERY에서 정렬 및 그룹화하기

필터링을 사용하면 올바른 행을 확인할 수 있지만, 실제 보고서에는 순서와 합계도 필요합니다. QUERY에서는 SQL 스타일의 두 절인 ORDER BY와 GROUP BY로 이 두 작업을 처리합니다.

이를 사용하면 어느 지역의 판매액이 가장 큰가 또는 거래를 가장 큰 것부터 가장 작은 것 순으로 나열하기와 같은 질문에 수식 하나로 답할 수 있습니다.

ORDER BY 절은 하나 이상의 열을 기준으로 결과를 정렬합니다. WHERE가 있다면 그 뒤에 작성합니다.

기본적으로 오름차순으로 정렬합니다(가장 작은 값부터, A부터 Z까지). 이 수식은 모든 행을 판매액이 낮은 순서부터 높은 순서로 나열합니다.

열 뒤에 DESC를 추가하면 가장 높은 값부터 가장 낮은 값 순으로 정렬합니다. 오름차순임을 명확히 하려면 ASC를 사용합니다.

이렇게 하면 가장 큰 판매액이 먼저 표시되므로 우수 실적자 목록에 적합합니다. LIMIT과 함께 사용하면 깔끔한 상위 3개 목록을 만들 수 있습니다.

ORDER BY에 여러 열을 쉼표로 구분해 나열하면 같은 값의 순서를 다시 정할 수 있습니다. 스프레드시트는 첫 번째 열로 정렬한 다음, 값이 같은 행을 다음 열로 정렬합니다.

여기서는 결과를 지역의 알파벳순으로 묶고, 각 지역 안에서는 판매액이 높은 순서로 표시합니다.

GROUP BY는 같은 값을 공유하는 행을 하나의 요약 행으로 합칩니다. 범주별 합계를 만들 때 사용하는 기능입니다.

사용하려면 SELECT에서 그룹화할 열과 다른 열에 적용한 집계 함수를 함께 지정해야 합니다. 집계 함수의 예로는 SUM, COUNT, AVG가 있습니다.

이 수식은 지역별 판매액을 합산합니다. SUM(D)는 판매액 열을 더하고, GROUP BY A는 지역마다 한 행을 만듭니다.

결과는 작은 피벗 테이블과 같습니다. 하나의 함수만으로 동부의 합계와 서부의 합계를 각각 확인할 수 있습니다.

합산하는 대신 COUNT를 사용하면 행 수를 셀 수 있습니다. 이를 통해 각 지역에서 성사된 거래가 몇 건인지 알 수 있습니다.

COUNT(D)를 사용하면 비어 있지 않은 판매액 셀의 개수를 세어 그룹별 행 수를 빠르게 확인할 수 있습니다.

각 그룹의 평균값을 구하려면 AVG를 사용합니다. 여기서는 지역별 평균 거래 규모를 구합니다.

집계 함수를 섞어 사용할 수도 있습니다. 같은 쿼리에서 SUM(D)와 AVG(D)를 모두 선택하면 합계와 평균을 나란히 표시할 수 있습니다.

흔히 발생하는 오류가 있습니다. SELECT의 각 열 중 집계 함수로 감싸지지 않은 열은 반드시 GROUP BY에 포함해야 합니다.

SELECT A, B, SUM(D) GROUP BY A는 B가 집계되지도 그룹화되지도 않았으므로 실패합니다. A와 B를 모두 그룹화하거나, 선택 목록에서 B를 삭제해야 합니다.

절을 결합하여 요약 결과의 순위를 정할 수 있습니다. 그룹화한 후 집계값을 기준으로 정렬하면 가장 큰 그룹을 맨 위에 표시할 수 있습니다.

이렇게 하면 각 지역의 총 판매액을 가장 높은 값부터 가장 낮은 값 순으로 나열하여 바로 사용할 수 있는 순위표를 만들 수 있습니다.

그룹화한 열에는 sum Sales처럼 보기 좋지 않은 헤더가 붙습니다. 세련된 보고서를 만들려면 LABEL 절을 추가해 이름을 바꾸십시오.

이렇게 하면 합계 열의 이름이 Total Sales로 바뀝니다. 레이블 텍스트는 필터 값과 마찬가지로 작은따옴표를 사용합니다.

정렬과 그룹화를 얼마나 이해했는지 확인해 보십시오.

이제 QUERY 결과를 원하는 형태로 구성할 수 있습니다.

• ORDER BY col [ASC|DESC]는 정렬을 수행하며, 같은 값을 구분하려면 열을 추가합니다
• GROUP BY와 SUM, COUNT, AVG를 함께 사용하면 범주별 요약을 만들 수 있습니다
• 선택한 비집계 열은 반드시 GROUP BY에 포함해야 합니다
• LABEL은 집계 열의 헤더 이름을 바꿉니다

이제 수식 하나로 정렬하고 그룹화한 레이블 지정 보고서를 만들 수 있습니다.