はじめに
自分は普段SQLなどの小ネタをブログに書いたりしているのですが、説明のためにテーブルを用意したりするのはちょっと面倒です。
なのでやりたいことだけ書いて、全部解説してデータも例示してもらうことにしました。
こちらの仕事としては、集計した結果をcase文で出力するようなプロンプトを用意するだけです。
プロンプト1
一つの注文に複数の商品が紐づくデータがあります。商品には三つほどの種類があります。注文で集計しつつ商品の種類を出力するsqlを書いてください
このプロンプトの狙いとしては、注文テーブルと、注文テーブルIDに対して1対Nの商品IDが格納される注文詳細テーブル、そして商品テーブルを作ってもらい、注文テーブルIDでGroup byしつつ商品の情報をCASE文で出してもらうことでした。
ChatGPTの応答1
以下のようなテーブル構造を仮定し、商品を種類A、種類B、種類Cの3つに分類するクエリを示します。
-- ordersテーブル：注文情報 CREATE TABLE orders ( order_id INT, order_date DATE, customer_id INT, PRIMARY KEY (order_id) ); -- order_detailsテーブル：注文明細情報 CREATE TABLE order_details ( order_id INT, product_id INT, quantity INT, price INT, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id) ); -- productsテーブル：商品情報 CREATE TABLE products ( product_id INT, product_name VARCHAR(50), product_type VARCHAR(10), PRIMARY KEY (product_id) );
-- 商品の種類A、種類B、種類Cに分類しながら、注文単位で集計するクエリ SELECT o.order_id, o.order_date, SUM(CASE WHEN p.product_type = 'A' THEN od.quantity ELSE 0 END) AS type_a_quantity, SUM(CASE WHEN p.product_type = 'B' THEN od.quantity ELSE 0 END) AS type_b_quantity, SUM(CASE WHEN p.product_type = 'C' THEN od.quantity ELSE 0 END) AS type_c_quantity FROM orders o INNER JOIN order_details od ON o.order_id = od.order_id INNER JOIN products p ON od.product_id = p.product_id GROUP BY o.order_id, o.order_date;
このクエリでは、注文情報を表すordersテーブルと注文明細情報を表すorder_detailsテーブルをJOINし、商品情報を表すproductsテーブルともJOINしています。そして、CASE文を使用して、商品の種類A、種類B、種類Cごとに数量を合計し、それぞれの数量をカラムに出力しています。また、注文単位で集計するため、ordersテーブルのorder_idとorder_dateでグルーピングしています。
このようにして、注文データから商品の種類別の数量を集計しつつ、注文単位で集計することができます。
ChatGPTの応答1について所感
自分がイメージしているテーブル構造およびクエリを書いてもらうことができました。
自分は商品名などをCASE文で出すのかなと漫然と思っていたのですが、集計対象として意味のある注文に対する商品の数量をちゃんと集計していますね。
たしかに商品名などは元の注文詳細テーブルから出せばいいので、この集計クエリで出す必要はありません。
意味のあるデータだけ出しているようで、感心しました。
また実際のデータがあればもっと分かりやすいと思ったので、次の応答で出してもらうようにしました。
プロンプト2
具体的なデータのレコードで説明してくれますか？
ChatGPTの応答2
はい、データのレコードを以下に示します。
ordersテーブル：
| order_id | order_date | customer_id |
|---|---|---|
| 1 | 2022-01-01 | 100 |
| 2 | 2022-01-02 | 200 |
| 3 | 2022-01-03 | 300 |
order_detailsテーブル：
| order_id | product_id | quantity | price |
|---|---|---|---|
| 1 | 1 | 2 | 100 |
| 1 | 2 | 3 | 200 |
| 2 | 1 | 1 | 100 |
| 2 | 3 | 2 | 300 |
| 3 | 2 | 4 | 200 |
| 3 | 3 | 1 | 300 |
productsテーブル：
| product_id | product_name | product_type |
|---|---|---|
| 1 | 商品A | A |
| 2 | 商品B | B |
| 3 | 商品C | C |
このとき、商品の種類A、種類B、種類Cごとの数量を注文単位で集計するクエリを実行すると、以下のような結果が得られます。
| order_id | order_date | type_a_quantity | type_b_quantity | type_c_quantity |
|---|---|---|---|---|
| 1 | 2022-01-01 | 2 | 3 | 0 |
| 2 | 2022-01-02 | 1 | 0 | 2 |
| 3 | 2022-01-03 | 0 | 4 | 1 |
注文1では、商品Aが2個、商品Bが3個注文されているため、type_a_quantityが2、type_b_quantityが3となります。商品Cは注文されていないため、type_c_quantityは0です。
同様に、注文2では、商品Aが1個、商品Cが2個注文されているため、type_a_quantityが1、type_b_quantityが0、type_c_quantityが2となります。
注文3では、商品Bが4個、商品Cが1個注文されているため、type_a_quantityは0、type_b_quantityは4、type_c_quantityは1となります。
以上のように、商品の種類別に集計することができました。
ChatGPTの応答2について所感
具体的なレコードを出して、さらになぜこうなるかの結果まで説明してもらいました。
これはSQL学習が捗りそうですね。
