SQL関数クロスリファレンス - クエリ制御・テストデータ系関数

Last updated at 2025-09-29Posted at 2025-09-28

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

BigQuery、Snowflake、Treasure Data、Athena、MySQL、Oracleのクエリ制御・テストデータ編について、構文の違いや注意点をまとめました。

📋 関数一覧

件数制限
上位N件取得
ページング
IF構文
DECODE条件分岐
QUALIFY句
NULLIF条件付きNULL化
PIVOT構文
最大値取得
最小値取得
VALUES構文
連番生成
日付連続生成
ランダム抽出
ウィンドウ関数重複除去
JSONスカラー値抽出
JSON要素抽出
JSON展開
配列展開
配列結合
配列要素数取得
配列要素存在チェック
配列要素位置取得
配列部分抽出
配列重複除去
配列共通要素抽出
配列差分要素抽出

件数制限

BigQuery (N/A)

SELECT * FROM table LIMIT 100

Snowflake (N/A)

SELECT * FROM table LIMIT 100

Treasure Data (N/A)

SELECT * FROM table LIMIT 100

Athena (N/A)

SELECT * FROM table LIMIT 100

MySQL (N/A)

SELECT * FROM table LIMIT 100

Oracle (N/A)

SELECT * FROM table WHERE ROWNUM <= 100

Tips

BigQuery: LIMIT句で制限
Snowflake: LIMIT句で制限
Treasure Data: LIMIT句で制限
Athena: LIMIT句で制限
MySQL: LIMIT句で制限
Oracle: FETCH句（12c以降）も利用可

上位N件取得

BigQuery (N/A)

SELECT * FROM table ORDER BY column DESC LIMIT 10

Snowflake (N/A)

SELECT * FROM table ORDER BY column DESC LIMIT 10

Treasure Data (N/A)

SELECT * FROM table ORDER BY column DESC LIMIT 10

Athena (N/A)

SELECT * FROM table ORDER BY column DESC LIMIT 10

MySQL (N/A)

SELECT * FROM table ORDER BY column DESC LIMIT 10

Oracle (N/A)

SELECT * FROM table ORDER BY column DESC FETCH FIRST 10 ROWS ONLY

Tips

BigQuery: ORDER BY + LIMIT
Snowflake: ORDER BY + LIMIT
Treasure Data: ORDER BY + LIMIT
Athena: ORDER BY + LIMIT
MySQL: ORDER BY + LIMIT
Oracle: 12c以降利用可

ページング

BigQuery (N/A)

SELECT * FROM table_name
ORDER BY id  -- 必須：結果の一貫性確保
LIMIT 10 OFFSET 20

Snowflake (N/A)

SELECT * FROM table_name
ORDER BY id  -- 必須：結果の一貫性確保
LIMIT 10 OFFSET 20

Treasure Data (N/A)

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY column1) as rn FROM (SELECT DISTINCT column1, column2 FROM table_name) AS sub1) AS sub2 WHERE rn BETWEEN 21 AND 30

Athena (N/A)

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY column1) as rn FROM (SELECT DISTINCT column1, column2 FROM table_name) AS sub1) AS sub2 WHERE rn BETWEEN 21 AND 30

MySQL (N/A)

SELECT * FROM table ORDER BY id LIMIT 20, 10

Oracle (N/A)

SELECT * FROM table ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Tips

BigQuery: LIMIT + OFFSETORDER BYなしでは結果が非決定的。大きなOFFSETは性能劣化に注意
Snowflake: LIMIT + OFFSETORDER BYなしでは結果が非決定的。大きなOFFSETは性能劣化に注意
Treasure Data: ROW_NUMBER()を使用したページング。OFFSETはPrestoで非対応
Athena: ROW_NUMBER()を使用したページング。OFFSETはPrestoで非対応
MySQL: LIMIT offset, count
Oracle: OFFSET + FETCH

IF構文

BigQuery (指定型)

IF(condition, true_value, false_value)

Snowflake (指定型)

IFF(condition, true_value, false_value)

