【BigQuery】GEOGRAPHY型でGroupByしたいときはANY_VALUE()が使える

GEOGRAPHY型は集計列に指定できないので、ちょっと困ったことになることがあります。

検証用データ

例えばこんな風に、日付、位置、売上額が入ったデータを受け取って、そこから日毎の売上合計を算出したかったとします。

WITH salesAmount AS (
  SELECT *
  FROM
    UNNEST([
      STRUCT(DATE'2022-04-01' AS date, ST_GEOGPOINT(0, 0) AS point, 1000 AS amount),
      STRUCT('2022-04-01', ST_GEOGPOINT(0, 0), 500),
      STRUCT('2022-04-02', ST_GEOGPOINT(0, 1), 10000),
      STRUCT('2022-04-03', ST_GEOGPOINT(1, 1), 2000)
    ])
)

SELECT * FROM salesAmount

実行結果

date	point	amount
2022-04-01	POINT(0 0)	1000
2022-04-01	POINT(0 0)	500
2022-04-02	POINT(0 1)	10000
2022-04-03	POINT(1 1)	2000

日毎に売り場を変えるので、 point は日付ごとに一意になります。

GROUP BYを試す

ここで、 date と point で GROUP BY しようとすると…

SELECT 
  date,
  point,
  SUM(amount) AS totalAmount
FROM salesAmount
GROUP BY date, point

こんなエラーメッセージが表示されます。

Grouping by expressions of type GEOGRAPHY is not allowed

GEOGRAPHY型の項目は GROUP BY に設定できないのだそう。

ANY_VALUE() が使える

こんな場合は、 ANY_VALUE() を使うのが便利です。

SELECT 
  date,
  ANY_VALUE(point) AS point,
  SUM(amount) AS totalAmount
FROM salesAmount
GROUP BY date

実行結果

date	point	totalAmount
2022-04-01	POINT(0 0)	1500
2022-04-02	POINT(0 1)	10000
2022-04-03	POINT(1 1)	200