구글 스프레드시트 쿼리(query) 사용하기

by nanumi posted Sep 26, 2021
?

단축키

Prev이전 문서

Next다음 문서

ESC닫기

크게 작게 위로 아래로 댓글로 가기 인쇄

 

 

 

QUERY

데이터에서 Google Visualization API 검색 언어로 검색을 실행합니다.

사용 예

QUERY(A2:E6,"select avg(A) pivot B")

QUERY(A2:E6,F2,FALSE)

구문

QUERY(데이터, 쿼리, 헤더)

  • 데이터 - 쿼리를 수행할 셀 범위입니다.

    • 데이터의 각 열에는 부울 값, 숫자(날짜/시간 유형 포함) 및 문자열 값만 허용됩니다.

    • 한 열에 여러 데이터 유형을 입력할 경우, 쿼리를 위해 가장 많은 데이터 유형을 열의 데이터 유형으로 판단합니다. 소수인 데이터 유형은 null 값으로 간주됩니다.

  • 쿼리 - 수행할 쿼리로, the Google Visualization API 쿼리 언어로 쓰여집니다.

    • 쿼리 값은 따옴표로 묶거나, 적절한 텍스트를 포함하는 셀에 대한 참조여야 합니다.

    • 쿼리 언어에 대한 자세한 내용은 https://developers.google.com/chart/interactive/docs/querylanguage 를 참조하세요.

  • 헤더 - [ 선택사항 ] - 데이터 상단의 헤더 행의 개수입니다. 값이 누락되거나 -1로 설정된 경우 데이터의 콘텐츠를 기반으로 추정됩니다.

예시 데이터

Select 및 Where 절을 사용하여 특정 조건을 충족하는 행을 반환합니다.

Select 및 Group by 절을 사용하여 Salary 값의 합계를 구합니다.

열의 특정 값을 변환하여 새 열에 삽입합니다.

행 전체의 Dept 값을 합쳐 Salary의 최대값으로 정렬합니다.

입력 범위의 헤더 행의 개수를 지정합니다. 그러면 여러 헤더 행이 있는 범위 입력이 단일 행의 헤더 입력으로 변환될 수 있습니다.

 


구글 스프레드 시트에서 QUERY 함수와 같이 사용할 수 있는 것으로 GROUP BY가 있습니다.

GROUP BY는 집계 함수로 합계, 평균, 최대값, 최소값등 지정 범위에 있는 값을 가지고 여러 집계를 구할 수 있습니다.

SQL과 같습니다.

 

  GROUP BY

