MariaDB講座 / CASEで条件付きの値と集計を作る

MariaDB講座

CASEは条件に応じて一つの結果値を選ぶSQL式です。表示状態や条件付き集計に使えますが、最初に真になる分岐、ELSE、NULLの方針によって結果が変わります。

式と処理制御を区別する

SELECT内のCASE式は一つの値を返し、ストアドプログラムのCASE文による処理分岐とは別です。CASE 値 WHENは等価の対応、CASE WHEN 条件は範囲や複合条件を表します。重なる条件は最初の真の分岐が採用され、ENDで式を閉じます。

条件が正しくても件数や金額の単位を間違えることがあります。COUNTとSUM、レポートの照合も合わせて考えます。

在庫状態の違う商品を準備する

course_userでshopcourseへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で環境と同名表を確認します。通常例をUTF-8ファイルに保存してSOURCEで実行できます。既存の同名表は用途・定義・行を調べ、不要と確認できるまでは置き換えません。

USE shopcourse;
CREATE TABLE products_24_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p24_price CHECK (price >= 0),
  CONSTRAINT ck_p24_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_24_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

SQL内の韓国語サンプルは文字コードと結果を原文にそろえたものです。ID1～6はノート、ペン、マグカップ、ライト、ファイル、ケーブル、分類は文具・生活用品・電子用品です。数値とIDで結果を比較します。noteは1・4がNULL、2・5が空文字、他は記入済みで、在庫は20・0・12・5・50・0です。

在庫0は二商品、1～9は一商品、10以上は三商品です。実際の販売可否には予約、入荷予定や公開状態も関係し得るため、このラベルだけで全業務を表すわけではありません。

狭い条件を先に置く

原文の韓国語ラベルは売り切れ・在庫少・販売可能を意味します。

SELECT product_id,product_name,stock,
 CASE WHEN stock=0 THEN '품절'
      WHEN stock<10 THEN '재고 적음'
      ELSE '판매 가능' END AS stock_label
FROM products_24_lab ORDER BY product_id;

ID2・6が売り切れ、4が在庫少、1・3・5が販売可能です。stock < 10を先にすると0もそこで採用され、売り切れへ到達しません。分岐順だけを入れ替えて観察し、元へ戻します。ELSEなしで無一致ならNULLなので、欠損入力か未定義の出力かを区別します。

条件付きSUMとCOUNTを比較する

次の四列を比べます。

SELECT SUM(CASE WHEN stock=0 THEN 1 ELSE 0 END) AS out_of_stock,
 COUNT(CASE WHEN stock=0 THEN 1 END) AS also_out_of_stock,
 COUNT(CASE WHEN stock=0 THEN 1 ELSE 0 END) AS all_non_null,
 SUM(CASE WHEN category='문구' THEN price*stock ELSE 0 END) AS stationery_value
FROM products_24_lab;

1/0のSUMは2、1/NULLのCOUNTは2、1/0のCOUNTは6、文具の金額は145000です。COUNTは1の数ではなく非NULLの数を数えるため、0も対象です。元行が一つもないときの集計規則も別に確認します。

0のグループを残す

数値と表示ラベルは別列にし、分岐へ無関係な文字と金額を混在させません。未知価格を0にするかも方針を決めます。

SELECT category,
 SUM(CASE WHEN stock>0 THEN 1 ELSE 0 END) AS available_products
FROM products_24_lab GROUP BY category ORDER BY category;

在庫あり件数は文具2、生活用品2、電子用品0です。WHERE stock > 0を先に使うと電子用品のグループ自体が消えます。条件付き集計は元グループを残せるので、必要な出力の形で選びます。

集計前に各行の分岐を確認する

主キー、在庫とCASE結果を先に表示し、等価と範囲、AND/ORの括弧、ELSEを確認します。このSELECTが在庫を書き換えたわけではありません。合計だけを見るより、誤った条件を特定しやすくなります。

再実習では選択中のDB、定義、保存行を確認し、必要なものを保存した後、削除可能なこの記事の実習表だけを削除します。DROP TABLEは定義と行を消し、通常のROLLBACKでは戻せません。他の記事や実アプリケーションの表へ範囲を広げないでください。

DROP TABLE shopcourse.products_24_lab;