BigQueryでAdvent Calendarを作成してみた

この投稿は「[グレンジ Advent Calendar 2024](https://qiita.com/advent-calendar/2024/grenge)」の11日目の記事です。

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

## 記事データの準備

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

```sql
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の違いを徹底解説 |

## 日付から週データを抽出

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

```sql
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の違いを徹底解説 |

## カレンダーの表示

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

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

## まとめ

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