hadoop - 如何从配置单元中的日期中提取月份并按月份分组

Question

我有如下的蜂巢表，现在我需要按每个月的平均值对数据进行分组

样本蜂巢表：

 dat        amazon  tesla  infosys  facebook  apple 
 03/01/17  753.67   808.01 216.99   14.74     116.86
 04/01/17  757.18   807.77 226.99   15.13     118.69
 05/02/17  780.45   813.02 226.75   15.02     120.67
 06/05/17  795.99   825.21 229.01   14.82     123.41

样本输出：

month  amazon  tesla  infosys  facebook  apple 
 1     782.2   843.23 548.87    24.42    143.35
 2     743.2   896.12 453.34    44.34    143.55

我需要每个月的平均值，请帮助我

标签： hadoophivehiveql

Answer 1

select cast(substr(dat, 4, 2) as int) as month,
       avg(amazon)                    as amazon,
       avg(tesla)                     as tesla,
       avg(infosys)                   as infosys,
       avg(facebook)                  as facebook,
       avg(apple)                     as apple
  from tablename
 group by cast(substr(dat, 4, 2) as int);