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;
商品の分析(1)
全商品の名前を重複無く取得
SELECT DISTINCT(name)
FROM items;
全商品の名前と値段を、値段が高い順に並べる
SELECT name,price
FROM items
ORDER BY price DESC;
名前の一部に「シャツ」を含む商品の、全てのカラムの値を取得
SELECT *
FROM items
WHERE name LIKE '%シャツ%';
商品の分析(2)
商品一個あたりの利益の計算
利益の計算方法
利益 = 価格 ー 原価
(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 = "グレーパーカー"
);
この記事が気に入ったらサポートをしてみませんか?