Treasure Data (指定型)

IF(condition, true_value, false_value)

Athena (指定型)

IF(condition, true_value, false_value)

MySQL (指定型)

IF(condition, true_value, false_value)

Oracle (指定型)

CASE WHEN condition THEN true_value ELSE false_value END

Tips

BigQuery: CASE文も利用可
Snowflake: SnowflakeはIFF
Treasure Data: CASE文も利用可
Athena: CASE文も利用可
MySQL: CASE文も利用可
Oracle: OracleのIFはPL/SQL

DECODE条件分岐

BigQuery (指定型)

CASE WHEN expr = search1 THEN result1 ... ELSE default_result END

Snowflake (指定型)

DECODE(expr, search1, result1, ..., default_result)

Treasure Data (指定型)

CASE WHEN expr = search1 THEN result1 ... ELSE default_result END

Athena (指定型)

CASE WHEN expr = search1 THEN result1 ... ELSE default_result END

MySQL (指定型)

CASE WHEN expr = search1 THEN result1 ... ELSE default_result END

Oracle (指定型)

DECODE(expr, search1, result1, [search2, result2, ...], [default])

Tips

BigQuery: DECODEの代わりにCASEを使用
Snowflake: 式と値のペアに基づいて結果を返す
Treasure Data: DECODEの代わりにCASEを使用
Athena: DECODEの代わりにCASEを使用
MySQL: DECODEの代わりにCASEを使用
Oracle: 式と値のペアに基づいて結果を返す

QUALIFY句

BigQuery (BOOLEAN)

QUALIFY window_function_condition

Snowflake (BOOLEAN)

QUALIFY window_function_condition

Treasure Data (BOOLEAN)

サブクエリ必須

Athena (BOOLEAN)

サブクエリ必須

MySQL (BOOLEAN)

サブクエリ必須

Oracle (BOOLEAN)

サブクエリ必須

Tips

BigQuery: サブクエリ不要
Snowflake: SQL簡潔化
Treasure Data: Window関数はサブクエリ内
Athena: Window関数はサブクエリ内
MySQL: Window関数はサブクエリ内
Oracle: Window関数はサブクエリ内

NULLIF条件付きNULL化

BigQuery (指定型)

NULLIF(expression1, expression2)

Snowflake (指定型)

NULLIF(expression1, expression2)

Treasure Data (指定型)

NULLIF(expression1, expression2)

Athena (指定型)

NULLIF(expression1, expression2)

MySQL (指定型)

NULLIF(expression1, expression2)

Oracle (指定型)

NULLIF(expression1, expression2)

Tips

BigQuery: expression1がexpression2と等しい場合にNULLを返す
Snowflake: expression1がexpression2と等しい場合にNULLを返す
Treasure Data: expression1がexpression2と等しい場合にNULLを返す
Athena: expression1がexpression2と等しい場合にNULLを返す
MySQL: expression1がexpression2と等しい場合にNULLを返す
Oracle: expression1がexpression2と等しい場合にNULLを返す

PIVOT構文

BigQuery (TABLE)

PIVOT(aggregate_expression FOR column IN (value_list))

Snowflake (TABLE)

PIVOT(aggregate_expression FOR column IN (value_list))

Treasure Data (TABLE)

CASE WHEN集計

Athena (TABLE)

CASE WHEN集計

MySQL (TABLE)

CASE WHEN集計

Oracle (TABLE)

PIVOT(aggregate_expression FOR column IN (value_list))

Tips

BigQuery: EXECUTE IMMEDIATE使用で動的PIVOT可能
Snowflake: 動的PIVOTも対応
Treasure Data: CASE文で手動実装
Athena: CASE文で手動実装
MySQL: CASE文で手動実装
Oracle: XML PIVOTも対応

最大値取得

BigQuery (指定型)

GREATEST(expression1, expression2, ...)

Snowflake (指定型)

GREATEST(expression1, expression2, ...)

Treasure Data (指定型)

GREATEST(expression1, expression2, ...)

Athena (指定型)

