CODEWITHSUNDEEP
hivehiveql
LorcanT
LorcanT

Reputation: 63

Select from a Hive Table where date less than max date

I'm trying to select * from a Hive table where a date column called TRANS_DATE say is >= than the 365 days before the maximum TRANS_DATE.

Below is the query I've tried so far:

select * from TABLE
where (TRANS_DATE > DATE_SUB(max(TRANS_DATE), 365)) and 
        (TRANS_DATE < max(TRANS_DATE));

Below is the error I have gotten: "Error while compiling statement: FAILED: SemanticException [Error 10128]: Line 2:28 Not yet supported place for UDAF 'max'"

An example of the date format is: "2006-05-30 00:00:00.0"

The query is to be to read data from a hive table into Qlikview so ideally I'd like not to define variables before hand and would prefer to do the select dynamically. Apologies if any of this is daft as I'm new to Hive.

Upvotes: 2

Views: 4212

Answers (1)

leftjoin
leftjoin

Reputation: 38325

calculate max_date in a subquery and cross join with table:

select * 
  from TABLE a
       cross join --cross join with single row
       (select max(TRANS_DATE) as max_trans_date from TABLE) b 
 where (a.TRANS_DATE > DATE_SUB(b.max_trans_date, 365)) 
   and (a.TRANS_DATE < b.max_trans_date);

With analytic function:

select a.* from
(select a.*,
       max(TRANS_DATE) over() as max_trans_date 
  from TABLE a) a
 where (a.TRANS_DATE > DATE_SUB(a.max_trans_date, 365)) 
   and (a.TRANS_DATE < a.max_trans_date);

Upvotes: 1

Related Questions