数据统计-行转列

create table vn09jj5.temp_data
(
 yyyy int,
 mm int,
 sales decimal(10,2)
)
stored as textfile ; 


insert into vn09jj5.temp_data(yyyy,mm,sales)
values
--2018
(2018,1,1801),
(2018,2,1802),
(2018,3,1803),
(2018,4,1804),
(2018,5,1805),
(2018,6,1806),
(2018,7,1807),
(2018,8,1808),
(2018,9,1809),
(2018,10,1810),
(2018,11,1811),
(2018,12,1812),
--2019
(2019,1,1901),
(2019,2,1902),
(2019,3,1903),
(2019,4,1904),
(2019,5,1905),
(2019,6,1906),
(2019,7,1907),
(2019,8,1908),
(2019,9,1909),
(2019,10,1910),
(2019,11,1911),
(2019,12,1912);

select yyyy,mm,sales 
from vn09jj5.temp_data ;

+-------+-----+----------+
| yyyy  | mm  |  sales   |
+-------+-----+----------+
| 2018  | 1   | 1801.00  |
| 2018  | 2   | 1802.00  |
| 2018  | 3   | 1803.00  |
| 2018  | 4   | 1804.00  |
| 2018  | 5   | 1805.00  |
| 2018  | 6   | 1806.00  |
| 2018  | 7   | 1807.00  |
| 2018  | 8   | 1808.00  |
| 2018  | 9   | 1809.00  |
| 2018  | 10  | 1810.00  |
| 2018  | 11  | 1811.00  |
| 2018  | 12  | 1812.00  |
| 2019  | 1   | 1901.00  |
| 2019  | 2   | 1902.00  |
| 2019  | 3   | 1903.00  |
| 2019  | 4   | 1904.00  |
| 2019  | 5   | 1905.00  |
| 2019  | 6   | 1906.00  |
| 2019  | 7   | 1907.00  |
| 2019  | 8   | 1908.00  |
| 2019  | 9   | 1909.00  |
| 2019  | 10  | 1910.00  |
| 2019  | 11  | 1911.00  |
| 2019  | 12  | 1912.00  |
+-------+-----+----------+

 

select 
 mm,
 sum(case when yyyy=2018 then sales else 0 end) as sum_2018,
 sum(case when yyyy=2019 then sales else 0 end) as sum_2019
from vn09jj5.temp_data
group by mm
order by mm
;

+-----+-----------+-----------+
| mm  | sum_2018  | sum_2019  |
+-----+-----------+-----------+
| 1   | 1801.00   | 1901.00   |
| 2   | 1802.00   | 1902.00   |
| 3   | 1803.00   | 1903.00   |
| 4   | 1804.00   | 1904.00   |
| 5   | 1805.00   | 1905.00   |
| 6   | 1806.00   | 1906.00   |
| 7   | 1807.00   | 1907.00   |
| 8   | 1808.00   | 1908.00   |
| 9   | 1809.00   | 1909.00   |
| 10  | 1810.00   | 1910.00   |
| 11  | 1811.00   | 1911.00   |
| 12  | 1812.00   | 1912.00   |
+-----+-----------+-----------+

 

 

create table vn09jj5.temp_data_2
(
 yyyy int,
 mm int,
 sales decimal(10,2)
)
stored as textfile ; 


insert into vn09jj5.temp_data_2(yyyy,mm,sales)
values
--2019
(2019,1,1901),(2019,2,1902),(2019,3,1903),
(2019,4,1904),(2019,5,1905),(2019,6,1906),
(2019,7,1907),(2019,8,1908),(2019,9,1909),
(2019,10,1910),(2019,11,1911),(2019,12,1912),
--2020
(2020,1,2001),(2020,2,2002),(2020,3,2003),
(2020,4,2004),(2020,5,2005),(2020,6,2006),
(2020,7,2007);

 

select 
 mm,
 sum(case when yyyy=2019 then sales else 0 end) as sum_2019,
 sum(case when yyyy=2020 then sales else 0 end) as sum_2020
from vn09jj5.temp_data_2
group by mm
order by mm
;

+-----+-----------+-----------+
| mm  | sum_2019  | sum_2020  |
+-----+-----------+-----------+
| 1   | 1901.00   | 2001.00   |
| 2   | 1902.00   | 2002.00   |
| 3   | 1903.00   | 2003.00   |
| 4   | 1904.00   | 2004.00   |
| 5   | 1905.00   | 2005.00   |
| 6   | 1906.00   | 2006.00   |
| 7   | 1907.00   | 2007.00   |
| 8   | 1908.00   | 0.00      |
| 9   | 1909.00   | 0.00      |
| 10  | 1910.00   | 0.00      |
| 11  | 1911.00   | 0.00      |
| 12  | 1912.00   | 0.00      |
+-----+-----------+-----------+