GREATEST(expression1, expression2, ...)

MySQL (指定型)

GREATEST(expression1, expression2, ...)

Oracle (指定型)

GREATEST(expression1, expression2, ...)

Tips

BigQuery: 引数リストの最大値を返す
Snowflake: 引数リストの最大値を返す
Treasure Data: 引数リストの最大値を返す
Athena: 引数リストの最大値を返す
MySQL: 引数リストの最大値を返す
Oracle: 引数リストの最大値を返す

最小値取得

BigQuery (指定型)

LEAST(expression1, expression2, ...)

Snowflake (指定型)

LEAST(expression1, expression2, ...)

Treasure Data (指定型)

LEAST(expression1, expression2, ...)

Athena (指定型)

LEAST(expression1, expression2, ...)

MySQL (指定型)

LEAST(expression1, expression2, ...)

Oracle (指定型)

LEAST(expression1, expression2, ...)

Tips

BigQuery: 引数リストの最小値を返す
Snowflake: 引数リストの最小値を返す
Treasure Data: 引数リストの最小値を返す
Athena: 引数リストの最小値を返す
MySQL: 引数リストの最小値を返す
Oracle: 引数リストの最小値を返す

VALUES構文

BigQuery (TABLE)

SELECT * FROM UNNEST([STRUCT(1 as id, 'Alice' as name), STRUCT(2 as id, 'Bob' as name)])

Snowflake (TABLE)

SELECT * FROM VALUES (1, 'Alice'), (2, 'Bob') AS t(id, name)

Treasure Data (TABLE)

SELECT * FROM (VALUES (1, 'Alice'), (2, 'Bob')) AS t(id, name)

Athena (TABLE)

SELECT * FROM (VALUES (1, 'Alice'), (2, 'Bob')) AS t(id, name)

MySQL (TABLE)

SELECT 1 as id, 'Alice' as name UNION ALL SELECT 2, 'Bob'

Oracle (TABLE)

SELECT * FROM (SELECT 1 as id, 'Alice' as name FROM DUAL UNION ALL SELECT 2, 'Bob' FROM DUAL)

Tips

BigQuery: STRUCT配列をUNNEST
Snowflake: VALUES句でインライン
Treasure Data: VALUES句のみ
Athena: VALUES句のみ
MySQL: UNION ALLで代替。MySQL 8.0.19以降はVALUES (1, 'Alice'), (2, 'Bob')の構文も利用可能
Oracle: UNION ALL手動作成

連番生成

BigQuery (TABLE)

SELECT * FROM UNNEST(GENERATE_ARRAY(1, 10)) AS numbers

Snowflake (TABLE)

SELECT ROW_NUMBER() OVER (ORDER BY NULL) as number FROM TABLE(GENERATOR(ROWCOUNT => 10))

Treasure Data (TABLE)

SELECT number FROM UNNEST(SEQUENCE(1, 10)) AS t(number)

Athena (TABLE)

SELECT number FROM UNNEST(SEQUENCE(1, 10)) AS t(number)

MySQL (TABLE)

WITH RECURSIVE numbers AS (SELECT 1 as n UNION ALL SELECT n+1 FROM numbers WHERE n < 10) SELECT n as number FROM numbers

Oracle (TABLE)

SELECT LEVEL as number FROM DUAL CONNECT BY LEVEL <= 10

Tips

BigQuery: GENERATE_ARRAYで連番
Snowflake: GENERATOR関数
Treasure Data: SEQUENCE関数
Athena: SEQUENCE関数
MySQL: 再帰CTE
Oracle: CONNECT BY LEVEL

日付連続生成

BigQuery (TABLE)

SELECT date FROM UNNEST(GENERATE_DATE_ARRAY('2023-01-01', '2023-01-10')) AS date

Snowflake (TABLE)

SELECT DATEADD('day', ROW_NUMBER() OVER (ORDER BY NULL) - 1, '2023-01-01') as date FROM TABLE(GENERATOR(ROWCOUNT => 10))

Treasure Data (TABLE)

