题目:有一张数据产量表 如下
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)