Google BigQuery Analytics

Chapter 10Advanced 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 ...