CODEWITHSUNDEEP
sqlhive
steves
steves

Reputation: 331

Selecting date ranges in hive db?

I have a transactions table in Hive. Goal: count number of distinct transactions from maximum date to 3 months earlier: max(date) - 3M.

My date field is a variant of timestamp: 2017-09-30 23:59:00.0

Here is what I have done so far:

select count(distinct(transaction_id)) from table
where max(cast(date_fld_ts as date)) - INTERVAL '3' MONTH

It fails to run, please advise what do I miss here?

ERROR:

An error occurred when executing the SQL command:
select count(distinct(transaction_id)) from db.table 
where max(cast(date_fld_ts as date)) - INTERVAL '3' MONTH

Error while compiling statement: FAILED: SemanticException [Error 10128]: Line 2:6 Not yet supported place for UDAF 'max' [SQL State=42000, DB Errorcode=10128]
1 statement failed.

Execution time: 0.48s

Upvotes: 0

Views: 71

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can't use an aggregate function in where. Instead get the max date and use it later.

select count(distinct transaction_id)
from (select max(cast(date_fld_ts as date)) over() as max_date,t.*
      from table t
     ) t
where date_fld_ts >= add_months(max_date,-3) and date_fld_ts <= max_date

Upvotes: 1

Related Questions