【BigQuery】集計関数メモ

1 COUNT（行数を数える）
2 SUM（合計を求める）
3 AVG（平均を求める）
4 MIN（最小値を求める）
5 MAX（最大値を求める）
6 STRING_AGG（文字列を1行にまとめる）

COUNT（行数を数える）

select
  count(num) -- 2
from
  (
    select 1    as num union all
    select 10   as num union all
    select null as num
  ) as t

select
  count(num) -- 2
from
  (
    select 1    as num union all
    select 10   as num union all
    select null as num
  ) as t

SUM（合計を求める）

select
  sum(num) -- 11
from
  (
    select 1    as num union all
    select 10   as num union all
    select null as num
  ) as t

select
  sum(num) -- 11
from
  (
    select 1    as num union all
    select 10   as num union all
    select null as num
  ) as t

AVG（平均を求める）

select
  avg(num) -- 5.5
from
  (
    select 1    as num union all
    select 10   as num union all
    select null as num
  ) as t

select
  avg(num) -- 5.5
from
  (
    select 1    as num union all
    select 10   as num union all
    select null as num
  ) as t

MIN（最小値を求める）

select
  min(num) -- 1
from
  (
    select 1    as num union all
    select 10   as num union all
    select null as num
  ) as t

select
  min(num) -- 1
from
  (
    select 1    as num union all
    select 10   as num union all
    select null as num
  ) as t

MAX（最大値を求める）

select
  max(num) -- 10
from
  (
    select 1    as num union all
    select 10   as num union all
    select null as num
  ) as t

select
  max(num) -- 10
from
  (
    select 1    as num union all
    select 10   as num union all
    select null as num
  ) as t

STRING_AGG（文字列を1行にまとめる）

select
  string_agg(str),      -- a,b,c
  string_agg(str, ''),  -- abc
  string_agg(str, '/'), -- a/b/c
from
  (
    select 'a' as str union all
    select 'b' as str union all
    select 'c' as str
  ) as t

select
  string_agg(str),      -- a,b,c
  string_agg(str, ''),  -- abc
  string_agg(str, '/'), -- a/b/c
from
  (
    select 'a' as str union all
    select 'b' as str union all
    select 'c' as str
  ) as t