この記事は ChatGPT Qiita Advent Calendar 2024 の 19 日目の記事です。

ChatGPT の Web 画面で SQL 作成を併走してもらって助かっているメモです。

背景

私自身のスキル領域のベースは Web フロントエンドを軸にしています。とはいえ、バックエンドの制作が絡んできてしまうこともありますし、とくに最近はデータベースまわりも一緒に対応することも多いです。

データベースに関しては、なんとなく SQL が分かっているとしても MySQL や PostgreSQL などで固有の記述であったり時系列系の週ごととか前年比とかいろいろな出し方が求められます。

ここがうまくいくとフロントエンドでのグラフ表示に代表される JSON データの調整が減るので、結果として制作コストが良い感じになり、開発がしやすくなります。

さて、このデータベースまわり。各開発に合ったものを検索サイトで検索で調べていたのですが、最近は ChatGPT の Web 画面で SQL 作成を併走してもらって助かっているという話を記しておきます。

今回のデータはあくまでもダミーです。また 2024/12/18 の情報で進めます。

使うのは ChatGPT の Web 画面

ということで、最近は Copilot や Gemini などいろいろありますが ChatGPT の Web 画面を例に進めてみます。GPT o4 です。

私も良く使います。

まずはスタート

PostgreSQL で SQL 部分のクエリを教えてほしいのでお付き合いください。PostgreSQL は作成済みで、ここから、データベース定義などすすめていきます。まず私から希望のデータベース定義などお伝えしていくのでお待ちください。

こんな質問でスタート。コツとしては待ってねって言うこと。いきなり、いろいろな SQL 分の例を話しだしたり PostgreSQL 自体の紹介をしだすので、それを抑えてます。

テーブル定義伝えて CREATE 文を教えてもらう

ありがとうございます！

今回は IoT のセンサーデータを蓄積したいのです。データベース名は sensor_data とします。

作成日は create_time は日付型、変更日は update_time、センサータイプは sensor_type で文字列、センサーデータは JSON で入りますが JSON 型ではなく文字列で保管したいです。

こちらのデータベース作成の SQL 文ください。

書いてみて、データベース定義なのかテーブル定義なのかめっちゃ曖昧な聞き方です。

しかし、うまくデータベース自体の作成とテーブルの作成を分けて伝えてくれます。親切。そして自分のデータベース定義・テーブル定義の曖昧さにも気づけます。

伝達ミスの修正

ありがとうございます。データベース定義とテーブル定義があいまいにしてました。データベースは my_iot_dashboard として、データベース名を sensor_data にしたいので修正してください。

ということで、自分のデータベース定義・テーブル定義を明確にしておきます。

-- テーブルの作成
CREATE TABLE sensor_data (
    id SERIAL PRIMARY KEY,           -- 自動インクリメントの一意キー
    create_time DATE NOT NULL,       -- 作成日
    update_time DATE NOT NULL,       -- 変更日
    sensor_type VARCHAR(255) NOT NULL, -- センサータイプ
    sensor_data TEXT NOT NULL        -- センサーデータを文字列型で保管
);

いい感じの定義が返ってきました。 -- と使ってコメントをつけてくるのもありがたい。さりげなく id もつけてくれるのもありがたい。

ちなみに、なぜここで修正しておくかというと、訂正についてもメモリに記憶してくれるので、経緯を覚えてくれるからです。

仕様変更も伝えると直してくれる

sensor_data が結局各センサータイプに応じた数値が入ることになりました。ですので sensor_data の定義を数値にします。いま一度 CREATE 文ください。

制作を進めていくとデータの定義を変えたくなったりします。これも伝えます。

-- テーブルの作成
CREATE TABLE sensor_data (
    id SERIAL PRIMARY KEY,           -- 自動インクリメントの一意キー
    create_time DATE NOT NULL,       -- 作成日
    update_time DATE NOT NULL,       -- 変更日
    sensor_type VARCHAR(255) NOT NULL, -- センサータイプ
    sensor_data NUMERIC NOT NULL     -- センサーデータを数値型で保管
);

いいですね。こうやって、テーブル作成からデータ挿入・更新など進めていきます。

データ挿入時の日付データ変換もサポート

さて、ここから実際の案件でも良く遭遇する日付型のデータ変換の例です。

データを挿入したいのですが、sensor_type は temperature で sensor_data は温度データが入るのですが、create_time と update_time が Node-RED 内でタイムスタンプを扱っているのでミリ秒の現在時刻なんですが、うまく入れてあげたいです。どう書くといいでしょう？

と聞くと、

こんな感じで、まず SQL だけで完結する書き方を伝えてくれます。

INSERT INTO sensor_data (create_time, update_time, sensor_type, sensor_data)
VALUES (
    to_timestamp(1693456798123 / 1000.0),  -- create_time (ミリ秒 → 秒に変換)
    to_timestamp(1693456798123 / 1000.0),  -- update_time (ミリ秒 → 秒に変換)
    'temperature',                        -- sensor_type
    25.6                                  -- sensor_data (例: 温度データ)
);

