SQL专栏——sql语句的基础操作（四）SQL题目练习

一、行转列

– 创建学生信息表，完成学生信息分析

– 创建表

CREATE TABLE students_score(`cname` VARCHAR(10),cource VARCHAR(10),score INT) ENGINE=INNODB;      

– 插入数据

INSERT INTO students_score VALUES('张三','语文',74);
INSERT INTO students_score VALUES('张三','数学',83);
INSERT INTO students_score VALUES('张三','物理',93);
INSERT INTO students_score VALUES('李四','语文',74);
INSERT INTO students_score VALUES('李四','数学',84);
INSERT INTO students_score VALUES('李四','物理',94);      

原始数据表输出格式如下图。

基于学生表实现行转列，输出结果如下图。

SELECT cname,
  MAX(
  CASE
    WHEN cource='语文'
    THEN score
    ELSE 0
  END) AS "语文",
  MAX(
  CASE
    WHEN cource='数学'
    THEN score
    ELSE 0
  END) AS "数学",
  MAX(
  CASE
    WHEN cource='物理'
    THEN score
    ELSE 0
  END) AS "物理"
FROM students_score
GROUP BY cname      

二、连锁门店数据分析

连锁门店数据表包括：customer_details（顾客信息表）、store_details（门店信息表）、transactions（交易流水表）、store_reviews（门店评分表）。表的结构如下。

使用SQL完成下面的统计分析功能。

1、客户信息分析

1).最受欢迎的信用卡

按照最多用户拥有来查找

select credit_type
from customer_details 
group by credit_type 
order by count(credit_type) desc limit 1;      

2).前5个最多的客户职业

select job,count(job) job_counts 
from customer_details
group by job
order by job_counts desc limit 5;      

SELECT job
FROM customer_details
GROUP BY job
ORDER BY COUNT(job) DESC LIMIT 5;      

3).前三个美国女性持有的最流行的行用卡

select credit_type
from customer_details 
where  gender='Female'
group by credit_type 
order by count(credit_type) desc limit 3;      

4).按性别和国家进行客户统计

select credit_type,count(distinct credit_no) credit_counts from customer_details
where country='China' and gender='Female'
group by credit_type
order by credit_counts desc limit 10;      

SELECT country,
SUM(CASE WHEN gender='Femal'THEN  1 ELSE 0 END)female,
SUM(CASE WHEN gender='Male'THEN 1 ELSE 0 END)male
FROM customer_details
GROUP BY country;r      

2、交易分析

1).按月度统计总收益

SELECT YEAR(DATE),MONTH(DATE),SUM(price)
FROM transactions
GROUP BY YEAR(DATE),MONTH(DATE)
ORDER BY YEAR(DATE),MONTH(DATE);      

2).按季度统计总收益

SELECT YEAR(DATE),QUARTER(DATE),SUM(price)
FROM transactions
GROUP BY YEAR(DATE),QUARTER(DATE)
ORDER BY YEAR(DATE),QUARTER(DATE);      

3).按年统计总收益

SELECT YEAR(DATE),SUM(price)
FROM transactions
GROUP BY YEAR(DATE)
ORDER BY YEAR(DATE);      

4).统计每周各天的总收益

select weekday(date),SUM(price)
from transactions
group by weekday(date)
order by weekday(date);      

5)按时间段统计平均收益和总收益

select case
when hour(time)>5 and hour(time)<=8 then 'early morning'
when hour(time)>8 and hour(time)<=11 then 'morning'
when hour(time)>11 and hour(time)<=13 then 'noon'
when hour(time)>13 and hour(time)<=18 then 'afternoon'
when hour(time)>18 and hour(time)<=22 then 'evening'
else 'night'
end as time_slot,
round(sum(price),2)as total_revenue,
round(avg(price),2)as avg_revenue
from transactions
group by time_slot;      

SELECT CASE HOUR(TIME)
WHEN 5 THEN "Early Monring"
WHEN 6 THEN "Early Monring"
WHEN  7 THEN "Early Monring"

WHEN 8 THEN "Monring"
WHEN 9 THEN "Monring"
WHEN 10 THEN "Monring"

WHEN 11 THEN "Noon"
WHEN 12 THEN "Noon"

WHEN 13 THEN "Afternoon"
WHEN 14 THEN "Afternoon"
WHEN 15 THEN "Afternoon"
WHEN 16 THEN "Afternoon"
WHEN 17 THEN "Afternoon"

WHEN 18 THEN "Evening"
WHEN 19 THEN "Evening"
WHEN 20 THEN "Evening"
WHEN 21 THEN "Evening"

ELSE "Evening" END time_slot,
ROUND(AVG(price),2) avg_price,
ROUND(SUM(price),2) sum_price
FROM transactions
GROUP BY time_slot;      

6).统计消费次数排行前10位的客户

8).统计每年度、季度总客户数

9).找出平均消费额最大的客户

10).统计最受欢迎的产品（分别从购买客户数量、购买频次、消费额三个维度分析）

3、门店分析

1).按客流量找出最受欢迎的门店

2).按客户消费额找出最受欢迎的门店

3).按交易频次找出最受欢迎的门店

4).按客流量找出每个门店最受欢迎的商品

5).统计每个门店客流量与雇员的比率