ChatGPTで作るSQLがヤバい※Oracleの話多め #SQL

n番煎じ、今更ながら…。
ChatGPTは過去遊びでしか使ったことがなかったのですが、
今、超長文SQL群を改修してまして、何重にもなった副問合せと集計関数を読み解くのに疲れて…ChatGPTに手を出しました。

そして、
え！！ChatGPTやばい！！
介護は必要だけどすぐ形にしてくれるしなんなら私より知識あるわ！！
只今、職を失いました！！
ってなったので、この衝撃を書き残しておこうと思います。

やりたいこと

作るSQLの要件はざっくり、

dba_hist_sysstatから、physical readsなど各統計情報のvalueの増分値を取得する
統計情報種別毎・1日毎に、1ヶ月間集計
日時判別のために、dba_hist_snapshotと結合する
valueには累積値が入っている。ただし、インスタンス再起動があるとリセットされる。 つまり、「累積だから」と直前のスナップショットのvalueを引くだけではうまくいかない。どうにか考慮する

(dba_*はOracleのデータディクショナリビューです)

方針

流れは、

日本語でやってほしいこと書く
DeepLで翻訳※
ChatGPTにぶちこむ

技術系の話は英語で聞くほうが良いと聞いたので、一旦翻訳してます。実際日本語より精度良い印象でした。
普段英語サイトのおかげで業務できてるまであるので当然かも？

※「英語で調べて日本語で解説をおねがいします。」と打てば、日本語で質問しても英語同等の精度になります。こちらのほうが便利です！コメントで教えてくださりありがとうございましたm(_ _)m

プロンプトのコツはよく知らなかったので、特に意識しませんでした。
なお、無課金のGPT-3.5で行いました。
※その後課金してGPT-4でも試しましたが、違いは感じなかったです。

ChatGPTによる完成形

ChatGPTが作成したSQLは以下の通りです。

WITH base_query AS (
  SELECT
    ss.stat_id,
    ss.stat_name,
    s.snap_id,
    s.begin_interval_time,
    ss.value,
    MIN(s.startup_time) OVER () AS base_startup_time,
    s.startup_time
  FROM
    dba_hist_sysstat ss
    JOIN dba_hist_snapshot s ON ss.snap_id = s.snap_id
  WHERE
    s.begin_interval_time >= TO_DATE('2023-03-01', 'YYYY-MM-DD')
    AND s.begin_interval_time < TO_DATE('2023-04-01', 'YYYY-MM-DD')
),
restart_query AS (
  SELECT
    stat_id,
    stat_name,
    snap_id,
    begin_interval_time,
    value,
    base_startup_time,
    startup_time,
    CASE
      WHEN LAG(startup_time) OVER (PARTITION BY stat_id ORDER BY snap_id) = startup_time
      THEN value - LAG(value) OVER (PARTITION BY stat_id ORDER BY snap_id)
      ELSE NULL
    END AS diff
  FROM
    base_query
)
SELECT
  TRUNC(begin_interval_time) AS stat_date,
  stat_name,
  SUM(diff) AS daily_value
FROM
  restart_query
WHERE
  diff IS NOT NULL
GROUP BY
  TRUNC(begin_interval_time),
  stat_name
ORDER BY
  TRUNC(begin_interval_time),
  stat_name;

軽く説明加えると、

メインの問い合わせでGROUP BY句に日毎、統計情報種別毎になるよう設定しています。
WITH句では副問合せを事前に定義しています。
ウィンドウ関数のLAG()で、1行前(=1つ前のスナップショット)から以下の情報を取得し、処理します。
startup_time → 比較してインスタンス再起動の可否を判断
value → 引くことで差分増分値を算出(直前にインスタンス再起動があればNULL)

普通にやばすぎる！！！！！
ChatGPTに聞かなければWITH句もLAG関数も思いつかなかったんですが…。

一応、このSQLはまだ欠陥があります。2023-03-01より前のスナップショット情報がないので、2023-03-01の最初のスナップショットでは差分増分値が取得できなさそうだな、とか。
とはいえ末恐ろしいとしか言いようがない。。です。。

本当の完成系は諸々手を加えまして、現在検証中になります。
個人的に登録したChatGPTと違って、こちらは配属先企業の資産になるかなと思うので、公開しないつもりです。

思ったこと

