Let’s Use Spreadsheet Query Function (4) WHERE Clause

クエリイメージ Spreadsheet
この記事は約17分で読めます。
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.)← this time
  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

Query function:Basic of WHERE Clause

About it is a WHERE clause, basically it is created to set the condition after selecting the extraction range with the SELECT statement. It is an image similar to the if/ifs part of the spreadsheet function sumif/sumifs.

Image
=query(data ,"select * where (column) = 'Target of extraction'")

Sample
=query(sample!A1:H7,"select * where B = 'Eng'") #Extract the data of Eng in the B column dept from the previously published data

The search method is mainly

  • Match (multiple match, OR match, forward match, backward match)
  • Inclusion (like, contains, etc.)
  • above below
  • Blank or not
  • Regular expression

classified as above. Following sections, I will explain one by one by checking the actual syntax method.

The original data to be used in the future has the link pasted, but I will paste the image as well as pasting it again.

URL Link: https://developers.google.com/chart/interactive/docs/querylanguage

元データ

WHERE Clause Use AND to match multiple conditions (logical product)

If you want to use multiple extraction conditions (image of sumifs function), set multiple extraction conditions in the WHERE clause with AND.

For example, let’s say you want to extract the people whose age is 35 and whose dept belongs to Eng. Feel like below.

=query(sample!A1:H7,"select * where B = 'Eng' AND F =35")

After where, B=XX and F=XX are connected by AND condition. With this, only the data satisfying both conditions can be extracted. As I will explain later, there are various conditions (large or small, Null does not appear, etc.) as well as =, so it is also possible to use it.

For example, if you want to extract people whose dept belongs to Eng and whose age is 30 years or older, it is as follows.

=query(sample!A1:H7,"select * where B = 'Eng' AND F >=30")

WHERE Clause OR is used for either condition match (logical sum)

This is an aggregation method that the spreadsheet function sumifs does not support by default, but you can easily do it in the WHERE clause. It is OK if you change the AND part to OR.

For example, let’s say you want to extract people whose dept belongs to Eng or whose age is under 30. Feel like below.

=QUERY(sample!A1:H7,"select * where B = 'Eng' or F <30")

WHERE Clause Prefix match is starts with/suffix match is ends with

Use “starts with” to search for lines that start with a particular letter/number/symbol.

For example, if dept starts with S, only data belonging to the Sales department will be extracted.

=QUERY(sample!A1:H7,"select * where B starts with 'S'")

Also, if you want to extract people who joined the company in 2005, you can extract hireDate starting with 2005.

=query(sample!A1:H7,"where E starts with 2005")

On the other hand, use “ends with” to find lines ending with a particular letter/number/symbol.

For example, ends with is useful when you want to extract only employees who joined the company on the 10th.

=query(sample!A1:H7,"where E ends with 10")

WHERE Clause Date for searching dates

Now, I want to extract the people who joined the company on October 10, 2005. Even if you specify the date using the WHERE clause, the expected result will not be displayed normally.

❌ =query(sample!A1:H7,"where E = 2005-10-10") #ValueError
❌ =query(sample!A1:H7,"where E = '2005-10-10'") #Nothing is extracted

This means that if you want to search for dates, you need to prefix date with your search criteria.

⭕️ =query(sample!A1:H7,"where E = date '2005-10-10'")

This is because if you do not add the date parameter, the entered condition (2005-10-10 in this case) will not be judged as a date, and the query function will judge that there is no matching value.

If you want to refer to the date from a cell instead of entering the date directly, use it in combination with the TEXT function.

⭕️ =query(sample!A1:H7,"where E = date '”& TEXT(cell range,”YYYY-MM-DD”) & “'")

Enclosing with &, the usage of TEXT function is very popular. Use it when you have a search condition input frame on the spreadsheet, so please familiarize yourself with it. If you do not know the TEXT function, I think that various articles will come out when you google it.

WHERE Clause Datetime of timeofday/timestamp type for searching time Also review other types