SELECT DATE_ADD('day', number - 1, DATE '2023-01-01') as date FROM UNNEST(SEQUENCE(1, 10)) AS t(number)

Athena (TABLE)

SELECT DATE_ADD('day', number - 1, DATE '2023-01-01') as date FROM UNNEST(SEQUENCE(1, 10)) AS t(number)

MySQL (TABLE)

WITH RECURSIVE dates AS (SELECT DATE('2023-01-01') as d UNION ALL SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM dates WHERE d < '2023-01-10') SELECT d as date FROM dates

Oracle (TABLE)

SELECT DATE '2023-01-01' + LEVEL - 1 as date FROM DUAL CONNECT BY LEVEL <= 10

Tips

BigQuery: 日付配列生成
Snowflake: GENERATOR + DATEADD
Treasure Data: SEQUENCE + DATE_ADD
Athena: SEQUENCE + DATE_ADD
MySQL: 再帰CTE
Oracle: CONNECT BY LEVEL

ランダム抽出

BigQuery (N/A)

SELECT * FROM table ORDER BY RAND() LIMIT 10

Snowflake (N/A)

SELECT * FROM table ORDER BY RANDOM() LIMIT 10

Treasure Data (N/A)

SELECT * FROM table_name ORDER BY rand() LIMIT n

Athena (N/A)

SELECT * FROM table_name ORDER BY rand() LIMIT n

MySQL (N/A)

SELECT * FROM table ORDER BY RAND() LIMIT 10

Oracle (N/A)

SELECT * FROM table ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 10 ROWS ONLY

Tips

BigQuery: RAND()でランダムソート
Snowflake: RANDOM()でランダムソート
Treasure Data: RANDOM()でランダムソート
Athena: RANDOM()でランダムソート
MySQL: RAND()でランダムソート
Oracle: DBMS_RANDOMでランダムソート

ウィンドウ関数重複除去

BigQuery (N/A)

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY column ORDER BY id) as rn FROM table) WHERE rn = 1

Snowflake (N/A)

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY column ORDER BY id) as rn FROM table) WHERE rn = 1

Treasure Data (N/A)

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY column ORDER BY id) as rn FROM table) WHERE rn = 1

Athena (N/A)

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY column ORDER BY id) as rn FROM table) WHERE rn = 1

MySQL (N/A)

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY column ORDER BY id) as rn FROM table) t WHERE rn = 1

Oracle (N/A)

SELECT * FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY column ORDER BY id) as rn FROM table t) WHERE rn = 1

Tips

BigQuery: 重複の最初のレコードのみ
Snowflake: 重複の最初のレコードのみ
Treasure Data: 重複の最初のレコードのみ
Athena: 重複の最初のレコードのみ
MySQL: 重複の最初のレコードのみ
Oracle: 重複の最初のレコードのみ

JSONスカラー値抽出

BigQuery (STRING)

JSON_VALUE(json_string, json_path)

Snowflake (指定型)

variant:path

Treasure Data (STRING)

JSON_EXTRACT_SCALAR(json_string, json_path)

Athena (STRING)

JSON_EXTRACT_SCALAR(json_string, json_path)

MySQL (指定型)

JSON_VALUE(json_doc, path)

Oracle (指定型)

JSON_VALUE(json_doc, path)

Tips

BigQuery: 結果はスカラー値
Snowflake: ドット表記で直接アクセス
Treasure Data: スカラー値取得にはJSON_EXTRACT_SCALARを使用
Athena: スカラー値取得にはJSON_EXTRACT_SCALARを使用
MySQL: 結果はスカラー値 (MySQL 8.0.21+)
Oracle: 結果はスカラー値

JSON要素抽出

BigQuery (STRING)

JSON_EXTRACT(json_string, json_path)

Snowflake (VARIANT)

GET_PATH(variant, path)

Treasure Data (JSON)

JSON_EXTRACT(json_string, json_path)

Athena (JSON)

JSON_EXTRACT(json_string, json_path)

MySQL (JSON)

