Go to Qiita Advent Calendar Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@yo2158(yo 2158)

SQL関数クロスリファレンス - ビジネスロジック系関数

Last updated at Posted at 2025-09-28

ページ: 目次 | ← 前の記事 | 次の記事 →

BigQuery、Snowflake、Treasure Data、Athena、MySQL、Oracleのビジネスロジック編について、構文の違いや注意点をまとめました。

📋 関数一覧

満年齢計算

BigQuery (INT64)

CAST(FLOOR((CAST(FORMAT_DATE('%Y%m%d', CURRENT_DATE()) AS INT64) - CAST(FORMAT_DATE('%Y%m%d', birth_date) AS INT64)) / 10000) AS INT64)

Snowflake (NUMBER)

FLOOR((CAST(TO_CHAR(CURRENT_DATE(), 'YYYYMMDD') AS INTEGER) - CAST(TO_CHAR(birth_date, 'YYYYMMDD') AS INTEGER)) / 10000)

Treasure Data (BIGINT)

CAST(FLOOR((CAST(date_format(current_date, '%Y%m%d') AS BIGINT) - CAST(date_format(birth_date, '%Y%m%d') AS BIGINT)) / 10000) AS BIGINT)

Athena (BIGINT)

CAST(FLOOR((CAST(date_format(current_date, '%Y%m%d') AS BIGINT) - CAST(date_format(birth_date, '%Y%m%d') AS BIGINT)) / 10000) AS BIGINT)

MySQL (BIGINT)

CAST(FLOOR((CAST(DATE_FORMAT(CURDATE(), '%Y%m%d') AS SIGNED) - CAST(DATE_FORMAT(birth_date, '%Y%m%d') AS SIGNED)) / 10000) AS SIGNED)

Oracle (NUMBER)

