【面试题】统计产量数据：难点（分组汇总 列转行 查询结果插入)

题目：有一张数据产量表 如下

Year	Month	data
2016	1	5000
2016	2	9000
...	...	...
2017	12	12000

编写SQL，产生如下数据，并保存到tab2,其中S1 S2 S3 S4代表4个季度

Year	S1	S2	S3	S4
2016	1200	3000	6000	8000
2017	1800	2800	7000	7500

1    首先对问题进行分解，原始数据中是每个月的产量，而我们实际需要的是每个季度，所以先对季度数据进行分组汇总

select `Year`,CONCAT('S',FLOOR((`Month`+2)/3)) as s,sum(data) AS `Data` from table1 GROUP BY `Year` ,s

这里用到了几个函数 

FLOOR() 向下取整    CONCAT(str1,str2...) 字符串连接    sum()汇总求和

得出如下结果

Year	s	data
2016	S1	1800
2016	S2	2000
...	...	...
2017	S1	1500
2017	S2	3216

2 然后对比需求，发现需要对数据进行 列转行处理

SELECT YEAR,  
		MAX(CASE s WHEN 'S1' THEN Data ELSE 0 END ) S1,  
		MAX(CASE s WHEN 'S2' THEN Data ELSE 0 END ) S2,  
		MAX(CASE s WHEN 'S3' THEN Data ELSE 0 END ) S3,  
		MAX(CASE s WHEN 'S4' THEN Data ELSE 0 END ) S4  
FROM (select `Year`,CONCAT('S',FLOOR((`Month`+2)/3)) as s,sum(data) AS `Data` from table1 GROUP BY `Year` ,s) r  
GROUP BY YEAR

这里主要解释几点，首先我们对年份进行分组汇总，然后利用max(Case When)进行列转行处理

3 最后，就是将结果插入一张新表 create table table1 as table2

DROP TABLE IF EXISTS table2;
create table table2 as 	(SELECT YEAR,
	MAX(CASE s WHEN 'S1' THEN Data ELSE 0 END ) S1,
	MAX(CASE s WHEN 'S2' THEN Data ELSE 0 END ) S2,
	MAX(CASE s WHEN 'S3' THEN Data ELSE 0 END ) S3,
	MAX(CASE s WHEN 'S4' THEN Data ELSE 0 END ) S4
FROM (select `Year`,CONCAT('S',FLOOR((`Month`+2)/3)) as s,sum(data) AS `Data` from table1 GROUP BY `Year` ,s) r
GROUP BY YEAR)