SQLの基礎の基礎③

ファッション通販サイトのデータベースを分析

・サイドのデータベース
usersテーブル･･･ユーザーの情報を保存する
itemテーブル･･･サイトの商品の情報を保存する
sales_recordsテーブル･･･商品がいつ誰に売れたかを保存する

・データベースの内容
itemテーブル
カラム型　　　　データ型
　 id 　　　　　　 数値　　　　　データ番号
   name　　　　   テキスト　　　　商品名
  gender      　　　 数値    　　　　男用(0)or女用(1)or男女兼用(2)
   price　　　　　  数値　　　　　　値段
   cost 　　　　　  数値　　　　　　原価

sales_recordsテーブル
カラム型　　　　データ型
     id　　　　　　  数値　　　　　データ番号
  item_id　　　　    数値　　　　　  商品id
  user_id　　　　　数値                   ユーザーid
purchased_at　　   日付　　　　　  購入日

usersテーブル
カラム型　　　　データ型
　 id　　　　　　　数値　　　　　データ番号　　　　　　　　
  name　　　         テキスト     　　  ユーザー名
 gender　　　　　  数値　　　　　　性別（男性 '0'  女性 '1'）
    age　　　　　　 数値　　　　　　年齢

ユーザーの分析

ユーザー全体の平均年齢を取得

SELECT AVG(age)
FROM users;

20歳未満の男性ユーザーの、全てのカラムの値を取得

SELECT *
FROM users
WHERE age<20 AND gender=0;

ユーザーの年齢ごとの人数と、その年齢を取得

SELECT age, COUNT (*)
FROM users
GROUP BY age;

商品の分析（１）

全商品の名前を重複無く取得

SELECT DISTINCT(name)
FROM items;

全商品の名前と値段を、値段が高い順に並べる

SELECT name,price
FROM items
ORDER BY price DESC;

名前の一部に「シャツ」を含む商品の、全てのカラムの値を取得

SELECT *
FROM items
WHERE name LIKE '%シャツ%';

商品の分析（２）

商品一個あたりの利益の計算
利益の計算方法
利益　＝　価格　ー　原価
　　　      (price)         (cost)

全商品の名前、値段、利益を取得
SELECT name, price, price-cost
FROM items;​

集計関数と四則演算
MAX, MIN, SUM, AVG などの集計関数は、図のようにして四則演算と併用できる

全商品の利益の平均を取得
SELECT AVG(price - cost)
FROM items;​

ORDER BYと四則演算
ORDER BYもまた、下の図のように四則演算と併用することができる

各商品あたりの利益が上位5件の商品の名前と利益を取得
SELECT name, price-cost
FROM items
ORDER BY price-cost DESC
LIMIT 5;​

「グレーパーカー」より値段が高い商品の名前と値段を取得
SELECT name,price
FROM items
WHERE price > (
 SELECT price
 FROM items
 WHERE name="グレーパーカー"
);

7000円以下で「グレーパーカー」より利益が高い商品を取得
SELECT name, price-cost
FROM items
WHERE price <= 7000 AND price-cost > (
 SELECT price-cost
 FROM items
 WHERE name="グレーパーカー"
);

販売履歴を分析

商品ごとに商品のid、売れた個数を取得
SELECT item_id, COUNT(*)
FROM sales_records
GROUP BY item_id;​

売れた数が多い上位5商品のidと個数を取得
SELECT item_id, COUNT(*)
FROM sales_records
GROUP BY item_id
ORDER BY COUNT(*) DESC
LIMIT 5;​

結合したテーブルの分析

売れた数が多い上位5商品のIDと名前、個数を取得
SELECT items.id, items.name, COUNT(*)
FROM sales_records
JOIN items
ON sales_records.item_id = items.id
GROUP BY items.id, items.name
ORDER BY COUNT(*) 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(*) AS "販売個数"
FROM sales_records
GROUP BY purchased_at
ORDER BY purchased_at ASC;​

日ごとの売上額とその日付を取得
SELECT sales_records.purchased_at, SUM(items.price) AS "売上額"
FROM sales_records
JOIN items
ON sales_records.item_id = items.id
GROUP BY purchased_at
ORDER BY purchased_at ASC;​

複雑なユーザーデータの分析

10個以上購入したユーザーIDとユーザー名、購入した商品の数を取得
SELECT users.id, users.name, count(*) AS "購入数"
FROM sales_records
JOIN users
ON sales_records.user_id = users.id
GROUP BY users.id, users.name
HAVING count(*) >= 10;​

「サンダル」を購入したユーザーの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="サンダル"
)
GROUP BY users.id;​

複雑な商品データの分析

男性向け、女性向け、男女兼用商品ごとに指定されたデータを取得
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.id, items.name, SUM(items.price) AS "売上額"
FROM sales_records
JOIN items
ON sales_records.item_id = items.id
GROUP BY items.id, items.name, items.price
ORDER BY SUM(items.price) DESC
LIMIT 5;

グレーパーカーより売上額が高い商品の指定されたデータを取得
SELECT items.id, items.name, SUM(items.price) AS "売上額"
FROM sales_records
JOIN items
ON sales_records.item_id = items.id
GROUP BY items.id, items.name, items.price
HAVING SUM(items.price) > (
 SELECT SUM(items.price)
 FROM sales_records
 JOIN items
 ON sales_records.item_id = items.id
 WHERE items.name = "グレーパーカー"
);​​