Retrieve the most recent week of data, regardless of when the data stopped being collected, in HiveQL

user_1639594 0
Viewed 22

I need to retrieve the most recent week of data, regardless of when the data stopped being collected. Thus, instead of using the current date and minusing 7 I need to take the max date in the table and minus 7.

I am trying:

select * from my_table where datetime_column >= date_sub(max(datetime_column), 7)

But max date doesn't seem to be suported in HIVE.

hive sql
2 Answers

Using subquery:

select * 
from my_table 
where datetime_column >= date_sub((SELECT max(datetime_column) FROM my_table), 7)
user_50708790

Using analytic function will perform better because source table will be read once.

select * --list columns here to exclude max_dtm if you do not need it
from
(
select t.*,
       max(datetime_column) over() as max_dtm 
from my_table t
)s
where datetime_column >= date_sub(max_dtm , 7)
user_27003440
Related