ChatGPTに指示したり、調整したりする上でアーキテクチャの知識は必要ですが、
実装はChatGPTでできちゃう時代が来たのかもしれないです。

私の知らないSQL知識でもインターネットにあれば引っ張ってきちゃうので…。
元情報は人間が作っていることに、一応嬉しさを感じます。

もし質問が蓄積して、簡単に答えにありつけるようになったら、とりあえず私の職務はいらなそうですね。

誰かが言ってましたが本当に、、何をつくるのか、何がしたいのか、を考えなきゃいけないですね…。
(まあいまのところ、危機感より「おもろいこれ！！！！」のほうが強いですが)

情報漏洩に気を遣いつつ、業務でも個人でも活用していきたいと思いました。

※2023/03/20 追記
↓こちらの記事を見て思い出したのですが、
GPT-4との新たな開発体験: AIとペアプロを極める - Zenn
ChatGPTと対話したり、SQLを提案してもらう中で、私のほうも新しい発想が得られたと思います。
例えば、実際の会話で私から「startup_timeを見て再起動を判断しては」と話すところがあるのですが、元を辿れば、ChatGPTからLAG関数を見せられ「これもできるんじゃないか」と連想したことでした。

このSQL作成をきっかけに、ChatGPTをよく使うようになりました。
改めて思ったのは、ChatGPTには、エンジニアの成長を助ける力もあるということです。
単語解説にしても、SQLを書かせるにしても、ほぼ最短ルートで必要な手助けをしてくれます。

今のところ、アーキテクチャの深い理解はChatGPTにはないものです。(SQL作成でもずいぶん介護しました)
人間が行うべきところは今後も補強しないとなーと思いました。

実際の会話

まとめ方が思いつかなかったので、質問内容と解答(と私の感想)を順に記載しようと思います。
順々に頭良くなってくChatGPTが見れます。長いので読む必要はありません。
ChatGPTでSQL作るとき参考になるかもしれないので、一応残しました。

※DeepLで日本語に翻訳してます。実際は英語で会話していました。

質問ごとに番号振っています。

番号	質問内容
①	Oracleで1ヶ月の日別、stat_name別のvalueを出力するSQLを記述してください。
②	sql*plusで行います。dba_hist_sysstatとdba_hist_snapshotをfrom句で指定してください。
③	valueは累積増分です。ある日のvalueだけを取得したい場合は、前日までのvalueを差し引く必要があります。
④	運用中、インスタンスが再起動した場合、valueは0にリセットされます。この時、前日までのvalueを引くのは正しくないのではないでしょうか。厳密には、startup_timeを見て、各日の値を集計する必要があると思います。
⑤	AWRのスナップショットは1時間ごとに取得しています。インスタンス再起動直前の時間のvalueは気にする必要はありませんが、それ以外のvalueは日次で集計したいと思います。先ほど伝えたように、インスタンス再起動の影響を考慮したSQLを作成してください。
⑥	そうすると、インスタンスの再起動が行われた日が不正な値になってしまうと思うのです。正しいクエリは、行ごとに比較して、startup_timeが変わっていなければ前のvalueを引き、startup_timeが変わっていれば何もせず、これらのvalueを日ごとに集計することだと思うのですが。

①

問. Oracleで1ヶ月の日別、stat_name別の値を出力するSQLを記述してください。

答. stats_tableというテーブルがあり、stat_date（DATE）、stat_name（VARCHAR2）、value（NUMBER）というカラムがあり、2023年3月の各stat_nameの日次値を出力したいとすると、Oracleでは以下のSQLクエリが使えることになります。

SELECT stat_date, stat_name, SUM(value) AS total_value
FROM stats_table
WHERE stat_date >= DATE '2023-03-01' AND stat_date < DATE '2023-04-01'
GROUP BY stat_date, stat_name
ORDER BY stat_date, stat_name;

このクエリは、WHERE句で2023年3月の月だけを含むようにデータをフィルタリングし、GROUP BY句でstat_dateとstat_nameによってデータをグループ化し、SUM関数で各グループの合計値を計算します。結果は、ORDER BY句を使用して、stat_dateとstat_nameでソートされます。

おお、普通に変な答えだ。stats_tableって知らんテーブルが出てきた。
うーん伝え方が悪かったかな。

②

問. sql*plusで行います。dba_hist_sysstatとdba_hist_snapshotをfrom句で指定してください。

