SQLのコード・関数を表一覧にしてみた

SQLで使用する構文や関数です。
毎回、何があるか思考が散らばってしまうので、一時見直しのためのメモです。
詳しい内容は、ググって調べるとする。

・SQLコード・関数　

コード・関数	内容	メモ
join	内部結合
left join	外部結合　左
right join	外部結合　右
full join	外部結合　全部
self join	同テーブル結合	結合のコードに　-1 を記述し1レコードずらす
cross join	同テーブル結合	総当たりで結合。３レコードあれば３×３で９レコードになる
infull	結合後のnullの置換	infull(フィールド、置換する文字・数字)
coalesce	結合後のnullの置換	coalesce(フィールド、置換するフィールド)
<, <> 非等値結合	マーケットバスケット分析	例：1id の買い物(三点買ったとする)の総組み合わせが表示される
union	集合演算子	aとbいずれかに含まれるという意味（和集合）
union distinct	集合演算子	重複を許さず、結果テーブルから除く　※重複とはselect句で取得している全フィールドの内容が一致。一致してなければ除外されない
union all	集合演算子	重複を許し、結果テーブルに残す
intersect	集合演算子	aとb両方に含まれる（積集合）
except	集合演算子	aとbに両方に一致するものは表示されない。一致しないaのレコードが表示される
with	仮想テーブルの作成 with 仮想テーブルの名前　as (仮想テーブルを作成するsql文)。複数作成することも可能
sum(if())	sum(if(値 + 比較演算子 ,数値が含まれているフィールド,0))	単一のフィールドで指定カテゴリの合計金額を出したいときに使う
if(比較演算子,1,0)	if(比較演算子,1,0)で特定の値にフラグ、目印をつける
unnest	GA4のカラム入れ子を解く RECORD型のデータ(１レコードに仮想的なテーブルが埋め込まれている)の入れ子を解く

数値系の関数↓

コード・関数	内容	メモ
floor()	切り捨て
ceil()	切り上げ
round()	四捨五入	round(フィールド、数字)　数字の箇所で四捨五入の下桁の数が決まる
abs()	絶対値を求める
mod()	割り算の余りを求める 例　mod(フィールド、フィールドの数を割り算する数字) =0
rand()	疑似乱数を発生させる 例　order by rand() limit 数字 で毎回絞り込む数字が変わる
cast()	データ型を変換する 例　cast(数字 as データ型)　　string, float64,int64,bool.date など

文字列の関数↓

コード・関数	内容	メモ
concat()	文字を結合する	例　concat(フィールド(文字系), "何かの文字") フィールドの値同士の結合も可能　文字列型以外の文字列も文字列とみなされるconcat(数値, "何かの文字",数値)
substr()	文字列の一部を取り出す	例　substar(対象文字列, 位置, 文字数)
left()	文字列を左から取り出す	例　left(フィールド文字, 取り出す文字の数)
right()	文字列を右から取り出す	例　right()フィールド文字,取り出す文字の数)
instr()	文字列の出現位置の整数	例　instr(対象文字列、検索文字列、検索開始位置、出現回数)　　substarと組み合わせて位置と文字数を指定することができる
strpos()	文字列の出現位置の整数(簡易版)	例　strpos(対象文字列, 検索文字列)
replace()	文字の置換	例　replace(対象文字列, 検索文字列, 置換後の文字列)
length()	文字列の長さを数値で取得する	例　length(文字列のフィールド)

正規表現の関数↓(メタ表現)

コード・関数	内容	メモ
regexp_contains()	指定した数値があるか判定r は正規表現を示す文字	例　regexp_contains(フィールド, r"\d{8}\d*| \d{4} - \d{4})")　\d は任意の数字、{8} は直前の文字が8回連続している。true,false が戻り値 \d*は0回以上の数字の繰り返し　最低８回連続した数字でさらに数字が続いてもよいという意味になる。\d{4} - \d{4}　は４回連続した数字と　- ４回連続した数字
regexp_extract(対象文字列、正規表現、検索開始位置、出現関数)	出現回数を指定するときにつかう
regexp_replace()	指定した文字列を置換する	regexp_replace(対象文字列、正規表現、置換後の文字列) regexp_replace(フィールド, r"\d{8}\d*

日付の関数↓

コード・関数	内容	メモ
current_date	date型 本日の日付　2012-01-01	例　current_date("asia/tokyo")もしくは"+9"で日本と指定する
current_datetime	datetime型	本日の日付と時間　2012/01/01 10:30:00
current_time	time型	本日の時間10:30:00
current_timestamp	timestampl型
date_add()	日付に一定の値を加える	date_add(date型の値、interval、加える整数 year/ quater/ month/ week/ day)
datetime_add()	日付・時間に一定の値を加える	datetime_add(date_time型の値、interval、加える整数 year/ quarter/ month/ week/ day/ hour/ minute/ second/ millisecond/ microsecond)
date	文字列を日付に変換する	例　select date "2019-05-01" as reiwa で文字列を日付に変換できる
datetime	文字列を日付・時間に変換する	例　datetime(2020,7,12,15,0,0) →　2020-07-12 T15:00:00
date_diff()	日付の差分を取得する	date_diff(date型の新しい日付、data型の古い日付、デイトパート)
datetime_diff()	日付・時刻の差分を取得する	datetime_diff(date型の新しい日付、data型の古い日付、パート)
date_trunc()	日付を丸める	date_trunc(date型の値、デイトパート) 　例
datetime_trunc()	日付・時間を丸める	datetime_trunc(datetime型の値、パート)　例　datetime_trunc(フィールド(日付), month)
extract()	日付の属性を指定する	extract(日付の属性　from date型の値) 日付の属性の種類　year, quarter , month, day,week,dayofyear(年の中の何日目かを表す整数),dayofweek(曜日を表す整数)
format_date	表示形式を指定するキーワード	format_date(表示形式を指定するキーワード, date型の値)
format_datetime	表示形式を指定するキーワード	format＿datetime(表示形式を指定するキーワード, datetime型の値）

統計集計関数↓

コード・関数	内容	メモ
var_pop/ var_samp	分散を求める 全数は pop 標本は samp　　例　select year var_pop(min_wage)
stddev_pop/ stddev_samp	標準偏差 全数は pop 標本は samp 例　select year stddev_pop(min_wage)
corr	2変数の相関係数を求める 正の相関か負の相関か。　1 から 0 から -1で表される
order byの特殊な使い方	"ORDER BY MOD(MAX(cast(day_number AS int64)), 7)　こう記述すると日曜から土曜まで曜日を順序良く表示できる"

まとめ

構文や関数の思考の紐付け的な役割のようなメモです