CODEWITHSUNDEEP
sqloracle-databasegroup-byhour
Snehal K
Snehal K

Reputation: 21

Grouping of records hour by hour in oracle

My o/p of the query is like below

Date       Hour Orders
2018-02-22 00  22
2018-02-22 03  12
2018-02-22 04  12
2018-02-22 08  12

But I want it like this

Date       Hour Orders
2018-02-22 00 22
2018-02-22 01 0
2018-02-22 02 0
2018-02-22 03 12
2018-02-22 04 12
2018-02-22 05 0
2018-02-22 06 0
2018-02-22 07 0
2018-02-22 08 12

Even though there is no order placed during that hour, that hour should be displayed and it should show order placed as 0.

Upvotes: 0

Views: 187

Answers (2)

Mahamoutou
Mahamoutou

Reputation: 1555

Another way to do that is to use oracle "data-densification" method. The key thing is to add partition by clause after outer join like below.

With Needed_Hours (hr) as ( 
    select lpad(level-1, 2, '0')
    from dual 
    connect by level <= (
        select max(to_number(Hour)) - min(to_number(Hour)) + 1 
        from your_table
    )
)
select t."Date", h.hr hour, nvl(t.orders, 0)orders
from Needed_Hours h
    left join your_table t partition by (t."Date")
        on h.hr = t.hour;

Upvotes: 3

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

Assuming you have timestamp data(namely dt), split into two columns as date and hour and apply outer join among the data set derived through row generation by difference of extremum hours and the original table(namely t) such as

WITH t1 AS
(
 SELECT level-1 AS lvl 
   FROM dual 
CONNECT BY level <= ( SELECT EXTRACT(HOUR FROM MAX(dt) - MIN(dt))+1 FROM t )
), t2 AS
(
 SELECT TRUNC(dt) AS "Date", EXTRACT( HOUR FROM dt ) AS hr, orders FROM t
)
SELECT MAX("Date") OVER (ORDER BY lvl ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Date", 
       lvl AS "Hour", NVL(orders,0) AS "Orders"
  FROM t1
  LEFT JOIN t2
    ON t2.hr = t1.lvl
 ORDER BY t1.lvl

Demo

Upvotes: 1

Related Questions