FLOOR((TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')) - TO_NUMBER(TO_CHAR(birth_date, 'YYYYMMDD'))) / 10000)

Tips

  • BigQuery: 数値計算式。DATE_DIFFは誕生日前でも年差を返すため不正確
  • Snowflake: 数値計算式。DATEDIFF年差は誕生日前でも加算されるため不正確
  • Treasure Data: 数値計算式。DATE_DIFF年差は誕生日前でも加算されるため不正確
  • Athena: date_formatの書式は'%Y%m%d'。数値に変換して満年齢を計算。
  • MySQL: 数値計算式。TIMESTAMPDIFF年差は誕生日前でも加算されるため不正確
  • Oracle: 数値計算式。MONTHS_BETWEENは正確だが、統一性のため数値式採用

年度計算

BigQuery (INT64)

IF(EXTRACT(MONTH FROM date) >= 4, EXTRACT(YEAR FROM date), EXTRACT(YEAR FROM date) - 1)

Snowflake (NUMBER)

IFF(MONTH(date) >= 4, YEAR(date), YEAR(date) - 1)

Treasure Data (BIGINT)

CASE WHEN MONTH(date) >= 4 THEN YEAR(date) ELSE YEAR(date) - 1 END

Athena (BIGINT)

CASE WHEN MONTH(date) >= 4 THEN YEAR(date) ELSE YEAR(date) - 1 END

MySQL (INT)

IF(MONTH(date) >= 4, YEAR(date), YEAR(date) - 1)

Oracle (NUMBER)

CASE WHEN EXTRACT(MONTH FROM date) >= 4 THEN EXTRACT(YEAR FROM date) ELSE EXTRACT(YEAR FROM date) - 1 END

Tips

  • BigQuery: 4月開始年度
  • Snowflake: 4月開始年度
  • Treasure Data: 4月開始年度
  • Athena: 4月開始年度
  • MySQL: 4月開始年度
  • Oracle: 4月開始年度

月末締日計算

BigQuery (DATE)

LAST_DAY(date, MONTH)

Snowflake (DATE)

LAST_DAY(date)

Treasure Data (DATE)

LAST_DAY_OF_MONTH(date)

Athena (DATE)

LAST_DAY_OF_MONTH(date)

MySQL (DATE)

LAST_DAY(date)

Oracle (DATE)

LAST_DAY(date)

Tips

  • BigQuery: その月の最終日
  • Snowflake: その月の最終日
  • Treasure Data: その月の最終日
  • Athena: その月の最終日
  • MySQL: その月の最終日
  • Oracle: その月の最終日

メールアドレスバリデーション

BigQuery (BOOL)

REGEXP_CONTAINS(email, r'^[a-zA-Z0-9.\!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:[.][a-zA-Z0-9-]+)+$')

Snowflake (BOOLEAN)

REGEXP_LIKE(email, '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\\.[a-zA-Z0-9-]+)+$')

Treasure Data (BOOLEAN)

REGEXP_LIKE(email, '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\\.[a-zA-Z0-9-]+)+$')

Athena (BOOLEAN)

REGEXP_LIKE(email, '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\\.[a-zA-Z0-9-]+)+$')

MySQL (BOOLEAN)

REGEXP_LIKE(email, '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\\.[a-zA-Z0-9-]+)+$')

Oracle (BOOLEAN)

REGEXP_LIKE(email, '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\\.[a-zA-Z0-9-]+)+$')

Tips

  • BigQuery: 多くのDBで動作する汎用的な正規表現パターン。ローカル部はRFC準拠の記号を許容し、ドメイン部はハイフンを含む複数ラベルを想定
  • Snowflake: 多くのDBで動作する汎用的な正規表現パターン。ローカル部はRFC準拠の記号を許容し、ドメイン部はハイフンを含む複数ラベルを想定
  • Treasure Data: 多くのDBで動作する汎用的な正規表現パターン。ローカル部はRFC準拠の記号を許容し、ドメイン部はハイフンを含む複数ラベルを想定
  • Athena: 多くのDBで動作する汎用的な正規表現パターン。ローカル部はRFC準拠の記号を許容し、ドメイン部はハイフンを含む複数ラベルを想定
  • MySQL: 多くのDBで動作する汎用的な正規表現パターン。ローカル部はRFC準拠の記号を許容し、ドメイン部はハイフンを含む複数ラベルを想定
  • Oracle: 多くのDBで動作する汎用的な正規表現パターン。ローカル部はRFC準拠の記号を許容し、ドメイン部はハイフンを含む複数ラベルを想定

UUID生成

BigQuery (STRING)

GENERATE_UUID()

Snowflake (VARCHAR)

UUID_STRING()

Treasure Data (VARCHAR)

UUID()

Athena (VARCHAR)

UUID()

MySQL (VARCHAR)

UUID()

Oracle (RAW)

SYS_GUID()

Tips

  • BigQuery: UUIDを生成
  • Snowflake: UUIDを生成
  • Treasure Data: UUIDを生成
  • Athena: UUIDを生成
  • MySQL: UUIDを生成
  • Oracle: UUIDを生成。結果はRAW(16)型のため、文字列として扱うにはRAWTOHEX(SYS_GUID())のように変換が必要

MD5ハッシュ計算

BigQuery (STRING)

TO_HEX(MD5(string))

Snowflake (VARCHAR)

MD5(string)

Treasure Data (VARCHAR)

MD5(to_utf8(string))

Athena (VARCHAR)

MD5(to_utf8(string))

MySQL (VARCHAR)

MD5(string)

Oracle (RAW)

STANDARD_HASH(string, 'MD5')

Tips

  • BigQuery: MD5はBYTESを返すためTO_HEXで文字列表現に変換
  • Snowflake: MD5ハッシュ値を計算
  • Treasure Data: MD5ハッシュ値を計算
  • Athena: 文字列をto_utf8でvarbinaryに変換してからMD5ハッシュ値を計算。
  • MySQL: MD5ハッシュ値を計算
  • Oracle: STANDARD_HASHでMD5ハッシュ値を取得

SHA1ハッシュ計算

BigQuery (STRING)

TO_HEX(SHA1(string))

Snowflake (VARCHAR)

SHA1(string)

Treasure Data (VARCHAR)

to_hex(SHA1(to_utf8(string)))

Athena (VARCHAR)

to_hex(SHA1(to_utf8(string)))

MySQL (VARCHAR)

SHA1(string)

Oracle (RAW)

STANDARD_HASH(string, 'SHA1')

Tips

  • BigQuery: SHA1結果のBYTESをTO_HEXで比較可能な文字列に変換
  • Snowflake: 文字エンコーディング（UTF-8変換）と出力形式（HEX大小文字）の差異に注意。HEX_ENCODE()で統一
  • Treasure Data: to_utf8()でバイナリ変換→SHA1→to_hex()で16進文字列化（40桁HEX）。Presto SQL互換
  • Athena: to_utf8()でバイナリ変換→SHA1→to_hex()で16進文字列化（40桁HEX）。比較時は大小文字統一
  • MySQL: SHA1ハッシュ計算
  • Oracle: STANDARD_HASHでSHA1ハッシュ値を取得

SHA256ハッシュ計算

BigQuery (STRING)

TO_HEX(SHA256(string))

Snowflake (VARCHAR)

HEX_ENCODE(SHA2(string, 256))

Treasure Data (VARCHAR)

SHA256(to_utf8(string))

Athena (VARCHAR)

to_hex(SHA256(to_utf8(string)))

MySQL (VARCHAR)

SHA2(string, 256)

Oracle (RAW)

STANDARD_HASH(string, 'SHA256')

Tips

  • BigQuery: SHA256結果のBYTESをTO_HEXで文字列化
  • Snowflake: 文字エンコーディング（UTF-8変換）と出力形式（HEX大小文字）の差異に注意。HEX_ENCODE()で統一
  • Treasure Data: SHA256ハッシュ計算
  • Athena: to_utf8()でバイナリ変換→SHA256→to_hex()で16進文字列化（64桁HEX）。比較時は大小文字統一
  • MySQL: SHA256ハッシュ値を計算
  • Oracle: STANDARD_HASHでSHA256ハッシュ値を取得

BASE64エンコード

BigQuery (STRING)

TO_BASE64(CAST(string AS BYTES))

Snowflake (VARCHAR)

BASE64_ENCODE(TO_BINARY(string_input, 'UTF-8'))

Treasure Data (VARCHAR)

TO_BASE64(binary_input)

Athena (VARCHAR)

TO_BASE64(binary_input)

MySQL (TEXT)

TO_BASE64(string)

Oracle (RAW)

UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(string))

Tips

  • BigQuery: 文字列はCAST(... AS BYTES)でバイト化してからTO_BASE64
  • Snowflake: バイナリデータをBase64エンコード
  • Treasure Data: バイナリデータをBase64エンコード
  • Athena: バイナリデータをBase64エンコード
  • MySQL: 文字列をBase64エンコード
  • Oracle: 文字列をRAWに変換してUTL_ENCODE.BASE64_ENCODEに渡す

BASE64デコード

BigQuery (BYTES)

CAST(FROM_BASE64(string) AS STRING)

Snowflake (BINARY)

BASE64_DECODE_STRING(string_input)

Treasure Data (VARBINARY)

from_utf8(from_base64(string_input))

Athena (VARBINARY)

from_utf8(from_base64(string_input))

MySQL (BLOB)

FROM_BASE64(string)

Oracle (RAW)

UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encoded)))

