구글 스프레드시트 - QUERY 함수로 원하는 데이터 추출하기

전체 목차

01. GoogleFinance 함수로 주가 데이터 가져오기(1)

02. GoogleFinance 함수로 주가 데이터 가져오기(2)

03. GOOGLETRANSLATE 함수로 번역하기

 

포스트 목차.

• 01. QUERY 함수 소개
• 02. 함수 인자 설명
• 03. 사용 예제

 

본 포스트에서는 구글 스프레드시트에서 제공하는 구글 전용 함수 중에, 스프레드 시트의 특정영역을 데이터베이스처럼 활용할 수 있게 해주는 QUERY 함수에 대해서 소개하려고 한다.

 

엑셀에서도 시트의 특정 영역을 데이터베이스 처럼 다룰 수 있는 함수들을 소개한 적이 있다. 해당 사항이 궁금하면 다음링크를 참고하자

https://eggdrop.tistory.com/45

DAVERAGE함수, DMAX함수, DMIN 함수

https://eggdrop.tistory.com/46

DCOUNT, DCOUNTA, DSUM, DGET, DPRODUCT, DVAR, DSTDEV 함수

 

01. QUERY 함수 소개

QUERY 함수는 다음 형태로 사용될 수 있다.

QUERY(데이터, 검색어, [헤더])

데이터: 데이터베이스처럼 사용할 시트의 영역을 나타낸다. 헤더를 따로 지정하지 않는 경우 첫번째 줄이 필드의 이름을 나타낸다.

 

검색어: 데이터베이스 중에서 관심있게 보고 싶은 필드와 원하는 레코드를 특정할 수 있게 하는 조건이 들어가야 한다.  Google Visualization API 검색 언어의 문법으로 작성할 수 있다.

 

헤더: 필드의 이름에 해당하는 데이터의 행을 입력할 수 있다. 숫자로 입력한다.

 

02. 함수 인자 설명

함수 인자 중에서 "검색어" 파트를 자세히 살펴보자.

 

검색어 파트에는 Google Visualization  API 검색 언어 문법을 만족하는 텍스트 문자열을 입력하면 된다. Google Visualization API에 대한 자세한 내용은 다음 링크를 참고하자.

https://developers.google.com/chart/interactive/docs/querylanguage

Query Language Reference (Version 0.7)  |  Charts  |  Google Developers

 

SQL의 select 문과 비슷한 형태로 사용할 수 있다.

 

(1) 기본 형태

기본적이 형태는 다음과 같다.

 

select [표기할 열] where [조건]

(2) WHERE 절

예를 들어 C열의 데이터가 밀짚모자 일당인 D열의 정보만 수집하고 싶으면 다음과 같이 검색어를 작성하면 된다.

 

select D where C = '밀짚모자 일당'

 

where 절에 복수의 조건이 들어갈 경우에는 조건들의 관계에 맞게 (,), and, or를 사용하여 결합 할 수 있다. 

 

예를 들어 H열의 데이터가 5억 초과, F열의 데이터가 180 미만인 레코드의 C, D, E 열의 정보를 수집하고 싶으면 다음과 같이 검색어를 작성하면 된다. 

 

select C, D, E where H > 500000000 and F < 180

 

(3) 정렬 순서

검색어의 뒷부분에 다음과 같은 구문을 추가하면 정렬 순서를 변경할 수 있다.

ORDER BY 필드문자1, [필드문자2], .... [DESC|ASC]

 

필드를 지시하는 열번호를 이용하여, 정렬의 기준이 되는 필드를 지정할 수 있으며, DESC 또는 ASC를 사용하여 내림차순, 오름차순을 정할 수 있다.

 

(4) 그룹화 및 통계

필드에 채워진 데이터가 숫자일 경우에 다음과 같은 통계 기능을 제공한다.

 

함수명	설명
MAX(필드문자)	where 절의 조건에 맞게 조회된데이터에 한하여, 지정한 필드의 최대값을 표기한다.
MIN(필드문자)	where 절의 조건에 맞게 조회된데이터에 한하여, 지정한 필드의 최소값을 표기한다.
AVG(필드문자)	where 절의 조건에 맞게 조회된데이터에 한하여, 지정한 필드의 평균값을 표기한다.
SUM(필드문자)	where 절의 조건에 맞게 조회된데이터에 한하여, 지정한 필드의 총합을 표기한다.

또한 특정 필드별 공통값으로 그룹을 만들수도 있다. 

 

GROUP BY 필드문자1, [필드문자2], .... 를 사용하면 된다.

 

예를 들어) C열 필드별로 그룹화하여, C열의 데이터명, G열의 최대값, G열의 평균값, G열의 최소값, G열의 총합을 조회하여 C열로 내림차순 정렬하기 위한 검색어는 다음과 같다.

 

select C, MAX(G), AVG(G), MIN(G), SUM(G) GROUP BY C ORDER BY C DESC

 

(5) 피봇

GROUP BY 와 유사한 작업을 하되, 표기하는 데이터의 열과행을 변경하여 표기하는 기능을 담당한다.

 

select 후에 나오는 표기할 열을 지정할 때는 필드별로 대표값(avg, min, max, sum...등)의 값이 나오게 해야한다.  다음과 같이 활용할 수 있다.

 

예) C열 필드를 기준으로 그룹화 후에 H열 필드를 평균한 값을 행과 열을 변경하여 표기한다.

select avg(H) pivot C

 

 

03. 사용 예제

몇몇 예제를 다루면서 실제 사용방법을 익혀보자.

 

사용할 데이터 예제는 다음과 같다.

 

C열부터 I열까지 데이터가 채워져 있다.

 

(1) 밀짚모자 일당의 이름을 가져오자

select D where C = '밀짚모자 일당'

 

결과는 다음과 같다.

 

 

(2) 흰 수염 해적단의 이름을 가져오자

select D where C = '흰 수염 해적단'

 

결과는 다음 과 같다.

 

(3) 빅맘 해적단의 키의 최대값을 가져오자

select MAX(F) where C = '빅 맘 해적단'

 

결과는 다음과 같다.

(4) 소속별로 나이의 최대값, 평균값, 최소값, 총합을 구해서 소속 이름으로 내림차순 정렬해보자.

select C, MAX(G), AVG(G), MIN(G), SUM(G) GROUP BY C ORDER BY C DESC

 

결과는 다음과 같다.

 

(5) 소속별로 현상금 평균값을 피봇해보자.

select avg(H) pivot C

결과는 다음과 같다.

데이터가 좌에서 우로 나오는 것을 알 수 있다.

 

(6) 현상금 500,000,000 초과, 키 180 미만인 레코드의 소속, 이름, 이명을 가져오자

select C,D,E where H > 500000000 and F <180

 

결과는 다음과 같다.