I introduced the Date type earlier, but until now, I’ve been looking at two types: string (character string)/number (number). So what is the list of data types used in the WHERE clause in the first place? Let’s take a look below.

Typedetail
stringstring : “aa”, ‘bb’ etc
numbernumber (positive/negative int) : 1, 1.0, -2 , -71 etc
booleantrue or false
datedate, yyyy-mm-dd:date “2005-10-10”
timeofday timeofday, HH:mm:ss: timeofday “12:30:45”
datetimedate and time, yyyy-mm-dd HH:mm:ss: datetime ‘2008-03-18 12:30:34.123’

Nuimber(Numerical values) and string(character strings) can be entered as they are, but regarding dates and times, it will not work unless you declare the data type such as date/timeofday/datetime before the search condition.

WHERE Clause Most used search method “LIKE”

You can use the LIKE parameter to extract data based on two wildcards.

Wilidcards

  1. %(Percent sign): Zero or more than 1 character
  2. _ (Underscore): Any one character

Search using Like and %

By using% that is recognized as a specific zero or more than one character, you can cover all prefix match, internal match and suffix match searches.

Prefix match

Suppose you want to find someone who joined the company in 2005. Since it is a prefix match, you can search for 2005+%.

=query(sample!A1:H7,"where E like '2005%'")

This makes same results with “starts with“.

Suffix match

Let’s search for people who joined the company on October 10th. It’s a backwards match, you can go with %+10-10.

=query(sample!A1:H7,"where E like '%10-10'")

This makes same results with “ends with“.

internal match

Let’s say you want to search for people who join the company on the 10th because it can be any month of 2005.

=query(sample!A1:H7,"where E like '2005%10'")
lLike search is just str/num search

Now, as you may have noticed, it was necessary to add a DATE parameter when searching for a date, but this time the LIKE parameter does not require a DATE parameter. It’s searching for a date, but the type you’re actually searching for is a number, not a date.

Therefore, when performing LIKE search, it is not necessary to change the search condition to DATE type.

Search using Like and _(underscore)

When using the underscore, use it when you know the number of characters or when you want to search for a phrase within a specific number of characters.

For example, if you want to search for a person whose salary is 3 digits, the form is as follows.

=query(sample!A1:H7,"where D like '___'") # three underscores

Of course, it is possible to combine% and _(underscore), so it is often used as a set.

WHERE CONTAINS search  Similar to LIKE search but a little different

It is similar to LIKE search, but a little different is CONTAINS search. It simply determines whether a specific character or symbol is included. Although it is possible to substitute for LIKE search, it is easy to use it for phrase search because you do not have to consider the conditions such as surrounding characters. After that, there is also a way to use it for those who are operating like data that includes a symbol used as a guide in the data (when ordering, attach a ★ to the project name).

=query(data ,"select * where A contains '★'")

WHERE Blank search “NULL” is null /is not null

iIf you use “is null“, you can extract only white space, and if you use “is not null“, you can extract only non-white space.

not blank(is not null)
=query(sample!A1:H7,"select * where H is not null")
blank(is null)
=query(sample!A1:H7,"select * where H is null")

Although not blank may not be used very often, is not null is often used when searching for data leaks.

WHERE Regular expression search “MATCHES”

You can use the MATCHES parameter to extract data based on the regular expression you set.

To be honest, except for “Multiple condition AND/OR”, “Date DATE”, and “Blank”, you can substitute only MATCHES.

For example, to extract only those who have three-digit income introduced in LIKE _ (underbar), you can substitute a regular expression as follows.

=query(sample!A1:H7,"where D matches '.{3}'") 

.. Means any one character, and {number} specifies the number of repetitions. There are many ways to write regular expressions, so I’d like to introduce a list somewhere. At this stage, I hope you can google it.

Summary

I have introduced a lot of rushing footage, but there are many. That’s why searching with WHERE is a major function in query.

Please try learning various patterns. I think there are some things that have similar functions, but in reality, there are differences in detailed specifications. I would like to explain it a little when the last of this series, 9th , data is dirty (with line breaks or different data types in the same column).

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

コメント