=QUERY(범위, "SELECT 열, 집계함수(열) GROUP BY 열)

 

많이 사용하는 집계함수입니다.

함수 

 처리

 sum()

 합계

 avg()

 평균

 count()

 개수

 max()

 최대값

 min()

 최소값

 

GROUP BY와 count를 사용하여 날짜별로 데이터가 몇 개씩 있는지 값을 구해보도록 하겠습니다.

 

group by

 

 

 

사용 수식

=QUERY(A1:D15, "SELECT A, COUNT(A) GROUP BY A",true)

 

쿼리문을 해석할 때는 뒤에서부터 해석하는 것이 좋습니다.

GROUP BY에 A열을 지정하였습니다.

A열에 작성된 값을 그룹화 시킨다는 의미입니다.

즉, 같은 날짜별로 값이 묶이게 됩니다.

그룹화 시킨 데이터를 SELECT로 표시하고 있습니다.

먼저 A열을 출력하고 있습니다.

A열은 그룹화 시켰기 때문에 중복된 값은 제거되고 하나씩만 표시가 됩니다.

두 번째로는 집계 함수인 COUNT() 함수를 사용하여 개수를 표시하고 있습니다.

COUNT 함수에 A열을 지정하여 같은 날짜가 몇개씩 있는지 표시하고 있습니다.

 

  SUM 함계 함수

예제를 하나더 보도록 하겠습니다.

이번에는 많이 사용하는 SUM 함수를 사용하여 예제를 보겠습니다.

 

group by sum

 

 

사용 수식

=QUERY(A1:D15, "SELECT B, SUM(C) GROUP BY B",true)

 

이번에도 쿼리문을 뒤에서부터 보도록 하겠습니다.

GROUP BY에 B열을 지정하여 종류 별로 그룹화 시켰습니다.

SELECT에는 B 항목을 표시하도록 하고 있습니다.

GROUP BY 묶었기 때문에 중복된 값이 있어도 하나씩만 표시가 됩니다.

두번째로 SUM 함수로 종류별 판매 수량 합계를 구하고 있습니다.

날짜와 상관없이 판매 목록에서 같은 종류의 데이터만 묶어서 합계를 구하게 됩니다.


날짜 항목의 최대값 구하기

 

 

Raw Data Source:

---------------------------------------------------
| Data 1 |   Date      |  
---------------------------------------------------
| name 1  |  1/1/2018   | 
---------------------------------------------------
| name 1  |  1/2/2018   | 
---------------------------------------------------
| name 2  |  1/3/2018   | 
---------------------------------------------------
| name 2  |  1/5/2018   | 
---------------------------------------------------
| name 2  |  1/1/2018   | 
---------------------------------------------------
| name 2  |  1/2/2018   | 
---------------------------------------------------
| name 2  |  1/3/2018   | 
---------------------------------------------------
=query(range;"Select A,min(B), max(B) group by A label min(B) 'Min Date', max(B) 'Max Date' ")

 

일자 개체번호 상태 1 1900-01-01 3 4
2021-09-24 087393109 교배정보 7 2021-01-01 KPN1225 미분만
2021-09-24 087393109 교배정보 6 2020-01-26 KPN1254 20201110
2021-09-24 087393109 교배정보 5 2019-02-26 KPN1112 20191208
2021-09-24 087393109 교배정보 4 2018-03-06 KPN1069 20181227
2021-09-24 087393109 교배정보 3 2017-03-12 KPN1062 20180105
2021-09-24 087393109 교배정보 2 2016-04-11 KPN999 20170123
2021-09-24 087393109 교배정보 1 2015-02-28 KPN953 20151210
2021-09-24 087393109 분만정보 6 2020-01-26 KPN1254 2020-11-10
2021-09-24 087393109 분만정보 5 2019-02-26 KPN1112 2019-12-08
2021-09-24 087393109 분만정보 4 2018-03-06 KPN1069 2018-12-27
2021-09-24 087393109 분만정보 3 2017-03-12 KPN1062 2018-01-05
2021-09-24 087393109 분만정보 2 2016-04-11 KPN999 2017-01-23
2021-09-24 087393109 분만정보 1 2015-02-28 KPN953 2015-12-10
2021-09-24 090983447 교배정보 6 2020-09-14 KPN1212 20210703
2021-09-24 090983447 교배정보 6 2020-06-20 KPN1225 미분만
2021-09-24 090983447 교배정보 5 2019-06-03 KPN1112 20200322
2021-09-24 090983447 교배정보 4 2018-06-22 KPN1151 20190411
2021-09-24 090983447 교배정보 3 2017-06-20 KPN912 20180409
2021-09-24 090983447 교배정보 2 2016-05-29 KPN975 20170320
2021-09-24 090983447 교배정보 1 2015-07-09 KPN975 20160501
2021-09-24 090983447 분만정보 6 2020-09-14 KPN1212 2021-07-03
2021-09-24 090983447 분만정보 5 2019-06-03 KPN1112 2020-03-22
2021-09-24 090983447 분만정보 4 2018-06-22 KPN1151 2019-04-11
2021-09-24 090983447 분만정보 3 2017-06-20 KPN912 2018-04-09
2021-09-24 090983447 분만정보 2 2016-05-29 KPN975 2017-03-20
2021-09-24 090983447 분만정보 1 2015-07-09 KPN975 2016-05-01
2021-09-24 097388228 교배정보 5 2021-07-13 KPN1627 미분만
2021-09-24 097388228 교배정보 4 2020-06-23 KPN1225 20210409
2021-09-24 097388228 교배정보 3 2019-06-20 KPN1112 20200405
2021-09-24 097388228 교배정보 2 2018-06-28 KPN1006 20190411
2021-09-24 097388228 교배정보 2 2018-06-19 KPN1006 미분만
2021-09-24 097388228 교배정보 1 2017-06-06 KPN999 20180327
2021-09-24 097388228 교배정보 1 2016-05-28 KPN908 미분만
2021-09-24 097388228 분만정보 4 2020-06-23 KPN1225 2021-04-09
2021-09-24 097388228 분만정보 3 2019-06-20 KPN1112 2020-04-05
2021-09-24 097388228 분만정보 2 2018-06-28 KPN1006 2019-04-11
2021-09-24 097388228 분만정보 1 2017-06-06 KPN999 2018-03-27
2021-09-24 097429625 교배정보 5 2021-02-26 KPN1490 미분만
2021-09-24 097429625 교배정보 5 2021-01-12 KPN1572 미분만
2021-09-24 097429625 교배정보 5 2020-10-24 KPN1225 미분만
2021-09-24 097429625 교배정보 4 2019-09-30 KPN1202 20200715
2021-09-24 097429625 교배정보 3 2018-07-25 KPN1006 20190505
2021-09-24 097429625 교배정보 2 2017-08-01 KPN1034 20180524
2021-09-24 097429625 교배정보 1 2016-07-07 KPN975 20170417
2021-09-24 097429625 분만정보 4 2019-09-30 KPN1202 2020-07-15
2021-09-24 097429625 분만정보 3 2018-07-25 KPN1006 2019-05-05
2021-09-24 097429625 분만정보 2 2017-08-01 KPN1034 2018-05-24
2021-09-24 097429625 분만정보 1 2016-07-07 KPN975 2017-04-17
2021-09-24 104292681 교배정보 5 2021-04-13 KPN1333 미분만
2021-09-24 104292681 교배정보 5 2021-01-28 KPN1490 미분만
2021-09-24 104292681 교배정보 4 2020-01-24 KPN1254 20201113
2021-09-24 104292681 교배정보 3 2018-12-23 KPN1112 20191010
2021-09-24 104292681 교배정보 2 2017-12-27 KPN1046 20181011
2021-09-24 104292681 교배정보 1 2016-12-25 KPN1059 20171015
2021-09-24 104292681 분만정보 4 2020-01-24 KPN1254 2020-11-13
2021-09-24 104292681 분만정보 3 2018-12-23 KPN1112 2019-10-10
2021-09-24 104292681 분만정보 2 2017-12-27 KPN1046 2018-10-11
2021-09-24 104292681 분만정보 1 2016-12-25 KPN1059 2017-10-15
2021-09-24 105118108 교배정보 5 2021-07-13 KPN1627 미분만
2021-09-24 105118108 교배정보 4 2020-07-06 KPN1560 20210426
2021-09-24 105118108 교배정보 3 2019-06-09 KPN1112 20200325
2021-09-24 105118108 교배정보 3 2019-01-16 KPN1452 미분만
2021-09-24 105118108 교배정보 2 2018-01-06 KPN919 20181025
2021-09-24 105118108 교배정보 1 2017-01-06 KPN1055 20171028
2021-09-24 105118108 분만정보 4 2020-07-06 KPN1560 2021-04-26
2021-09-24 105118108 분만정보 3 2019-06-09 KPN1112 2020-03-25
2021-09-24 105118108 분만정보 2 2018-01-06 KPN919 2018-10-25
2021-09-24 105118108 분만정보 1 2017-01-06 KPN1055 2017-10-28
2021-09-24 113073114 교배정보 4 2020-12-01 KPN1225 20210922
2021-09-24 113073114 교배정보 4 2020-10-22 KPN1212 미분만
2021-09-24 113073114 교배정보 3 2019-12-12 KPN1189 20200930
2021-09-24 113073114 교배정보 2 2019-01-05 KPN1456 20191024
2021-09-24 113073114 교배정보 1 2018-01-24 KPN946 20181113
2021-09-24 113073114 분만정보 4 2020-12-01 KPN1225 2021-09-22
2021-09-24 113073114 분만정보 3 2019-12-12 KPN1189 2020-09-30
2021-09-24 113073114 분만정보 2 2019-01-05 KPN1456 2019-10-24
2021-09-24 113073114 분만정보 1 2018-01-24 KPN946 2018-11-13
2021-09-24 113077054 교배정보 4 2021-03-23 KPN1465 미분만
2021-09-24 113077054 교배정보 3 2020-03-14 KPN1254 20210102
2021-09-24 113077054 교배정보 2 2019-04-09 KPN1112 20200127
2021-09-24 113077054 교배정보 2 2019-01-05 KPN1456 미분만
2021-09-24 113077054 교배정보 1 2017-12-28 KPN1099 20181019
2021-09-24 113077054 분만정보 3 2020-03-14 KPN1254 2021-01-02
2021-09-24 113077054 분만정보 2 2019-04-09 KPN1112 2020-01-27
2021-09-24 113077054 분만정보 1 2017-12-28 KPN1099 2018-10-19
2021-09-24 114084737 교배정보 3 2020-10-02 KPN1212 20210720
2021-09-24 114084737 교배정보 2 2019-11-16 KPN1216 20200901
2021-09-24 114084737 교배정보 2 2019-08-06 KPN1249 미분만
2021-09-24 114084737 교배정보 1 2018-07-31 KPN1006 20190516
2021-09-24 114084737 교배정보 1 2018-06-13 KPN1151 미분만
2021-09-24 114084737 분만정보 3 2020-10-02 KPN1212 2021-07-20
2021-09-24 114084737 분만정보 2 2019-11-16 KPN1216 2020-09-01
2021-09-24 114084737 분만정보 1 2018-07-31 KPN1006 2019-05-16
2021-09-24 121803037 교배정보 3 2021-05-25 KPN1136 미분만
2021-09-24 121803037 교배정보 3 2021-04-15 KPN1333 미분만
2021-09-24 121803037 교배정보 3 2021-02-28 KPN1315 미분만
2021-09-24 121803037 교배정보 2 2020-01-11 KPN1254 20201102
2021-09-24 121803037 교배정보 1 2018-12-16 KPN1112 20191005
2021-09-24 121803037 교배정보 1 2018-11-26 KPN1006 미분만
2021-09-24 121803037 교배정보 1 2018-10-18 KPN1112 미분만
2021-09-24 121803037 교배정보 1 2018-09-09 KPN1112 미분만
2021-09-24 121803037 교배정보 1 2018-08-20 KPN1112 미분만
2021-09-24 121803037 분만정보 2 2020-01-11 KPN1254 2020-11-02
2021-09-24 121803037 분만정보 1 2018-12-16 KPN1112 2019-10-05
2021-09-24 125048082 교배정보 3 2021-02-10 KPN1490 미분만
2021-09-24 125048082 교배정보 3 2021-01-21 KPN1225 미분만
2021-09-24 125048082 교배정보 3 2020-12-30 KPN1225 미분만
2021-09-24 125048082 교배정보 2 2019-12-31 KPN1274 20201019
2021-09-24 125048082 교배정보 1 2019-01-11 KPN1452 20191025
2021-09-24 125048082 분만정보 2 2019-12-31 KPN1274 2020-10-19
2021-09-24 125048082 분만정보 1 2019-01-11 KPN1452 2019-10-25
2021-09-24 127742879 교배정보 3 2021-03-23 KPN1465 미분만
2021-09-24 127742879 교배정보 2 2020-02-26 KPN1254 20201209
2021-09-24 127742879 교배정보 1 2019-02-24 KPN1112 20191209
2021-09-24 127742879 분만정보 2 2020-02-26 KPN1254 2020-12-09
2021-09-24 127742879 분만정보 1 2019-02-24 KPN1112 2019-12-09
2021-09-24 130518303 교배정보 2 2020-12-01 KPN1225 20210921
2021-09-24 130518303 교배정보 2 2020-09-29 KPN1212 미분만
2021-09-24 130518303 교배정보 1 2019-11-29 KPN1274 20200916
2021-09-24 130518303 교배정보 1 2019-11-10 KPN1216 미분만
2021-09-24 130518303 교배정보 1 2019-10-02 KPN1202 미분만
2021-09-24 130518303 교배정보 1 2019-09-11 KPN1202 미분만
2021-09-24 130518303 교배정보 1 2019-08-22 KPN1249 미분만
2021-09-24 130518303 교배정보 1 2019-08-01 KPN1146 미분만
2021-09-24 130518303 분만정보 2 2020-12-01 KPN1225 2021-09-21
2021-09-24 130518303 분만정보 1 2019-11-29 KPN1274 2020-09-16
2021-09-24 133945030 교배정보 2 2021-07-26 KPN1329 미분만
2021-09-24 133945030 교배정보 2 2021-02-28 KPN1261 미분만
2021-09-24 133945030 교배정보 1 2020-02-22 KPN1274 20201212
2021-09-24 133945030 교배정보 1 2019-12-30 KPN1274 미분만
2021-09-24 133945030 분만정보 1 2020-02-22 KPN1274 2020-12-12
2021-09-24 133946725 교배정보 3 2021-09-20 KPN1352 미분만
2021-09-24 133946725 교배정보 2 2020-10-22 KPN1225 20210812
2021-09-24 133946725 교배정보 1 2019-11-03 KPN1216 20200818
2021-09-24 133946725 분만정보 2 2020-10-22 KPN1225 2021-08-12
2021-09-24 133946725 분만정보 1 2019-11-03 KPN1216 2020-08-18
2021-09-24 133961013 교배정보 2 2021-01-04 KPN1548 미분만
2021-09-24 133961013 교배정보 2 2020-11-26 KPN1225 미분만
2021-09-24 133961013 교배정보 1 2019-12-30 KPN1274 20201020
2021-09-24 133961013 분만정보 1 2019-12-30 KPN1274 2020-10-20
2021-09-24 138557274 교배정보 2 2021-04-15 KPN1333 미분만
2021-09-24 138557274 교배정보 1 2020-05-01 KPN1216 20210217
2021-09-24 138557274 분만정보 1 2020-05-01 KPN1216 2021-02-17
2021-09-24 138568404 교배정보 2 2021-05-15 KPN1465 미분만
2021-09-24 138568404 교배정보 1 2020-04-20 KPN1216 20210208
2021-09-24 138568404 분만정보 1 2020-04-20 KPN1216 2021-02-08
2021-09-24 145036787 교배정보 1 2021-08-09 KPN1358 미분만
2021-09-24 145036787 교배정보 1 2021-06-09 KPN1465 미분만
2021-09-24 145036787 교배정보 1 2021-05-20 KPN1136 미분만
2021-09-24 145036787 교배정보 1 2021-05-01 KPN1333 미분만
2021-09-24 145036787 교배정보 1 2021-04-12 KPN1465 미분만
2021-09-24 145036787 교배정보 1 2021-02-22 KPN1315 미분만
2021-09-24 145036787 교배정보 1 2021-01-12 KPN1573 미분만
2021-09-24 146719670 교배정보 1 2021-06-28 KPN1465 미분만
2021-09-24 146719670 교배정보 1 2021-04-05 KPN1465 미분만
2021-09-24 146719670 교배정보 1 2021-03-15 KPN1465 미분만
2021-09-24 146719670 교배정보 1 2021-02-22 KPN1315 미분만
2021-09-24 146719670 교배정보 1 2021-01-12 KPN1573 미분만
2021-09-24 146719670 교배정보 1 2020-12-23 KPN1225 미분만
2021-09-24 146719670 교배정보 1 2020-12-01 KPN1225 미분만
2021-09-24 148046305 교배정보 1 2021-08-13 KPN1337 미분만
2021-09-24 148046305 교배정보 1 2021-06-24 KPN1465 미분만
2021-09-24 148667016 교배정보 1 2021-09-02 KPN1358 미분만
2021-09-24 148667016 교배정보 1 2021-08-06 KPN1358 미분만
2021-09-24 148667016 교배정보 1 2021-07-11 KPN1352 미분만
2021-09-24 148667016 교배정보 1 2021-06-24 KPN1465 미분만
2021-09-24 156406521 교배정보 1 2021-09-18 KPN1329 미분만

 

 

 

쿼리

=(query(Select B,C,min(E) group by A,B,C label A'개체번호', C '일자',min(E) '작은날짜'),1)

 

결과)))

 
Select B,C,min(E) group by A,B,C label A'개체번호', C '일자',min(E) '작은날짜'
 
