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
)

参考

標準 SQL の式、関数、演算子 | BigQuery | Google Cloud

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

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

結果