Hive行转列的应用之计算公司累加收入

公司代码,年度,1月-------------------------12月的收入金额
burk,year,tsl01,tsl02,tsl03,tsl04,tsl05,tsl06,tsl07,tsl08,tsl09,tsl10,tsl11,tsl12
853101,2010,100200,25002,19440,20550,14990,17227,40990,28778,19088,29889,10990,20990
853101,2011,19446,20556,14996,17233,40996,28784,19094,28779,19089,29890,10991,20991
853101,2012,19447,20557,14997,17234,20560,15000,17237,28780,19090,29891,10992,20992
853101,2013,20560,15000,17237,41000,17234,20560,15000,17237,41000,29892,10993,20993
853101,2014,19449,20559,14999,17236,41000,28788,28786,19096,29897,41000,28788,20994
853101,2015,100205,25007,19445,20555,17236,40999,28787,19097,29898,29894,10995,20995
853101,2016,100206,25008,19446,20556,17237,41000,28788,19098,29899,29895,10996,20996
853101,2017,100207,25009,17234,20560,15000,17237,41000,15000,17237,41000,28788,20997
853101,2018,100208,25010,41000,28788,28786,19096,29897,28786,19096,29897,10998,20998
853101,2019,100209,25011,17236,40999,28787,19097,29898,28787,19097,29898,10999,20999
846271,2010,100210,25012,17237,41000,28788,19098,29899,28788,19098,29899,11000,21000
846271,2011,100211,25013,19451,20561,15001,17238,41001,28789,19099,29900,11001,21001
846271,2012,100212,100213,20190,6484,46495,86506,126518,166529,206540,246551,286562,326573
846271,2013,100213,100214,21297,5008,44466,83924,123382,162839,202297,241755,281213,320671
846271,2014,100214,100215,22405,3531,42436,81341,120245,159150,198055,236959,275864,314769
846271,2015,100215,100216,23512,2055,19096,29897,28786,19096,29897,41000,29892,308866
846271,2016,100216,100217,24620,579,38377,76175,28788,28786,19096,29897,41000,302964
846271,2017,100217,100218,25727,898,36347,73592,40999,28787,19097,29898,29894,297062
846271,2018,100218,100219,26835,2374,34318,71009,41000,28788,19098,29899,29895,291159
846271,2019,100219,100220,27942,3850,32288,68427,17237,41000,15000,17237,41000,285257

建表语句
CREATE TABLE `burk`(
  `bruk_id` string, 
  `year` string, 
  `m1` string, 
  `m2` string, 
  `m3` string, 
  `m4` string, 
  `m5` string, 
  `m6` string, 
  `m7` string, 
  `m8` string, 
  `m9` string, 
  `m10` string, 
  `m11` string, 
  `m12` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ','；

 

1、统计每个公司每年按月累计收入  行转列 --> sum窗口函数

输出结果
公司代码,年度,月份,当月收入,累计收入

行转列

 select bruk_id,year,month,income from burk lateral view explode(map(`m1`,1,`m2`,2,`m3`,3,`m4`,4,`m5`,5,`m6`,6,`m7`,7,`m8`,8,`m9`
,9,`m10`,10,`m11`,11,`m12`,12)) t   as income, month;

求累加

select  bruk_id,year,month,income, sum(income) over(partition by bruk_id,year order by month)  as sum_income from( select bruk_id,year,month,income from burk lateral view explode(map(`m1`,1,`m2`,2,`m3`,3,`m4`,4,`m5`,5,`m6`,6,`m7`,7,`m8`,8,`m9`
,9,`m10`,10,`m11`,11,`m12`,12)) t   as income, month) t1; 

 其他写法（使用with将表取别名）

with t1 as( select bruk_id,year,month,income from burk lateral view explode(map(`m1`,1,`m2`,2,`m3`,3,`m4`,4,`m5`,5,`m6`,6,`m7`,7,`m8`,8,`m9`
,9,`m10`,10,`m11`,11,`m12`,12)) t   as income, month) 
select  bruk_id,year,month,income, sum(income) over(partition by bruk_id,year order by month)  as sum_income from  t1; 

 

 

2、统计每个公司当月比上年同期增长率  行转列 --> lag窗口函数

结果：
公司代码,年度,月度,增长率（当月收入/上年当月收入 - 1）

代码：

select  bruk_id,year,month,income,lag_num,if(lag_num is null, 1,(income/lag_num-1) )as incr from (select  bruk_id,year,month,income,lag(income,1) over(partition by bruk_id,month order by year) as lag_num from  (select bruk_id,year,month,income from burk lateral view explode(map(`m1`,1,`m2`,2,`m3`,3,`m4`,4,`m5`,5,`m6`,6,`m7`,7,`m8`,8,`m9`,9,`m10`,10,`m11`,11,`m12`,12)) t   as income, month)  as t2) t3;

 其他写法（使用with将表取别名）

with t1 as(select bruk_id,year,month,income from burk lateral view explode(map(`m1`,1,`m2`,2,`m3`,3,`m4`,4,`m5`,5,`m6`,6,`m7`,7,`m8`,8,`m9`
,9,`m10`,10,`m11`,11,`m12`,12)) t   as income, month),
t2 as(select  bruk_id,year,month,income,lag(income,1) over(partition by bruk_id,month order by year) as lag_num from t1)
select    bruk_id,year,month,income,lag_num,if(lag_num is null, 1,(income/lag_num-1) )as incr  from t2;