I have a record creation time stamp in my table . I need to write a query to get the count of records every two hours between two specific dates.
Starting from 1st January 2017 till today.
I have the following table USER
USER_ID CREATION_DATE
1 01-JAN-2017 00:00:00
2 01-JAN-2017 01:00:00
3 01-JAN-2017 01.22:00
4 02-JAN-2017 01:00:00
I need a query which would give me result like below
DATE | 00-02 hr| 02-04 hr| 04-06 hr|06-08 hr|08-10 hr|10-12 hr|12-14 hr| ....
01-JAN-2017| 3 | 0 | 0 |0 |0 |0 |0 |..
02-JAN-2017|1 | 0 | 0 |0 |0 |0 |0 |..
.
.
.
You need a two-step process:
- Map the times into 2 hour buckets
- Pivot the results of this
You can do the first step by:
- Getting the hour of day
- Dividing this by two
- Returning the floor of this
So 01:00 -> 0, 02:00 -> 1, 03:00 -> 1, 04:00 -> 2, etc.
You can do this with something like this:
floor(extract(hour from to_timestamp(dt))/2)
Then find how many rows are in each bucket for each day by getting the count(*) partitioned by day and the calculation above:
count(*) over (
partition by trunc(dt), floor(extract(hour from to_timestamp(dt))/2)
)
Finally you need to pivot the results to get the columns as rows. Return the min (or max) count for each bucket you've determined above.
Put this all together and you get:
with rws as (
select trunc(sysdate)+level/20 dt
from dual connect by level <= 20
), cts as (
select trunc(dt) dy,
count(*) over (
partition by trunc(dt), floor(extract(hour from to_timestamp(dt))/2)
) ct,
floor(extract(hour from to_timestamp(dt))/2) hr
from rws
)
select *
from cts
pivot (
min(ct) for hr in (0 as "0-2", 1 as "2-4", 3 as "4-6", 4 as "6-8") --etc.
);
DY 0-2 2-4 4-6 6-8
09-JUN-2017 00:00:00 1 2 2 2
10-JUN-2017 00:00:00 1 For more on pivoting, read:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot