-- 一、创建学生信息表,完成学生信息分析
-- 创建表
CREATE TABLE stu_scores(`cname` VARCHAR(10),cource VARCHAR(10),score INT)
ENGINE=INNODB; -- 插入数据
INSERT INTO stu_scores VALUES('张三','语文',74);
INSERT INTO stu_scores VALUES('张三','数学',83);
INSERT INTO stu_scores VALUES('张三','物理',93);
INSERT INTO stu_scores VALUES('李四','语文',74);
INSERT INTO stu_scores VALUES('李四','数学',84);
INSERT INTO stu_scores VALUES('李四','物理',94);
-- 1. 基于学生表实现行转列,
SELECT a.`cname` 姓名,
(SELECT score FROM stu_scores WHERE a.`cname`=cname AND cource='语文') 语文,
(SELECT score FROM stu_scores WHERE a.`cname`=cname AND cource='数学') 数学,
(SELECT score FROM stu_scores WHERE a.`cname`=cname AND cource='物理') 物理
FROM stu_scores a
GROUP BY cname;
-- 同表中转列
SELECT
cname AS "姓名",
SUM(IF(cource="语文", score, 0)) AS "语文",
SUM(IF(cource="数学", score, 0)) AS "数学",
SUM(IF(cource="物理", score, 0)) AS "物理"
FROM
stu_scores
GROUP BY cname;
-- 二、连锁门店数据分析
-- 创建表
CREATE TABLE customer_details(
customer_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '1 - 500',
first_name VARCHAR(32),
last_name VARCHAR(32),
email VARCHAR(32) COMMENT 'such as willddy@gmail.com',
gender VARCHAR(8) COMMENT 'Male or female',
address VARCHAR(256),
country VARCHAR(128),
`language` VARCHAR(32),
job VARCHAR(64) COMMENT 'job title/position',
credit_type VARCHAR(32) COMMENT 'credit card type, such as visa',
credit_no VARCHAR(64) COMMENT 'credit card number');
CREATE TABLE store_details(
store_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '1 - 5',
store_name VARCHAR(32),
employee_number INT COMMENT 'how many employee in this store');
CREATE TABLE transactions(
transaction_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '1 - 1000',
customer_id INT COMMENT '1 - 500',
store_id INT COMMENT '1 - 5',
price DOUBLE(12,2) COMMENT 'such as 5.08',
product VARCHAR(32) COMMENT 'things bought',
`date` VARCHAR(16) COMMENT 'when to purchase',
`time` VARCHAR(16) COMMENT 'what time to purchase');
CREATE TABLE store_reviews(
stransaction_id INT COMMENT '1 - 8000',
store_id INT COMMENT '1 - 5',
review_store INT COMMENT '1 - 5');
-- 1、客户信息分析
-- 1).最受欢迎的信用卡
SELECT credit_type
FROM customer_details
GROUP BY credit_type
ORDER BY COUNT(1) DESC
LIMIT 1;
-- 2).前 5 个最多的客户职业
SELECT job
FROM customer_details
GROUP BY job
ORDER BY COUNT(1) DESC
LIMIT 5;
-- 3).前三个美国女性持有的最流行的行用卡
SELECT credit_type
FROM customer_details
WHERE gender='Female'
GROUP BY credit_type
ORDER BY COUNT(1) DESC
LIMIT 3;
-- 4).按性别和国家进行客户统计
SELECT t1.`gender`,t1.`country`,COUNT(1) 人数
FROM customer_details t1
GROUP BY gender,country;
-- 2、交易分析
-- 1).按月度统计总收益
SELECT SUM(price),SUBSTRING(`date`,1,6) 月份
FROM transactions
GROUP BY SUBSTRING(`date`,1,6);
-- 2).按季度统计总收益
SELECT SUM(price),YEAR(`date`) 年份,QUARTER(`date`) 季度
FROM transactions
GROUP BY YEAR(`date`),QUARTER(`date`);
-- 3).按年统计总收益
SELECT SUM(price),YEAR(`date`) 年份
FROM transactions
GROUP BY YEAR(`date`);
-- 4).统计每周各天的总收益
SELECT SUM(price),YEAR(`date`) 年份,WEEK(`date`), DAYOFWEEK(`date`)
FROM transactions
GROUP BY YEAR(`date`),WEEK(`date`), DAYOFWEEK(`date`);
-- 5)按时间段统计平均收益和总收益
SELECT
SUM(price) 总收益,
ROUND(AVG(price),2) 平均收益,
CONCAT(HOUR(`time`),':00','~',HOUR(`time`),':59') 时间段
FROM transactions
GROUP BY HOUR(`time`);
-- 6).统计消费次数排行前 10 位的客户
SELECT b.`first_name`,COUNT(1) 消费次数
FROM transactions a
JOIN customer_details b
ON a.customer_id=b.`customer_id`
GROUP BY a.customer_id
ORDER BY COUNT(1) DESC
LIMIT 10;
-- 8).统计每年度、季度总客户数
SELECT YEAR(`date`) 年份,QUARTER(`date`) 季度,
COUNT(DISTINCT customer_id) 当前客户数,
(SELECT @r:=@r+COUNT(DISTINCT customer_id)) 总客户数
FROM transactions a, (SELECT @r:=0) b
GROUP BY YEAR(`date`),QUARTER(`date`);
-- 9).找出平均消费额最大的客户
SELECT b.`first_name`,AVG(price) 平均消费额
FROM transactions a
JOIN customer_details b
ON a.customer_id=b.`customer_id`
GROUP BY a.customer_id
ORDER BY 平均消费额 DESC
LIMIT 1;
-- 10).统计最受欢迎的产品(分别从购买客户数量、购买频次、消费额三个维度分析)
SELECT
product ,
COUNT(DISTINCT customer_id) 购买客户数量,
COUNT(price) 购买频次,
SUM(price) 消费额
FROM transactions a
GROUP BY product
ORDER BY
购买客户数量 DESC,
购买频次 DESC,
消费额 DESC
LIMIT 10;
-- 3、门店分析
-- 1).按客流量找出最受欢迎的门店
SELECT t2.*
FROM
(SELECT store_id
FROM transactions
GROUP BY store_id
ORDER BY COUNT(DISTINCT customer_id) DESC
LIMIT 1) t1
JOIN store_details t2
ON t1.store_id=t2.`store_id`;
-- 2).按客户消费额找出最受欢迎的门店
SELECT t2.*
FROM
(SELECT store_id
FROM transactions
GROUP BY store_id
ORDER BY SUM(price) DESC
LIMIT 1) t1
JOIN store_details t2
ON t1.store_id=t2.`store_id`;
-- 3).按交易频次找出最受欢迎的门店
SELECT t2.*
FROM
(SELECT store_id
FROM transactions
GROUP BY store_id
ORDER BY COUNT(price) DESC
LIMIT 1) t1
JOIN store_details t2
ON t1.store_id=t2.`store_id`;
-- 4).按客流量找出每个门店最受欢迎的商品
SELECT t2.`store_name`,t1.product
FROM
(SELECT store_id,product
FROM transactions
GROUP BY store_id
ORDER BY COUNT(DISTINCT customer_id) DESC) t1
JOIN store_details t2
ON t1.store_id=t2.`store_id`;
-- 5).统计每个门店客流量与雇员的比率
SELECT
t2.`store_name`,
CONCAT(ROUND(t1.cus_count/t2.`employee_number`,0),':1') 流量与雇员比例
FROM
(SELECT store_id,COUNT(DISTINCT customer_id) cus_count
FROM transactions
GROUP BY store_id
ORDER BY cus_count DESC) t1
JOIN store_details t2
ON t1.store_id=t2.`store_id`;
-- 6).按年度-月份统计每家门店的收益
SELECT t2.`store_name`,t1.`date`,t1.收益
FROM
(SELECT store_id,DATE_FORMAT(`date`,'%Y-%m') `date`,SUM(price) 收益
FROM transactions
GROUP BY store_id,`date`) t1
JOIN store_details t2
ON t1.store_id=t2.`store_id`;
-- 7).找出每家门店最繁忙的时刻
SELECT s.`store_name`,t1.`time`
FROM
(SELECT store_id,HOUR(`time`) `time`,COUNT(DISTINCT customer_id) a
FROM transactions
GROUP BY store_id,HOUR(`time`)) t1
join store_details s
on t1.store_id=s.`store_id`
WHERE
(SELECT COUNT(1)
FROM
(SELECT store_id,HOUR(`time`),COUNT(DISTINCT customer_id) b
FROM transactions
GROUP BY store_id,HOUR(`time`)) t2
WHERE t1.store_id=t2.store_id
AND t1.a<t2.b)=0;