googlesheetsquery
1.0.0
Google의 쿼리 언어는 SQL에 사용되는 많은 패턴을 차용하여 Google 시트의 데이터를 분석하는 빠르고 강력한 방법입니다. 이 튜토리얼에서는 쿼리를 사용하여 샘플 데이터 세트의 특정 질문에 답하는 기본 방법을 익힐 것입니다.
먼저 가지고 놀 수 있는 샘플 데이터가 필요합니다. 가져오기 기능을 사용하여 Wikipedia에서 세계 인구 데이터 테이블을 가져옵니다.
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 3)이 함수는 위 URL에서 세 번째 테이블을 찾아 각 셀에서 데이터를 스크랩하여 Google 시트로 가져옵니다. 시원한! 하지만 이 데이터를 좀 더 쉽게 쿼리할 수 있도록 한 가지 작업을 더 수행해 보겠습니다.
데이터가 있는 셀을 클릭하여 강조 표시한 다음 CMD + A를 눌러 전체 표를 강조 표시합니다. 전체 테이블이 강조표시된 상태에서 데이터 > 명명된 범위를 클릭하여 명명된 범위 패널을 엽니다. 그러면 오른쪽에서 날아갑니다.
명명된 범위는 셀 그룹에 부여할 수 있는 별명입니다. 우리 나라의 이름을 '국가'로 지정합시다.
엄청난! 이제 쿼리를 시작할 준비가 되었습니다!
셀 J1을 강조 표시합니다. 이 셀을 사용하여 아래의 모든 쿼리를 입력합니다.
전체 데이터 세트의 사본을 표시합니다.
=QUERY(countries, "select *",1)
국가 및 지역 열만 표시합니다.
=QUERY(countries, "select B, C",1)
WHERE 절은 데이터 세트에서 보다 구체적인 질문을 시작하는 좋은 방법입니다.
인구가 1억 명 이상인 국가를 표시하세요.
=QUERY(countries, "SELECT B, E where E >= 100000000")
이름에 'es'가 포함된 국가를 표시하세요.
=QUERY(countries, "SELECT B where B contains 'es'")
이름에 'es'가 없는 국가를 표시하세요.
=QUERY(countries, "SELECT B where not B contains 'es'")
'S'로 시작하는 국가를 표시하세요.
=QUERY(countries, "SELECT B where B STARTS WITH 'S'")
's'로 끝나는 국가를 표시하세요(대소문자를 구분하세요.
=QUERY(countries, "SELECT B where B ENDS WITH 's'")
'United'가 포함된 국가 보기:
=QUERY(countries, "SELECT B where B CONTAINS 'United'")
AND 절에 더욱 구체적인 추가를 적용해 보겠습니다!
인구가 100,000명 미만인 아메리카 대륙의 모든 국가를 보여주세요.
=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000", 1)
AND 절은 무한대로 연결될 수 있습니다.
이 쿼리는 인구가 100,000명 미만에서 10,000명을 초과하는 아메리카 대륙의 모든 국가를 반환합니다.
=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000 AND F >= 10000", 1)
데이터세트를 탐색할 때 가장 먼저 하고 싶은 일 중 하나는 특정 필드의 최대값, 최소값, 평균값과 같은 몇 가지 간단한 집계를 확인하는 것일 수 있습니다. 데이터 세트에 있는 국가의 2017년 최대, 최소 및 평균 인구를 찾아보겠습니다.
=QUERY(countries, "select max(F), min(F), avg(F)", 1)
멋지네요
약간만 수정하면 각 대륙의 최대, 최소, 평균 인구를 볼 수 있습니다.
=QUERY(countries, "select C, max(F), min (F), avg(F) GROUP by C", 1)
'개수'와 '그룹화 기준'을 함께 사용하면 데이터 세트를 탐색할 수 있는 또 다른 좋은 방법입니다. 이를 결합하면 하나 이상의 열에서 항목 수를 계산하고 보기를 집계할 수 있습니다.
이 두 절을 사용하여 목록에 있는 국가가 각 지역에 몇 개 있는지 살펴보겠습니다.
=QUERY(countries, "SELECT C, count(B) GROUP by C", 1)
'ORDER BY' 절을 추가하여 모든 지역의 목록을 가장 높은 것부터 낮은 것까지 오름차순으로 표시해 보겠습니다.
=QUERY(countries, "SELECT C, count(B) GROUP by C ORDER by C asc", 1)
'PIVOT' 절은 값을 단일 행으로 집계합니다.
예를 들어, 2017년 전체 인구의 지역별 합계를 표시하려면 다음을 사용할 수 있습니다.
=QUERY(countries, "SELECT sum(F) pivot C", 1)
쿼리의 일부로 수학 연산을 수행하여 데이터를 분석할 수도 있습니다.
이제 2017년 인구를 전체 세계 인구 대비 백분율로 표시한 국가 목록을 보여드리겠습니다.
=QUERY(countries, "select B, C, (F / 7550262101) * 100", 1)
백분율 데이터는 멋져 보이지만 열 헤더는 매우 읽기 쉽습니다. 셀을 클릭하고 이름을 바꿀 수도 있지만 'label' 매개변수를 추가하여 쿼리의 일부로 이름을 설정할 수도 있습니다.
=QUERY(countries, "select B, C, (F / 7550262101) * 100 Label (F / 7550262101) * 100 'Percentage'", 1)
Google 쿼리 언어 문서: https://developers.google.com/chart/interactive/docs/query언어
Coding Is For Losers의 Google 스프레드시트 쿼리 기능 개요: https://codingisforlosers.com/google-sheets-query-function/#why
