BigQueryでAdvent Calendarを作成してみた

Last updated at 2024-12-20Posted at 2024-12-20

この投稿は「グレンジ Advent Calendar 2024」の11日目の記事です。

BigQueryを使用して、Advent Calendarをカレンダー形式で表示させたいと思います。

記事データの準備

まず、日付と記事タイトルのデータのクエリを用意します。
タイトルは生成AIで作成してもらいました。

WITH

calendar AS (
  SELECT
    date,
    title,
  FROM
    UNNEST([
      STRUCT(DATE('2024-12-01') AS date, 'JavaScriptで雪を降らせる方法：アニメーションの基本と応用' AS title),
      STRUCT('2024-12-02', '初心者でもわかるDockerコンテナの魔法'),
      STRUCT('2024-12-03', 'Pythonで作る自動クリスマスカード生成プログラム'),
      STRUCT('2024-12-04', 'ChatGPT APIを使ったAIボットでクリスマスをもっと楽しく！'),
      STRUCT('2024-12-05', 'Next.jsで超簡単なポートフォリオサイトを作る'),
      STRUCT('2024-12-06', 'Gitの知られざる便利コマンド10選'),
      STRUCT('2024-12-07', 'Rustで始める高速なアプリケーション開発'),
      STRUCT('2024-12-08', '3DプリンターとFusion360で作るクリスマスオーナメント'),
      STRUCT('2024-12-09', '5分で理解するCI/CDパイプラインの基本'),
      STRUCT('2024-12-10', 'React Hooksの応用で作るインタラクティブなカレンダー'),
      STRUCT('2024-12-11', 'Kubernetesの内部構造をわかりやすく解説'),
      STRUCT('2024-12-12', 'Go言語で始めるシンプルなWeb API開発'),
      STRUCT('2024-12-13', '機械学習を使ったプレゼント選びのアルゴリズムを作ってみた'),
      STRUCT('2024-12-14', 'Terraformで作るクラウドインフラの自動化'),
      STRUCT('2024-12-15', 'Linuxのcronジョブでできる面白いアイデア集'),
      STRUCT('2024-12-16', 'Unityで簡単な2Dゲームを作る：初心者向けガイド'),
      STRUCT('2024-12-17', 'Vue.jsで作るシンプルなToDoアプリ'),
      STRUCT('2024-12-18', 'セキュリティの基本：パスワード管理ツールを自作してみた'),
      STRUCT('2024-12-19', 'SQLのクエリ最適化でパフォーマンスを劇的に改善する方法'),
      STRUCT('2024-12-20', 'WebAssemblyを活用したフロントエンドパフォーマンスの向上'),
      STRUCT('2024-12-21', 'Swiftで作るシンプルなiOSアプリ'),
      STRUCT('2024-12-22', 'AI画像生成モデルの裏側を理解しよう'),
      STRUCT('2024-12-23', 'Bashスクリプトで自動化する日常のタスク'),
      STRUCT('2024-12-24', '初心者のためのデバッグツール完全ガイド'),
      STRUCT('2024-12-25', 'クラウドサービス比較2024：AWS、Azure、GCPの違いを徹底解説')
    ])
)

SELECT * FROM calendar

実行すると、以下のように表示されます。