JSON_UNQUOTE(JSON_EXTRACT(json_text, '$.key'))

Oracle (CLOB)

JSON_QUERY(json_doc, path)

Tips

BigQuery: 結果はJSON文字列。スカラー値取得にはJSON_VALUEを使用
Snowflake: GET_PATHの結果はVARIANTなので::STRINGなどで文字列化する
Treasure Data: 結果はJSON型。スカラー値取得にはJSON_EXTRACT_SCALARを使用
Athena: 結果はJSON型。スカラー値取得にはJSON_EXTRACT_SCALARを使用
MySQL: JSON_EXTRACT結果はQUOTES付きのためJSON_UNQUOTEで整形
Oracle: 結果はJSONフラグメント。スカラー値取得にはJSON_VALUEを使用

JSON展開

BigQuery (TABLE)

SELECT * FROM UNNEST(JSON_EXTRACT_ARRAY('["a","b","c"]')) AS items

Snowflake (TABLE)

SELECT VALUE as item FROM TABLE(FLATTEN(input => PARSE_JSON('["a","b","c"]')))

Treasure Data (TABLE)

SELECT json_extract_scalar(item, '$') FROM UNNEST(CAST(JSON_PARSE('["a","b","c"]') AS ARRAY(JSON))) AS t(item)

Athena (TABLE)

SELECT json_extract_scalar(item, '$') FROM UNNEST(CAST(JSON_PARSE('["a","b","c"]') AS ARRAY(JSON))) AS t(item)

MySQL (TABLE)

-- スカラー配列の展開
SELECT val
FROM JSON_TABLE(json_text, '$[*]' 
  COLUMNS(val VARCHAR(100) PATH '$')
) AS jt

Oracle (TABLE)

SELECT json_value(item, '$') FROM JSON_TABLE('["a","b","c"]', '$[*]' COLUMNS (item VARCHAR2(100) PATH '$'))

Tips

BigQuery: JSON配列をUNNEST
Snowflake: FLATTEN + PARSE_JSON
Treasure Data: JSON_PARSE + UNNEST
Athena: JSON_PARSE + UNNEST
MySQL: JSON_TABLEでJSON配列を行に展開
Oracle: JSON_TABLE + JSON_VALUE

配列展開

BigQuery (TABLE)

-- 文字列配列として展開
SELECT item FROM UNNEST(JSON_EXTRACT_STRING_ARRAY('[a,b,c]')) AS item
-- または直接配列リテラル
SELECT item FROM UNNEST(['a','b','c']) AS item

Snowflake (TABLE)

SELECT value FROM TABLE(FLATTEN(input => ARRAY_CONSTRUCT('a', 'b', 'c')))

Treasure Data (TABLE)

SELECT number FROM UNNEST(ARRAY[1, 2, 3, 4, 5]) AS t(number)

Athena (TABLE)

SELECT number FROM UNNEST(ARRAY[1, 2, 3, 4, 5]) AS t(number)

MySQL (TABLE)

-- 配列を行に展開
SELECT val 
FROM JSON_TABLE(json_array, '$[*]' 
  COLUMNS(val VARCHAR(100) PATH '$')
) AS jt

Oracle (TABLE)

SELECT item FROM JSON_TABLE('["a","b","c"]', '$[*]' COLUMNS (item VARCHAR2(100) PATH '$'))

Tips

BigQuery: 配列を行に展開
Snowflake: FLATTEN関数
Treasure Data: UNNEST + ARRAY
Athena: UNNEST + ARRAY
MySQL: JSON_TABLEで配列を行に展開 (MySQL 8.0+)
Oracle: JSON_TABLE使用

配列結合

BigQuery (ARRAY)

ARRAY(SELECT AS STRUCT left_val, right_val FROM UNNEST(array1) AS left_val WITH OFFSET idx JOIN UNNEST(array2) AS right_val WITH OFFSET idx2 ON idx = idx2)

Snowflake (ARRAY)

ARRAYS_ZIP(array1, array2)

Treasure Data (MAP)

