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?

【BigQuery】カラム同士を比較して最小値・最大値を求める(LEAST / GREATEST)

Posted at

やりたいこと

各レコードでカラム同士を比較して最小値・最大値を計算したい。

image.png

MIN関数やMAX関数のようにレコード同士の比較ではなく、同一レコード内でカラム同士の比較をしたい。

2つのカラムの比較であればCASE文で対処できるが、3つ以上のカラムの比較をラクに行いたい。

結論

WITH
  test_table AS (
  /*
    テスト用テーブルの作成
  */
    SELECT 1 AS column_a, 2 AS column_b, 3 AS column_c
    UNION ALL
    SELECT 4 AS column_a, 6 AS column_b, 5 AS column_c
    UNION ALL
    SELECT 9 AS column_a, 7 AS column_b, 8 AS column_c
  )

SELECT
  column_a
  ,column_b
  ,column_c
  ,LEAST(column_a, column_b, column_c) AS min_value
  ,GREATEST(column_a, column_b, column_c) AS max_value

FROM
  test_table
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
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?