こういう細かな変換（to_timestamp）のようなやり方が自分のナレッジにしっかり備わっておらず迷うので助かります。

もちろん Node-RED 側でやるのもいいよと伝えてくれます。個人的には SQL で完結してくれたほうが良いので、そっちを選びました。

たまには感謝をしつつ

ここまではうまくいってるよという意思表示で、感謝を伝えたりもします。メモリにポジティブな影響があるかも、というおまじないです。

より実践的に週別の温度グラフのデータを欲しい

いろいろ開発が進んできまして、グラフ用に週ごとの温度データ推移を取り出したいです。今年の 1 月から 12 月末の期間が欲しいです。

ここまで内容を伝えていると、このような具体的な週別グラフのお願いも聞いてくれます。

これが返答です。週ごとの平均値を明示してないですが、ちゃんと平均値で出してくれてますね。

-- 今年の 1 月 1 日から 12 月 31 日までの週ごとの温度データ推移を取得
SELECT 
    DATE_TRUNC('week', create_time) AS week_start,  -- 各週の開始日
    AVG(sensor_data) AS average_temperature        -- 各週の平均温度
FROM 
    sensor_data
WHERE 
    sensor_type = 'temperature'                    -- センサータイプが温度
    AND create_time >= '2024-01-01'               -- 今年の 1 月 1 日以降
    AND create_time < '2025-01-01'                -- 今年の 12 月 31 日まで
GROUP BY 
    DATE_TRUNC('week', create_time)               -- 週ごとにグループ化
ORDER BY 
    week_start;                                   -- 週の開始日で並び替え

私はというと DATE_TRUNC の使い方を毎度調べるくらいですし、地味に AS で新しいカラム名の定義なども気が利いてます。

後半には、実際の SQL 構文での各設定が説明されて、出力されるでーたのイメージも出てきます。

より開発の事情に踏まえた情報に対応してもらう

たとえば Node-RED の場合 mustache 形式で値が入れれるので、そのまま使えるように整えたいので、こんなお願いもしてみます。

さっきの create_time と update_time のデータ挿入の話ですが、実は Node-RED 内で template ノードを使って SQL 文を載せます。そこでは Node-RED 内からきた currentTime というミリ秒の入った値が msg.currentTime として使われるのですが、mustache 形式で扱うので、それに合わせた SQL 文をくださいー。

と、聞くと、うまく状況を踏まえてくれます。

書き方はバッチリ！

しかし、おしい。 msg.currentTime とお願いしちゃったので、そのまま使ってくれてますね。ほんとはここ msg. は省略できるので currentTime でいけるんです。

というわけで、

いいですね、実はNode-RED の template ノード内の mustache 形式 は msg.currentTime の msg. が省略できます。これに合わせてくださいー。

ということを知らせます。

バッチリ、そのまま使える形に直してくれました！

インデントを分かりやすくお願いすることもできる

INSERT INTO sensor_data (create_time, update_time, sensor_type, sensor_data)
VALUES (
    to_timestamp( / 1000.0), -- create_time: ミリ秒を秒に変換
    to_timestamp( / 1000.0), -- update_time: ミリ秒を秒に変換
    'temperature',                              -- sensor_type: 固定で 'temperature'
    25.6                                        -- sensor_data: センサーデータの例
);

さきほどの SQL 文でもすぐ使えるのですが、もっと値が多いときは、もうちょっとインデントが効いてると読みやすい場合があるので、今後も SQL 文を分かりやすくもらうのを見越して、インデントを分かりやすくお願いしてみます。

いいかんじです！では前の返答で一番最初に返してくれたサンプルを使おうと思いますが、インデントを分かりやすくお願いできますか。

で、お願いしてみます。

うん、良い感じに INSERT INTO のところもインデントが効きました。

今後、こう答えてほしいので、メモリも更新してもらいました。

経緯を思い出してくれる

だんだん開発が進んでくると、最初に、何か仕様変更した経緯とか覚えてなかったりしますよね。

ひさびさにこの作業をします～。sensor_data が最初のほうに仕様変更があった気がしますが、どんな話でしたっけ？

そういうときはこうやって聞いてみます。

おおー、ちゃんと覚えて説明してくれます！実際、長く開発をしていると経緯が思い出せなくなることもあるので、ここに書いてある限りは聞けるのでいいですね。

案件の細かなことは書かないようにしてる

コツというより注意点ですが、ChatGPT の Web 画面では情報が保護されない可能性があります。

ですので、私の場合は、案件の詳細についてはふれずに触れずに、案件が分からない範囲に一般化・抽象化して実際にデータをどうしたいか伝えて併走してもらうようにしています。

ここまでここまでくるとローカル LLM で自前で環境を作ってしまえば、もっと突っ込んで書けるのになあとも思いますね。

けれど一般化・抽象化する経緯で、結構、頭がまとまるので、それはそれでありだなとも思ってます。