【休日雑談】BigQuery100日チャレンジ・クエリまとめ（1）

BigQueryを理解すべく100日チャレンジしていたときのクエリまとめです。

練習したいけどクエリが何も思い浮かばない…！というときに参照ください。
クエリそのものは粗いかもしれないのでご了承ください。

ちなみにGAやGSCのクエリのボリュームが少ないというのもあり課金はされない程度のレベルのものです。

100日間の履歴（1〜50日目）

BigQuery100日チャレンジ（1日目）

プロジェクトを作る。 pic.twitter.com/wouRoXIux7

— FG🐡 (@fuguti) January 31, 2023

BigQuery100日チャレンジ（2日目）

いきなり間違えてしまったデータセットを削除する。

「削除」 pic.twitter.com/TnjALg4vuZ

— FG🐡 (@fuguti) February 1, 2023

BigQuery100日チャレンジ（3日目）

適当なカスタマーファイルをcsvで作ってテーブルを作る。

方法はかつて下記のURLにあったものを参考にした。（消えてしまった）
ttps://qiita.com/ymto/items/d818899850801bb591f6 pic.twitter.com/00pQOYHG8M

— FG🐡 (@fuguti) February 2, 2023

BigQuery100日チャレンジ（4日目）

オライリー「初めてのSQL」より「9-1 サブクエリとは何か」項目。 pic.twitter.com/R7dgDW4DAy

— FG🐡 (@fuguti) February 3, 2023

BigQuery100日チャレンジ（5日目）

INで複数条件を設定する。 pic.twitter.com/d8atTQ18Hi

— FG🐡 (@fuguti) February 4, 2023

BigQuery100日チャレンジ（6日目）

Command + *でコメントアウトできた。 pic.twitter.com/0OLeUHuCRo

— FG🐡 (@fuguti) February 5, 2023

BigQuery100日チャレンジ（7日目）

CONCATを使ってFullnameとIminainameをつくる。
（そしてここでfirstnameとlastnameを逆に書いていることに気付く） pic.twitter.com/0F5Ys7SuqK

— FG🐡 (@fuguti) February 6, 2023

BigQuery100日チャレンジ（8日目）

NOT IN で東京以外の人を出して、iminainameにTokyo名字の人が出てこなくなる（それはそう pic.twitter.com/5oo5RABEeD

— FG🐡 (@fuguti) February 7, 2023

BigQuery100日チャレンジ（9日目）

新しい別のテーブルを作る。その際に何の項目か分かるようにスキーマの編集で説明を入れる。 pic.twitter.com/aVIHGVeDNK

— FG🐡 (@fuguti) February 8, 2023

BigQuery100日チャレンジ（10日目）

都道府県ごとの数を出す。
GROUP BY 1 を使って玄人になった気持ちになる。 pic.twitter.com/CCChtBtnUS

— FG🐡 (@fuguti) February 9, 2023

BigQuery100日チャレンジ（11日目）

BETWEENを使ってバックリンクを200〜500持っている人の氏名を抜き出す。（昇順）

バックリンクを持っている人？🤔 pic.twitter.com/VZyFLLxnSf

— FG🐡 (@fuguti) February 10, 2023

BigQuery100日チャレンジ（12日目）

よくわからないままとりあえず試したTRIM関数。雰囲気でTRIM関数を使っている。

ここを参考にhttps://t.co/iq6dSbTghj pic.twitter.com/I3swqlAgov

— FG🐡 (@fuguti) February 11, 2023

BigQuery100日チャレンジ（13日目）

顧客フルネームを抜粋したビューを作成した。
おおー！ってなるｗ pic.twitter.com/1pKRFjlqyB

— FG🐡 (@fuguti) February 12, 2023

BigQuery100日チャレンジ（14日目）

chatGPTくんに結合の方法を聞いてやってみた。+ 簡略化。 pic.twitter.com/FFurfSOXDI

— FG🐡 (@fuguti) February 13, 2023

BigQuery100日チャレンジ（15日目）

exceptで特定のカラムを除外する。

こちらを参考にした。https://t.co/ppboMpzXXS

