Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

max value of previous 30 days for every record of a table in hive

max value of previous 30 days for every record of a table in hive

New Contributor

hi, i have a table in which I need to calculate the max among the previous 30 days for every record , is there a way in hive with sub queries?, see below for sample data

FIELD1FIELD2FIELD3DATEVARMAX VAR
USATXRTB201609301
USATXRTB201610011
USATXRTB201610021
USATXRTB201610031
USATXRTB201610041
USATXRTB201610052
USATXRTB201610062
USATXRTB201610072
USATXRTB201610082
USATXRTB201610091
USATXRTB201610102
USATXRTB201610112
USATXRTB201610122
USATXRTB201610132
USATXRTB201610142
USATXRTB201610152
USATXRTB201610162
USATXRTB201610172
USATXRTB201610182
USATXRTB201610192
USATXRTB201610202
USATXRTB201610212
USATXRTB201610222
USATXRTB201610232
USATXRTB201610242
USATXRTB201610253
USATXRTB201610262
USATXRTB201610272
USATXRTB201610282
USATXRTB201610292
USATXRTB2016103023
USATXRTB2016103123
USATXRTB2016110123
USATXRTB2016110223
USATXRTB2016110323
USATXRTB2016110423
USATXRTB2016110523
USATXRTB2016110623
USATXRTB2016110723
USATXRTB2016110823
USATXRTB2016110923
USATXRTB2016111023
USATXRTB2016111123
USATXRTB2016111223
USATXRTB2016111323
USATXRTB2016111423
USATXRTB2016111523
USATXRTB2016111623
USATXRTB2016111723
USATXRTB2016111823
USATXRTB2016111923
USATXRTB2016112023
USATXRTB2016112123
USATXRTB2016112223
USATXRTB2016112323
USATXRTB2016112423
USATXRTB2016112522
USATXRTB2016112622
USATXRTB2016112722
USATXRTB2016112822
USATXRTB2016112922
UKLONDONRTB201609301
UKLONDONRTB201610011
UKLONDONRTB201610021
UKLONDONRTB201610031
UKLONDONRTB201610041
UKLONDONRTB201610051
UKLONDONRTB201610061
UKLONDONRTB201610071
UKLONDONRTB201610081
UKLONDONRTB201610091
UKLONDONRTB201610101
UKLONDONRTB201610111
UKLONDONRTB201610121
UKLONDONRTB201610131
UKLONDONRTB201610141
UKLONDONRTB201610151
UKLONDONRTB201610161
UKLONDONRTB201610171
UKLONDONRTB201610181
UKLONDONRTB201610191
UKLONDONRTB201610201
UKLONDONRTB201610211
UKLONDONRTB201610221
UKLONDONRTB201610231
UKLONDONRTB201610241
UKLONDONRTB201610254
UKLONDONRTB201610262
UKLONDONRTB201610272
UKLONDONRTB201610282
UKLONDONRTB201610292
UKLONDONRTB2016103024
UKLONDONRTB2016103124
UKLONDONRTB2016110124
UKLONDONRTB2016110224
UKLONDONRTB2016110324
UKLONDONRTB2016110424
UKLONDONRTB2016110524
UKLONDONRTB2016110624
UKLONDONRTB2016110724
UKLONDONRTB2016110824
UKLONDONRTB2016110924
UKLONDONRTB2016111024
UKLONDONRTB2016111124
UKLONDONRTB2016111224
UKLONDONRTB2016111324
UKLONDONRTB2016111424
UKLONDONRTB2016111524
UKLONDONRTB2016111624
UKLONDONRTB2016111724
UKLONDONRTB2016111824
UKLONDONRTB2016111924
UKLONDONRTB2016112024
UKLONDONRTB2016112124
UKLONDONRTB2016112224
UKLONDONRTB2016112324
UKLONDONRTB2016112424
UKLONDONRTB2016112522
UKLONDONRTB2016112622
UKLONDONRTB2016112722
UKLONDONRTB2016112822
UKLONDONRTB2016112922

1 REPLY 1

Re: max value of previous 30 days for every record of a table in hive

Rising Star

You can try hive windowing function. Something like below.

select tdate,var, max(var) over (order by tdate ROWS between CURRENT ROW AND 30 FOLLOWING ) maxvar from testwindow;

You can also include "PARTITION BY" clause if you need to group it by some other column/s.

HTH