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).統計每個門店客流量與雇員的比率