CODEWITHSUNDEEP
datehivecounting
Dick McManus
Dick McManus

Reputation: 789

Soft coding dates in HIVE

I am having a difficult time soft coding dates in HIVE. I want to do a count of unique ID's the appear in each week of my data.

e.g.

SELECT    
Count(distinct ID)    
From Database.Table    
Where filter1 = "Thing1" And filter2 = "Thing2"    
AND TO_DATE(timestamp_utc) between TO_DATE("2016-04-10") AND TO_DATE("2016-04-16")

But I want this not just for one week but for each week so far this year, without having to run it 20 times with different dates manually typed in.

Any suggestions? Thank you.

Upvotes: 0

Views: 36

Answers (1)

nobody
nobody

Reputation: 11090

Filter the data for the year,get the week number, group by id,week number

select count(distinct id) as id_count,weekofyear(TO_DATE(timestamp_utc)) as weeks
from Table
where filter1 = "Thing1" and filter2 = "Thing2" and TO_DATE(timestamp_utc) > '2016-01-01'
group by id,weeks

Upvotes: 1

Related Questions