【BigQuery】平均値・中央値を計算する(要編集)

使用するデータ

	datetime	user_id	purchase
0	2021/1/4 14:06	3	1000
1	2021/1/4 1:45	3	5000
2	2021/1/5 6:30	3	100
3	2021/1/1 3:58	2	1000
4	2021/1/3 5:50	4	1000
5	2021/1/2 6:48	3	100
6	2021/1/5 21:36	1	10000
7	2021/1/3 9:55	3	1000
8	2021/1/5 10:16	2	5000
9	2021/1/2 11:02	1	100

<作成コード>

import random
import datetime
import pandas as pd

now = datetime.datetime.now()

data = []
for i in range(10):
    rand_datetime = now.replace(day=random.randint(1,5), hour=random.randint(1,23), minute=random.randint(1,59), second=0, microsecond=0)
    id_ = random.randint(1,4)
    purchase = random.choice([100,1000,5000,10000])
    data.append([rand_datetime, id_, purchase])
pd.DataFrame(data,columns=['datetime', 'user_id', 'purchase']).to_csv('test_data.csv')

これを自身のBQに「test_data」というテーブル名でアップロードした体で進めていきます。

平均値と中央値を集計する

平均値はほかのSQLと同じように計算できますが、中央値はなぜかmedianがありません。ですが、fhoffaさんが作ってくれたUDF関数で中央値を計算できます。

SELECT
  DATE(datetime) AS date
  ,fhoffa.x.median(ARRAY_AGG(purchase)) AS sales_median
  ,AVG(purchase) AS sales_avg
FROM
  test_data
GROUP BY
  date

Bigqueryはたまに他のSQLと違ってクセのある点があるので、SQLでやったことあるから余裕でしょｗｗｗｗってクエリ叩いてみたら案外集計に時間が掛かったなんてことがあるので油断できない。。。

関連記事：【SQL/BigQuery】分析関数で移動平均を集計する

関連記事：【BigQuery】日付データを月単位、年単位に変換する

関連記事：【BigQuery】日付データの加算・減算処理

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析（できるDigital Camp）

木田和廣インプレス 2021年02月19日頃

楽天ブックス

Amazon

Kindle