mysql基础练习三：

-- 一、创建学生信息表，完成学生信息分析
-- 创建表
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;