CODEWITHSUNDEEP
hivebigdata
saucypigeon
saucypigeon

Reputation: 61

Count how many entries were in each group in Hive

My DB looks like this and represents a purchase made by a customer at a certain store:

Customer ID | Store ID | Date & Time
1 | 1884 | 2016-10-30 13:00:00
5 | 2001 | 2016-10-30 13:00:00

The dataset is very large. Time is spaced by 1 hours.

I need to count how many customers made a purchase during each hour of the day. Returned data should look like this:

Store ID | Unique Date & Time | Number of purchases 
1884 | 2016-10-30 13:00:00 | 8
1884 | 2016-10-30 14:00:00 | 12

I am out of ideas and would appreciate any help I can get.

Upvotes: 0

Views: 1653

Answers (2)

kirtan_shah
kirtan_shah

Reputation: 78

I am supposing this query will be run once every hour and the data which is generated is appended to the existing dataset

Below query will give you count of the purchases made by the customer in a particular store at the [Date and Time]

select [Date and Time],[Store ID],count(customer id) 
from [tablename] group by [Date and Time],[Store ID] 
order by [Date and Time] DESC

if you want to get a complete count of all purchases per hour irrespective of store id then you can try this:

select [Date and Time],count(customer id) 
from [tablename]  group by [Date and Time] 
order by [Date and Time] DESC

order by desc is used to get latest timestamp results on top

Upvotes: 0

Mahesh Gareja
Mahesh Gareja

Reputation: 1812

Select id and group it with hours and day.

SELECT [ Store ID ], count(*)
FROM table1
GROUP BY DATEPART(day, [ Date & Time]), DATEPART(hour, [ Date & Time]);

Upvotes: 1

Related Questions