【BigQuery】#3 組み合わせごとに会員数（ID数）をカウントする【組み合わせ分析】

facilityの組み合わせごとの会員数をカウントする

前回作成したfacilityの組み合わせ（set_facility）を使って、facilityの組み合わせ毎に会員数がどのくらいいるのかカウントします。
SQLのコードは下記です。

SELECT
  set_facility,
  COUNT(DISTINCT ID) AS ID_count
FROM 
  `table_name`
GROUP BY 
  set_facility

COUNT( [数えたい要素] )

今回は[数えたい要素]をIDとすることで会員数をカウントしています。
またCOUNT関数内にDISTINCTを入れることでIDを重複せずにカウントすることができます。
またGROUP BY句でset_facilityを指定することで、set_facility毎に会員数を数えるようにしています。

BigQueryでのクエリ結果

それではBigQuery上で実際にクエリを実行します。

クエリ

SELECT
  set_facility,
  COUNT(DISTINCT ID) AS ID_count
FROM 
  `table_name`
GROUP BY 
  set_facility
ORDER BY 
  ID_count DESC

分かりやすく表示するために、ORDER BY句で会員数の多い順にデータを表示するようにしています。

クエリ実行結果

facilityの組み合わせ毎に会員数をカウントすることができました！
ジムが5人と一番多くなっていることがわかります。

まとめ

1～#3までのコードをまとめると以下のようになります。

WITH tbl_time AS (--time-stampをYYYY-MM表記に変換
SELECT  
  FORMAT_DATE("%Y-%m", DATE(TIMESTAMP_SECONDS(time_stamp))) AS date,
  record_number,
  ID,
  facility
FROM 
 `table_name`
)

, tbl_set AS (--ID毎にfacilityの組み合わせを作成
SELECT 
  ID,
  STRING_AGG(DISTINCT facility ORDER BY facility asc) as set_facility
FROM 
  tbl_time
GROUP BY 
  ID
)

SELECT
  time0.date,
  set0.set_facility,
  COUNT(DISTINCT set0.ID) AS ID_count
FROM 
  tbl_set AS set0
  INNER JOIN 
  tbl_time AS time0
  ON 
  (
      set0.ID = time0.ID
  )
GROUP BY 
  time0.date,
  set0.set_facility
ORDER BY 
  ID_count DESC

WITH句を使用して複数テーブルを作成しています。
また最後にINNER JOIN句を用いて、facilityの組み合わせ（set_facility）とdateをIDをキーに結合しています。
（今回はdateは「2022-02」のみのためtbl_setの中にdateを組み込んでもいいのですが、もしdateが複数ある場合はこのように最後に結合させる必要があります。）

クエリ実行結果

このデータ処理を行うことで組み合わせ分析がいろいろとできそうですね！