Database Reference
In-Depth Information
Chapter 10
Advanced Queries
At this point, you should be familiar with BigQuery SQL (Chapter 7, “Running
Queries”) and how BigQuery SQL is executed (Chapter 10, “Understanding
Query Execution”). This chapter builds on those two topics, shows some
additional things you can do with BigQuery, and demonstrates how they
relate to the underlying architecture.
The chapter is divided into four portions:
•
Advanced SQL
: Describes how to use more advanced SQL constructs
(variants of
JOIN
, windowing functions, and so on) that are part of
standard SQL.
•
BigQuery SQL extensions
: Describes features in BigQuery, such as
queries over nested and repeated fields that do not exist in standard SQL.
It also relates these features to the BigQuery architecture.
•
Query Troubleshooting
: Gives some common errors encountered
when writing queries and some tricks to avoid those errors. There are
some cases where queries that seem like they should work actually fail;
this section describes why they fail and how to fix them.
•
Query Recipes
: Contains a number of recipes for answering common
questions in SQL, such as how to compute a trailing 3-day average and
how to perform cohort analysis.
You can read this chapter through directly, but it may also be a useful
reference guide. For example, the troubleshooting section can be helpful
when debugging a query that you think should run successfully but BigQuery
doesn't agree. The recipes section can be useful to refer to if you're struggling
with how to phrase your question in SQL. If you have never worked with SQL
before, you may want to look through a basic SQL tutorial before reading this
chapter because it assumes familiarity with the basics of SQL.
Advanced SQL
This section describes some more advanced SQL features that are available
in BigQuery. If you're a SQL guru, you might just want to skim and note
