Article Outline
BigQuery Scripting
TOC
Collection Outline
[BIGQUERY] 分析入門
[BIGQUERY] SQL
■ 句(節)
■ 演算子
■ BigQuery特有
■ 関数
- approx_count_distinct() - 集計近似
- coalesce()
- countif()
- extract()
- greatest()
- ifnull()
- last_day()
- lead() / lag()
- left() / right()
- lower()
- nullif()
- parse_date()
- row_number() - 番号付
- safe_cast()
- split()
- st_distance()
- nth_value()
- first_value() / last_value()
- percentile_()
データ型
■ エラー
- error
- Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex
[BIGQUERY] ADVANCE
[BIGQUERY] ML
[BIGQUERY] DS
■ DS100ノック
■
[BIGQUERY] GA
|| BigQuery Scripting
| 変数宣言
# 集計期間
decleare TERM_START string default '2020-01-01';
decleare TERM_END string default '2020-12-31';
decleare PREP_TS string;
set PREP_TS = (
select format_date('%Y%m%d', current_date('Asia/Tokyo')) as TODAY
);
with
, rsv as (
select * from `pj.ds.hoge_reservation_*` where _TABLE_SUFFIX = PREP_TABLE_SUFFIX
)
, output as (
select
MENU_ID
, count(distinct LOG_NO) as CV
from
rsv
where
RSV_DATE_TRUNC between datetime(TERM_START) and datetime(TERM_END)
)
select * from output;
cf.手続き型言語※1
|| Reference
- 手続き型言語 - GoogleCloud (※1)
- BigQuery ScriptingでPythonっぽいループ処理をしてみた - Qiita
- BigQuery ScriptingがBetaリリースされたので軽くウォークスルーしてみる - medium https://www.yasuhisay.info/entry/2022/03/14/093500
- BigQuery Scriptingの便利な使い方をまとめてみた - yasuhisa's blog