DB

BigQueryでグループ毎の最初や最後の値を取得する方法

グループ毎の最初や最後の値を取得したい

グループ毎の最大値や最小値を取得するのであれば MAX()MIN() を使えばいい。

しかし別のカラムでソートした最初または最後の値はどのように取得すればいいのか。

そこで今回はBigQueryでグループ毎の最初や最後の値を取得する方法について紹介する。

この記事を書いている人


からさん
システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。最近GCPを色々と習得中。

記事を読むメリット

BigQueryでグループ毎の最初や最後の値を取得する方法がわかる


BigQueryでグループ毎の最初や最後の値を取得する方法


方法を説明する為に、まずはデータを用意する。

CREATE OR REPLACE TABLE
project_name.dataset_name.tmp_sample
as
(
  (select "a" as g, 2 as n, date "2020-12-01" as d)
  union all
  (select "a" as g, 1 as n, date "2020-12-02" as d)
  union all
  (select "a" as g, 3 as n, date "2020-12-03" as d)
  union all
  (select "a" as g, 4 as n, date "2020-12-04" as d)
  union all
  (select "b" as g, 2 as n, date "2020-12-01" as d)
  union all
  (select "b" as g, 3 as n, date "2020-12-02" as d)
  union all
  (select "b" as g, 6 as n, date "2020-12-03" as d)
  union all
  (select "b" as g, 1 as n, date "2020-12-04" as d)
  union all
  (select "c" as g, 3 as n, date "2020-12-01" as d)
  union all
  (select "c" as g, 4 as n, date "2020-12-02" as d)
  union all
  (select "c" as g, 5 as n, date "2020-12-03" as d)
  union all
  (select "c" as g, 7 as n, date "2020-12-04" as d)
)

結果

Rowgnd
1a42020/12/4
2a12020/12/2
3a22020/12/1
4a32020/12/3
5b12020/12/4
6b22020/12/1
7b32020/12/2
8b62020/12/3
9c52020/12/3
10c72020/12/4
11c32020/12/1
12c42020/12/2


これを g のカラムのa~cのグループの中で、d カラムの順で最初と最後のカラム n の値を取得したい。

この場合 FIRST_VALUE()LAST_VALUE() を使う。

使用する場合は以下のような構文を書く。

SELECT FIRST_VALUE(取得したいカラム)
OVER (
PARTITION BY グループ化したいカラム
ORDER BY ソート順のカラム
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)


先程作成したテーブルに適用するとこのようになる。

SELECT
*,
FIRST_VALUE(n)
  OVER (PARTITION BY g
  ORDER BY d ASC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  as first_n,
LAST_VALUE(n)
  OVER (PARTITION BY g
  ORDER BY d ASC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  as last_n
from
project_name.dataset_name.tmp_sample
ORDER BY g,d

結果

Rowgndfirst_nlast_n
1a22020/12/124
2a12020/12/224
3a32020/12/324
4a42020/12/424
5b22020/12/121
6b32020/12/221
7b62020/12/321
8b12020/12/421
9c32020/12/137
10c42020/12/237
11c52020/12/337
12c72020/12/437


最初と最後の値だけ欲しい場合は distinct でまとめてしまう。

SELECT distinct
g,
FIRST_VALUE(n)
  OVER (PARTITION BY g
  ORDER BY d ASC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  as first_n,
LAST_VALUE(n)
  OVER (PARTITION BY g
  ORDER BY d ASC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  as last_n
from
project_name.dataset_name.tmp_sample
ORDER BY g

結果

Rowgfirst_nlast_n
1a24
2b21
3c37



まとめ


今回はBigQueryでグループ毎の最初や最後の値を取得する方法について紹介した。

やり方としては PARTITION句でグループ化したデータに対して FIRST_VALUE()LAST_VALUE() を使うとグループ内での最初と最後の値が取得できる。



他にもBigQueryに関する記事もあるので、もし気になったものがあれば読んでみて欲しい。

KRSW

駆け出し機械学習エンジニア。機械学習、DB、WEBと浅く広い感じ。 Junior machine learning engineer. Not a specialist but a generalist who knows DB, WEB too.

役に立ったらシェアしてくれると励みになります。

にほんブログ村 IT技術ブログへ

-DB
-

Translate »

© 2021 ITips