여기서는 QUERY함수의 사용법을 배우고 FILTER와의 차이점을 이해해 봅니다. 쿼리가 뭘까요? SQL입니다. 오..... 뭔가 완전 어려울 거 같습니다. 그런데 무서워할 필요가 없습니다. 0장에서 얘기한 것처럼 여태 SQL을 볼 일이 없었던 분은 배울 필요가 없었던 거고 앞으로도 별로 쓸 일이 없을 거거든요. 여태 안 하던 걸 새로 하자고 구글 시트를 배우는 건 아닙니다. 물론 하는 걸 말리지는 않습니다.
SQL은 SELECT * FROM A WHERE B=C 형태의 구문으로 데이터베이스의 자료를 가져오는 문법입니다.구글 시트의 QUERY 함수는 데이터범위를 따로 지정하기 때문에 위 형식에서 FROM이 빠집니다. 데이터범위는 같은 시트의 주소 범위일 수도, {} 배열일 수도 있고, IMPORTRANGE 나 FILTER의 반환값일 수도 있습니다.
주의할 점은 데이터범위가 같은 스프레드시트의 셀 주소로 참조되어 있는지, IMPORTRANGE처럼 별도의 범위로 가공된 값인지에 따라 필드 이름을 검색어(쿼리문)에서 지칭하는 방식이 다르다는 점입니다. 자기 스프레드시트에서 참조할 경우에는 컬럼의 문자(A,B,C,D,...)를, 다른 범위에서 참조할 때는 (Col1, Col2, Col3,....)를 필드 이름으로 사용합니다. Col1에서 C는 대문자, ol은 소문자로 적어주셔야 합니다...
잘 모를 때는 예제시트이지요. 0.EXAMPLE의 예제에서는 QUERY('raw1'!$A9:$W, "select A, D, K where E='직영몰' and C='판매중' order by B",0) 라는 수식을 사용했습니다. 즉 같은 스프레드시트의 A:W라는 컬럼을 가져오므로, A,D,K를 필드명으로 사용합니다. 쿼리라는 게 크게 어려워 보이지도 않습니다. 우리도 한 번 만들어 봅시다.
1장에서 사용했던 Team Directory에서 이름이랑 ID를 한꺼번에 가져올 겁니다. QUERY(데이터범위,검색어)에서 데이터범위는 배열 형태로 가져오면 되니 IMPORTRANGE("1Z_i03d6Yj4kc2lNWPQUu3lblQJypIjO1MoopDp7ku4g", "Team!A2:J")를 사용합니다. 검색어는, 일단 SELECT *을 해볼까요? **
<aside> <img src="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/27b825e1-96b8-4faf-9e54-ad969c334cf1/fx.png" alt="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/27b825e1-96b8-4faf-9e54-ad969c334cf1/fx.png" width="40px" /> [예제3-6] *QUERY( IMPORTRANGE("1z8oZq4YOp6kzsCUb1r0pnFDrsVmY5VekWAzz935vSX0", "Team!A2:J"), "SELECT ")
</aside>
터집니다. 왜 터질까요. 옆 셀에 데이터가 있는데 겹쳐서 터지는 거구요, 옆 셀 데이터를 살짝 지워보면 전체 데이터가 다 들어오는 걸 볼 수 있습니다. SELECT *을 하면 그냥 원래 데이터범위의 값을 모두 던져줍니다. 여기서는 그냥 IMPORTRANGE하는 거랑 똑같죠. 쿼리를 SELECT Col1, Col6 WHERE Col2 = '경영본부'로 바꿔 봅니다. Col에서 대소문자 주의하시구요.
<aside> <img src="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/27b825e1-96b8-4faf-9e54-ad969c334cf1/fx.png" alt="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/27b825e1-96b8-4faf-9e54-ad969c334cf1/fx.png" width="40px" /> [예제3-7] QUERY( IMPORTRANGE("1Z_i03d6Yj4kc2lNWPQUu3lblQJypIjO1MoopDp7ku4g", "Team!A2:J"), "SELECT Col1, Col6 WHERE Col2 = '경영본부'")
</aside>
데이터범위에서 첫번째(이름), 여섯번째(ID) 열을 가져오는데 두번째(그룹) 열이 "경영본부"인 항목만 가져옵니다. 그럼 이번에는 9:00에 출근하는 사람만 가져와 볼까요? 출근시간은 열번째 열에 들어 있습니다. AND Col10='9:00 AM'을 넣어봅니다. 터집니다. 왜 터질까요. 구글링을 해보니 시간을 가져오기 위해서는 timeofday를 앞에 써줘야 한다고 합니다. AND Col10= timeofday '9:00 AM'으로 바꿔봅니다. 그래도 안 되네요. 오류의 설명을 봅니다.
어쩌고 저쩌고 양식이 HH:mm:ss[.SSS]여야 한다고 하네요. '09:00:00'으로 바꿔줍니다.
<aside> <img src="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/27b825e1-96b8-4faf-9e54-ad969c334cf1/fx.png" alt="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/27b825e1-96b8-4faf-9e54-ad969c334cf1/fx.png" width="40px" /> [예제3-8] QUERY( IMPORTRANGE("1Z_i03d6Yj4kc2lNWPQUu3lblQJypIjO1MoopDp7ku4g", "Team!A2:J"), "SELECT Col1, Col6 WHERE Col2 = '경영본부' and Col10 = timeofday '09:00:00'")
</aside>
날짜 기준인 경우 앞에 timeofday대신 date를 붙이고 '2019-01-01'의 양식으로 만듭니다. 날마다 기준이 바뀌는 경우, 즉 매일매일 어제의 판매량을 조회하고 싶은 경우 쿼리의 날짜 부분을 특정 셀 값으로 대체하면 편하게 사용할 수 있습니다. 예제시트에서 '직영몰'을 '오픈마켓'으로 바꿔보세요.
<aside> <img src="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/27b825e1-96b8-4faf-9e54-ad969c334cf1/fx.png" alt="https://s3-us-west-2.amazonaws.com/secure.notion-static.com/27b825e1-96b8-4faf-9e54-ad969c334cf1/fx.png" width="40px" /> [예제3-9] 직영몰 → 오픈마켓
</aside>
예제 테이블의 수식은 =query('raw1'!$A9:$W, "select A, D, K where E='"&E23&"' and C='판매중' order by B",0)로 되어 있습니다. 즉 쿼리문 중간을 " "로 끊고 변수가 들어 있는 셀의 양쪽을 &로 이어서 대체한 것입니다. 이와 같이 어떤 특정일, 특정월 처럼 변수를 쿼리에 적용할 수 있습니다. 쿼리에서 날짜를 사용할 때는 TEXT(날짜셀, "YYYY-MM-DD")로 형식을 맞춰주면 됩니다.
위에서 살펴 본 것 이외에도 QUERY는 SQL문의 복잡한 문법을 사용할 수 있습니다. 단순히 만족하는 항목만을 반환받는 것이 아니라 SUM, AVG, COUNT, MAX, MIN 과 같은 집계(aggregation)쿼리를 사용해서 데이터를 계산할 수 있습니다. SUM(Col1) 과 같은 식으로 쿼리를 수행하면 SUMIFS와 유사한 결과를 얻을 수 있습니다.