Tips

  • BigQuery: FROM_BASE64の結果BYTESをCASTで文字列へ戻す
  • Snowflake: Base64文字列をデコード
  • Treasure Data: Base64文字列をデコード
  • Athena: Base64文字列をデコードし、from_utf8で人間が読める文字列に変換。
  • MySQL: Base64文字列をデコード
  • Oracle: UTL_ENCODE.BASE64_DECODE結果をUTL_RAW.CAST_TO_VARCHAR2で文字列に戻す

16進数変換

BigQuery (STRING)

TO_HEX(CAST(string AS BYTES))

Snowflake (VARCHAR)

HEX_ENCODE(TO_BINARY(string_input, 'UTF-8'))

Treasure Data (VARCHAR)

to_hex(to_utf8(string_input))

Athena (VARCHAR)

to_hex(to_utf8(string_input))

MySQL (VARCHAR)

HEX(value)

Oracle (VARCHAR2)

RAWTOHEX(UTL_RAW.CAST_TO_RAW(string))

Tips

  • BigQuery: 文字列をBYTESへCASTしてTO_HEXで16進表現に変換
  • Snowflake: バイナリデータを16進数文字列に変換
  • Treasure Data: バイナリデータを16進数文字列に変換
  • Athena: ハッシュ関数やバイナリ変換は、DBやエンコーディングの違いで結果が変動する可能性があります。環境依存の挙動に注意してください。
  • MySQL: 数値を16進数文字列に変換
  • Oracle: RAWデータを16進数文字列に変換

16進数逆変換

BigQuery (BYTES)

CAST(FROM_HEX(hex_string) AS STRING)

