前回のブログで、「飛び飛びの日付データ可視化のは「日付の配列」との結合が吉」という記事を書きました。が、ちっともページビューが伸びませんでした。^^; うーん、残念。少し難しかったかしら？今読んでいただいているこの記事は、私としてはすごく価値あると思ってるんですが、前回のブログ記事よりも難しいので、まあ、ページビューは伸びないでしょうね。
でも、個人ブログの良さは、そうした伸びなそうなテーマについても自由に書けることです。
データ
今回も、前回のブログと同じデータを使います。これです。2024年の9月に不定期に料理を作った、そのログです。架空です。毎日作っているわけではないので、日付が飛び飛びになっています。
お題
このデータで、「連続して料理を作った日の数」別の「発生回数」をSQLで可視化する。というのがお題です。9月1日～4日は4日連続、9月9日は（1日だけなので、そういう言い方はしないですが、敢えていうと）1日連続、12日～13日は2日連続、16日は1日連続、18日～20日は3日連続、23日～24日は2日連続、28日と30日はそれぞれ1日連続です。
それらをまとめると、以下になります。
- 4日連続で料理を作った回数：1回
- 3日連続で料理を作った回数：1回
- 2日連続で料理を作った回数：2回
- 1日連続で料理を作った回数：4回
今は、データが小さいので、人間が目視で確認できますが、データが大きくなっても対応できるよう、上記の結果をSQLを記述して得るのがお題です。SQLについて、腕に覚えのある人は、ぜひチャレンジしてみてください。ま、私の実力があれということもありますが、そんなに簡単ではないのかな？と思います。実はわたし、自分で1時間ほどトライしたあと一旦諦め、その後、正解をChatGPT（バージョンは4o）に教えてもらいました。
その解法が非常に素晴らしかったので、紹介する。という内容のエントリーです。
SQLが返した結果テーブル
SQLは、こんな結果を返してきました。素晴らしいですね。可視化したかったことがキチンと可視化されています。ちなみに、実際にはChatGPTは横持ちで結果を返してきましたが、縦持ちの方がわかりやすいと思ったので、その部分は私がSQL文を改修して、以下の出力としました。
上記の結果を求めるSQL文
上記の結果を求めるSQL文は以下のとおりです。「各日が前日からどれくらい離れているか」のコメントがついているWITH句のFROM句で指定されている cook_masterというのが、データを保持したテーブルです。3つのWITH句でデータを整形しながら、46行目からの本体のSQL文で結果テーブルを取得しています。
「各日が前日からどれくらい離れているか」（第1のWITH句）の処理結果
「各日が前日からどれくらい離れているか」とコメントがついているWITH句（以降、第1のWITH句）を実行すると、以下の結果を得ていることが分かります。ふむふむ、なるほど。ここは難しくありませんね。day_diffカラムは、日の昇順でレコードを並べた時の前の行との日付の差を可視化しているだけです。9月1日と9月2日は「1」、9月4日と9月9日は「5」みたいな感じですね。
「連続期間をグループ化」（第2のWITH句）の処理結果
「連続期間をグループ化」というWITH句（以降、第2のWITH句）を実行すると、第1のWITH句の処理結果を対象としてSQL文が実行され、以下の結果を得ることができます。ここに私は最も感動しました。このWITH句が何をしているのかというと、上の行との差（つまり、第1のWITH句の結果テーブルの day_diff カラム）について、テーブル単位で、日の昇順で「累計」しています。ただし、単純な累計ではなく、「day_diffの値が1であれば0を加算し、それ以外は1を加算した累計」です。
その結果、カラム group_id には、日が連続していれば同じ値が残るようになっています。
「各グループの連続日数を計算」（以降、第3のWITH句）の処理結果
第2のWITH句での結果がでれば、もう、あとは難しくありませんね。group_idごとにレコード数をカウントすればよいだけです。結果は以下の通りになります。group_idの「1」については4レコード、「2」については1レコード、、、という形で結果が得られます。レコード数がconsecutive_daysというカラムに格納され、「何日継続したか」の値となっています。
本体SQL
本体のSQL（46行目から50行目）では、第3のWITH句が返してくれた、「連続した日数」を持つ consecutive_days をグループ化し、レコード数を求めています。結果は本記事の上部に掲載している「SQLが返した結果テーブル」の通りです。
まとめ
このブログ記事から学んでいただけることは、以下だと思います。まず、第一義的には
連続した日の回数
のような、ちょっとトリッキーな結果も、SQL文を工夫することで入手することができる。
一方、もう少し抽象化した「学び」としては以下があるかと思います。
- ChatGPT(4o)のSQL生成能力は高く、十分に実用的
- ただし、検証したり、自分の求める形でアウトプットを得るには、自分自身がSQLの知識を持っていることが必要
- Window関数ってすごいね（24行目と33行目にある、「OVER」を伴った関数がWindow関数です）
宣伝
（ここから宣伝です）この記事をここまで読んでいただいた方は、ChatGPTはすごいけれど、その能力を仕事上で適切に活かすには、SQLって、やっぱりできた方がいいな！！と思ったのではないかと思います。
そういえば、SQLについて、学習用の良い動画講座があるんですよ。たくさんのレビューを頂き、5点満点中4.6点と、お陰様で評判も良いようです。よろしければどうぞ。（画像をクリックするとUdemy.comにジャンプします。）
え？SQLは本で勉強したい？なるほど、では、こちらをどうぞ。このブログ記事のテーマであるWindow関数についても触れています。