zip_with(array1, array2, (x, y) -> (x, y))

Athena (MAP)

zip_with(array1, array2, (x, y) -> (x, y))

MySQL (JSON)

SELECT JSON_ARRAYAGG(JSON_OBJECT(jt1.val, jt2.val))
FROM 
  JSON_TABLE(array1, '$[*]' COLUMNS (ord FOR ORDINALITY, val VARCHAR(100) PATH '$')) AS jt1
  JOIN 
  JSON_TABLE(array2, '$[*]' COLUMNS (ord FOR ORDINALITY, val VARCHAR(100) PATH '$')) AS jt2 
  ON jt1.ord = jt2.ord

Oracle (CLOB)

SELECT JSON_ARRAYAGG(JSON_OBJECT(jt1.val VALUE jt2.val))
FROM 
  JSON_TABLE(array1, '$[*]' COLUMNS (ord FOR ORDINALITY, val VARCHAR2(100) PATH '$')) jt1
  JOIN 
  JSON_TABLE(array2, '$[*]' COLUMNS (ord FOR ORDINALITY, val VARCHAR2(100) PATH '$')) jt2 
  ON jt1.ord = jt2.ord

Tips

BigQuery: 同じOFFSETをキーにUNNESTした配列同士をJOINして構造体配列を構築
Snowflake: 2つの配列を結合し、各要素が構造体(OBJECT)である配列を返す。比較にはTO_JSON()を使用。
Treasure Data: MAP_ZIP_WITHで2つの配列を結合
Athena: 2つの配列を、要素ごとのペアを持つrowの配列に変換する。
MySQL: 2つの配列をキーと値として結合するには、JSON_TABLEのFOR ORDINALITY句で行番号を生成し、それらをJOINキーとして使用します。
Oracle: 専用関数なし。2つの配列をJSON_TABLEとFOR ORDINALITYで展開し、行番号をキーにJOINして手動でペア作成

配列要素数取得

BigQuery (INT64)

ARRAY_LENGTH(array)

Snowflake (INTEGER)

ARRAY_SIZE(array)

Treasure Data (INTEGER)

CARDINALITY(array)

Athena (INTEGER)

CARDINALITY(array)

MySQL (INT)

JSON_LENGTH(json_array)

Oracle (NUMBER)

JSON_VALUE(json_array, '$.size()')

Tips

BigQuery: CARDINALITYの代わりにARRAY_LENGTHを使用
Snowflake: CARDINALITYの代わりにARRAY_SIZEを使用
Treasure Data: 配列の要素数を返す
Athena: 配列の要素数を返す
MySQL: CARDINALITYの代わりにJSON_LENGTHを使用 (MySQL 8.0+)
Oracle: Oracle 19c+ではJSON_VALUEとpath expressionの.size()メソッドで配列の長さを取得する。

配列要素存在チェック

BigQuery (BOOL)

EXISTS(SELECT 1 FROM UNNEST(array) AS x WHERE x = value)

Snowflake (BOOLEAN)

ARRAY_CONTAINS(value, array)

Treasure Data (BOOLEAN)

CONTAINS(array, value)

Athena (BOOLEAN)

CONTAINS(array, value)

MySQL (BOOLEAN)

JSON_CONTAINS(json_array, value)

Oracle (BOOLEAN)

JSON_EXISTS(json_array, '$[*] ? (@ == value)')

Tips

BigQuery: UNNESTとEXISTSで要素の存在を確認
Snowflake: 配列に要素が含まれるか確認
Treasure Data: 配列に要素が含まれるか確認
Athena: 配列に要素が含まれるか確認
MySQL: JSON配列に要素が含まれるか確認 (MySQL 8.0+)
Oracle: JSON_EXISTSで要素の存在を確認

配列要素位置取得

BigQuery (INT64)

SELECT offset FROM UNNEST(array) AS x WITH OFFSET AS offset WHERE x = value LIMIT 1

Snowflake (INTEGER)

ARRAY_POSITION(array, value)

Treasure Data (INTEGER)

