MariaDB講座 / COUNT・SUM・AVG・MIN・MAXで集計する

MariaDB講座

COUNT、SUM、AVG、MIN、MAXは件数・合計・平均・上下限を求めます。COUNT(*)は行、COUNT(列)は非NULLの値を数えるため、単位、平均の分母、欠損の方針を決めてから解釈します。

一行の意味と測定単位を決める

商品種類の数、在庫総数、一商品当たりの平均、在庫の金額は別の指標です。一商品が一行か、重複していないか、数値が個か金額かを確認します。正しいSQLでも業務上違う指標を計算し得ます。

分類別ならGROUP BY、結合後ならJOINの重複集計も確認します。SUM(price*stock)は金額、SUM(stock)は数量、COUNT(*)は行数です。

欠損と在庫0を含むデータを用意する

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

USE shopcourse;
CREATE TABLE products_22_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_p22_price CHECK (price >= 0),
  CONSTRAINT ck_p22_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_22_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の商品も存在する行なのでCOUNT(*)に入ります。空文字も非NULLです。NULLの扱いを集計にも適用します。

集計結果を照合する

次の一結果で比較します。

SELECT COUNT(*) AS all_rows,COUNT(note) AS known_notes,
 SUM(stock) AS stock_total,AVG(stock) AS stock_average,
 MIN(price) AS lowest_price,MAX(price) AS highest_price,
 SUM(price*stock) AS inventory_total
FROM products_22_lab;

all_rowsは6、known_notesは4、stock_totalは87、stock_averageは14.5、最低価格1500.00、最高25000.00、inventory_totalは366000.00です。平均の表示桁数は異なる場合があります。0は分子にも分母にも入り、NULLは確認済み0とは違います。

一行もない場合を確認する

存在しない負価格を条件にします。

SELECT COUNT(*),SUM(stock),AVG(stock),COALESCE(SUM(stock),0)
FROM products_22_lab WHERE price < 0;

結果は0、NULL、NULL、0です。COUNTは0件、SUMとAVGは空入力でNULLです。合計表示へ0を補う方針と、未観測の平均を測定済み0とすることは別です。

行の計算を先に確認する

主キーと各行の値を読みます。

SELECT product_id,price,stock,price*stock AS row_value
FROM products_22_lab ORDER BY product_id;

SUM(price*stock)は各商品の金額を合計します。SUM(price)*SUM(stock)は関係のない合計同士を掛ける別指標です。MINやMAXは価格値を返すだけで、対応する商品名を自動で選びません。同額もあるため、任意の名前を集計へ付けず、商品一覧が必要なら条件や順序を定めます。

元の行と件数を再確認する

小さな表なら主キーごとに手計算と照合できます。初期データの二重追加、NULLの勝手な0化、結合による行増殖を確認します。大きな合計はDBだけでなくアプリケーションの数値範囲も必要です。

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

DROP TABLE shopcourse.products_22_lab;