概要 バッチで実行するクエリを変更した後に、前後のテーブルで1行全体を比較し、変化があった行を調べたい。 複数列をまとめてJOINのキーにしてしまいたい。 ARRAY型、STRUCT型でも手軽にJOINしたい。 ということが最近あった。愚直やると、 対象のすべての…

概要 一旦サブクエリを挟んでいる人も多いのではないでしょうか。 今回はサンプルとして、Google Analyticsのサンプルデータを使用して、transactionの日次合計と月次合計を同時に出す。 アウトプットのイメージ 解説の方に記載。 今回のキーワード window関…

閑話 小ネタです。次はBigQueryMLあたりを調べてを書いていきたいと思っています。 異常検知はもう少し勉強してから再開していきます。 概要 横持ちのデータを縦持ちに変換します。 縦持ちにできて嬉しいことは色々あるかと思いますが、例えばBigQueryMLでの…

概要 前回まで、異常検知系では指標の実装をメインに書いてきました(2記事だけですが)。一方で、前回のブログの終わりでも書きましたが、別パターンのsampleデータの生成と複数系列への対応も行いたいです。 BigQueryで時系列データから異常部位検出 (EMA 指…

概要 異常検知系2個目。例により実装しやすそうなので採用。 直近に重みを付けた移動平均です。 こちらの本を参考にしました。 www.kspub.co.jp その他参考にした記事 www.moneypartners.co.jp 定義 なお、 今後も異常検知系を増やし、最終的には統合してな…

概要 BigQuery上でひらがなをカタカナに変換するSQLUDFを作成する。 処理の流れは、以下の記事と同じである。 sem-aa-bq.hatenablog.com アウトプットのイメージ CREATE TEMP FUNCTION hirakata(x STRING) AS ( udf ); SELECT --コメント部分が実行結果 hira…

概要 ネタがないので柔らかめな内容。興味のある方はクイズだと思って解いてみてください。 問題 ARRAYを受け取ったら、ARRAYの最後の要素と、最後から1つ前の要素を合計し、それを継ぎ足したARRAYを作るようなUDFを作ってください。UDFについてはこちらを参…

概要 手法についてはこちらの異常部位検出の解説部分が、分かりやすかったです。 www.albert2005.co.jp用途としては、ネット広告について言うと、配信全体の異常なクリック増加・減少や、特定のキーワードの異常検知に使えると考えている。 具体的に何をやっ…

概要 STRUCTは慣れると便利です。今回は複数列をまとめて処理する流れとサンプルクエリを書きます。 例 SQLを書いて昨対比を出すことを考える。 あるタイミングの実績集計に、昨年の実績集系列もあるようなものを作成し、成長率などを計算したい。 例えばweb…

例えばAdwords(Google広告)へのkwや広告文の入稿データの作成を行う場合、文字数のカウントをエクセルのLENBで行うことが多いと思う。BigQuery上でも同様の文字数カウントを実現する。LENBを出したいカラム名がkeywordの場合、以下のように書けば良い。 LENG…

概要 以下のチュートリアルをやってみた。 Getting started with BigQuery ML for data analysts | BigQuery | Google Cloud これを少しアレンジして、 Google Analyticsのデータから、次のセッションでcv(transaction)が発生するか、BigQuery ML のロジステ…

概要 特定の要素を持つ配列全体を、EXISTS句を使って抽出します。 BigQuery Exportのデータなど、構造化されたBigQueryのデータを処理する際、元の構造を保ったまま特定の条件にあった行だけを抽出したいことがあるかと思います。例 特定のページビューが発…

以下の流れで処理しています。 TO_CODE_POINTS 関数を使って文字列をコードポイントの配列に変換します。 コードポイント配列をUNNESTします。 コードポイントが全角英数にマッチする場合、対応する半角英数のコードポイントになるようにずらします。 コード…

想定する場面 BigQueryに格納されている、サイト内検索のデータや、Adwordsの検索クエリレポートなどから、よく使われる単語を抽出します。 例えば、 「Big Query run」 「j Query run」 という2行のデータがある時 word count Query 2 run 2 j 1 Big 1 のよ…

想定する場面 何かクエリを試したいけど手頃なテーブルがBigQuery上にない時がたまによくあるかと思います。 必要なデータが少ない場合は、以下の方法でサブクエリにデータを書ききり、テーブル代わりとして扱えるものを用意した方が早いことがあります。 今…

想定する場面 例えば、複数のカラムでSUMをとり、カラム名として元のカラム名をそのままつけたい、 ということは、割とよくあると思います。 そういった時に、この機能が使えます。 作業イメージ やり方 今回は、Google Analyticsのサンプルデータセットを使…

書いていること 主にBigQueryについて書きます。たまに他のことも書きます。 chaso-hamaokaのプロフィール 普段はアクセス解析やウェブ広告の運用をしています。たまにPython、javaScript、Unity、Kotlin。