ARRAY_POSITION(array, value)

Athena (INTEGER)

ARRAY_POSITION(array, value)

MySQL (JSON PATH)

SELECT jt.ordinality - 1 FROM JSON_TABLE(json_array, '$[*]' COLUMNS(ordinality FOR ORDINALITY, val INT PATH '$')) AS jt WHERE jt.val = target_value LIMIT 1

Oracle (NUMBER)

-- FOR ORDINALITYで位置取得
SELECT jt.idx - 1 AS position
FROM JSON_TABLE(json_array, '$[*]' 
  COLUMNS(
    idx FOR ORDINALITY,
    val VARCHAR2(100) PATH '$'
  )
) jt
WHERE jt.val = 'target_value'
FETCH FIRST 1 ROWS ONLY

Tips

BigQuery: UNNESTにAS xを付けOFFSETと組み合わせて位置を取得
Snowflake: 配列内の要素の位置を返す (1-based)
Treasure Data: 配列内の要素の位置を返す (1-based)
Athena: 配列内の要素の位置を返す (1-based)
MySQL: FOR ORDINALITY列は1始まりなので0基準へ調整してインデックスとして扱う (MySQL 8.0+)
Oracle: JSON_TABLEのFOR ORDINALITYで行番号取得（0ベース変換要）

配列部分抽出

BigQuery (ARRAY)

ARRAY(SELECT x FROM UNNEST(array) AS x WITH OFFSET AS offset WHERE offset BETWEEN start_index AND end_index)

Snowflake (ARRAY)

ARRAY_SLICE(array, from_index, to_index)

Treasure Data (ARRAY)

SLICE(array, start, length)

Athena (ARRAY)

SLICE(array, start, length)

MySQL (JSON)

SELECT JSON_ARRAYAGG(val) FROM (SELECT val, ordinality FROM JSON_TABLE(json_array, '$[*]' COLUMNS(ordinality FOR ORDINALITY, val INT PATH '$')) AS jt WHERE ordinality BETWEEN start_pos AND end_pos ORDER BY ordinality) AS slice_vals

Oracle (CLOB)

JSON_QUERY(json_array, '$[start_index to end_index]')

Tips

BigQuery: UNNESTにエイリアスを付けOFFSETで範囲抽出
Snowflake: 0-basedインデックス。ARRAY_SLICE(a, start, end)はstart以上end未満を返す（end非包含）
Treasure Data: 配列の部分を抽出
Athena: 配列の部分を抽出
MySQL: FOR ORDINALITY列で範囲抽出し、外側でソート後にJSON_ARRAYAGGで戻す (MySQL 8.0+)
Oracle: JSON_QUERYで部分配列を抽出

配列重複除去

BigQuery (ARRAY)

ARRAY(SELECT DISTINCT x FROM UNNEST(array) AS x)

Snowflake (ARRAY)

ARRAY_DISTINCT(array)

Treasure Data (ARRAY)

ARRAY_DISTINCT(array)

Athena (ARRAY)

ARRAY_DISTINCT(array)

MySQL (JSON)

SELECT JSON_ARRAYAGG(val) FROM (SELECT DISTINCT val FROM JSON_TABLE(json_array, '$[*]' COLUMNS(val INT PATH '$')) AS jt ORDER BY val) AS dedup

Oracle (CLOB)

-- Oracle 21c以降
JSON_ARRAYAGG(DISTINCT value)
-- それ以前のバージョン
SELECT JSON_ARRAYAGG(val) FROM (
  SELECT DISTINCT val FROM JSON_TABLE(...)
)

Tips

BigQuery: UNNESTにエイリアスを付けDISTINCTで重複除去
Snowflake: 配列の重複要素を除去
Treasure Data: 配列の重複要素を除去
Athena: 配列の重複要素を除去
MySQL: JSON_TABLEで展開後にDISTINCT＋並べ替えを行いJSON_ARRAYAGGで配列を再生成 (MySQL 8.0+)
Oracle: JSON_ARRAYAGG(DISTINCT)は21c以降。それ以前はサブクエリでDISTINCT後に集約

