Database Reference
In-Depth Information
the following section for calculating concurrency of operations is an example
of how you can design the join so that the number of intermediate rows is
manageable.
Regrettably, there is no simple prescription for dealing with these errors.
Hopefully, we have at least given you enough information to identify the
parts of the query that are causing the error and ideas for how to work
around the error. Fortunately, as the execution engine evolves over time and
expands its capabilities, these errors will be less common and you can forget
this section!
Recipes
This section is organized as a grab bag of queries that are useful in reporting
applications. We have tried to concentrate on queries that look fairly
different from a similar query over data stored in a relational database. It
is worth skimming this chapter to get a sense for the types of reports being
addressed. That way, when you do encounter a report that seems tricky to
generate using BigQuery, you will know if this section has a pertinent recipe
and can return to it to look up the details.
Pivot
The operation of pivoting or transposing a table is common when shaping
data for reports or visualization. In SQL the natural way to generate totals
broken down by multiple dimensions is to use a grouping clause with
required dimensions. This works well because it generalizes to an arbitrary
number of dimensions. However, when working with two dimensions,
especially when one dimension has only a handful of possible values, it is
useful to have a column for each value of the dimension. For example, you
might be interested in finding the longest 100 words across the plays of
Shakespeare together with the counts for how often they appear in each
work.
SELECT word, corpus, corpus_total
FROM (
SELECT
word,
LENGTH(word) AS word_len,
corpus,
