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)

ROW_NUMBER() OVER (ORDER BY ...)

Snowflake (NUMBER)

ROW_NUMBER() OVER (ORDER BY ...)

Treasure Data (BIGINT)

ROW_NUMBER() OVER (ORDER BY ...)

Athena (BIGINT)

ROW_NUMBER() OVER (ORDER BY ...)

MySQL (BIGINT)

ROW_NUMBER() OVER (ORDER BY ...)

Oracle (NUMBER)

ROW_NUMBER() OVER (ORDER BY ...)

Tips

  • BigQuery: パーティション内で一意
  • Snowflake: パーティション内で一意
  • Treasure Data: パーティション内で一意
  • Athena: パーティション内で一意
  • MySQL: MySQL 8.0以降
  • Oracle: 標準的なウィンドウ関数。ROWNUMは疑似列で取得順依存のため非推奨

文字列連結集計

BigQuery (STRING)

STRING_AGG(expression[, delimiter])

Snowflake (VARCHAR)

LISTAGG(expression, delimiter) WITHIN GROUP (ORDER BY col)

Treasure Data (VARCHAR)

ARRAY_JOIN(ARRAY_AGG(expression), delimiter)

Athena (VARCHAR)

ARRAY_JOIN(ARRAY_AGG(expression), delimiter)

MySQL (VARCHAR)

GROUP_CONCAT(expression[SEPARATOR delimiter])

Oracle (VARCHAR2)

LISTAGG(expression, delimiter) WITHIN GROUP (ORDER BY ...)

Tips

  • BigQuery: 区切り文字指定可能
  • Snowflake: WITHIN GROUP句必須
  • Treasure Data: 配列経由で実現
  • Athena: 配列経由で実現
  • MySQL: 最大長制限あり
  • Oracle: ORDER BY必須

配列集計

BigQuery (ARRAY)

ARRAY_AGG(expression)

Snowflake (ARRAY)

ARRAY_AGG(expression)

Treasure Data (ARRAY)

ARRAY_AGG(expression)

Athena (ARRAY)

ARRAY_AGG(expression)

MySQL (JSON)

JSON_ARRAYAGG(expression)

Oracle (NESTED TABLE)

COLLECT(expression)

Tips

  • BigQuery: NULL値も含まれる
  • Snowflake: NULL値も含まれる
  • Treasure Data: NULL値も含まれる
  • Athena: NULL値も含まれる
  • MySQL: MySQL 5.7以降
  • Oracle: COLLECTはネストした表を返す。使用前にCREATE TYPE AS TABLE OF でコレクション型の定義が必要

GROUP BY ALL句

BigQuery (N/A)

SELECT col1, COUNT(*) FROM sample_table GROUP BY ALL

Snowflake (N/A)

GROUP BY ALL

Treasure Data (N/A)

GROUP BY 1, 2, ...

Athena (N/A)

GROUP BY 1, 2, ...

MySQL (N/A)

GROUP BY 1, 2, ...

Oracle (N/A)

GROUP BY column1, column2, ...

Tips

  • BigQuery: SELECT句で指定された全ての非集計列でグループ化
  • Snowflake: SELECT句で指定された全ての非集計列でグループ化
  • Treasure Data: GROUP BY ALLの代わりにGROUP BY句で全ての非集計列を指定
  • Athena: GROUP BY ALLの代わりにGROUP BY句で全ての非集計列を指定
  • MySQL: GROUP BY ALLの代わりにGROUP BY句で全ての非集計列を指定
  • Oracle: OracleではGROUP BY句に列名または式を明示的に指定する必要があり、列の位置(1, 2, ..)での指定はサポートされていません。

条件付き最大値

BigQuery (ANY)

MAX_BY(value_expr, order_expr)

Snowflake (ANY)

MAX_BY(value_expr, order_expr)

Treasure Data (ANY)

max_by(value_expr, order_expr)

Athena (ANY)

max_by(value_expr, order_expr)

MySQL (ANY)

サブクエリ代替

Oracle (ANY)

MAX(value) KEEP (DENSE_RANK FIRST ORDER BY order_col DESC)

Tips

  • BigQuery: 最新レコード値取得
  • Snowflake: 最新レコード値取得
  • Treasure Data: Presto標準関数（環境により差異の可能性あり）
  • Athena: Presto標準関数
  • MySQL: ROW_NUMBER()で代替
  • Oracle: KEEP句で最大値に対応する値を取得

条件付き最小値

BigQuery (ANY)

MIN_BY(value_expr, order_expr)

Snowflake (ANY)

MIN_BY(value_expr, order_expr)

Treasure Data (ANY)