087393109 일자 작은날짜
     
087393109 교배정보 2015. 2. 28
087393109 분만정보 2015. 2. 28
090983447 교배정보 2015. 7. 9
090983447 분만정보 2015. 7. 9
097388228 교배정보 2016. 5. 28
097388228 분만정보 2017. 6. 6
097429625 교배정보 2016. 7. 7
097429625 분만정보 2016. 7. 7
104292681 교배정보 2016. 12. 25
104292681 분만정보 2016. 12. 25
105118108 교배정보 2017. 1. 6
105118108 분만정보 2017. 1. 6
113073114 교배정보 2018. 1. 24
113073114 분만정보 2018. 1. 24
113077054 교배정보 2017. 12. 28
113077054 분만정보 2017. 12. 28
114084737 교배정보 2018. 6. 13
114084737 분만정보 2018. 7. 31
121803037 교배정보 2018. 8. 20
121803037 분만정보 2018. 12. 16
125048082 교배정보 2019. 1. 11
125048082 분만정보 2019. 1. 11
127742879 교배정보 2019. 2. 24
127742879 분만정보 2019. 2. 24
130518303 교배정보 2019. 8. 1
130518303 분만정보 2019. 11. 29
133945030 교배정보 2019. 12. 30
133945030 분만정보 2020. 2. 22
133946725 교배정보 2019. 11. 3
133946725 분만정보 2019. 11. 3

 

 