答. 必要な権限があると仮定して、次のSQLクエリを使用すると、2023年3月の1ヶ月間、dba_hist_sysstatテーブルから各stat_nameの日次値を出力できます。

SELECT TRUNC(s.begin_interval_time, 'DD') AS stat_date, ss.stat_name, SUM(ss.value) AS total_value
FROM dba_hist_sysstat ss
JOIN dba_hist_snapshot s ON ss.snap_id = s.snap_id
WHERE s.begin_interval_time >= TO_DATE('2023-03-01', 'YYYY-MM-DD') AND s.begin_interval_time < TO_DATE('2023-04-01', 'YYYY-MM-DD')
GROUP BY TRUNC(s.begin_interval_time, 'DD'), ss.stat_name
ORDER BY TRUNC(s.begin_interval_time, 'DD'), ss.stat_name;

このクエリは、dba_hist_sysstatテーブルとdba_hist_snapshotテーブルをsnap_idで結合し、WHERE句で2023年3月のみを含むようにデータをフィルタし、GROUP BY句でTRUNC（s.begin_interval_time、 'DD' ）（日付を日にちに切り捨て）およびss.stat_nameでデータをグループ化し、SUM関数で各グループの合計値を計算する。結果は、TRUNC(s.begin_interval_time, 'DD')とORDER BY句を使用したss.stat_nameでソートされます。

結構本格的になってきた。指示した通り結合してる。
てか前回指示したのにやらなかった日毎の集計、ちゃっかりやってるな。
情報が足りてきたらより良い答えが選べるのね、なるほど。

③

問. valueは累積増分です。ある日の値だけを取得したい場合は、前日までの値を差し引く必要があります。

答. 各日の増分値を計算するには、LAG関数を使用して前日の累積値を取得し、現在の日の累積値から差し引くことができます。増分値の計算を含むクエリーの更新版はこちらです。

SELECT TRUNC(s.begin_interval_time) AS stat_date, ss.stat_name, 
       SUM(ss.value - LAG(ss.value, 1, 0) OVER (PARTITION BY ss.stat_id ORDER BY s.begin_interval_time)) AS incremental_value
FROM dba_hist_sysstat ss
JOIN dba_hist_snapshot s ON ss.snap_id = s.snap_id
WHERE s.begin_interval_time >= TO_DATE('2023-03-01', 'YYYY-MM-DD') AND s.begin_interval_time < TO_DATE('2023-04-01', 'YYYY-MM-DD')
GROUP BY TRUNC(s.begin_interval_time), ss.stat_name
ORDER BY TRUNC(s.begin_interval_time), ss.stat_name;

このクエリは、前日の累積値（LAG関数で取得）を当日の累積値から差し引くことで増分値を計算する。LAG関数には3つの引数があります。1つ目は評価する式（この場合はss.value）、2つ目は戻る行数（この場合は1、つまり前の行）、3つ目は前の行がない場合に返すデフォルト値（この場合は0）を指定します。OVER句は、結果セットをss.stat_idで分割し、s.begin_interval_timeで並べるために使用されます。クエリの残りの部分は以前と同じです。

ええぇウィンドウ関数使えるんですかChatGPTさん？！？！
多少知識はあれど、わたくし、今の職場で見たことないナァ…。。
その方法思いつかなかった、勉強になります。。。

てか、本当にやばい。詰めは甘いけども、私より発想力も実装力もある。どうしよう…
大事な要件を聞いてみよう。

④

問. 運用中、インスタンスが再起動した場合、valueは0にリセットされます。この時、前日までのvalueを引くのは正しくないのではないでしょうか。厳密には、startup_timeを見て、各日の値を集計する必要があると思います。

答. おっしゃる通り、インスタンスが再起動されるとdba_hist_sysstatの値は0にリセットされ、現在の日の値から前日の値を引くと不正な増分値を与えてしまいます。このシナリオを処理するには、v$instanceビューのstartup_timeカラムを使用してインスタンスがいつ起動したかを判断し、現在の値から起動時の値を差し引いて増分値を取得します。以下は、インスタンスの起動時間を含み、それに応じて増分値を計算するクエリの更新バージョンです。

