0

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.

Community
  • 1
  • 1
saucypigeon
  • 63
  • 4

2 Answers2

1

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]);
Mahesh Gareja
  • 1,652
  • 2
  • 12
  • 23
0

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

kirtan_shah
  • 78
  • 7
  • This may run into problem with minutes and secs so strip value to day & hour to avoid issues. – Ravinder Karra Jan 25 '17 at 18:04
  • Still no luck, this code returns the count for all stores. In other words, it doesn't reset when store id is changing. – saucypigeon Jan 26 '17 at 05:44
  • @promashk ,you can add a where clause at the end of first query mentioned above which restricts time to previous one hour. Now this query should be run every hour of the day ,output of which should be inserted into a table say x. Now when you query data from x the other day,restrict it to only previous days data .[http://stackoverflow.com/questions/34757504/hive-how-to-calculate-time-difference] – kirtan_shah Jan 26 '17 at 18:08