なにこれ 以下のようなArrayっぽいStringが入っている項目を user_name(STRING) values(STRING) hoge ["1","2"] fuga ["5"] 以下のようにばらすクエリの解説です。 user_name(STRING) value(STRING) hoge 1 hoge 2 fuga 5 考え方 ArrayっぽいStringを、Array…

BigQueryでArrayっぽいStringをフラット化する

なにこれ MySQLでtimestamp型から日次(daily)・週次(weekly)・月次(monthly)で集計するための変換方法です。 日次・月次はto_char関数で'YYYY-MM-DD'や'YYYY-MM'を出力すればいいのですが、週次が少し面倒です。 週次は、週番号とMODであまりを計算すること…

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

背景 集計で、一定期間の月末の日付シリーズが必要になりました。 月末を出すためには月初を出す必要があるので、一緒にまとめておこうと思います。 クエリイメージ 数字のシリーズを作成 --> 固定した日付に対して、1ヶ月後・2ヶ月後...と足していく クエリ…

BigQueryで指定期間の月初・月末の日付シリーズを用意する

以前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で集計する