SELECT TRUNC(s.begin_interval_time) AS stat_date, ss.stat_name, SUM(CASE
    WHEN s.startup_time <= s.begin_interval_time THEN ss.value - LAG(ss.value, 1, 0) OVER (PARTITION BY ss.stat_id ORDER BY s.begin_interval_time)
    ELSE ss.value - (SELECT value FROM dba_hist_sysstat WHERE snap_id = s.snap_id AND stat_id = ss.stat_id AND begin_interval_time = s.startup_time)
  END) AS incremental_value
FROM dba_hist_sysstat ss
JOIN dba_hist_snapshot s ON ss.snap_id = s.snap_id
WHERE s.begin_interval_time >= TO_DATE('2023-03-01', 'YYYY-MM-DD') AND s.begin_interval_time < TO_DATE('2023-04-01', 'YYYY-MM-DD')
GROUP BY TRUNC(s.begin_interval_time), ss.stat_name
ORDER BY TRUNC(s.begin_interval_time), ss.stat_name;

このクエリは、CASEステートメントを使用して、インスタンスが現在の間隔の前または後に開始されたかを判断し、増分値を計算するために適切な値を減算します。もしs.startup_timeがs.begin_interval_time以下であれば、このクエリは以前と同様に現在の日の値から前の日の値を差し引きます。そうでない場合、クエリは現在の値から起動時の値（起動時間に対応するstat_idとbegin_interval_timeの値を選択するサブクエリを使用して取得）を差し引きます。この方法は、dba_hist_sysstatテーブルがインスタンスの動作期間全体のデータを含んでいると仮定していることに注意してください。データにギャップがある場合（例えば、不完全なAWRスナップショットによる）、このクエリーを使用して計算された増分値は正確でない可能性があります。

