BigQueryで配列に添字をつける(複数の配列をzipする)

BigQueryのStandard SQLを使っていて配列から位置を指定して要素を取り出すことはできるのですが、UNNEST()を使ってそれぞれの要素を取り出した時にある要素が何番目かという情報を一緒に得る方法がわからなかったのでやり方を考えました(もしかしたら簡単に得る方法があるかも？)

余談ですが、最近BigQueryに触っていて意外とSQLでなんでも書けるなぁと思い始めてきました(BigQueryはWITHの再帰ができないのが残念ですが)

追記
添字を得るだけなら簡単に書けました
sucrose.hatenablog.com

CROSS JOINによる方法

配列の長さがわからないのでGENERATE_ARRAY(1, 100)のように1から十分な長さまでの配列を作って、それとCROSS JOINしてi番目の要素を取り出す関数のSAFE_ORDINAL()(0-indexedならSAFE_OFFSET())で配列から要素を取り出します

#StandardSQL
WITH data AS (SELECT SPLIT('a,b,c', ',') AS split)

SELECT
  i
  , split[SAFE_ORDINAL(i)] AS token
FROM data, UNNEST(GENERATE_ARRAY(1, 100)) AS i
WHERE NOT split[SAFE_ORDINAL(i)] IS NULL
ORDER BY i

同様に2つの配列の値を引いてくればzip的に使うこともできます

#StandardSQL
WITH 
  data1 AS (SELECT SPLIT('a,b,c', ',') AS split)
  , data2 AS (SELECT SPLIT('あ,い,う,え,お', ',') AS split)

SELECT
  i
  , data1.split[SAFE_ORDINAL(i)] AS token
  , data2.split[SAFE_ORDINAL(i)] AS token2
FROM data1, data2, UNNEST(GENERATE_ARRAY(1, 100)) AS i
WHERE NOT data1.split[SAFE_ORDINAL(i)] IS NULL
AND NOT data2.split[SAFE_ORDINAL(i)] IS NULL
ORDER BY i

UDF(ユーザー定義関数)による方法

BigQueryのSQLではJavaScriptによるUDFが動かせます

• Standard SQL User-Defined Functions  |  BigQuery  |  Google Cloud

以下のコード例のようにJavaScriptの関数を定義するだけで簡単に使い回しができます

#StandardSQL
CREATE TEMPORARY FUNCTION array_with_index(x ARRAY<STRING>)
RETURNS ARRAY<STRUCT<index INT64, value STRING>>
LANGUAGE js AS """
  return x.map(
    (value, index) => ({value, 'index': index + 1})
  );
""";

WITH data AS (SELECT SPLIT('a,b,c', ',') AS split)

SELECT
  array_with_index(split) AS result
FROM data

配列をzipする場合も同様にUDFを定義して使えばよいです(型名とカラム名を指定しないといけないのであまり汎用的にはならないですが)

#StandardSQL
CREATE TEMPORARY FUNCTION array_with_index(x ARRAY<STRING>, y ARRAY<STRING>)
RETURNS ARRAY<STRUCT<index INT64, value1 STRING, value2 STRING>>
LANGUAGE js AS """
  return x.map(
    (value, index) => ({'value1': value, 'value2': y[index], 'index': index + 1})
  );
""";

WITH 
  data1 AS (SELECT SPLIT('a,b,c', ',') AS split)
  , data2 AS (SELECT SPLIT('あ,い,う,え,お', ',') AS split)

SELECT
  array_with_index(data1.split, data2.split) AS result
FROM data1, data2

liftとPMI(Pointwise Mutual Information)

相関ルール分析(頻出パターンマイニング？)で使われるリフト値(lift)と、共起の強さを測るのに使われるPMIがほぼ同じものだなぁと思ったのでメモ(それ以上の内容はない)

相関ルール分析

Association rule learning - Wikipedia
相関ルール - 機械学習の「朱鷺の杜Wiki」

相関ルール分析は商品を購入したときのバスケットデータ分析やレコメンド的なのに使われる手法です
それぞれの1回分のデータ(購入など)をトランザクションと呼んで、あるアイテム\(X\)が含まれるトランザクションには\(Y\)も一緒に含まれていることを示すためのものです

基本的なやり方として以下のような数値を計算してこれらが適当に大きいものが関連のあるものと推定できるので推薦などに使います
1回のトランザクションにアイテム\(X\)が含まれる確率を\(P(X)\)としたとき
support(支持度)は2つのアイテム\(X, Y\)が1つのトランザクションに同時に含まれる確率を表しています
$$\mathrm{support}(X\Rightarrow Y) = P(X, Y)$$

confidence(確信度)は\(X\)を含むトランザクションに\(Y\)も含まれる確率です
$$\mathrm{confidence}(X\Rightarrow Y) = P(Y|X)$$

lift(リフト)はアイテム\(X, Y\)が1つのトランザクションに同時に含まれる確率が\(X\)と\(Y\)がそれぞれ含まれる確率が独立であると考えたときよりもどれぐらい大きいかを表します
$$\mathrm{lift}(X\Rightarrow Y) = \frac{P(X, Y)}{P(X) P(Y)}$$

PMI(Pointwise Mutual Information)

Pointwise mutual information - Wikipedia
自然言語処理における自己相互情報量 (Pointwise Mutual Information, PMI) | キャンベルとヨセミテ
PMIは2つの出来事が一緒に起こる度合いのことで(共起尺度)、これが偶然よりも大きいか小さいかを表しています
例えば自然言語処理では関係のある単語のペアか調べるのに使ったりします
式は以下のような感じで\(x, y\)の2つの出来事が起こる確率を、\(x,y\)それぞれの出来事が起こる確率で割ったものに\(\log\)関数を付けたものです

$$\mathrm{pmi}(x, y) = \log\frac{p(x,y)}{p(x)p(y)} = \log\frac{p(x|y)}{p(x)} = \log\frac{p(y|x)}{p(y)}$$

昔書いた記事ではこの辺の記事で使いました
sucrose.hatenablog.com
sucrose.hatenablog.com

liftとPMI

ここまで読んだ皆さんはわかったかと思いますが、2つの式を見比べるとlogがついているかということ以外はまったく同じ式です
$$\mathrm{lift} = \frac{P(X, Y)}{P(X) P(Y)}$$$$\mathrm{pmi}(x, y) = \log\frac{p(x,y)}{p(x)p(y)} = \log\frac{p(x|y)}{p(x)} = \log\frac{p(y|x)}{p(y)}$$
logは単調増加関数なのであるアイテムに対する推薦に使う分にはlogの有無で大小関係が変わりないのでliftを使ってもPMIを使っても同様の結果が得られるっぽいです