その1からの続きです。
【休日雑談】BigQuery100日チャレンジ・クエリまとめ（1）
100日間の履歴（51〜100日目）
BigQuery100日チャレンジ（51日目）
BigQueryじゃんけん。 pic.twitter.com/Zxi5VDDaki
— FG🐡 (@fuguti) March 22, 2023
BigQuery100日チャレンジ（52日目）
BigQueryアナグラムチャレンジ。
アグナムラ
グナアラム
ムナグアラ
グムナラア pic.twitter.com/HuEXef0Zjn— FG🐡 (@fuguti) March 23, 2023
BigQuery100日チャレンジ（53日目）
間違った行のdelete。（と、削除履歴）
こちらを参考にした。https://t.co/GZuTEbKaOO pic.twitter.com/KsEB01r13A
— FG🐡 (@fuguti) March 24, 2023
BigQuery100日チャレンジ（54日目）
完成しないことが決まったBigQueryアナグラムチャレンジと特定の手しか出ないBigQueryじゃんけん。
FARM_FINGERPRINTヘルプhttps://t.co/8KRYIZ4jF0 pic.twitter.com/pz0VR1iIPC
— FG🐡 (@fuguti) March 25, 2023
BigQuery100日チャレンジ（55日目）
アラスカの時間、日本の時間、最終更新日を無意味に並べる。
CURRENT_DATEhttps://t.co/f7ZDhMHKqZ pic.twitter.com/hx4SEza7HU
— FG🐡 (@fuguti) March 26, 2023
BigQuery100日チャレンジ（56日目）
DATE_DIFFを使って最終更新日からの差分を出す。
DATE_DIFFhttps://t.co/fWakRTtcDu pic.twitter.com/10omUlBC2J
— FG🐡 (@fuguti) March 27, 2023
BigQuery100日チャレンジ（57日目）
DATE_ADDを使って登録期限切れ（1年）を出してみる。
DATE_ADDhttps://t.co/F41ewGjwI3 pic.twitter.com/kRHFgjaOBT
— FG🐡 (@fuguti) March 28, 2023
BigQuery100日チャレンジ（58日目）
更新期限の日付（月末）を出す。
LAST_DAYhttps://t.co/Z9pJc42T8Y pic.twitter.com/mIPgLxqDUq
— FG🐡 (@fuguti) March 29, 2023
BigQuery100日チャレンジ（59日目）
COUNTIFのテスト。Trueの数を返す。
COUNTIFhttps://t.co/uYb2bOBaS1 pic.twitter.com/BU6SxMqEtB
— FG🐡 (@fuguti) March 30, 2023
BigQuery100日チャレンジ（60日目）
COUNTIFに式を入れる。
当たり前だけど文字はTとtで区別される。 pic.twitter.com/gTEDUutJbN— FG🐡 (@fuguti) March 31, 2023
BigQuery100日チャレンジ（61日目）
複数条件をCOUNTIFで抽出してみる。 pic.twitter.com/qwtGutoQix
— FG🐡 (@fuguti) April 1, 2023
BigQuery100日チャレンジ（62日目）
体重と体脂肪、最低気温と体脂肪、最低気温と平均気温の相関。2枚目はexcelで検証の図。
CORRhttps://t.co/hCzIgNCrgI pic.twitter.com/kYVTTKQwJO
— FG🐡 (@fuguti) April 2, 2023
BigQuery100日チャレンジ（63日目）
パブリックデータにシェークスピアの演劇があってそれで遊ぶ。
参考：https://t.co/hy6exQ7L9D pic.twitter.com/6JCd8XgoJs
— FG🐡 (@fuguti) April 3, 2023
BigQuery100日チャレンジ（64日目）
Havingの箇所、corporaと2の違い。ほむほむ。 pic.twitter.com/k0tAmydlp5
— FG🐡 (@fuguti) April 4, 2023
BigQuery100日チャレンジ（65日目）
Wikipediaのデータセットもあった。とりあえずJapanと酒で文字数順に並べる。 pic.twitter.com/EI3bCPcHT2
— FG🐡 (@fuguti) April 5, 2023
BigQuery100日チャレンジ（66日目）
event_paramsのUNNEST pic.twitter.com/KkHH1ZgErj
— FG🐡 (@fuguti) April 6, 2023
BigQuery100日チャレンジ（67日目）
ChatGPTのお導きにより出た練習問題。
バックリンクの平均を出して平均以上の人と平均以下の人を出す。 pic.twitter.com/3fkYPBent6
— FG🐡 (@fuguti) April 7, 2023
BigQuery100日チャレンジ（68日目）
2022年1月1日以降に登録した人と、その所有バックリンクの数を求める。
CharGPT先生の練習問題をヒントに。 pic.twitter.com/yFjHYMtMA0— FG🐡 (@fuguti) April 8, 2023
BigQuery100日チャレンジ（69日目）
複数のSQL文を書く。 pic.twitter.com/KSupbDKkKk
— FG🐡 (@fuguti) April 9, 2023
BigQuery100日チャレンジ（70日目）
BigQueryを使ったメッセージ。実務でよく使う（嘘）。 pic.twitter.com/jFb2iIJhom
— FG🐡 (@fuguti) April 10, 2023
BigQuery100日チャレンジ（71日目）
毎回select * やってたけどプレビューでわかる。 pic.twitter.com/p1LLOdgpnC
— FG🐡 (@fuguti) April 11, 2023
BigQuery100日チャレンジ（72日目）
複数の条件で抽出。
福井の人、または、目標進捗50%以上でバックリンクを500以上持っている人。 pic.twitter.com/Rp9ViR2nI7— FG🐡 (@fuguti) April 12, 2023
BigQuery100日チャレンジ（73日目）
処理の重さの違い。
・1項目と特定の日
・1項目と特定の日以降
・すべての項目と特定の日
・3項目と特定の日 pic.twitter.com/poEB7jIv4X— FG🐡 (@fuguti) April 13, 2023
BigQuery100日チャレンジ（74日目）
処理の重さの違い。
・LIMIT 10
・LIMIT 1000
・LIMIT なし
・将来の日付（データがない） pic.twitter.com/VzCcU4ghk4— FG🐡 (@fuguti) April 14, 2023
BigQuery100日チャレンジ（75日目）
スプレッドシートのデータをBiqQueryに流す。
（「このクエリを実行すると、0 B が処理されます」）こちらを参考にした。思ったより簡単だった。https://t.co/whv86YIinv pic.twitter.com/JQy7FN0Cq1
— FG🐡 (@fuguti) April 15, 2023
BigQuery100日チャレンジ（76日目）
データからモデルを作る。
ここを参考にした。https://t.co/cPgAcnnxrt pic.twitter.com/YhrZKTtmfE— FG🐡 (@fuguti) April 16, 2023
BigQuery100日チャレンジ（77日目）
モデルの評価をする pic.twitter.com/3aOzspev0r
— FG🐡 (@fuguti) April 17, 2023
BigQuery100日チャレンジ（78日目）＆100日過ぎてからなにか描く（103日目）
解約予測と消費データを出して、Tableauで描画。ダッシュボードで並べる。 pic.twitter.com/ZOwKXrZKoD
— FG🐡 (@fuguti) April 18, 2023
BigQuery100日チャレンジ（79日目）
スプレッドシートのデータを更新するとBigQuery側のデータも反映される。ただし存在しないフィールドは出てこない。 pic.twitter.com/8RlHhJVUZQ
— FG🐡 (@fuguti) April 19, 2023
BigQuery100日チャレンジ（80日目）
標準偏差三種盛り。@yamariku_1004 先生に教えてもらった。
ヘルプはここhttps://t.co/fgMkpFdrU4 pic.twitter.com/0B9CEfwJUn
— FG🐡 (@fuguti) April 20, 2023
BigQuery100日チャレンジ（81日目）
都道府県別の預金合計をいろいろな方法で抽出してみる pic.twitter.com/eYcXn3s2Kt
— FG🐡 (@fuguti) April 21, 2023
BigQuery100日チャレンジ（82日目）
NOT BETWEENを使って間の日付を除外する。 pic.twitter.com/m6xwxkCziq
— FG🐡 (@fuguti) April 22, 2023
BigQuery100日チャレンジ（83日目）
スプレッドシートで顧客22人分の小学校時代のテストの点数をつくり、30人分の顧客情報と合わせる。
joinと顧客の参照元指定と結果の違い。
30人側の顧客名称を指定しないと一部空になる。 pic.twitter.com/weAID7g4rc— FG🐡 (@fuguti) April 23, 2023
BigQuery100日チャレンジ（84日目）
小学校の成績とバックリンク所持数の相関。あまり関係なさそう（それはそう） pic.twitter.com/xy2sk2QpXv
— FG🐡 (@fuguti) April 24, 2023
BigQuery100日チャレンジ（85日目）
STRING_AGG()で複数のデータをひとつにする。
repeatedだ！と思ったけど、自分で区切りを決めているのもあり当然そんなことはなかった。
あと GROUP_CONCATを使おうとしたけどうまくいかなかった。 pic.twitter.com/pZJsv6Gzif— FG🐡 (@fuguti) April 25, 2023
BigQuery100日チャレンジ（86日目）
BOOLEANのカウントはCOUNTではだめ。COUNTIFをつかう。
Search Consoleの匿名化されたクエリとそうではないものの数を数える。 pic.twitter.com/ybXaggGWPq— FG🐡 (@fuguti) April 26, 2023
BigQuery100日チャレンジ（87日目）
IFを使ってWEB検索とそれ以外の検索タイプのクリック、表示回数合計を出す。 pic.twitter.com/DkWrKUUy3Z
— FG🐡 (@fuguti) April 27, 2023
BigQuery100日チャレンジ（88日目）
CASE文使って検索タイプ別のクリック数と表示回数の合計を出す。 pic.twitter.com/u7XsZ0xqXe
— FG🐡 (@fuguti) April 28, 2023
BigQuery100日チャレンジ（89日目）
ヘルプに則ってSearch Consoleの匿名クエリと、クエリのNULLの数を数える。一致する。
テーブルのスキーマhttps://t.co/fXCDKzwCmT pic.twitter.com/8qJB5aVilf
— FG🐡 (@fuguti) April 29, 2023
BigQuery100日チャレンジ（90日目）
30行のtableとその中から10行抜粋したtableをUNION
UNION ALL -> 40
UNION DISTINCT ->30
INTERSECT DISTINCT ->10
EXCEPT DISTINCT ->20JOINみたいに絵に描くとわかりやすそう。
ヘルプhttps://t.co/3yt6Rl0tWG pic.twitter.com/5LR4vV6FUV
— FG🐡 (@fuguti) April 30, 2023
BigQuery100日チャレンジ（91日目）
CROSS JOINって何に使うんだろう…と思ってたけどクエリ×地域の掛け合わせとかに使えそう（多分違う pic.twitter.com/pDj1TArZaM
— FG🐡 (@fuguti) May 1, 2023
BigQuery100日チャレンジ（92日目）
池田の重複問題、JOINの条件を複数指定することで解消。
池田の重複問題https://t.co/Jv0kE0PSBH pic.twitter.com/7FupJbKwWt
— FG🐡 (@fuguti) May 2, 2023
BigQuery100日チャレンジ（93日目）
IFNULLを使って匿名クエリをぬるぬるする。 pic.twitter.com/sJQCZUcetE
— FG🐡 (@fuguti) May 3, 2023
BigQuery100日チャレンジ（94日目）
GA4のevent.paramsをUNNESTしてuser_pseudo_id、event_timestamp順に並べる。その中からpage_locationで区切ったものとそうではないもの。
ログ感が出ていて面白い。 pic.twitter.com/lBRX2EeRrD— FG🐡 (@fuguti) May 4, 2023
BigQuery100日チャレンジ（95日目）
Search Consoleの匿名クエリをクリック、表示回数の多い順に並べる。
WITH句を使いたかっただけなのと、WITH tと言ってみたかっただけのクエリ。 pic.twitter.com/pfCeOg0tMC— FG🐡 (@fuguti) May 5, 2023
BigQuery100日チャレンジ（96日目）
スケジュールされたクエリ。
毎月30日15:00:00にフルネームのかなに「い」を含む人物を抽出する。これを設定した担当者が退職して引き継がれてなかったらホラーなクエリ。 pic.twitter.com/Ysqc0adkKU
— FG🐡 (@fuguti) May 6, 2023
BigQuery100日チャレンジ（97日目）
横持ちデータを縦持ちに変換。木田さんの本から。
データの型の違いは面倒だと思ってたのでこれは感動。 pic.twitter.com/XQJzbOT9NZ— FG🐡 (@fuguti) May 7, 2023
BigQuery100日チャレンジ（98日目）
横持ちのデータを複数縦持ちに変換してそれぞれの平均を出してみる。これは便利ー！
※ただこのケースでは普通にAVGでいけるのではと、後から思ったｗ pic.twitter.com/nhbwn9irY2
— FG🐡 (@fuguti) May 8, 2023
BigQuery100日チャレンジ（99日目）
_table_suffixでGA4の特定のtableを抽出しに行く。 pic.twitter.com/FY1YmaRu7s
— FG🐡 (@fuguti) May 9, 2023
BigQuery100日チャレンジ（100日目）
SUBSTRING関数。
〜BigQuery100日チャレンジ 完〜 pic.twitter.com/mFNZHIjMoY
— FG🐡 (@fuguti) May 10, 2023
まとめ
さすがに色々と試行錯誤すると一定の理解は進むように思った100日でした。
私は100日の途中から読み始めたのですが、これから勉強するということであれば木田さんの本「集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析」がとても丁寧に解説されているのでおすすめです。