date	title
2024-12-01	JavaScriptで雪を降らせる方法：アニメーションの基本と応用
2024-12-02	初心者でもわかるDockerコンテナの魔法
2024-12-03	Pythonで作る自動クリスマスカード生成プログラム
2024-12-04	ChatGPT APIを使ったAIボットでクリスマスをもっと楽しく！
2024-12-05	Next.jsで超簡単なポートフォリオサイトを作る
2024-12-06	Gitの知られざる便利コマンド10選
2024-12-07	Rustで始める高速なアプリケーション開発
2024-12-08	3DプリンターとFusion360で作るクリスマスオーナメント
2024-12-09	5分で理解するCI/CDパイプラインの基本
2024-12-10	React Hooksの応用で作るインタラクティブなカレンダー
2024-12-11	Kubernetesの内部構造をわかりやすく解説
2024-12-12	Go言語で始めるシンプルなWeb API開発
2024-12-13	機械学習を使ったプレゼント選びのアルゴリズムを作ってみた
2024-12-14	Terraformで作るクラウドインフラの自動化
2024-12-15	Linuxのcronジョブでできる面白いアイデア集
2024-12-16	Unityで簡単な2Dゲームを作る：初心者向けガイド
2024-12-17	Vue.jsで作るシンプルなToDoアプリ
2024-12-18	セキュリティの基本：パスワード管理ツールを自作してみた
2024-12-19	SQLのクエリ最適化でパフォーマンスを劇的に改善する方法
2024-12-20	WebAssemblyを活用したフロントエンドパフォーマンスの向上
2024-12-21	Swiftで作るシンプルなiOSアプリ
2024-12-22	AI画像生成モデルの裏側を理解しよう
2024-12-23	Bashスクリプトで自動化する日常のタスク
2024-12-24	初心者のためのデバッグツール完全ガイド
2024-12-25	クラウドサービス比較2024：AWS、Azure、GCPの違いを徹底解説

日付から週データを抽出

週データが必要なので、クエリを修正します。

WITH

calendar AS (
  SELECT
    date,
    title,
  FROM
    UNNEST([
      STRUCT(DATE('2024-12-01') AS date, 'JavaScriptで雪を降らせる方法：アニメーションの基本と応用' AS title),
      STRUCT('2024-12-02', '初心者でもわかるDockerコンテナの魔法'),
      STRUCT('2024-12-03', 'Pythonで作る自動クリスマスカード生成プログラム'),
      STRUCT('2024-12-04', 'ChatGPT APIを使ったAIボットでクリスマスをもっと楽しく！'),
      STRUCT('2024-12-05', 'Next.jsで超簡単なポートフォリオサイトを作る'),
      STRUCT('2024-12-06', 'Gitの知られざる便利コマンド10選'),
      STRUCT('2024-12-07', 'Rustで始める高速なアプリケーション開発'),
      STRUCT('2024-12-08', '3DプリンターとFusion360で作るクリスマスオーナメント'),
      STRUCT('2024-12-09', '5分で理解するCI/CDパイプラインの基本'),
      STRUCT('2024-12-10', 'React Hooksの応用で作るインタラクティブなカレンダー'),
      STRUCT('2024-12-11', 'Kubernetesの内部構造をわかりやすく解説'),
      STRUCT('2024-12-12', 'Go言語で始めるシンプルなWeb API開発'),
      STRUCT('2024-12-13', '機械学習を使ったプレゼント選びのアルゴリズムを作ってみた'),
      STRUCT('2024-12-14', 'Terraformで作るクラウドインフラの自動化'),
      STRUCT('2024-12-15', 'Linuxのcronジョブでできる面白いアイデア集'),
      STRUCT('2024-12-16', 'Unityで簡単な2Dゲームを作る：初心者向けガイド'),
      STRUCT('2024-12-17', 'Vue.jsで作るシンプルなToDoアプリ'),
      STRUCT('2024-12-18', 'セキュリティの基本：パスワード管理ツールを自作してみた'),
      STRUCT('2024-12-19', 'SQLのクエリ最適化でパフォーマンスを劇的に改善する方法'),
      STRUCT('2024-12-20', 'WebAssemblyを活用したフロントエンドパフォーマンスの向上'),
      STRUCT('2024-12-21', 'Swiftで作るシンプルなiOSアプリ'),
      STRUCT('2024-12-22', 'AI画像生成モデルの裏側を理解しよう'),
      STRUCT('2024-12-23', 'Bashスクリプトで自動化する日常のタスク'),
      STRUCT('2024-12-24', '初心者のためのデバッグツール完全ガイド'),
      STRUCT('2024-12-25', 'クラウドサービス比較2024：AWS、Azure、GCPの違いを徹底解説')
    ])
),

