CODEWITHSUNDEEP
sqlhive
Cybernetic
Cybernetic

Reputation: 13334

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

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.

Upvotes: 2

Views: 27

Answers (2)

leftjoin
leftjoin

Reputation: 38335

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)

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175954

Using subquery:

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

Upvotes: 1

Related Questions