구글스프레드시트 쿼리함수 이용하기 - 1
더 이상 함수는 그만!!
첫 회사. 두근거리는 긴장감을 가지고 업무를 시작한다.
옆자리 사수가 엑셀 파일을 던져준다.
엑셀 함수를 보자, 눈이 어지러워지기 시작한다.
아직도 그의 한마디가 잊혀지지 않는다.
"우리 위층에 있는 00팀장은 Vlookup도 안 쓰고 다른 함수를 조합해서 쓴다고 하더라. 그 분이 나보다도 엑셀은 더 잘할 것이다."
아니 이럴수가.
그 똑똑한 내 옆자리 사수보다 고수가 있다니, 역시 세상은 넓구나....
6년 후
지금의 나는 위층 팀장보다도 엑셀을 더 잘 할 것이다.
지금의 나는 엑셀에서 Vlookup 따위는 쓰지도 않는다. 이전에는 index, match 함수를 vlookup 대신 썼지만, 지금은 Xlookup으로 대신한다.
조금 더 정교한 데이터 가공을 위해서는 차라리 엑셀을 사용하지 않고, 구글 스프레드시트에 데이터를 모아놓은 후 쿼리문을 이용한다.
필요한 기능이 있다면 그 기능을 Apps Script를 활용해서 함수로 만들어 사용하면 된다.
(프로그래밍의 변수/반복문/조건문 등 기본 문법에 익숙한 사람이라면 충분히 가능하다)
하지만 데이터를 전문적으로 다루는 사람이 아니라면, 굳이 그 정도까지 해야 될 이유가 있는지는 모르겠다.
그래서 오늘은 구글 스프레드시트 쿼리문을 이용하여 데이터 가공을 해보자.
구글 쿼리란?
https://developers.google.com/chart/interactive/docs/querylanguage
(위의 링크는 구글 쿼리 문서를 모아둔 디벨로퍼 문서이다. 대부분의 프로그래밍 언어는 디벨로퍼 문서를 통해 기능 개념과 예제를 설명하고 있기 때문에 잘 활용하도록 하자)
위 문서를 살펴보면 Query에 대해서 이렇게 정의하고 있다.
The query language provides the ability to send data manipulation and formatting requests to the data source, and ensure that the returned data structure and contents match the expected structure.
"쿼리 언어는 원하는 형태와 조건의 데이터 질의를 로우 데이터에 보낸 후 그 요청에 맞는 데이터를 반환하는 역할을 합니다."
"Query"라는 단어 자체가 "질의"라는 뜻을 지니고 있기 때문에, 우리는 질의를 통해서 원하는 기대값을 추출해 오기만 하면 되는 것이다.
원래 SQL을 배우기 위해서는 데이터 베이스에 대한 이해도가 있어야 한다. SQL은 RDBMS(Relational Databasement Management System)에서 주로 사용하는데, 데이터 추출뿐만 아니라 CRUD(Create, Read, Update, Delete) 4가지 기능이 본래의 목적이다.
Client-Server-DB 라는 형태에서 Server의 요청을 받아 DB에 데이터를 생성하고, 업데이트하고, 지우고, 읽어내는 요청을 하는 것이 본래의 목적이지만 데이터 가공만을 한다면 이런 것까지 알 필요는 없다.(물론 한번쯤 공부해 보는 것은 추천한다.)
복잡한 기능 대신 우리가 사용할 문법은 아래와 같다.
위에 적혀있는 문법을 통해서 우리는 조건에 맞는 데이터를 불러오고, Data Visualization 및 Report를 작성할 수 있을 것이다.
우선 오늘은 가장 많이 쓰이는 Select와 Where 구문에 대해서 살펴보자.
1. Select 구문
데이터베이스를 직접 건드리지 않는 사람이 쿼리에서 사용하는 질의는 100% Select 구문이다.
Select 구문은 표시할 열과 열의 순서를 정할 수 있다.
모든 열을 불러오고자 할 경우에는 SELECT *을 사용한다.
간단한 예제를 살펴보도록 하자.
Raw Data Sheet위 데이터 시트는 3개의 A, B, C 컬럼이 "코드명", "회사명, "전화번호" 헤더를 지니고있다.
오늘은 필자가 계약을 따낸 회사에게 코드명을 부여한 날이다.
(그래서 쿼리문을 작성하다보니 글도 작성하게 되었다.)
동업자의 요청은 하나였다.
코드명만 있는 회사를 보고 싶다.
Raw 파일에 리스트가 수백개가 될 경우 구분이 힘들 것이다.
물론 필터를 걸 수도 있겠지만, 썸머리 시트를 하나 더 만들어서 계약된 업체만 보고자 한다.
그래서 계약된 업체에 코드값을 부여했다.
솔직히 말해서 가장 좋은 것은 "완료 여부"라는 컬럼 1개를 더 추가해서,
계약이 완료된 회사는 "완료"라는 데이터를 집어 넣어 구분하는 것이다.
하지만 오늘은 빈 값이 아닌 데이터만을 불러오기 위해서 위와 같이 데이터를 넣어 보았다.
쿼리문 및 결과값쿼리문을 살펴보도록 하자.
=QUERY(testInfo!$A$1:$B$1000,"select A, B where A is not null")
QUERY
쿼리 함수이다. 쿼리 함수는 (범위 선택, 쿼리문 입력,헤더) 3개의 파라미터로 구성되어있다.
1) 범위 선택
testInfo!$A$1:$B$1000 범위를 지정한 것을 알 수 있다. 물론 달러 표시를 하여 범위를 묶어 놓았다.
("testInfo"는 시트 이름이다.)
하지만 만약 범위를 벗어나면 에러가 뜬다.
2) 쿼리문 입력
쿼리문 입력이 생소할 수도 있지만, 한번 학습해놓으면 전혀 어렵지 않다.
우선 Select 구문을 입력한다는 뜻은
앞에 설정된 testInfo!$A$1:$B$1000 컬럼 범위에 속하는 모든 컬럼을 선택하겠다는 뜻이다.
내가 현재 보고 싶은 값은 아래 단 두 가지이다.
A 컬럼: 코드명
B 컬럼: 회사명
"Select A, B"라는 뜻은 A,B 컬럼의 범위 안에 속하는 모든 데이터를 가져오라는 뜻이다.
Where : 조건문
Where은 조건문이다.
말 그대로 조건을 다는 것이다.
필자가 하고 싶은 것은 A의 데이터 중 빈 값이 아닌 모든 데이터를 가져오고자 한다.
"Where A is not null"은 A 컬럼 중 데이터가 Null 값이 아닌 데이터를 가져오라는 뜻이다.
프로그래밍 언어에서는 빈 값을 Null 값이라고도 하는데, 이 Null 값은 엑셀을 하면서도 많이 보았을 것이다.
저 짧은 쿼리문을 사용하면, 데이터를 리스트 형태로 가져온다.
데이터를 리스트 형태로 가져온다는 뜻은 아래와 같다.
쿼리문 이용하여 한 개의 데이터 값이 아닌 복수의 데이터를 리스트라는 프로그래밍 데이터 형태로 가져와 데이터를 반환
그렇기 때문에 저 수식을 한 개만 입력하면 우리가 2개의 컬럼값을 요청했기 때문에, 저절로 2개의 컬럼에 데이터가 반환되게 되는 것이다.
A1에는 쿼리문이 작성되어있다
A2는 텍스트 값이 입력되어있는 것을 알 수 있다. 이것은 리스트 형태로 데이터를 리턴하기 때문이다.벌써 끝났다.
너무 쉽기도 하지만, 처음 접하는 이들에게는 생소할 것이다.
저 쿼리문을 이용해 다이내믹한 데이터 시각화와 데이터 가공이 가능하다.
앞으로 프리랜서 일을 하며 데이터 가공을 하다 작업을 하게 되면 그때그때 정리해서 올려보도록 하겠다.
참고.
Comento라는 싸이트에서 Google Spreadsheet 쿼리 강의를 진행하고 있습니다. Google SpreadSheet에서 제공하는 쿼리 함수 및 텍스트 마이닝을 위한 정규표현식 그리고 대시보드 만들기까지, Google Spreadsheet을 이용한 데이터분석의 모든 것을 강의하고 있으니 관심있으신 분들은 참고 바랍니다!