calendar_with_weekday AS (
  SELECT
    EXTRACT(WEEK FROM date) AS week,
    EXTRACT(DAYOFWEEK FROM date) AS weekday,
    EXTRACT(DAY FROM date) AS day,
    title,
  FROM
    calendar
)

SELECT * FROM calendar_with_weekday

EXTRACTを使用して、週数と曜日と日を抽出しています。

実行すると、以下のように表示されます。

week	weekday	day	title
48	1	1	JavaScriptで雪を降らせる方法：アニメーションの基本と応用
48	2	2	初心者でもわかるDockerコンテナの魔法
48	3	3	Pythonで作る自動クリスマスカード生成プログラム
48	4	4	ChatGPT APIを使ったAIボットでクリスマスをもっと楽しく！
48	5	5	Next.jsで超簡単なポートフォリオサイトを作る
48	6	6	Gitの知られざる便利コマンド10選
48	7	7	Rustで始める高速なアプリケーション開発
49	1	8	3DプリンターとFusion360で作るクリスマスオーナメント
49	2	9	5分で理解するCI/CDパイプラインの基本
49	3	10	React Hooksの応用で作るインタラクティブなカレンダー
49	4	11	Kubernetesの内部構造をわかりやすく解説
49	5	12	Go言語で始めるシンプルなWeb API開発
49	6	13	機械学習を使ったプレゼント選びのアルゴリズムを作ってみた
49	7	14	Terraformで作るクラウドインフラの自動化
50	1	15	Linuxのcronジョブでできる面白いアイデア集
50	2	16	Unityで簡単な2Dゲームを作る：初心者向けガイド
50	3	17	Vue.jsで作るシンプルなToDoアプリ
50	4	18	セキュリティの基本：パスワード管理ツールを自作してみた
50	5	19	SQLのクエリ最適化でパフォーマンスを劇的に改善する方法
50	6	20	WebAssemblyを活用したフロントエンドパフォーマンスの向上
50	7	21	Swiftで作るシンプルなiOSアプリ
51	1	22	AI画像生成モデルの裏側を理解しよう
51	2	23	Bashスクリプトで自動化する日常のタスク
51	3	24	初心者のためのデバッグツール完全ガイド
51	4	25	クラウドサービス比較2024：AWS、Azure、GCPの違いを徹底解説

カレンダーの表示

カレンダー形式になるように、クエリを修正します。

WITH

calendar AS (
  SELECT
    date,
    title,
  FROM
    UNNEST([
      STRUCT(DATE('2024-12-01') AS date, 'JavaScriptで雪を降らせる方法：アニメーションの基本と応用' AS title),
      STRUCT('2024-12-02', '初心者でもわかるDockerコンテナの魔法'),
      STRUCT('2024-12-03', 'Pythonで作る自動クリスマスカード生成プログラム'),
      STRUCT('2024-12-04', 'ChatGPT APIを使ったAIボットでクリスマスをもっと楽しく！'),
      STRUCT('2024-12-05', 'Next.jsで超簡単なポートフォリオサイトを作る'),
      STRUCT('2024-12-06', 'Gitの知られざる便利コマンド10選'),
      STRUCT('2024-12-07', 'Rustで始める高速なアプリケーション開発'),
      STRUCT('2024-12-08', '3DプリンターとFusion360で作るクリスマスオーナメント'),
      STRUCT('2024-12-09', '5分で理解するCI/CDパイプラインの基本'),
      STRUCT('2024-12-10', 'React Hooksの応用で作るインタラクティブなカレンダー'),
      STRUCT('2024-12-11', 'Kubernetesの内部構造をわかりやすく解説'),
      STRUCT('2024-12-12', 'Go言語で始めるシンプルなWeb API開発'),
      STRUCT('2024-12-13', '機械学習を使ったプレゼント選びのアルゴリズムを作ってみた'),
      STRUCT('2024-12-14', 'Terraformで作るクラウドインフラの自動化'),
      STRUCT('2024-12-15', 'Linuxのcronジョブでできる面白いアイデア集'),
      STRUCT('2024-12-16', 'Unityで簡単な2Dゲームを作る：初心者向けガイド'),
      STRUCT('2024-12-17', 'Vue.jsで作るシンプルなToDoアプリ'),
      STRUCT('2024-12-18', 'セキュリティの基本：パスワード管理ツールを自作してみた'),
      STRUCT('2024-12-19', 'SQLのクエリ最適化でパフォーマンスを劇的に改善する方法'),
      STRUCT('2024-12-20', 'WebAssemblyを活用したフロントエンドパフォーマンスの向上'),
      STRUCT('2024-12-21', 'Swiftで作るシンプルなiOSアプリ'),
      STRUCT('2024-12-22', 'AI画像生成モデルの裏側を理解しよう'),
      STRUCT('2024-12-23', 'Bashスクリプトで自動化する日常のタスク'),
      STRUCT('2024-12-24', '初心者のためのデバッグツール完全ガイド'),
      STRUCT('2024-12-25', 'クラウドサービス比較2024：AWS、Azure、GCPの違いを徹底解説')
    ])
),

