How to get last quarterly and last half yearly average of balance for each month in hive?

Question

I have a table with column cust_id, year_, month_, monthly_txn, monthly_bal. I need to calculate the previous three month and previous six month avg(monthly_txn) and variance(monthly_bal) for each month. I have a query which returns avg and variance for last three and six month only for last month not for each month. I am not good in analytical function in Hive.

  SELECT cust_id, avg(monthly_txn)y,variance(monthly_bal)x, FROM ( 
  SELECT cust_id, monthly_txn,monthly_bal,
            row_number() over (partition by cust_id order by year_,month_ desc) r
        from mytable) b WHERE r <= 3 GROUP BY cust_id

But I want something like below.

input:

 cust_id     year_   month_     monthly_txn  monthly_bal
1            2018     1              456    8979289
1            2018     2              675    4567
1            2018     3              645    4890
1            2017     1              342    44522
1            2017     2              378    9898900
1            2017     2              456    234492358
1            2017     4              3535   789
1            2017     5              456    345
1            2017     6              598    334

expecting output:

suppose for txn the quaterly and half yearly txn will be like this same for variance also

cust_id     year_    month_     monthly_txn  monthly_bal     q_avg_txn            h_avg_txn
   1         2018      1          456          8979289       avg(456,598,4561)    avg(456,598,4561,3535,4536,378)
   1         2018      2          675          4567          avg(675,456,598)     avg(675,456,3535,4561,598,4536)
   1         2018      3          645          4890          avg(645,675,645)     avg(645,675,645,3535,4561,598)
   1         2017      1          342          44522         avg(342)             avg(342)
   1         2017      2          378          9898900       avg(378,342)         avg(378,342)
   1         2017      3          4536         234492358     avg(4536,372,342)    avg(4536,378,342)
   1         2017      4          3535         789           avg(3535,4536,378)   avg(3535,4536,378,342) 
   1         2017      5          4561         345           avg(4561,3535,4536)  avg(4561,3535,4536,342,378)
   1         2017      6          598          334           avg(598,4561,3535)   avg(598,4561,3535,4536,342,378)

"previous three month and previous six months", then what will be the current date the system date or some specified date in where condition?? — , May 09 '18 at 07:49
For date I have only year and month. I have to do this on historical data. so current year and month will the month and year present in each record — Jaishree Rout, May 09 '18 at 07:58

score 1 · Answer 1 · answered May 09 '18 at 10:36

1

use unbounded preceding analytic functions (/* to get the quarterly and half years values) and then use the subquery to get results.

What is ROWS UNBOUNDED PRECEDING used for in Teradata?

answered May 09 '18 at 10:36

saravanatn

630
5
9

score 0 · Accepted Answer · answered May 09 '18 at 11:19

If you have data for every month of interest (i.e., no gaps), then this should work:

select t.*,
       avg(monthly_bal) over (partition by cust_id
                              order by year_, month_ 
                              rows between 2 preceding and current row
                             ) as avg_3,
       avg(monthly_bal) over (partition by cust_id
                              order by year_, month_ 
                              rows between 5 preceding and current row
                             ) as avg_6,
       variance(monthly_bal) over (partition by cust_id
                                   order by year_, month_ 
                                   rows between 2 preceding and current row
                                  ) as variance_3,
       variance(monthly_bal) over (partition by cust_id
                                   order by year_, month_ 
                                   rows between 5 preceding and current row
                                  ) as variance_6
from mytable t;

How to get last quarterly and last half yearly average of balance for each month in hive?

2 Answers2