Database Reference
In-Depth Information
SELECT AVG(max_mem_usage) AS avg_of_max
FROM (
SELECT MAX(running.memory.total) AS max_mem_usage
WITHIN RECORD
FROM [bigquery-e2e:ch10.sample_data])
When a field, rather than the top-level record, is specified in the clause,
the aggregation operator generates one value for each occurrence of the
specified field. You need to expand the schema a little further to illustrate
how field level aggregation is used. When you record the list of processes,
you also record the list of packages loaded by the process.
[
. . .,
{"name": "running", "type": "record", "mode":
"repeated", "fields": [
. . .,
{"name": "package", "type": "string", "mode":
"repeated"},
. . .
]}
]
Using this data you can compute the average memory used per package
loaded, broken down by application.
SELECT application_id,
AVG(mem_used / (num_pkgs + 1)) AS mem_per_pkg
FROM (
SELECT running.name AS application_id,
running.memory.total AS mem_used,
COUNT(running.package) WITHIN running AS
num_pkgs
FROM [bigquery-e2e:ch10.sample_data])
GROUP BY application_id
ORDER BY mem_per_pkg DESC
This query illustrates both field scoped aggregation and the mixing of
non-aggregate columns and aggregate columns in a single select expression.
Search WWH ::




Custom Search