B항목에서 특정개체번호 기준 , C항목에서 교배정보로 최초수정일자(최초의 분만일자) 구하는 쿼리

="Select min(E) WHERE (B='"&K2&"' AND C='교배정보') LABEL min(E) '최초수정일자'"

 

결과>>> 

최초수정일자
2019. 2. 24

 

 

 

 

 

 

 

최대 날짜를 원하는 셀의 기본 탭에 이 수식을 작성하고 있습니다.

 

 

 

 

 

"select A,B where F=date'2010-08-30'"

 

"select A,B where F=date '"&TEXT(E2,"yyyy-mm-dd")&"'"

 

"select A, B where F = date'"&E2&"'"
참고: 쿼리 수식에서 "yyyy-mm-dd" 형식을 따라야 합니다.

 

 

=query(sourcemaster,"select A,B,C,D,E,F where F = date '2010-08-30'")

 

 

 

=query(sourcemaster,"select A,B,C,D,E,F where F > date '1990-1-1' and F < date '2000-12-13'")

 

 

 

=query(sourcemaster,"select A,B,C,D,E,F where F = date '"&TEXT(H2,"yyyy-mm-dd")&"'")

 

 

 

select A,B,C,D,E,F where F = date'"&H3&"'")

 


 

Google SpreadSheet 쿼리: 열 헤더를 제거할 방법

해결책:

이 시도:

=QUERY(H4:L35,"select sum(L) where H='First Week' label sum(L) ''")

도움이 되기를 바랍니다!

 

=QUERY(QUERY(A1:D, "SELECT *", 1), "SELECT * OFFSET 1", 0)

 

쿼리문 안에 또 쿼리문이 있다.

쿼리문으로 추출한 결과를 다시 쿼리문을 써서 헤더행을 없애버린다.

 

참조할때 쓰기좋다.

 

외부 쿼리: "SELECT * OFFSET 1"첫 번째 행(헤더)을 제외합니다.

내부 쿼리는 ( 에 제공된 세 번째 인수를 통해) 헤더의 한 행을 명시적으로 지정 QUERY하지만 외부 쿼리는 없음을 지정합니다.

 
=INDEX(QUERY(H4:L35;"select sum(L) where H='First Week'"; -1),2,1)

이것은 반환된 배열을 구문 분석 하고 첫 번째 열에서 반환된 두 번째 레코드를 선택합니다.

계산 집약도가 낮은 필터 기능을 사용하여 이 작업을 수행할 수도 있습니다.

=SUM(FILTER(L4:L35, H4:H35 = "First Week"))