Let’s Use Spreadsheet Query Function (2) Structure of query function

クエリイメージ Spreadsheet
この記事は約8分で読めます。
Advertisements

Target Readers

  • who are interested in SQL, but who are troublesome to prepare the environment and data from scratch
  • who are extracting data with vlookup, but who feel the limit
  • who want to analyze data
  • Even non-programmers who want to use queries
  • who are asking queries delicious? (meaning that the query is delicious)

Query function series planned (planned)

  1. Outline and purpose
  2. Syntax of query function ← this time
  3. Basic SELECT clause (get all, get only specific columns)
  4. Extraction basics Try using WHERE clause (numerical value match, size, date match, size etc.)
  5. Try using the GROUP BY clause
  6. Let’s bring data from another sheet ~ Combination with ImportRange function ~
  7. Sorting basics ORDER BY clause
  8. Required if you do SQL Try a subquery
  9. There are line breaks in the data. contains/starts with helps when you can’t handle like

How to use Query function (function structure)

Syntax of query function

QUERY(data, query, [header])

data    The range of cells to perform the query on(you can indicate other sheet data in same spreadsheet)
queryThe query to perform, written in Google Visualization API Query Language
this is main scope of this article( scope of studying)
headeroptional. The number of header rows at the top of data. If omitted or set to -1, the value is guessed based on the content of data.

The part of the query is “” (double quotation), and it is displayed by enclosing it in a form like “SELECT * ・・・”.

When setting the search target, if you use a character string without query, enclose it with” (single quotation).

For the time being, it’s quick to try one as a sample. The contents of the query can be either, so let’s look at the query function while comparing it with the table above. Underlined colors are supported.

=query(sample!A1:H7,"select * where E > date '2005-01-01' AND F >30 ")

You can see that the red underlined part is surrounded by the basic “”, and the date ‘2005-01-01’ in it is further surrounded by single quotation. Please remember that when you type a specific character in “”, enclose it with”.

Query(about Google Visualization API query language)

Not all general SQL can be used for the Query function, only the query syntax provided by Google Visualization API query can be used. Therefore, you cannot use DDL/DML of MySQL or Postgres, so be careful.

Then, what kind of query can be executed is described in the Google Visualization API document.

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

Clauseusage
selectSelects which columns to return, and in what order. If omitted, all of the table’s columns are returned, in their default order.
whereReturns only rows that match a condition. If omitted, all rows are returned.
group byAggregates values across rows.
pivotTransforms distinct values in columns into new columns.
order bySorts rows by values in columns.
limitLimits the number of returned rows.
offsetSkips a given number of first rows.
labelSets column labels.(overwrite)
formatFormats the values in certain columns using given formatting patterns.
optionsno_format, no_values, only 2 items(※As of November 2019.)

It is divided into major items, and it is possible to search with a basic query. Subqueries are not directly supported and need to be implemented in a query function specific way. Since there is no join function, it seems that data cannot be joined, but this can actually be done by holding data like {range 1; range 2 }.

We will talk more about the data connection relation in the 6th “Let’s bring data from other sheets ~ Combination with ImportRange function ~”.

Summary

It was my first time of this series, so I took a quick look at the syntax of the Query function. The image that you actually manipulate the data is a query and the data itself is processed by the data part.

We’ll cover the simple query syntax in several steps. After that, we will look at how to combine the data, and finally assume the flow of introducing some methods for dealing with dirty data.

First, prepare the data you want to handle in your spreadsheet. If you can’t find what you want to retrieve or want to aggregate, you will never know what kind of Query function you should make.

The function is just support for aggregation, and you should have the knowledge of the data to take.

Series: Let’s Use Spreadsheet Query Function

While not studying SQL, I will become accustomed to using the spreadsheet query function. We will study from the simple usage of select statement, including the difference from SQL.

  1. Let’s Use Spreadsheet Query Function (1) Outline and Purpose
  2. Let’s Use Spreadsheet Query Function (2) Structure of query function
  3. Let’s Use Spreadsheet Query Function (3) SELECT Clause
  4. Let’s Use Spreadsheet Query Function (4) WHERE Clause
  5. Let’s Use Spreadsheet Query Function (5) Group by
  6. Let’s Use Spreadsheet Query Function (6) Let’s bring data from another sheet ~ Combination with ImportRange function ~
  7. スプレッドシートQuery関数を使ってみよう ⑦並び替えの基本 ORDER BY句
  8. スプレッドシートQuery関数を使ってみよう ⑧SQLやるなら必須 Query関数でもサブクエリをやってみる
  9. スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策

コメント