第7章 数据分类汇总及透视

7.1　分类汇总

#单列分类汇总
SELECT city,COUNT(id) AS id_count
FROM data1
GROUP BY city
ORDER BY id_count;

#多列分类汇总
SELECT city,colour,ROUND(SUM(price),2) AS id_count
FROM data1
GROUP BY city,colour;

7.2　数据透视

#查看原始数据表
SELECT *
FROM data1;

#使用CASE WHEN进行数据透视
CREATE VIEW data_Items AS(
	SELECT data1.city,
		CASE WHEN colour = "A" THEN price END AS A,
		CASE WHEN colour = "B" THEN price END AS B,
		CASE WHEN colour = "C" THEN price END AS C,
		CASE WHEN colour = "F" THEN price END AS F
	FROM data1
);
	
#查看结果
SELECT *
FROM data_Items;

#对字段进行求和汇总
CREATE VIEW data1_Extended_Pivot AS(
	SELECT city,SUM(A) AS A,SUM(B) AS B,SUM(C) AS C,SUM(F) AS F 
	FROM data_Items
	GROUP BY city
);

#查看结果 
SELECT *
FROM data1_Extended_Pivot;

#对空值进行处理 
CREATE VIEW data1_Extended_Pivot_Pretty AS(
	SELECT city,COALESCE(A, 0) AS A,COALESCE(B, 0) AS B,COALESCE(C, 0) AS 	C,COALESCE(F, 0) AS F
	FROM data1_Extended_Pivot
);

#查看数据透视结果 
SELECT *
FROM data1_Extended_Pivot_Pretty;