Database Reference
In-Depth Information
Wikipedia entries. It uses a subquery to figure out which titles have been
edited most often:
SELECT
a.title AS title,
edit_count,
MAX(num_characters) AS max_entry_size
FROM [publicdata:samples.wikipedia] b
JOIN (
SELECT
TOP(title, 100) AS title,
COUNT(*) AS edit_count
FROM [publicdata:samples.wikipedia]) a
ON a.title = b.title
GROUP BY
title,
edit_count
A common technique for composing queries in BigQuery is to start at the
inside of the query and work your way out. That is, if you have complex
queries with multiple joins and nested queries, you start by getting the inner
queries to work and then wrap them with layers of outer queries like an
onion. This way, each of the inner queries is something that you can run
on its own and hopefully test. If you try to compose the query outside in, it
is easy to get lost while you're composing the query. Strategies to speed up
and reduce the cost of experimentation are described in the “Data Sampling”
section later in this chapter.
Combining Tables: Implicit UNION and JOIN
In SQL, there are only two ways to combine data from multiple tables:
UNION
and
JOIN
.
UNION
takes the rows from multiple tables and appends
them into a single logical table.
JOIN
, however, takes the columns from
multiple tables and combines them into a single logical table.
UNION
is fairly
straightforward, but
JOIN
s come in many variants that control how the
table rows are matched when the columns are appended.