後半はchatGPTくんの回答（やり方が間違ってるのか、情報が間違ってるのかわからないけどできなかった） pic.twitter.com/OLy13RKLH1

— FG🐡 (@fuguti) February 14, 2023

BigQuery100日チャレンジ（16日目）

異なる顧客情報をfirst_nameで結合してビューにして、以降、失敗をする。
多分これhttps://t.co/f4YSDolWty pic.twitter.com/95sdca8BXS

— FG🐡 (@fuguti) February 15, 2023

BigQuery100日チャレンジ（17日目）

昨日の続き。JOINで事前にテーブルを指定するとambiguousが防げる。 pic.twitter.com/uxTk3quTnV

— FG🐡 (@fuguti) February 16, 2023

BigQuery100日チャレンジ（18日目）

続き。JOINの際にどちらのテーブルから引っ張るかを列挙してビューを作る。うまく動いた。（もうちょいやりようがある気もするｗ） pic.twitter.com/mmpfVVM1Uh

— FG🐡 (@fuguti) February 17, 2023

BigQuery100日チャレンジ（19日目）

ヘルプ見てて思った。型によって使い方が違う。https://t.co/H71Uk87AcE pic.twitter.com/83z25alIHi

— FG🐡 (@fuguti) February 18, 2023

BigQuery100日チャレンジ（20日目）

グループを作る。 pic.twitter.com/YthserLHql

— FG🐡 (@fuguti) February 19, 2023

BigQuery100日チャレンジ（21日目）

depositの値に「¥」を入れてしまい、文字列になったのでcsvで数値にして再びアップロードする。ぴえん。 pic.twitter.com/STc5SAibsn

— FG🐡 (@fuguti) February 20, 2023

BigQuery100日チャレンジ（22日目・フライング）

マテリアライズド・ビュー！ pic.twitter.com/Yn6clCXUXl

— FG🐡 (@fuguti) February 21, 2023

BigQuery100日チャレンジ（23日目）

INSERT INTOでデータを追加する。

こちらを参考にした。https://t.co/YbfwPtgKeH pic.twitter.com/wP3b32mT3f

— FG🐡 (@fuguti) February 22, 2023

BigQuery100日チャレンジ（24日目）

JOIN , INNER JOIN ,LEFT OUTER JOIN ,RIGHT OUTER JOIN

RIGHT OUTER JOINで昨日追加したデータがyから抽出される。 pic.twitter.com/6psZUnnDtV

— FG🐡 (@fuguti) February 23, 2023

BigQuery100日チャレンジ（25日目）

csvのアップロードで新たなデータを追加する。
女性データが追加された。合計60名のデータに。

命名はここで。https://t.co/IVNG7N3HOi pic.twitter.com/juLzlyP6eD

— FG🐡 (@fuguti) February 24, 2023

BigQuery100日チャレンジ（26日目）

31人分のデータしか抽出されないはずなのになぜか34人になる。よくよく見るとfirst_nameで紐付けてるので苗字（※）が同じ人のデータが重複して出てきている。
池田が二人いることによって起こる状況。

※本当はlast nameだけど最初の設定で間違えた pic.twitter.com/FwMpw7vZOd

— FG🐡 (@fuguti) February 25, 2023

BigQuery100日チャレンジ（27日目）

INFORMATION_SCHEMA.TABLESで情報を確認する。https://t.co/dnaKiRHaC5 pic.twitter.com/UJ8vCzg9i8

— FG🐡 (@fuguti) February 26, 2023

BigQuery100日チャレンジ（28日目）

ROW_NUMBERを使ってバックリンクの多い順にランキングを付けて並べ替える。 pic.twitter.com/4NKOHVqaxa

— FG🐡 (@fuguti) February 27, 2023

BigQuery100日チャレンジ（29日目）

customer_idと年齢、適当な日付を入れたデータをアップロードする。DATE爆誕。 pic.twitter.com/5XYUhRCqkI

— FG🐡 (@fuguti) February 28, 2023

BigQuery100日チャレンジ（30日目）

2つのデータをくっつけたものをマテリアライズドビュー化する。 pic.twitter.com/Ic4tfZX7wo

— FG🐡 (@fuguti) March 1, 2023

