Database Reference
In-Depth Information
All this is a round about way of saying that for the majority of queries,
repeated fields work the way you would expect. For many applications they
are the natural choice for modeling data and should be leveraged. The
remainder of this section describes nonstandard extensions to support
operations on repeated fields in the SQL variant supported by BigQuery.
These extensions are provided because some common operations on
repeated fields would be tedious to implement using standard SQL.
WITHIN
Consider the problem of finding the average memory used by the application
consuming the most memory in each record collected. This requires
computing the maximum memory over all apps in a single record. To do this
with the two relational tables previously introduced, you would perform a
nested
GROUP BY
query.
SELECT AVG(max_mem_usage) AS avg_of_max
FROM (
SELECT MAX(mem_usage) AS max_mem_usage
FROM Application GROUP BY record_Id)
This does not work with the nested schema because there is no explicit
record_id
field that can be used in the inner query. To support these kinds
of queries, BigQuery supports the
WITHIN
clause, which can be applied to
aggregation expressions in the
SELECT
clause.
<
aggregation
> WITHIN <RECORD|
record field
>
The
WITHIN
clause is used to narrow the scope of aggregation operators.
The default scope for an aggregation operator (
COUNT
,
SUM
,
MAX
, and so
on) is the
GROUP BY
clause if one is present; otherwise, its scope is all
the records that satisfy the
WHERE
clause. Instead of using the
GROUP BY
clause to create separate aggregation scopes, you can use the
WITHIN
clause
to specify scopes that correspond to the top-level record, with the
RECORD
keyword, or any nested record field. Each column in the
SELECT
clause
can specify a different aggregation scope using
WITHIN
or can be a
non-aggregate expression. This is in contrast to
GROUP BY
queries where
the only non-aggregate columns permitted are the fields present in the
GROUP BY
clause. The following query illustrates how to use the clause.
