自己紹介
この記事は BBSakura Networksアドベントカレンダー2025 の13日目の記事です。
こんにちは、BBSakura Networks株式会社 でネットワークエンジニアをしている丸野です。
背景
最近、SQL（厳密にはpsql）を使う機会が増えました。
理由は、BIツールとして Metabase というツールをよく使っていて、自社サービスを運用するための可視化をSQLで書いているためです。
最近のBIツールはホントよくできていて、GUIでポチポチするだけでも結構いい感じの可視化ができるんですが、SQLで直接書くことで細かくいろんなことができたり、テキストコピーによりノウハウを汎用性高く楽に使いまわせたりするので、ひととおり覚えちゃえば楽に感じてます。
そんなこんなで今回は、自分が可視化を取り組み始めたときに苦戦したことがあったので、その内容を情報として残すためにまとめてみました。
やりたいこと
「月次の売上個数を可視化したい」
よくあるような要件だと思います。
今回は アパレルショップのデータ管理 を雑な例としつつ、月次ごとに売れた個数をDBから直接出力してみようと思います。
各テーブルはこんな感じ。
####################################
## 実行環境準備まわりメモ
####################################
## ログイン、DB作成
$ sudo -i -u postgres
$ psql
psql> CREATE DATABASE mydb;
## DB確認
psql> \l
## 作ったDBに移動
psql> quit
$ psql -U postgres -d mydb
## ログアウト、DB削除
psql> quit
$ dropdb -U postgres mydb
------------------------------------
-- グッズのアイテム登録
------------------------------------
CREATE TABLE IF NOT EXISTS items (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);
INSERT INTO items (name)
VALUES
('ショルダーバッグ'), -- id=1
('ブランドシール'), -- id=2
('マグカップ'), -- id=3
('手ぬぐい') -- id=4
RETURNING id;
------------------------------------
-- 販売店登録
------------------------------------
CREATE TABLE IF NOT EXISTS stores (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);
INSERT INTO stores (name)
VALUES
('東京店'), -- id=1
('大阪店'), -- id=2
('福岡店'), -- id=3
('仙台店') -- id=4
RETURNING id;
------------------------------------
-- 注文データ登録
------------------------------------
CREATE TABLE IF NOT EXISTS orders (
id BIGSERIAL PRIMARY KEY,
item_id BIGINT NOT NULL,
store_id BIGINT NOT NULL,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (item_id) REFERENCES items(id),
FOREIGN KEY (store_id) REFERENCES stores(id)
);
-- サンプル注文データ
INSERT INTO orders (item_id, store_id, created_at)
VALUES
-- 2025/07
(1, 1, '2025-07-05 10:00:00+00'),
(2, 1, '2025-07-05 10:00:00+00'),
(3, 2, '2025-07-15 14:30:00+00'),
(4, 3, '2025-07-20 09:45:00+00'),
-- 2025/08
(2, 2, '2025-08-03 11:00:00+00'),
(3, 2, '2025-08-03 11:00:00+00'),
(2, 2, '2025-08-18 16:00:00+00'),
(4, 2, '2025-08-25 08:15:00+00'),
-- 2025/10
(1, 3, '2025-10-01 12:00:00+00'),
(2, 3, '2025-10-01 12:00:00+00'),
(3, 4, '2025-10-10 13:20:00+00'),
(4, 1, '2025-10-22 17:05:00+00'),
-- 2025/11
(3, 4, '2025-11-05 09:00:00+00'),
(1, 4, '2025-11-05 09:00:00+00'),
(2, 4, '2025-11-15 15:30:00+00'),
(4, 4, '2025-11-28 19:45:00+00')
RETURNING id;
※実際のサービスであれば、顧客情報や課金情報なども必要だと思いますが、今回は説明をシンプルにするために省略してます。
月次データを直接出力するためのSELECT文
これよりより良い書き方はありそうですが、こんな感じになりました。
SELECT
Tab01.yyyymm,
Tab01.item_name,
Tab01.store_name,
COALESCE(Tab02.order_count, 0) AS total_order
FROM
(
SELECT
TO_CHAR((_col01)::date, 'YYYY/MM') AS yyyymm,
items.id AS item_id,
items.name AS item_name,
stores.id AS store_id,
stores.name AS store_name
FROM
GENERATE_SERIES('2025-07-01'::date, DATE_TRUNC('month', now())::date, interval '1 month') AS _col01
CROSS JOIN
stores
CROSS JOIN
items
) AS Tab01
LEFT JOIN
(
SELECT
COUNT(orders.id) AS order_count,
TO_CHAR(DATE_TRUNC('month', orders.created_at)::date, 'YYYY/MM') AS yyyymm,
orders.item_id,
orders.store_id
FROM
orders
WHERE
orders.created_at IS NOT NULL
GROUP BY
yyyymm,
orders.item_id,
orders.store_id
) AS Tab02
ON
Tab01.yyyymm = Tab02.yyyymm
AND Tab01.item_id = Tab02.item_id
AND Tab01.store_id = Tab02.store_id
ORDER BY
Tab01.yyyymm ASC,
Tab01.item_id ASC,
Tab01.store_id ASC;
出力されるデータ
こんな感じ
yyyymm | item_name | store_name | total_order
---------+------------------+------------+-------------
2025/07 | ショルダーバッグ | 東京店 | 1
2025/07 | ショルダーバッグ | 大阪店 | 0
2025/07 | ショルダーバッグ | 福岡店 | 0
2025/07 | ショルダーバッグ | 仙台店 | 0
2025/07 | ブランドシール | 東京店 | 1
2025/07 | ブランドシール | 大阪店 | 0
2025/07 | ブランドシール | 福岡店 | 0
2025/07 | ブランドシール | 仙台店 | 0
2025/07 | マグカップ | 東京店 | 0
2025/07 | マグカップ | 大阪店 | 1
2025/07 | マグカップ | 福岡店 | 0
2025/07 | マグカップ | 仙台店 | 0
2025/07 | 手ぬぐい | 東京店 | 0
2025/07 | 手ぬぐい | 大阪店 | 0
2025/07 | 手ぬぐい | 福岡店 | 1
2025/07 | 手ぬぐい | 仙台店 | 0
2025/08 | ショルダーバッグ | 東京店 | 0
2025/08 | ショルダーバッグ | 大阪店 | 0
2025/08 | ショルダーバッグ | 福岡店 | 0
2025/08 | ショルダーバッグ | 仙台店 | 0
2025/08 | ブランドシール | 東京店 | 0
2025/08 | ブランドシール | 大阪店 | 2
2025/08 | ブランドシール | 福岡店 | 0
2025/08 | ブランドシール | 仙台店 | 0
2025/08 | マグカップ | 東京店 | 0
2025/08 | マグカップ | 大阪店 | 1
2025/08 | マグカップ | 福岡店 | 0
2025/08 | マグカップ | 仙台店 | 0
2025/08 | 手ぬぐい | 東京店 | 0
2025/08 | 手ぬぐい | 大阪店 | 1
2025/08 | 手ぬぐい | 福岡店 | 0
2025/08 | 手ぬぐい | 仙台店 | 0
~以下省略~
苦悩したところ「レコードが存在しない月をどう処理するか」
DBが持っている注文データはあくまで注文が発生したときのデータしかなく、データがない月の表現（いわゆるゼロ埋め的な処理）をSQLで実現するにはどうすればいいか、を考えるところが一番悩みました。
解決方法としては、データを修正する前に「日付 / item / store が網羅された、土台となるデータ」をサブクエリを使ってあらかじめ生成したうえで、注文データをJOINしていく形で解決しました。
※「日付 / item / store が網羅された、土台となるデータ」について
SELECT文では、GENERATE_SERIESを用いて年月の集合となる"_col01"を生成し、そこにCROSS JOINで"items", "stores"テーブルを結合しています。
イメージとしては、エクセルで集計する流れと全く同じです。
エクセルでデータを作るときも皆さん無意識にそうしていると思うんですけど、集計したいデータと日付のマトリックス表を作ると思います。それと同じです。
DBから直接SQLでデータ出力するメリット
月次データをDBから直接出力することができれば、あとはBIツール側で、
- 棒グラフを用いて、より見やすくする
- 検索条件を設けて、item や store でフィルタできるようにする
- ほぼリアルタイムでデータ更新される
など、色々できます。
まとめ
「今更 SQL の SELECT 文か」と思う人もいるかもしれないですが、
- ノウハウの使いまわしが簡単
- BI ツールにおける SQL は最も汎用性が高いので、他ツールへ移行したい場合も簡単
など、まだまだ SQL は便利だなと思いました。
将来的には LLM ツールを用いて、この辺の可視化ツールづくりは色々スキップできるかもしれませんが（やっていることはかなりシンプルなので）、考え方とか構造を理解することは無駄にならないと思います。
個人的にはこの悩みを解決してから、アプリの SQL 処理まわりの読み解きが早くなりました。実際はBI ツールを通して DB のデータ構造に対する理解が深まっただけかもしれませんが、、、
おわり。