以前PostgreSQLで日次・週次・月次集計する記事を投稿しました。 それのBigQuery版です。(StandardSQLを用います) blog.frogdusk.com テストデータ 2019/1/1から90日にわたって、日時がばらばらなデータを10,000行用意します。 SELECT DATETIME_ADD( DATETIM…

BigQueryで日次・週次・月次集計する

なにこれ BigQueryで集計する際に、手元の事前データを条件等で使うために入れたくなる時があります。 わざわざテーブルを作るまでではないとBigquery(SQL)で祝日判定する - 夕蛙のなく頃にで扱ったようにarrayをunnestすることで実現させていました。 これ…

BigQueryで2列以上の事前データを準備する

なにこれ 平日・休日・祝日毎に違いがあるかを確認するために、Bigquery(SQL)で祝日判定をしたいです。 そのためのクエリを考えます。 方向性 Bigquery(StandardSQL)にもSQLにも祝日判定してくれる便利な関数はないです。 そこで以下の2つが考えられますが、…

Bigquery(SQL)で祝日判定する

目次 目次 DBスペシャリストに合格した 勉強方法 書籍を読む 午前Ⅱの過去問を解く 午後Ⅰの過去問を解く 午後Ⅱの過去問を解く 勉強時系列 DBスペシャリストに合格した 応用情報技術者試験の次のレベルで高度試験のうちの1つ、DBスペシャリスト試験を2019春に…

DBスペシャリスト合格したので、勉強手順を紹介する

なにこれ ユーザー毎の最も行動する時間が知りたくなった。 すなわちユーザー毎の行動時間(hour)の最頻値を集計したい。 テストデータ column1: 2019-01-01〜2019-03-31までの時刻がバラバラなdatetime column2: ランダムな日本語文字1字(user_idに相当) SEL…

PostgreSQLで最頻値を求める

なにこれ PostgreSQLでのtimestamp型から日次(daily)・週次(weekly)・月次(monthly)で集計するための変換方法です。 日次・月次はto_char関数で'YYYY-MM-DD'や'YYYY-MM'を出力すればいいのですが、週次が少し面倒です。 今回の週次は、月曜日起点で集計する…

PostgreSQLで日次・週次・月次集計する

モチベーション 会社でコンバージョン率(CVR)を月次ないし日次でダッシュボード化し、追いかけている。 スタートアップでデータが少ないこともあり、月や日によって母数のばらつきが大きいので、ただCVRを出すだけでなく、95%信頼区間を計算して、エラーバー…

CTRやCVRのような比率データを95%信頼区間のエラーバーが付くようSQLで集計する