calendar_with_weekday AS (
  SELECT
    EXTRACT(WEEK FROM date) AS week,
    EXTRACT(DAYOFWEEK FROM date) AS weekday,
    EXTRACT(DAY FROM date) AS day,
    title,
  FROM
    calendar
)

SELECT
  `日`,
  `月`,
  `火`,
  `水`,
  `木`,
  `金`,
  `土`
FROM
  calendar_with_weekday
PIVOT(
  MAX(CONCAT(day, "\n", title))
  FOR weekday
  IN (
    1 AS `日`,
    2 AS `月`,
    3 AS `火`,
    4 AS `水`,
    5 AS `木`,
    6 AS `金`,
    7 AS `土`
  )
)
ORDER BY
  week

PIVOTを使用して、weekday列の行データから曜日列データに変換しています。

実行すると、以下のように表示されます。

日	月	火	水	木	金	土
1 JavaScriptで雪を降らせる方法：アニメーションの基本と応用	2 初心者でもわかるDockerコンテナの魔法	3 Pythonで作る自動クリスマスカード生成プログラム	4 ChatGPT APIを使ったAIボットでクリスマスをもっと楽しく！	5 Next.jsで超簡単なポートフォリオサイトを作る	6 Gitの知られざる便利コマンド10選	7 Rustで始める高速なアプリケーション開発
8 3DプリンターとFusion360で作るクリスマスオーナメント	9 5分で理解するCI/CDパイプラインの基本	10 React Hooksの応用で作るインタラクティブなカレンダー	11 Kubernetesの内部構造をわかりやすく解説	12 Go言語で始めるシンプルなWeb API開発	13 機械学習を使ったプレゼント選びのアルゴリズムを作ってみた	14 Terraformで作るクラウドインフラの自動化
15 Linuxのcronジョブでできる面白いアイデア集	16 Unityで簡単な2Dゲームを作る：初心者向けガイド	17 Vue.jsで作るシンプルなToDoアプリ	18 セキュリティの基本：パスワード管理ツールを自作してみた	19 SQLのクエリ最適化でパフォーマンスを劇的に改善する方法	20 WebAssemblyを活用したフロントエンドパフォーマンスの向上	21 Swiftで作るシンプルなiOSアプリ
22 AI画像生成モデルの裏側を理解しよう	23 Bashスクリプトで自動化する日常のタスク	24 初心者のためのデバッグツール完全ガイド	25 クラウドサービス比較2024：AWS、Azure、GCPの違いを徹底解説

まとめ

BigQueryで、比較的簡単にカレンダー形式の表示をすることができました。
PIVOTを使えば、他にもいろいろデータ表示に活用できそうですね。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up