BigQueryで（擬似的に）集計関数や分析関数のUDFを作成する

CREATE OR REPLACE FUNCTION `project.dataset.gini`(arr ANY TYPE) AS (
(
with
    ranked as (
      select
          x
        , row_number() over (order by x desc) as rank
      from
        unnest(arr) as x
    )

select
  1 - 2 * sum(x * (rank - 1/2)) / count(*) / sum(x)
from
  ranked
)
);

select
    -- 通常の集計関数
    avg(x)
    -- UDFの集計関数
  , `dataset.gini`(array_agg(x))
from
  unnest(generate_array(0,100)) as x

CREATE OR REPLACE FUNCTION `project.dataset.context_id`(pref INT64, arr ANY TYPE) AS (
(
with
    wndw as (
      select 
          x
        , p
        , lag(x) over (order by p) as lagx
      from
        unnest(arr) as x
        with offset as p  -- position
    )
  , add_id as (
      select 
          *
        , countif(x != lagx) over (order by p) as id
      from
        wndw
    )
select
    id
from
  add_id
where 1 = 1
  and p = pref
)
);

select
    -- 通常の分析関数
    avg(x) over wndw
    -- UDFの分析関数
    -- row_numberは1-basedなので0-basedに直す
  , `dataset.context_id`(
      row_number() over wndw - 1,
      array_agg(x) over wndw
    )
from
  unnest([1,2,2,3,3,3,4,5,2,3]) as x
  with offset as p
window
  wndw as (order by p)