ann-toque’s diary

@YASU11552288 の思うことをつらつらと

【BigQuery Advent Calendar 2020】二標本t検定の実装

この記事は BigQuery Advent Calendar 2020 - Qiita 17日目の記事です。

こんにちは。@YASU11552288 です。今回はBigQueryでt検定を実装したいと思います。pythonやRでライブラリは提供されているので、わざわざなんでと思う方もいらっしゃると思います。

ただBigQueryの便利なWEBエディタとコードエディタを行ったり来たりするのはめんどくさく、BigQuery上で簡単に効果検証をできるようにしたいと考えました。

※t検定の細かい話は他サイトにまとまっているのでここでは割愛させていただきます。参考のリンクは以下の通りです。

大まかな流れ

  1. テスト用のデータを用意
  2. 統計量を計算（自由度、平均、母集団標準偏差、不偏分散、プールされた不偏分散）
  3. 統計量からt値を計算し、該当する自由度を結合キーにしてt分布表と結合
  4. P < 0.05のt値と比較して、帰無仮説を棄却するかしないかを判定

t分布表テーブルの用意

工程の3番のに必要なテーブルは以下のようなものです。今回はp値が0.05を基準にするため、自由度とそれに対応したt値のみを保持しています。

df p005
1 12.706
2 4.303
3 3.182
… …
14 2.145

コード

WITH
/* 1. テスト用のデータを用意 */
  sample_data_a AS(
  SELECT
    *
  FROM
    UNNEST([49,40,52,37,55,38,45]) AS score ),
  sample_data_b AS(
  SELECT
    *
  FROM
    UNNEST([60,52,68,55,65,47,45,62,53]) AS score ),

/* 2. 統計量を計算（自由度、平均、母集団標準偏差、不偏分散、プールされた不偏分散） */
  stat AS (
  SELECT
    DISTINCT size_a,
    size_b,
    avg_a,
    avg_b,
    std_a,
    std_b,
    var_a,
    var_b,
    size_a + size_b -2 AS df_ab,
    SQRT(((size_a -1)*var_a + (size_b -1)*var_b) / (size_a + size_b - 2)) AS var_ab
  FROM
    sample_data_a
  CROSS JOIN (
    SELECT
      DISTINCT STDDEV_POP(score) OVER() AS std_a,
      VAR_POP(score) OVER() AS var_a
    FROM
      sample_data_a)
  CROSS JOIN (
    SELECT
      COUNT(score) AS size_a,
      AVG(score) AS avg_a
    FROM
      sample_data_a)
  CROSS JOIN (
    SELECT
      DISTINCT STDDEV_POP(score) OVER() AS std_b,
      VAR_POP(score) OVER() AS var_b
    FROM
      sample_data_b)
  CROSS JOIN (
    SELECT
      COUNT(score) AS size_b,
      AVG(score) AS avg_b
    FROM
      sample_data_b) )

/* 3. 統計量からt値を計算し、該当する自由度を結合キーにしてt分布表と結合 */
SELECT
  (avg_a -avg_b) / (var_ab * SQRT(1/size_a + 1/size_b)) AS t,
  df_ab,
  t_map.p005,
  CASE
    WHEN ABS((avg_a -avg_b) / (var_ab * SQRT(1/size_a + 1/size_b))) > t_map.p05 THEN 'p<0.05で帰無仮説を棄却'
  ELSE
  'p<0.05で帰無仮説を棄却しない'
END
  AS result
FROM
  stat
JOIN
  `play-ground-f0737.t_test.t_map` AS t_map -- 4. P < 0.05のt値と比較して、帰無仮説を棄却するかしないかを判定

ON
  stat.df_ab = t_map.df

結果

t（t値） df_ab（自由度） p005（自由度に該当するp=0.05のt値） 帰無仮説を棄却するかどうか
-3.1234277309172747 14 2.145 p<0.05で帰無仮説を棄却

うまく計算することができました！

挫折と裏話

関数化したい人生だった

UDFで永続化できればもっと楽だと思い「t_test(a,b)」という形で実装を試みたがそもそもUDFの思想と違うみたいで、挫折。 ARRAYやSTRCTを利用し力押しで行こうとしたが、サブクエリが記述できないのでやめた。でも、UDFのできること・できないことがわかったのは収穫だった！

ネタが！！

本当は12月初旬にネタを仕込んでいたのですが、まさかの6日目の記事の方と内容が丸かぶりして、慌ててテーマ見直して書きました笑 qiita.com

最後まで読んでいただきありがとうございました(￣▽￣)