min_by(value_expr, order_expr)

Athena (ANY)

min_by(value_expr, order_expr)

MySQL (ANY)

サブクエリ代替

Oracle (ANY)

MIN(value) KEEP (DENSE_RANK FIRST ORDER BY order_col ASC)

Tips

  • BigQuery: 最安値詳細取得
  • Snowflake: 最安値詳細取得
  • Treasure Data: Presto標準関数（環境により差異の可能性あり）
  • Athena: Presto標準関数
  • MySQL: ROW_NUMBER()で代替
  • Oracle: KEEP句で最小値に対応する値を取得

任意値取得

BigQuery (ANY)

ANY_VALUE(expr)

Snowflake (ANY)

ANY_VALUE(expr)

Treasure Data (ANY)

arbitrary(expr)

Athena (ANY)

arbitrary(expr)

MySQL (ANY)

ANY_VALUE(expr)

Oracle (ANY)

MIN/MAX代替

Tips

  • BigQuery: GROUP BY時の任意値
  • Snowflake: GROUP BY時の任意値
  • Treasure Data: 関数名がarbitrary
  • Athena: 関数名がarbitrary
  • MySQL: MySQL 5.7以降
  • Oracle: 19.8以降/21c以降はANY_VALUE利用可、以前はMIN/MAX代替

前行値取得

BigQuery (入力と同じ型)

LAG(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

Snowflake (入力と同じ型)

LAG(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

Treasure Data (入力と同じ型)

LAG(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

Athena (入力と同じ型)

LAG(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

MySQL (入力と同じ型)

LAG(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

Oracle (入力と同じ型)

LAG(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

Tips

  • BigQuery: デフォルト値指定可
  • Snowflake: デフォルト値指定可
  • Treasure Data: デフォルト値指定可
  • Athena: デフォルト値指定可
  • MySQL: MySQL 8.0以降
  • Oracle: デフォルト値指定可

次行値取得

BigQuery (入力と同じ型)

LEAD(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

Snowflake (入力と同じ型)

LEAD(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

Treasure Data (入力と同じ型)

LEAD(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

Athena (入力と同じ型)

LEAD(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

MySQL (入力と同じ型)

LEAD(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

Oracle (入力と同じ型)

LEAD(value_expression [, offset [, default_value]]) OVER ([PARTITION BY partition_by_clause] ORDER BY order_by_clause)

Tips

  • BigQuery: デフォルト値指定可
  • Snowflake: デフォルト値指定可
  • Treasure Data: デフォルト値指定可
  • Athena: デフォルト値指定可
  • MySQL: MySQL 8.0以降
  • Oracle: デフォルト値指定可

最初の値

BigQuery (入力と同じ型)

FIRST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

Snowflake (入力と同じ型)

FIRST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

Treasure Data (入力と同じ型)

FIRST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

Athena (入力と同じ型)

FIRST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

MySQL (入力と同じ型)

FIRST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

Oracle (入力と同じ型)

FIRST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

Tips

  • BigQuery: フレーム指定重要
  • Snowflake: フレーム指定重要
  • Treasure Data: フレーム指定重要
  • Athena: フレーム指定重要
  • MySQL: MySQL 8.0以降
  • Oracle: フレーム指定重要

最後の値

BigQuery (入力と同じ型)

LAST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

Snowflake (入力と同じ型)

LAST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

Treasure Data (入力と同じ型)

LAST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

Athena (入力と同じ型)

LAST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

MySQL (入力と同じ型)

LAST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

Oracle (入力と同じ型)

LAST_VALUE(value_expression) OVER ( [PARTITION BY partition_by_clause] ORDER BY order_by_clause [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] )

Tips

  • BigQuery: フレーム指定重要
  • Snowflake: フレーム指定重要
  • Treasure Data: フレーム指定重要
  • Athena: フレーム指定重要
  • MySQL: MySQL 8.0以降
  • Oracle: フレーム指定重要

WINDOW句

BigQuery (CLAUSE)

WINDOW window_name AS (window_specification)

Snowflake (CLAUSE)

個別OVER句使用

Treasure Data (CLAUSE)

WINDOW window_name AS (window_specification)

Athena (CLAUSE)

WINDOW window_name AS (window_specification)

MySQL (CLAUSE)

WINDOW window_name AS (window_specification)

Oracle (CLAUSE)

インライン定義のみ

Tips

  • BigQuery: 定義再利用で可読性向上
  • Snowflake: 名前付きWindow未対応
  • MySQL: MySQL 8.0以降
  • Oracle: 21c以降で名前付きサポート、以前は未対応

他のカテゴリ

目次に戻る

各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?