BigQueryで最頻値(mode, top)を集計する方法

この記事は？

下記のようにユーザが訪問した都道府県が並んでいるテーブルがあったとします。

分析の都合上、ユーザごとにどこの都道府県にいったのかを１：１で対応させたいとします。
その際、ユーザが最も多く行った都道府県を選択するのが合理的な場合があります。

この記事はそのような集計のさいに最頻値を取り出す方法について説明します。

解決方法

下記のようにAPPROX_TOP_COUNT(x,n)関数を使用します。

SELECT
user_id 
, APPROX_TOP_COUNT(shop_loc,1)[OFFSET(0)].value shop_loc
FROM
tbl
GROUP BY user_id

参考

BigQuery公式

--下記のSQLを自分のプロジェクトで実行すると再現できます。
WITH tbl AS(
SELECT "A" AS user_id, 'tokyo'  as shop_loc, 1 visit_num
UNION ALL
SELECT "A" AS user_id, 'tokyo' as shop_loc, 2 visit_num
UNION ALL
SELECT "A" AS user_id, 'osaka' as shop_loc, 3 visit_num
UNION ALL
SELECT "A" AS user_id, null as shop_loc, 4 visit_num
UNION ALL
SELECT "B" AS user_id, 'kyoto' as shop_loc, 1 visit_num
)

SELECT
user_id 
, APPROX_TOP_COUNT(shop_loc,1)[OFFSET(0)].value shop_loc
FROM
tbl
GROUP BY 1