BigQuery

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

この記事は約3分で読めます。

 

使用するデータ

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でやったことあるから余裕でしょwwwwってクエリ叩いてみたら案外集計に時間が掛かったなんてことがあるので油断できない。。。

 

関連記事:【BigQuery】DATE_DIFF()で日付の差分から経過日数を計算する

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

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

関連記事:【BigQuery】分析関数を使って獲得ポイントでユーザーを順位付けする

関連記事:【BigQuery】LAG関数とLEAD関数で前後のデータを同じ行にスライドさせる

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

 

 


プログラミング・スクレイピングツール作成の相談を受け付けています!

クラウドワークス・ココナラ・MENTAなどでPython・SQL・GASなどのプログラミングに関する相談やツール作成などを承っております!

過去の案件事例:

  • Twitter・インスタグラムの自動化ツール作成
  • ウェブサイトのスクレイピングサポート
  • ダッシュボード・サイト作成
  • データエンジニア転職相談

これまでの案件例を見る

キャリア相談もお気軽に!文系学部卒からエンジニア・データサイエンティストへの転職経験をもとに、未経験者がどう進むべきかのアドバイスを提供します。


スポンサーリンク
/* プログラミング速報関連記事一覧表示 */
ミナピピンの研究室

コメント

タイトルとURLをコピーしました