Snowflake (BINARY)

HEX_DECODE_STRING(string_input)

Treasure Data (VARBINARY)

from_utf8(from_hex(string_input))

Athena (VARBINARY)

from_utf8(from_hex(string_input))

MySQL (VARBINARY)

UNHEX(string)

Oracle (RAW)

UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(string))

Tips

  • BigQuery: FROM_HEXの結果BYTESをCASTで文字列へ戻す
  • Snowflake: 16進数文字列をバイナリデータに変換
  • Treasure Data: 16進数文字列をバイナリデータに変換
  • Athena: 16進数文字列をデコードし、from_utf8で人間が読める文字列に変換。
  • MySQL: 16進数文字列をバイナリデータに変換
  • Oracle: 16進数文字列をRAWデータに変換

緯度経度からの距離算出

BigQuery (FLOAT64)

ST_DISTANCE(ST_GEOGPOINT(lon1, lat1), ST_GEOGPOINT(lon2, lat2))

Snowflake (FLOAT)

ST_DISTANCE(ST_POINT(lon1, lat1), ST_POINT(lon2, lat2))

Treasure Data (DOUBLE)

N/A - 手動計算が必要

Athena (DOUBLE)

N/A - 手動計算が必要

MySQL (DOUBLE)

ST_DISTANCE_SPHERE(point1, point2)

Oracle (NUMBER)

SDO_GEOM.SDO_DISTANCE(geom1, geom2, unit)

Tips

  • BigQuery: 結果はメートル単位
  • Snowflake: 結果はメートル単位
  • Treasure Data: Haversine公式など
  • Athena: Haversine公式など
  • MySQL: 結果はメートル単位
  • Oracle: Spatialオプションが必要

地理ポイント生成

BigQuery (GEOGRAPHY)

ST_GEOGPOINT(longitude, latitude)

Snowflake (GEOGRAPHY)

ST_POINT(longitude, latitude)

Treasure Data (GEOMETRY)

ST_POINT(longitude, latitude)

Athena (GEOMETRY)

ST_POINT(longitude, latitude)

MySQL (GEOMETRY)

ST_POINT(longitude, latitude)

Oracle (SDO_GEOMETRY)

-- SDO_GEOMETRY使用例（要Spatial）
SDO_GEOMETRY(
  2001,  -- 2D point
  4326,  -- SRID (WGS84)
  SDO_POINT_TYPE(longitude, latitude, NULL),
  NULL,
  NULL
)

Tips

  • BigQuery: 経度と緯度から地理ポイントを生成
  • Snowflake: 経度と緯度から地理ポイントを生成
  • Treasure Data: 経度と緯度から地理ポイントを生成
  • Athena: 経度と緯度から地理ポイントを生成
  • MySQL: 経度と緯度から地理ポイントを生成
  • Oracle: この関数はOracle Spatial and Graphオプションのインストールと設定が前提です。単純なSQLではないため、利用環境の確認が必須です。詳細は公式ドキュメント「SDO_GEOMETRY Object Type」を参照してください。

地理面積計算

BigQuery (FLOAT64)

ST_AREA(geography)

Snowflake (FLOAT)

ST_AREA(TO_GEOGRAPHY(wkt))

Treasure Data (DOUBLE)

ST_AREA(ST_GeometryFromText(wkt))

Athena (DOUBLE)

ST_AREA(ST_GeometryFromText(wkt))

MySQL (DOUBLE)

ST_AREA(geometry)

Oracle (NUMBER)

SDO_GEOM.SDO_AREA(geometry, tolerance)

Tips

  • BigQuery: 地理オブジェクトの面積を計算
  • Snowflake: TO_GEOGRAPHYで測地系ジオメトリを生成しST_AREAで面積(㎡)を計算
  • Treasure Data: Presto (Trino) のジオメトリ関数。WKTをST_GeometryFromTextで読み込み平面単位の面積を取得
  • Athena: Prestoのジオメトリ関数。WKTをST_GeometryFromTextで読み込み平面単位の面積を取得
  • MySQL: 地理オブジェクトの面積を計算
  • Oracle: SDO_GEOMETRYはSpatial Optionが必要。環境により利用可否を確認

他のカテゴリ

目次に戻る

各DBのバージョンやエディションにより動作が異なる場合があります。本番環境での使用前に必ずテストしてください。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?