BigQuery のユーザ定義関数でベクトルの重み付き和を計算

BigQuery のユーザ定義関数でベクトルの重み付き和を計算する方法のメモ。
■ユーザ定義関数

create or replace function dataset.vector_wsum_int(wvs array<struct<w integer, v array<integer>>>)
returns array<integer> as (
  array(
    select
      sum(e * w)
    from
      unnest(wvs) as wv
      , unnest(wv.v) as e with offset idx
    group by
      idx
  )
);

■クエリ

select
  dataset.vector_wsum_int([
    struct(2 as w, [1, 2, 3, 4] as v),
    struct(3 as w, [2, 3, 4, 5] as v),
    struct(4 as w, [3, 4, 5, 6] as v)
  ])
;

■実行結果

[{
  "f0_": ["20", "29", "38", "47"]
}]

BigQuery のユーザ定義関数でベクトルの各要素に重み付け

BigQuery のユーザ定義関数でベクトルの各要素に重み付けをする方法のメモ。

■ユーザ定義関数

create function dataset.vector_weight_int(v array<integer>, m integer)
returns array<integer> as (
  array(
    select
      e * m
    from
      unnest(v) as e
  )
);

■テスト

select
  dataset.vector_weight_int([1, 2, 3, 4, 5], 3)
;

■実行結果

[{
  "f0_": ["3", "6", "9", "12", "15"]
}]