おっしゃる通り(You're right)とかいわれた…。こわい！！！
v$instanceビューは使ってなかったり、条件式に無理があったり、若干嘘つきなところはありますけど。。。
CASE式も使い始めた…。
それっぽいスクリプトになってきてしまった。

⑤

問. AWRのスナップショットは1時間ごとに取得しています。インスタンス再起動直前の時間の値は気にする必要はありませんが、それ以外の値は日次で集計したいと思います。先ほど伝えたように、インスタンス再起動の影響を考慮したSQLを作成してください。

答. 日単位で値を集計する必要があり、インスタンスが終日稼働していた（つまり、日中にインスタンスの再起動がなかった）と仮定できる場合、各日の最初と最後のスナップショットの差を計算し、結果を日とstat_nameでグループ化すれば、クエリを簡素化できます。インスタンス再起動の影響を考慮したクエリの更新版はこちらです。

SELECT TRUNC(s.begin_interval_time) AS stat_date, ss.stat_name, MAX(ss.value) - MIN(ss.value) AS daily_value
FROM dba_hist_sysstat ss
JOIN dba_hist_snapshot s ON ss.snap_id = s.snap_id
WHERE s.begin_interval_time >= TO_DATE('2023-03-01', 'YYYY-MM-DD') AND s.begin_interval_time < TO_DATE('2023-04-01', 'YYYY-MM-DD')
GROUP BY TRUNC(s.begin_interval_time), ss.stat_name
ORDER BY TRUNC(s.begin_interval_time), ss.stat_name;

このクエリは、各日の ss.valueと stat_nameの最大値と最小値の差を計算する。スナップショットは1時間ごとに取得されるため、インスタンスは1日中稼働しており、日中に発生した再起動はss.valueの値に反映されると仮定することができます。この方法は、指定された時間範囲のAWRデータにギャップがないことを前提にしていることに注意してください。もし、日中にインスタンスが再起動した場合に、再起動前に蓄積された値を除外して処理する必要がある場合は、各日の最初のスナップショット時の値を基準値として使用するようにクエリを修正することができます。この方法を実装したクエリの更新版はこちらです。

SELECT TRUNC(s.begin_interval_time) AS stat_date, ss.stat_name, MAX(ss.value) - (SELECT value FROM dba_hist_sysstat WHERE snap_id = (SELECT MIN(snap_id) FROM dba_hist_snapshot WHERE begin_interval_time >= TRUNC(s.begin_interval_time) AND begin_interval_time < TRUNC(s.begin_interval_time) + 1) AND stat_id = ss.stat_id) AS daily_value
FROM dba_hist_sysstat ss
JOIN dba_hist_snapshot s ON ss.snap_id = s.snap_id
WHERE s.begin_interval_time >= TO_DATE('2023-03-01', 'YYYY-MM-DD') AND s.begin_interval_time < TO_DATE('2023-04-01', 'YYYY-MM-DD')
GROUP BY TRUNC(s.begin_interval_time), ss.stat_name
ORDER BY TRUNC(s.begin_interval_time), ss.stat_name;

このクエリは、各日およびstat_nameのss.valueの最大値と、その日の最初のスナップショットの値を選択するサブクエリを使用して得られる、その日の最初のスナップショットの時点の値との間の差を計算するものである。この方法は、インスタンスが日中に再起動された場合、再起動前に蓄積された値を除外することで対処します。

全然意図を理解していなくて安心した。
もちろん問い方次第、私次第なところはあるけども！

⑥

問. そうすると、インスタンスの再起動が行われた日が不正な値になってしまうと思うのです。正しいクエリは、行ごとに比較して、startup_timeが変わっていなければ前の値を引き、startup_timeが変わっていれば何もせず、これらの値を日ごとに集計することだと思うのですが。

答. インスタンスが再起動した場合、再起動前に蓄積された値が失われるため、先に提案したアプローチでは正確な日次集計ができない可能性があることはご指摘の通りです。その場合、インスタンスの起動時間を追跡し、それを使用して日次の合計を正しく計算する必要があります。ここでは、各スナップショットのstartup_timeを前のスナップショットのstartup_timeと比較し、インスタンスが再起動されたかどうかを判断するクエリの修正版を示します。インスタンスが再起動されていない場合、クエリは現在の値と以前の値との差を計算します。インスタンスが再起動された場合、クエリは現在の行をスキップして次の行に進みます。そして、各日と stat_name の差分を集計することで、日ごとの合計が計算されます。

WITH base_query AS (
  SELECT
    ss.stat_id,
    ss.stat_name,
    s.snap_id,
    s.begin_interval_time,
    ss.value,
    MIN(s.startup_time) OVER () AS base_startup_time,
    s.startup_time
  FROM
    dba_hist_sysstat ss
    JOIN dba_hist_snapshot s ON ss.snap_id = s.snap_id
  WHERE
    s.begin_interval_time >= TO_DATE('2023-03-01', 'YYYY-MM-DD')
    AND s.begin_interval_time < TO_DATE('2023-04-01', 'YYYY-MM-DD')
),
restart_query AS (
  SELECT
    stat_id,
    stat_name,
    snap_id,
    begin_interval_time,
    value,
    base_startup_time,
    startup_time,
    CASE
      WHEN LAG(startup_time) OVER (PARTITION BY stat_id ORDER BY snap_id) = startup_time
      THEN value - LAG(value) OVER (PARTITION BY stat_id ORDER BY snap_id)
      ELSE NULL
    END AS diff
  FROM
    base_query
)
SELECT
  TRUNC(begin_interval_time) AS stat_date,
  stat_name,
  SUM(diff) AS daily_value
FROM
  restart_query
WHERE
  diff IS NOT NULL
GROUP BY
  TRUNC(begin_interval_time),
  stat_name
ORDER BY
  TRUNC(begin_interval_time),
  stat_name;

このクエリーは、まず窓関数を使用して全時間範囲にわたる最小のstartup_timeを計算し、次にdba_hist_sysstatおよびdba_hist_snapshotテーブルを結合して関連データを取得する。restart_query CTEは、LAG()関数を使用して、各スナップショットのstartup_timeと同じstat_idの前のスナップショットのstartup_timeを比較します。startup_timeが同じであれば、このクエリーは現在の値と以前の値の差を計算します。startup_timeが変更された場合、クエリはNULLを返します。そして、外側のクエリは各日とstat_nameの差分を合計します。この方法は、dba_hist_snapshotテーブルのstartup_timeカラムが正確で、指定した時間範囲のAWRデータにギャップがないことを仮定していることに注意してください。

ご指摘の通りです(You are correct that …)って話しかけてきた！こわい！！！
てかWITH句なんて知らんかった…副問合せネストだらけで保守性オワタSQLに一筋の光明が…。
まじでありがとうございます、勉強になります、、ChatGPTさん。
えってか完成度やばい、私には絶対書けない領域だ。。。

以上、やりとりの記録でした。
よいChatGPTライフを！！！