これからBigQuery を使っていくに際して、SQLの気を付けるべき点等、個人的な覚書としてここに残す。公開する意図としては、同じくSQLで迷う方がいるのでは？と思ったからである。
今回はSQLの基本形、集合関数、SQLの実行順序、その他関数、正規表現について触れる。
select 列 from テーブル ( SQLの基本形 )
select 列 from テーブル ;
テーブル（表）から必要な列を指定して列内のデータを表示させる。
*SQLの句や関数等は全て英語で記す。全て小文字で良い。
SQLの基本形＋where
where で列から特定のデータを抜き出す。
select 列 from テーブル where 列 検索句;
where number in(1,6)
number 列で1と6
where date between '2023-08-01' and '2023-08-31'
date 列で '2023-08-01' から '2023-08-31'まで
where name like '%子'
name列で子が付いているもの。後方一致
% : 0文字以上の任意の文字列
_ :任意の1文字
集合関数
count(number)
number列の行をカウントする。
sum(sales)
sales列の行を加算する。
*group by 句と併用する。
SQLの実行順序
SQLはselect から記述するが、実行はfromからである。
from
where
group by
集計関数（as 込みのケース有）
having
select 列（as 込みのケース有）
order by
*as で列名を作成可能。asは省略可。
*where は先に処理されるので、asで作成された内容が分からずエラーが表示される場合がある。
多彩な関数と用途
where mod(**,5) =0
5割り切れないの間引き条件
round(*****.****,-1)
1の位で四捨五入。例： round(1234.567,-1) = 1230.0
cast (*.** as int64) as int_number
少数の整数化
concat('Every ','Little ','Thing')
文字の結合。Every Little Thing
substring(***********,-5,5) as five_letters_right
右から5文字取得
count (distinct session) as visit_count,
訪問数カウント
*初回「1セッション」内のイベントが連続してカウントされている場合sessionは「１」が連続する。これをdistinctでvisit_countとしてまとめている。
datetime_diff(max(timestamp), min(timestamp), day) /count (distinct session_count) as avg_diff
timestamp の差を日単位で計算し、訪問数で割っている。
注意点：https://qiita.com/shiozaki/items/1212ed5f633774515d4a
正規表現
regexp_extract('03-0987-9876',r'([0-9]+)-')
03が抽出される。
regexp_extract_all('03-0987-9876',r'([0-9]+)-')
03と0978が別行で抽出される。
regexp_extract('google / organic',r'^(.+)/\s')
googleが抽出される。
regexp_replace('# Attention Please', r'^# ([a-zA-Z0-9\s]+$)', '<h1>\\1</h1>')
<h1>Attention Please</h1> が返る。
replace ('cherry pie', 'pie', 'cake')
cherry pieを cherry cake に変更する。
*正規表現ではない。参考情報。
regexp_contains(page, r'^/note/\?id=[0-9][0-9]?$')
正規表現に合致すれば「true」が返る。
