BigQuery UDF
説明を書く
CREATE FUNCTION foo(input STRING) RETRUNS STRING OPTIONS(description="ここに説明") AS ... で書ける
AS 以降 に SQL コメントとして書いたほうが分かりやすいかもしれない
置き場
LIMIT & OFFSET を変数にしたい
変数を渡せないので ROW_NUMBER を振って必要な範囲を取る
code:limitAndOffset.sql
CREATE TEMP FUNCTION pick(limit_ INT64, offset_ INT64)
RETURNS ARRAY< INT64 > AS (
ARRAY(
SELECT
n
FROM
(
SELECT
n,
ROW_NUMBER() OVER (
ORDER BY n) AS idx -- 1 origin
FROM
UNNEST(GENERATE_ARRAY(1, 100, 1)) AS n
)
WHERE
offset_ < idx AND idx <= offset_ + limit_
)
);
SELECT
pick(2, 0) AS num;
文字列配列から正規表現文字列を作る
code:assembleRegExp.sql
CREATE TEMP FUNCTION assembleRegExp(words ARRAY<STRING>)
RETURNS STRING
LANGUAGE js AS r"""
function escapeRegExp(str) {
return str.replace(/[.*+?^=!:${}()|\\/\\]/g, "\\$&"); }
return (?:${words.map(escapeRegExp).join("|")})
"""
フォーマッタにかけると assembleRegExp → assembleRegEXP にされて最悪
与えた期間を interval ごとに分割した期間をつくる
since ~ until の間を 1日ごとに区切った bucket を作りたい
末尾の bucket が 1日未満の場合は末尾の時間で埋める
code:range_bucket.sql
CREATE TEMP FUNCTION TARGET_BUCKETS(since TIMESTAMP, until TIMESTAMP)
RETURNS ARRAY< STRUCT< since TIMESTAMP, until TIMESTAMP > > AS (
ARRAY(
SELECT AS STRUCT
since,
until
FROM
(
SELECT
step AS since,
-- GENERATE~ で末尾が埋まらない場合は入力の until を埋める
IFNULL(LEAD(step, 1) OVER (
ORDER BY step), until) AS until
FROM
UNNEST(GENERATE_TIMESTAMP_ARRAY(since, until, INTERVAL 1 DAY)) AS step
)
WHERE
since != until
)
);