SQLを学ぶ上で関数の習得は必須です。
しかし全ての関数をマスターするには、関数の種類が多過ぎて困難でしょう。
本記事では、特に勉強し始めたばかりの初心者の方をターゲットに、業務でよく使う関数を紹介します。
なお、集計関数、Window関数、配列に関する関数、正規表現を用いる関数については、もっと勉強を進めてからの習得をオススメするため、別記事で解説する予定です。
細部の解説は公式リファレンスに任せます。
英語が難しければ翻訳機能を活用しましょう。
公式リファレンスの読み解き方
特に初心者の内は、公式リファレンスを読み解くよりも、先輩に教えてもらったり、実際に使われているクエリを参考に動かしてみる方が理解しやすいでしょう。
一方、将来初心者を抜け出す頃には、公式リファレンスを何となく読める程度になっていると、効率的なステップアップに繋がります。
特に注目した方が良いところだけ解説するので、時折調べてみましょう。
ABSという関数を１つ目の例とします。
上から順に、関数名、関数の書式、詳細説明（Description）、返却されるデータ型の説明（Return Data Type）、となっています。
関数名はそのままなので大丈夫ですよね。
関数の書式では、実際に使う時にどのように書いたら良いかを示しています。
「ABS」という関数名を書いた後、丸括弧で囲み、「X」と書いています。
このXは数値を示す引数として「X」という書き方で、詳細説明で紹介されています。
関数によって説明しやすい書き方になっているため、適宜読み替える必要があります。
詳細説明（Description）は翻訳機能を使うなどして目を通しましょう。
返却されるデータ型の説明（Return Data Type）では、その関数を使うことでどんな値が返却されるか説明しています。
ABS関数では、INT64型やFLOAT64型など複数のデータ型の引数に対応していて、引数と同じデータ型が返却されることが説明されています。
２つ目の例として、SUBSTR関数を使います。
１つ目の例のABS関数と比べ着目して欲しいポイントは、関数の書式の書き方と、最後に関数の使用例（Examples）が紹介されている点です。
まず関数の書式です。
「value」、「position」、「length」の３つの引数を使うように記載されていて、その内「length」のみ「[」「]」（角括弧）で囲まれています。
これは、「value」と「position」の２つの引数は必ず指定しなければならないが、３つ目の引数である「length」は指定しなくても使えることを意味します。
「[」「]」で囲まれた範囲は省略可、と覚えても良いです。
関数の使用例では、そのまま単体で実行して処理を確かめることができるクエリが紹介されています。
初心者の内は分かりにくいかもしれませんが、実際に動かしてみたり、一部を書き換えてみたりなどして試すのも良いでしょう。
計算に関する関数
SAFE_DIVIDE
割り算をする時は「/」（半角スラッシュ）を使いますが、分母が０になるとエラーになってしまいます。
SAFE_DIVIDE関数を使って割り算をすると、分母が０でない時の割り算は「/」と同じまま、分母が０の時はNULLを返しエラーになりません。
SELECT
-- 100 / 0, -- division by zero: 100 / 0
SAFE_DIVIDE(100, 0) -- NULL似た関数でSAFE_MULTIPLY関数がありますが、こちらはオーバーフロー（計算結果が非常に大きな桁数になり正しく処理できなくなる現象）によるエラーの時はNULLを返すもので、そこまで大きな桁数を取り扱う場面は少なく、使うことはほぼ無いでしょう。
MOD
割り算の余りを求める時に使います。
SELECT
MOD(11, 3), -- 11 / 3 の余り = 2
MOD(19, 5), -- 19 / 5 の余り = 4
MOD(100, 13), -- 100 / 13 の余り = 9
MOD(1188, 100) -- 1188 / 100 の余り = 88業務に使う時の例ですが、100円毎にスタンプ１個付与するキャンペーンを実施したとして、スタンプ付与に繋がらない余剰金額を集計する場面などで使えます。
MOD関数を使わずとも計算できますし、割り算の余りを算出したい場面も多くはないですが、割り算の余りを算出できる関数があることだけでも覚えておきましょう。
SELECT
MOD(1188, 100),
1188 - (100 * CAST(TRUNC(1188 / 100) AS INT64)) -- MOD関数を使わないと面倒で読みづらい書き方をすることになるCEIL/CEILING、FLOOR、TRUNC、ROUND
この４種類の関数では、小数点以下の値を操作できます。
CEIL関数、CEILING関数では、小数点以下を切り上げてくれます。
CEIL関数とCEILING関数は同じ挙動で、どちらの関数名を使っても大丈夫です。
FLOOR関数では、小数点以下を切り下げてくれます。
TRUNC関数では、小数点以下を０に近付けます。
正の値（プラスの値）であればFLOOR関数同様切り捨てられますが、負の値（マイナスの値）であればCEIL関数のように切り上げられます。
どの桁数で処理するかは指定することができますし、桁数指定を省略することもできます。
桁数指定を省略した場合は小数点以下を処理します。
ROUND関数では、指定した桁数で四捨五入してくれます。
桁数指定は省略することができて、省略すると小数点以下を四捨五入してくれます。
「ronding_mode」という丸めモード指定もできますが、これを指定する必要がある場面は稀なので、最初は覚えなくて良いでしょう。
SELECT
CEIL(1.5), -- 2.0
CEIL(-1.5), -- -1.0 -- 切り上げるので、負の値であれば０に近付く
FLOOR(1.5), -- 1.0
FLOOR(-1.5), -- -2.0 -- 切り下げるので、負の値であれば０から遠ざかる
TRUNC(1.5), -- 1.0
TRUNC(-1.5), -- -1.0
ROUND(1.5), -- 2.0
ROUND(-1.5) -- -2.0CEIL関数、FLOOR関数を使う時、負の値（マイナスの値）での挙動に注意する必要があります。
SELECT
CEIL(1.2345),
CEIL(1.2345 * 10) / 10, -- 小数点第二位で処理
CAST(CEIL(1.2345) AS INT64), -- INT型に直す場合
FLOOR(1.2345),
FLOOR(1.2345 * 100) / 100, -- 小数点第三位で処理
CAST(FLOOR(1.2345) AS INT64), -- INT型に直す場合
TRUNC(1.2345, 2), -- TRUNC関数では桁数を指定できる
ROUND(1.2345, 3) -- ROUND関数では桁数を指定できるCEIL関数、FLOOR関数で一の位や十の位を指定したい時や、小数点第二位や小数点第三位を指定したい時は一工夫必要です。
また、関数の処理結果をINT型で取り扱いたい場合はCASTなど使って直す型を必要があります。
一方、小数を直接CASTで型変換することができますが、この時の挙動はROUND関数同様の処理となります。
SELECT
ROUND(1.5), -- 2.0
ROUND(-1.5), -- -2.0
CAST(1.5 AS INT64), -- 2
CAST(-1.5 AS INT64) -- -2文字列編集に関する関数
LENGTH
文字数を数えてくれる関数です。
空文字の時は０になり、NULLの時はNULLを返します。
SELECT
LENGTH("ABCDEFG"), -- 7
LENGTH("あいうえお"), -- 5
LENGTH(""), -- 0
LENGTH(CAST(NULL AS STRING)) -- NULL何Byteか調べる時は工夫が必要ですが、業務で使うとしたらエンジニア側ですし、気にしなくて良いでしょう。
業務では間接的に使う場面が多いです。
例えば、文字列を操作する処理を書いた後、思ったとおりに処理されていそうかを確かめるために文字数で確認する時に使えます。
他にも、文章が入っているかもしれないカラムから、ある程度の文字数のレコードだけを試しに取り出してみて、どんな文章が入っているか見てみる、といった具合でしょうか。
LEFT、RIGHT、SUBSTR/SUBSTRING
文字列の一部を切り出します。
LEFT関数では文字列の左側、RIGHT関数では文字列の右側、SUBSTR関数、SUBSTRING関数では文字列の真ん中を切り出すことができます。
SUBSTR関数とSUBSTRING関数は同じ挙動で、どちらの関数名を使っても大丈夫です。
SELECT
LEFT("ABCDEFG", 3), -- 文字列の左側３文字を切り出す -- ABC
RIGHT("ABCDEFG", 3), -- 文字列の右側３文字を切り出す -- EFG
SUBSTR("ABCDEFG", 3, 4), -- 文字列の中で３文字目から４文字分切り出す -- CDEF
RIGHT(LEFT("ABCDEFG", 6), 4), -- 文字列の左側６文字を切り出してから右側４文字を切り出す -- CDEF -- SUBSTR関数と同様
SUBSTR("ABCDEFG", 1, 3), -- 文字列の中で１文字目から３文字分切り出す -- ABC -- LEFT関数と同様
SUBSTR("ABCDEFG", 5, 3), -- 文字列の中で４文字目から３文字分切り出す -- EFG -- RIGHT関数と同様
SUBSTR("ABCDEFG", LENGTH("ABCDEFG") - 4, 4), -- LENGTH関数を使って右側から数えた文字数を指定することもできる -- CDEF
SUBSTR("ABCDEFG", -5, 4), -- ２番目の引数で負の値を指定することによって、右から数えた時の順番で指定することもできる -- CDEF
SUBSTR("ABCDEFG", 5), -- ３番目の引数を指定しない時、２番目の引数で指定した文字以降の文字すべてを切り出す -- EFG -- RIGHT関数と同様
SUBSTR("ABCDEFG", -3) -- ２番目の引数で負の値を指定し、かつ、３番目の引数を指定しないことで、文字列の右側から３文字分切り出す -- EFT -- RIGHT関数と同様LEFT関数とRIGHT関数を組み合わせることでSUBSTR関数と同じ処理を行ったり、SUBSTR関数を使ってLEFT関数やRIGHT関数と同じ処理を行うこともできます。
つまり、LEFT関数とRIGHT関数の２つ、または、SUBSTR関数の１つだけ、使い方をマスターしてしまえば十分と言えます。
しかし読みやすさなど考慮すると、３種類ともマスターすることをオススメします。
例えば、「左から３文字だけ切り出す」といった処理を書いた時、SUBSTR関数で書くよりLEFT関数で書いた方が、意図が伝わりやすいです。
また、「３文字目から４文字分切り出す」といった処理を書いた時、LEFT関数とRIGHT関数を組み合わせて書くよりも、SUBSTR関数で書いた方が、意図が伝わりやすいです。
業務では、品番コードのような文字列から一部を抜き出す場面で使うことが多いでしょう。
SELECT
LEFT("123-4567-890", 3) AS code_1st, -- 123
SUBSTR("123-4567-890", 5, 4) AS code_2nd, -- 4567
RIGHT("123-4567-890", 3) AS code_3rd -- 890REPLACE
文字列から、検索文字列に一致した部分を置換します。
引数には、元となる文字列、検索したい文字列、一致したら置き換えたい文字列、以上３つを指定します。
SELECT
REPLACE("ABCDEFG", "BCDEF", "AAAAA"), -- AAAAAAG
REPLACE("私の名前は山田太郎です。", "山田太郎", "******"), -- 私の名前は******です。
REPLACE("a s d fgh j k l", " ", "") -- asdfghjkl -- 不要なスペースを削除TRIM
文字列の先頭や末尾に不要なスペースがある時、そのスペースを除去できます。
２つ目の引数を指定すると、スペースではなく指定した文字列で除去できます。
SELECT
TRIM(" 先頭と末尾に不要なスペースがあります。 "), -- 先頭と末尾に不要なスペースがあります。
TRIM(" 先頭と末尾と途中に 不要なスペースがあります。 "), -- 先頭と末尾と途中に 不要なスペースがあります。 -- 先頭と末尾のみ除去され、途中のスペースは除去されません。
TRIM("______先頭と末尾に不要なアンダーバーがあります。______", "_") -- 先頭と末尾に不要なアンダーバーがあります。似た関数で、LTRIM関数（先頭のスペースのみ除去）、RTRIM関数（末尾のスペースのみ除去）がありますが、これらを使い分ける場面はほぼ無いため、覚えずとも良いでしょう。
業務では、組織のデータ品質などにより使う場面の有無が左右されますが、たとえばフリーテキスト欄から入力された文字列で不要なスペースが多いケースなどで整形できます。
FORMAT
指定した書式で文字列を生成します。
引数を何個も指定することができて、その引数を文字列の中で様々な形で使うことができます。
例を見た方が分かりやすいでしょう。
SELECT
FORMAT("今日の天気は%sです。", "晴れのち曇り"), -- 今日の天気は晴れのち曇りです。
FORMAT("私の名前は%s %sです。", "山田", "太郎"), -- 私の名前は山田 太郎です。
FORMAT("これは数字の%dです。０埋め４桁にすると%04dです。", 10, 10), -- これは数字の10です。０埋め４桁にすると0010です。
FORMAT("Code : %03d-%04d-%06d", 12, 3456, 7890) -- Code : 012-3456-007890「%s」「%d」「%04d」などの特殊な指定が登場します。
「%s」は文字列を当てはめるところを意味し、「%d」はINT型の数値を当てはめるところを意味します。
「%04d」は「%d」に追加指定した形になっていて、「04」という書き方で０埋め４桁を指定しています。
「%03d」「%06d」はこれの応用で、０埋め３桁、０埋め６桁を意味します。
どこにどの引数が代入されるかは、引数を指定した順番に依存します。
他にも多種の指定ができるので、指定した書式にしたい時にリファレンスで使い方を調べてみると良いでしょう。
業務で利用する時の代表例は、ID（主キー、プライマリキー）のような文字列を自分で作る時です。
試しに、小中学校の生徒に一意なIDを付与するケースを考えてみましょう。
学校では、学年、クラス、出席番号の組み合わせが揃えば、一意に判断できます。
しかし、例えば出席番号のみだと、別の学年や別のクラスで同じ出席番号が登場します。
こういった時、「FORMAT(“%02d-%02d-%02d”, 学年, クラス, 出席番号)」のような形で一意なIDを生成できます。
日時操作に関する関数
日時を取り扱う関数は多数ありますが、組み合わせで覚えると比較的簡単です。
まず、取り扱うデータ型ですが、DATE型、TIME型、DATETIME型、TIMESTAMP型を覚えておきましょう。
DATE型では日付のみ取り扱えますし、DATETIME型では日付と時刻を取り扱うことができます。
TIMESTAMP型ではタイムゾーンまで取り扱えます。
TIME型は日付情報無く時刻のみとなるため、業務で使う場面は少ないでしょう。
取り扱うデータ型毎に、似た機能の関数が存在します。
例えば、CURRENT_DATE関数、CURRENT_DATETIME関数、CURRENT_TIMESTAMP関数です。
いずれも現在日時をDATE型、DATETIME型、TIMESTAMP型で取得する関数です。
このような組み合わせで覚えることができるので、まとめて覚えて省エネしましょう。
CURRENT_DATE、CURRENT_DATETIME、CURRENT_TIMESTAMP
CURRENT_DATETIME関数の公式リファレンスへのリンク
CURRENT_TIMESTAMP関数の公式リファレンスへのリンク
現在日時をDATE型、DATETIME型、TIMESTAMP型で取得する関数です。
引数無しで利用できますが、引数で地域を指定すると、その地域での日時に直してくれます。
なお、引数無しで利用した時のタイムゾーンはクエリの設定に左右されます。
SELECT
CURRENT_DATE(), -- 2023-04-01
CURRENT_DATETIME(), -- 2023-04-01T00:01:23.456789
CURRENT_TIMESTAMP(), -- 2023-04-01 00:01:23.456789 UTC
CURRENT_DATE("Asia/Tokyo"), -- 2023-04-01
CURRENT_DATETIME("Asia/Tokyo") -- 2023-04-01T09:01:23.456789DATE、DATETIME、TIMESTAMP
CURRENT_ほにゃららでは現在日時を取得できましたが、ここでは任意の日時をDATE型、DATETIME型、TIMESTAMP型で作る方法を紹介します。
DATE関数、DATETIME関数、TIMESTAMP関数のいずれも、複数の引数パターンに対応しています。
数値で指定したり、DATETIME型で指定したり、TIMESTAMP型で指定したり、などです。
例えば下記のような書き方があります。
使いやすい書き方を１種類覚えるだけで十分ではありますが、もしチームで書き方が決まっているようであれば合わせるようにしましょう。
SELECT
DATE(2023, 4, 1),
DATE("2023-04-01"),
DATE(CURRENT_DATETIME()),
DATE(CURRENT_TIMESTAMP(), "Asia/Tokyo"),
DATETIME(2023, 4, 1, 12, 34, 56),
DATETIME(DATE(2023, 4, 1), TIME(12, 34, 56)),
DATETIME(CURRENT_TIMESTAMP(), "Asia/Tokyo"),
TIMESTAMP("2023-04-01 12:34:56 UTC")DATE_ADD、DATE_SUB
日付を足したり引いたりします。
DATETIME_ADD関数などDATE型以外を取り扱う場合の関数もありますが、実際に多く使うのはDATE_ADD関数でしょう。
構文は、一つ目の引数にはDATE型、二つ目の引数はちょっと特殊で、「INTERVAL」と書いた後にINT型の数値を書いてDAYやWEEKなどの単位を指定します。
「INTERVAL」は決まり文句として覚えましょう。
単位には、DAY（日数）、WEEK（週＝７日）、MONTH（月）、QUARTER（四半期＝３か月）、YEAR（年）を指定できます。
DATETIME_ADD関数であれば時分秒なども指定できます。
INT型の数値を書くところには、負の値を指定することもできます。
DATE_ADD関数で-1を指定すれば、DATE_SUB関数で1を指定した時と同じになります。
業務では、チームによって書き方を統一する場合があるので、その時は合わせましょう。
DATE_SUB関数は使わずDATE_ADD関数の負の値で指定する形で統一する、DATE_SUB関数を使って正の値で指定する形で統一する、どちらもあり得ます。
SELECT
DATE_ADD(DATE("2023-04-01"), INTERVAL 1 DAY), -- 2023-04-02
DATE_SUB(DATE("2023-04-01"), INTERVAL 1 DAY), -- 2023-03-31
DATE_ADD(DATE("2023-04-01"), INTERVAL -1 DAY), -- 2023-03-31
DATE_SUB(DATE("2023-04-01"), INTERVAL -1 DAY), -- 2023-04-02
DATE_ADD(DATE("2023-04-01"), INTERVAL 2 WEEK), -- 2023-04-15
DATE_ADD(DATE("2023-04-01"), INTERVAL 4 MONTH), -- 2023-08-01
DATE_ADD(DATE("2023-04-01"), INTERVAL 1 QUARTER), -- 2023-07-01
DATE_ADD(DATE("2023-04-01"), INTERVAL 3 YEAR), -- 2026-04-01
DATE("2023-04-01") + 1, -- 2023-04-02
DATE("2023-04-01") - 1 -- 2023-03-31なお、単純に日数を加算するだけなら足し算でも解釈してくれます。
DATE_DIFF、DATETIME_DIFF、TIMESTAMP_DIFF
TIMESTAMP_DIFF関数の公式リファレンスへのリンク
二つのDATE型の値から、その差が何日かを算出します。
DATETIME型などでも使います。
日数でなく週や月でも算出できます。
関数を使えばINT型で返してくれますが、直接引き算で指定するとDATETIME型で返されますから、その違いには注意しましょう。
SELECT
DATE_DIFF(DATE("2024-04-01"), DATE("2023-04-01"), DAY), -- 366
DATE_DIFF(DATE("2024-04-01"), DATE("2023-04-01"), WEEK), -- 53
DATE_DIFF(DATE("2024-04-01"), DATE("2023-04-01"), MONTH), -- 12
DATE_DIFF(DATE("2023-04-01"), DATE("2024-04-01"), DAY), -- -366
DATE("2024-04-01") - DATE("2023-04-01"), -- 0-0 366 0:0:0
DATETIME_DIFF(DATETIME("2023-04-01 12:34:56"), DATETIME("2023-04-01 00:00:00"), SECOND), -- 45296
DATETIME("2023-04-01 12:34:56") - DATETIME("2023-04-01 00:00:00") -- 0-0 0 12:34:56日付の差を算出するとなると、現在日付と生年月日から年齢を算出する時に使いたくなる場面があるでしょう。
ただ、その年で誕生日を迎えたか否かの判定が欲しいため、少し厄介になります。
しかし処理が複雑になってしまうので、テクニックの一例ですが、日付を数値の形に直すことで引き算と割り算で算出することもあります。
参考までに具体例を紹介しておきます。
SELECT
DATE_DIFF(CURRENT_DATE(), DATE("2000-01-01"), YEAR), -- 2023-04-01時点では誕生日を迎えた後で23
DATE_DIFF(CURRENT_DATE(), DATE("2000-12-31"), YEAR), -- 2023-04-01時点では誕生日を迎える前で23（誕生日を迎えた後の年齢が算出されてしまっている）
DATE_DIFF(CURRENT_DATE(), DATE("2000-01-01"), YEAR) - IF(DATE_DIFF(CURRENT_DATE(), DATE(EXTRACT(YEAR FROM CURRENT_DATE()), EXTRACT(MONTH FROM DATE("2000-01-01")), EXTRACT(DAY FROM DATE("2000-01-01"))), DAY) < 0, 1, 0), -- 23
DATE_DIFF(CURRENT_DATE(), DATE("2000-12-31"), YEAR) - IF(DATE_DIFF(CURRENT_DATE(), DATE(EXTRACT(YEAR FROM CURRENT_DATE()), EXTRACT(MONTH FROM DATE("2000-12-31")), EXTRACT(DAY FROM DATE("2000-12-31"))), DAY) < 0, 1, 0), -- 22
CAST(FLOOR((CAST(FORMAT_DATE("%Y%m%d", CURRENT_DATE()) AS INT64) - CAST(FORMAT_DATE("%Y%m%d", DATE("2000-01-01")) AS INT64)) / 10000) AS INT64), -- 23
CAST(FLOOR((CAST(FORMAT_DATE("%Y%m%d", CURRENT_DATE()) AS INT64) - CAST(FORMAT_DATE("%Y%m%d", DATE("2000-12-31")) AS INT64)) / 10000) AS INT64), -- 22DATE_TRUNC
日付から、指定した単位の区間での最初の日付を取得します。
単位の指定は２つ目の引数で、YEAR（年）やMONTH（月）などを指定します。
たとえばMONTHを指定すれば、その月の最初の日付を取得できます。
WEEK（週）を指定すれば日曜日始まり基準で初日を取得できますが、「WEEK(MONDAY)」などで指定すれば、日曜日以外を始まりとして取得することもできます。
日付計算の時に使うことが多いので覚えておきましょう。
SELECT
DATE_TRUNC(DATE("2023-06-30"), YEAR), -- 2023-01-01
DATE_TRUNC(DATE("2023-06-30"), QUARTER), -- 2023-04-01
DATE_TRUNC(DATE("2023-06-30"), MONTH), -- 2023-06-01
DATE_TRUNC(DATE("2023-06-30"), WEEK), -- 2023-06-25
DATE_TRUNC(DATE("2023-06-30"), WEEK(MONDAY)), -- 2023-06-26
DATE_TRUNC(DATE("2023-06-30"), WEEK(SUNDAY)) -- 2023-06-25LAST_DAY
日付から、指定した単位の区間での最終日を取得します。
DATE_TRUNC関数の逆の機能になります。
日付から、指定した単位の区間での最終日を取得します。
単位の指定は２つ目の引数で、YEAR（年）やMONTH（月）などを指定します。
たとえばMONTHを指定すれば、その月の最後の日付を取得できます。
WEEK（週）を指定すれば日曜日始まり基準で最終日を取得できますが、「WEEK(MONDAY)」などで指定すれば、日曜日以外を始まりとして取得することもできます。
こちらも、日付計算の時に使うことが多いので覚えておきましょう。
SELECT
LAST_DAY(DATE("2023-05-01"), YEAR), -- 2023-12-31
LAST_DAY(DATE("2023-05-01"), QUARTER), -- 2023-06-30
LAST_DAY(DATE("2023-05-01"), MONTH), -- 2023-05-31
LAST_DAY(DATE("2023-05-01"), WEEK), -- 2023-05-06
LAST_DAY(DATE("2023-05-01"), WEEK(MONDAY)), -- 2023-05-07
LAST_DAY(DATE("2023-05-01"), WEEK(SUNDAY)) -- 2023-05-06EXTRACT
EXTRACT関数（DATE型）の公式リファレンスへのリンク
EXTRACT関数（DATETIME型）の公式リファレンスへのリンク
EXTRACT関数（TIMESTAMP型）の公式リファレンスへのリンク
日時から、月や日などの一部をINT型の数値として取得します。
指定が少し特殊です。
例えば「MONTH FROM」「MINUTE FROM」といった形で、単位の後ろに「FROM」と書いて、その後日時を指定します。
SELECT
EXTRACT(YEAR FROM DATE("2023-04-01")), -- 2023
EXTRACT(MONTH FROM DATE("2023-04-01")), -- 4
EXTRACT(HOUR FROM DATETIME("2023-04-01 12:34:56")), -- 12
EXTRACT(MINUTE FROM DATETIME("2023-04-01 12:34:56")), -- 34
EXTRACT(SECOND FROM DATETIME("2023-04-01 12:34:56")) -- 56活用の一例として、閏年の判定と偶数月・奇数月の判定を用意してみました。
SELECT
IF(MOD(EXTRACT(YEAR FROM DATE("2023-04-01")), 4) = 0 AND NOT (MOD(EXTRACT(YEAR FROM DATE("2023-04-01")), 100) = 0 AND MOD(EXTRACT(YEAR FROM DATE("2023-04-01")), 400) <> 0), "閏年です。", "閏年ではないです。"), -- 閏年ではないです。
IF(MOD(EXTRACT(YEAR FROM DATE("2024-04-01")), 4) = 0 AND NOT (MOD(EXTRACT(YEAR FROM DATE("2024-04-01")), 100) = 0 AND MOD(EXTRACT(YEAR FROM DATE("2024-04-01")), 400) <> 0), "閏年です。", "閏年ではないです。"), -- 閏年です。
IF(MOD(EXTRACT(YEAR FROM DATE("2100-04-01")), 4) = 0 AND NOT (MOD(EXTRACT(YEAR FROM DATE("2100-04-01")), 100) = 0 AND MOD(EXTRACT(YEAR FROM DATE("2100-04-01")), 400) <> 0), "閏年です。", "閏年ではないです。"), -- 閏年ではないです。
IF(MOD(EXTRACT(YEAR FROM DATE("2000-04-01")), 4) = 0 AND NOT (MOD(EXTRACT(YEAR FROM DATE("2000-04-01")), 100) = 0 AND MOD(EXTRACT(YEAR FROM DATE("2000-04-01")), 400) <> 0), "閏年です。", "閏年ではないです。"), -- 閏年です。
IF(MOD(EXTRACT(MONTH FROM DATE("2023-04-01")), 2) = 0, "偶数月", "奇数月") -- 偶数月FORMAT_DATE
日付を、指定したフォーマットの文字列に直します。
一つ目の引数でフォーマット、二つ目の引数で日付を指定して使います。
フォーマットの書き方が少し特殊で、例えば西暦年４桁を使いたい時は「%Y」と書きます。
色々な指定方法があるので、必要に応じてリファレンスを確認しましょう。
SELECT
FORMAT_DATE("%Y/%m/%d", DATE("2023-04-01")), -- 2023/04/01
FORMAT_DATE("%Y年%m月%d日", DATE("2023-04-01")), -- 2023年04月01日
FORMAT_DATE("%Y年%m月度", DATE("2023-04-01")), -- 2023年04月度
FORMAT_DATE("%y%m%d", DATE("2023-04-01")) -- 230401PARSE_DATE
FORMAT_DATE関数の逆で、決まったフォーマットの文字列から日付を抽出します。
SELECT
PARSE_DATE("%Y/%m/%d", "2023/04/01"), -- 2023-04-01
PARSE_DATE("%Y年%m月%d日", "2023年04月01日"), -- 2023-04-01
PARSE_DATE("%Y年%m月度", "2023年04月度"), -- 2023-04-01
PARSE_DATE("%y%m%d", "230401") -- 2023-04-01その他の関数
CAST、SAFE_CAST
データ型変換のための関数です。
詳細はリンク先で解説していますので、そちらをご参照ください。
データ型を理解するための記事を設置予定
IF、IFNULL、CASE文
条件分岐のための関数です。
詳細はリンク先で解説していますので、そちらをご参照ください。
条件式を理解するための記事を設置予定
振り返り
お疲れさまでした。
丸暗記しようとすると大変でしょうから、使う時に調べられるように、どんな関数をよく使うかだけは把握しておきましょう。
次は、BigQueryでよく使う操作として、テーブルやビューを保存する操作を紹介します。
ここまで習得すれば、単純な操作は一通りマスターすることになります。
BigQueryでテーブル・ビューを保存する操作を説明する記事を設置予定