Let’s Use Spreadsheet Query Function (8) Required if you do SQL!! Try a subquery with the Query function

クエリイメージ Spreadsheet
この記事は約6分で読めます。
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
  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 ← this time
  9. There are line breaks in the data. contains/starts with helps when you can’t handle like

Query:what is sub-query?

A subquery is a method that allows you to embed a SELECT statement in an SQL statement and use it as an extraction condition. In pure SQL, join works, so it is very effective.

It is possible to execute subquery-like behavior even in the Query function. When asked if I can make a complete subquery, what is my current knowledge? ?? There is a part that becomes, but let’s fold the subquery-like behavior into the query function as far as we can understand.

Subquery-like query construction

Simply put, the conditional clause of the query is created in the form of “& {function} &”. Let’s take a look at the actual query anyway.

=query(A1:H7,"select * where F >"& average(F2:F6)*1.1 &"")

In the above example, average () is used for the function part of “& _ {function} _ &” to do ✖️1.1. Only data larger than the average value in the range multiplied by 1.1 is extracted.

This average part is purely a spreadsheet function, so you can use any data format that doesn’t have any query clause problems (type matching).

Do you remember seeing something similar in the series? I used TEXT to refer to a cell in the 4th WHERE clause. Please refer to the link for the relevant article.

Even when the cell data uses this TEXT, the data extraction condition was actually made into a function with “& {function} &”. It means that subquery-like processing can be performed by applying it.

SUMMARY

If you use a lot of subqueries in the Query function, there is a drawback that the query function becomes longer and harder to read. It’s easy for SQL queries because it’s OK to start a new line.

Spreadsheets are required to make the basic data shape easy to understand. It’s easy to get used to adding conditions that can be searched by subqueries, but if you review the data format in the first place, you may be able to achieve what you want even if the function itself is very simple.

Next time, the final episode of the series, I will introduce the troubles caused by troubled data and their solutions.

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. Let’s Use Spreadsheet Query Function (7) Sorting basics “ORDER BY” clause
  8. Let’s Use Spreadsheet Query Function (8)Required if you do SQL!! Try a subquery with the Query function
  9. Let’s Use Spreadsheet Query Function (9)line breaks in the data etc. Query function error countermeasures

コメント