FirebaseAnalyticsのBigQueryから日次で中央値を取得する

最近エンジニア業より、データ解析・マーケティングの仕事がふえてきました。

今回、アプリの初回設定値の中央値の推移を追いたくなったので、日毎に中央値を取得して見ました

下記は、firebase_tableのInitというイベントのinit_valパラメータの中央値を過去28日分、日次で集計しています

WITH TABLE AS
  (SELECT event_date AS DAY,
          /* 
            percentile_cont(値, 0.5)で、中央値を取得可能
            PARTITION BY event_dateとすることで、日次で中央値が取得できる
           */
          percentile_cont(value.int_value, 0.5) over(PARTITION BY event_date) AS medium
   FROM firebase_table,
        UNNEST (event_params) AS event_p
   WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
     AND event_name = "Init"
     AND KEY = "init_val")

/* 
  上記のテーブルだと
  | day | value.int_value | medium |
  | 20181014 | 1 | 3 |
  | 20181014 | 5 | 3 |
  | 20181014 | 2 | 3 |
  | 20181014 | 3 | 3 |
  | 20181014 | 4 | 3 |
  | 20181014 | 5 | 3 |
  | 20181014 | 3 | 3 |
  | 20181014 | 1 | 3 |
  | 20181014 | 1 | 3 |

  みたいになるので、同じday, mediumのレコードが大量に表示されるのをgroup byでday毎にまとめている
  minをつかってるが、全部同じ値なので、minでもmaxでもavgでもなんでもいい
 */
SELECT min(DAY) AS d,
       min(medium) AS m
FROM TABLE
GROUP BY DAY,
         medium