配列共通要素抽出

BigQuery (ARRAY)

ARRAY(SELECT x FROM UNNEST(array1) AS x INTERSECT DISTINCT SELECT y FROM UNNEST(array2) AS y)

Snowflake (ARRAY)

ARRAY_INTERSECTION(array1, array2)

Treasure Data (ARRAY)

ARRAY_INTERSECT(array1, array2)

Athena (ARRAY)

ARRAY_INTERSECT(array1, array2)

MySQL (JSON)

SELECT JSON_ARRAYAGG(val) FROM (SELECT DISTINCT jt1.val AS val FROM JSON_TABLE(json_array1, '$[*]' COLUMNS(val INT PATH '$')) AS jt1 JOIN JSON_TABLE(json_array2, '$[*]' COLUMNS(val INT PATH '$')) AS jt2 ON jt1.val = jt2.val ORDER BY val) AS intersect_vals

Oracle (CLOB)

-- 2つの配列をJOINして共通要素を抽出
SELECT JSON_ARRAYAGG(jt1.val) 
FROM JSON_TABLE(json_array1, '$[*]' COLUMNS(val VARCHAR2(100) PATH '$')) jt1
JOIN JSON_TABLE(json_array2, '$[*]' COLUMNS(val VARCHAR2(100) PATH '$')) jt2 
ON jt1.val = jt2.val

Tips

BigQuery: UNNESTとINTERSECTで共通要素を抽出
Snowflake: 2つの配列の共通要素を抽出
Treasure Data: 2つの配列の共通要素を抽出
Athena: 2つの配列の共通要素を抽出
MySQL: 2つのJSON_TABLEをJOINし共通要素を抽出、JSON_ARRAYAGGで整列 (MySQL 8.0+)
Oracle: 専用関数は23cのみ。JSON_TABLEで展開しJOINで共通要素を抽出後、JSON_ARRAYAGGで再配列化

配列差分要素抽出

BigQuery (ARRAY)

ARRAY(SELECT x FROM UNNEST(array1) AS x EXCEPT DISTINCT SELECT y FROM UNNEST(array2) AS y)

Snowflake (ARRAY)

ARRAY_EXCEPT(array1, array2)

Treasure Data (ARRAY)

ARRAY_EXCEPT(array1, array2)

Athena (ARRAY)

ARRAY_EXCEPT(array1, array2)

MySQL (JSON)

SELECT JSON_ARRAYAGG(val) FROM (SELECT jt1.val FROM JSON_TABLE(json_array1, '$[*]' COLUMNS (val JSON PATH '$')) AS jt1 LEFT JOIN JSON_TABLE(json_array2, '$[*]' COLUMNS (val JSON PATH '$')) AS jt2 ON jt1.val = jt2.val WHERE jt2.val IS NULL) AS diff

Oracle (CLOB)

SELECT JSON_ARRAYAGG(jt1.val) FROM JSON_TABLE(json_array1, '$[*]' COLUMNS(val NUMBER PATH '$')) jt1 LEFT JOIN JSON_TABLE(json_array2, '$[*]' COLUMNS(val NUMBER PATH '$')) jt2 ON jt1.val = jt2.val WHERE jt2.val IS NULL

Tips

BigQuery: UNNESTとEXCEPTで差分要素を抽出
Snowflake: array1からarray2にない要素を抽出
Treasure Data: array1からarray2にない要素を抽出。配列の比較にはCAST(array_col AS JSON)が利用可能。
Athena: array1からarray2にない要素を抽出。配列の比較にはCAST(array_col AS JSON)が利用可能。
MySQL: JSON_TABLEで各配列を展開し、LEFT JOINで差分を抽出後、JSON_ARRAYAGGで配列を再生成します。 (MySQL 8.0+)
Oracle: 2つの配列をJSON_TABLEで展開し、LEFT JOIN ... IS NULLで差分を見つけ、JSON_ARRAYAGGで再配列化する。

他のカテゴリ

目次に戻る

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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up