BigQuery100日チャレンジ（31日目）

ROW_NUMBER
RANK
DENSE_RANK
の違い。 pic.twitter.com/alNE02jov7

— FG🐡 (@fuguti) March 2, 2023

BigQuery100日チャレンジ（32日目）

29日目にやった元ファイルのidが重複していたのでid振り直してアップロードする。一回休み。 pic.twitter.com/lYpSgsNs8a

— FG🐡 (@fuguti) March 3, 2023

BigQuery100日チャレンジ（33日目）

マテリアライズドビュー知らなかったシリーズ。 pic.twitter.com/n5ttYqgIdu

— FG🐡 (@fuguti) March 4, 2023

BigQuery100日チャレンジ（34日目）

いろいろなデータをくっつけたマテリアライズドビュー（雑）を作る。 pic.twitter.com/xMGWG22OI0

— FG🐡 (@fuguti) March 5, 2023

BigQuery100日チャレンジ（35日目）

PARTITION BYをつかって都道府県別にバックリンクを持っている人をランク付けする。

東京では近藤、長野では金城、埼玉は山本が一番持ってる（誰🤔） pic.twitter.com/muIwZ83ua8

— FG🐡 (@fuguti) March 6, 2023

BigQuery100日チャレンジ（36日目）

ビューの詳細下に元のクエリがある。 pic.twitter.com/80zkXmllv0

— FG🐡 (@fuguti) March 7, 2023

BigQuery100日チャレンジ（37日目）

ユーザー定義の関数を作る（この前会社で知った）

これはヘルプにあるAddFourAndDivide（xに4足して、y で割る）https://t.co/UR99igLdFR pic.twitter.com/Ku5QKhQQNJ

— FG🐡 (@fuguti) March 8, 2023

BigQuery100日チャレンジ（38日目）

何でも3の倍数になるユーザー関数を作り、関数を使ってバックリンクと預金を3倍にする。
バックリンクと預金が3倍に増えてちょっと嬉しい気持ちになる。 pic.twitter.com/T9vmlfwtYq

— FG🐡 (@fuguti) March 9, 2023

BigQuery100日チャレンジ（39日目）

都道府県別バックリンクと預金の平均を出してバックリンクの多い順に。
東京のバックリンクがもっとも多い。 pic.twitter.com/a0jydzFWRV

— FG🐡 (@fuguti) March 10, 2023

BigQuery100日チャレンジ（40日目）

登録日をグルーピングしてその数を数える。
お正月に登録した人は一人。 pic.twitter.com/P57epbdB69

— FG🐡 (@fuguti) March 11, 2023

BigQuery100日チャレンジ（41日目）

グルーピングごとのバックリンク平均と合計。 pic.twitter.com/Qsf289J2Qu

— FG🐡 (@fuguti) March 12, 2023

BigQuery100日チャレンジ（42日目）

バックリンク所有数をlarge〜smallの3種類に分類する。

これの魔改造。https://t.co/kxTmAddsy9 pic.twitter.com/WNxo2hC1kK

— FG🐡 (@fuguti) March 13, 2023

BigQuery100日チャレンジ（43日目）

baka_queryとそれ以外のクリックと表示回数。 pic.twitter.com/CEk745UgB8

— FG🐡 (@fuguti) March 14, 2023

BigQuery100日チャレンジ（44日目）

BigQueryに直接落としたSearch Consoleのanonymized_queryのデバイス別日毎の数値。 pic.twitter.com/y683SOhfZ1

— FG🐡 (@fuguti) March 15, 2023

BigQuery100日チャレンジ（45日目）

匿名のクエリとそうではないもののクリックとインプレッションを表示する。 pic.twitter.com/hJHkxdZsrb

— FG🐡 (@fuguti) March 16, 2023

BigQuery100日チャレンジ（46日目）

匿名・非匿名のクエリをマテリアライズドビュー化し、データポータルで参照。匿名・非匿名のクリック、表示回数の割合を出す。

（これを出すだけならCASE文を書く必要なかったのでは🤔、もっというと直接繋げば済む話なのでは🤔） pic.twitter.com/voV44v4Grn

— FG🐡 (@fuguti) March 17, 2023