【SQL】商品データベース分析のサンプルコード

SQL商品データ分析のサンプルコード
売り上げの履歴からデータ分析
テーブルを結合して分析
ユーザーデータも分析

SQL商品データ分析のサンプルコード

商品データ分析のサンプルコードを実施すればSQL操作にだいぶ慣れることができるのではないかということでサンプルコードをのせたいと思います。

以下のようなデータベースを想定しています。

このテーブルには、商品のid, 名前, 男性用か女性用か, 値段, コストの情報が入っています。

〇〇より値段が高い商品を求める

SELECT name, price
FROM items
WHERE price > (
SELECT price
FROM items
WHERE name = "グレーパーカー"
);

〇〇円以下で〇〇より利益が高い商品

SELECT name, price - cost
FROM items
WHERE 7000 >= price AND price - cost > (
SELECT price-cost
FROM items
WHERE name = "グレーパーカー"
);

売り上げの履歴からデータ分析

ユーザーID, 商品のid, いつ買われたかがわかるデータベース

商品ごとに何個売れたか

SELECT item_id, Count(item_id)
FROM sales_records
GROUP BY item_id

売れ筋商品上位５位を表示(商品id と売れた数)

SELECT item_id, COUNT(item_id)
FROM sales_records
GROUP BY item_id
ORDER BY COUNT(item_id) DESC
LIMIT 5;

テーブルを結合して分析

itemsテーブルとsales_recordsテーブルを結合してデータ分析

売れ筋商品上位５位（商品の名前　と　売れた数）

-- 売れた数が多い上位5商品の名前と個数を取得してください
SELECT items.name, COUNT(sales_records.item_id)
FROM sales_records
JOIN items
ON sales_records.item_id = items.id
GROUP BY sales_records.item_id
ORDER BY COUNT(sales_records.item_id) DESC
LIMIT 5;

全商品の売り上げ合計と合計利益(購入履歴は含まない)

SELECT SUM(items.price) AS "総売上", SUM(items.price - items.cost) AS "総利益"
FROM sales_records
JOIN items
ON sales_records.item_id = items.id;

日付ごとの販売個数と日付

SELECT purchased_at , COUNT(purchased_at) AS "販売個数"
FROM sales_records
GROUP BY purchased_at

日付事の売り上げ額

SELECT purchased_at , SUM(items.price) AS "売上額"
FROM sales_records
JOIN items
ON sales_records.item_id = items.id
GROUP BY sales_records.purchased_at;

ユーザーデータも分析

ユーザーの情報が入ったusersテーブルと販売履歴の情報が入ったsales_recordsを結合してデータ分析

10個以上購入したユーザー名と購入した商品の数

SELECT users.name, COUNT(users.name) AS "購入数"
FROM sales_records
JOIN users
ON sales_records.user_id = users.id
GROUP BY sales_records.user_id HAVING COUNT(users.name) >= 10;

3つのテーブルを結合「「サンダル」を購入したユーザーのidと名前を取得してください」
こちらは非常に難しいサンプルコードです。

SELECT users.id, users.name
FROM sales_records
JOIN users
ON sales_records.user_id = users.id
WHERE sales_records.item_id = (
  SELECT id
  FROM items
  WHERE name = "サンダル"
);

男性向け商品、女性向け商品、男女兼用商品それぞれの性別（0・1・2の数字）売上額

SELECT items.gender, SUM(items.price) AS "売上額"
FROM sales_records
JOIN items
ON sales_records.item_id = items.id
GROUP BY items.gender;

— 売上額が上位5位の商品の指定されたデータを取得してください

SELECT items.name, items.price, COUNT(*), COUNT(*)*items.price AS "売上額"
FROM sales_records
JOIN items
ON sales_records.item_id = items.id
GROUP BY items.id
ORDER BY COUNT(*)*items.price DESC
LIMIT 5

「グレーパーカー」より売上額が高い商品の・名前・商品の値段・売上額を表示

SELECT items.name, items.price, COUNT(*) * items.price AS "売上額"
FROM sales_records
JOIN items
ON sales_records.item_id = items.id
GROUP BY items.id
HAVING (COUNT(*) * items.price) > (
  SELECT COUNT(*) * items.price
  FROM sales_records
  JOIN items
  ON sales_records.item_id = items.id
  WHERE items.name = "グレーパーカー"
);

SQLは思った以上に難しい。テーブルの結合を意識すること。どんなときにどの構文を使うかを把握しておかないとだめっぽいけど複雑すぎる。