DB

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

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)
)

結果

Row g n d
1 a 4 2020/12/4
2 a 1 2020/12/2
3 a 2 2020/12/1
4 a 3 2020/12/3
5 b 1 2020/12/4
6 b 2 2020/12/1
7 b 3 2020/12/2
8 b 6 2020/12/3
9 c 5 2020/12/3
10 c 7 2020/12/4
11 c 3 2020/12/1
12 c 4 2020/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

結果

Row g n d first_n last_n
1 a 2 2020/12/1 2 4
2 a 1 2020/12/2 2 4
3 a 3 2020/12/3 2 4
4 a 4 2020/12/4 2 4
5 b 2 2020/12/1 2 1
6 b 3 2020/12/2 2 1
7 b 6 2020/12/3 2 1
8 b 1 2020/12/4 2 1
9 c 3 2020/12/1 3 7
10 c 4 2020/12/2 3 7
11 c 5 2020/12/3 3 7
12 c 7 2020/12/4 3 7


最初と最後の値だけ欲しい場合は 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

結果

Row g first_n last_n
1 a 2 4
2 b 2 1
3 c 3 7



まとめ


今回は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 »

Copyright© ITips , 2021 All Rights Reserved.