X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • March 4, 2015

Mind the gaps

In our last few posts, we looked at solving FizzBuzz in SQL using a row generator, and the performance of several row generators. In this post, we'll take a look at another application for row generators: filling in "gaps".

Here's the CONNECT BY row generator we used to solve FizzBuzz:

select  level
from    dual
connect by level <= 100 ;

this generates 100 rows of numbers 1..100. But row generators aren't limited to numbers. By using date arithmetic, you can generate dates:

select  date '2015-01-01' + level -1 as gendate
from    dual
connect by date '2015-01-01' + level -1 < date '2015-02-01' ;

GENDATE
---------
01-JAN-15
02-JAN-15
03-JAN-15
04-JAN-15
05-JAN-15
...
29-JAN-15
30-JAN-15
31-JAN-15

31 rows selected.

Or by using ASCII codes, you can generate characters:

select chr(65 + level - 1) as letter
from dual
connect by level <= 26;

LETT
----
A
B
C
D
...
X
Y
Z

26 rows selected.

Row generators are particularly useful for filling in "gaps" in the data. What do I mean by "gaps"? Well, here's an example from the OE.ORDERS table.

 Name                 Null?    Type
 -------------------- -------- ----------------------------
 ORDER_ID             NOT NULL NUMBER(12)
 ORDER_DATE           NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE
 ORDER_MODE                    VARCHAR2(8)
 CUSTOMER_ID          NOT NULL NUMBER(6)
 ORDER_STATUS                  NUMBER(2)
 ORDER_TOTAL                   NUMBER(8,2)
 SALES_REP_ID                  NUMBER(6)
 PROMOTION_ID                  NUMBER(6)

Suppose that we want to know how many orders are recorded for each day of June, 2007. We can use trunc(order_date) to get the count of all orders on each day for which there were ANY orders:

--How many orders on each day in June 2007?
select trunc(order_date) as orderdate, count(*) as ordercount
from oe.orders
where order_date between date '2007-06-01' and date '2007-06-30'
group by trunc(order_date) order by 1;

ORDERDATE ORDERCOUNT
--------- ----------
04-JUN-07          1
07-JUN-07          2
13-JUN-07          1
18-JUN-07          1
27-JUN-07          1
29-JUN-07          4

6 rows selected.

This gives the number of orders for each day that had any orders. But it doesn't give a zero for the days on which there were no orders. There's no data in the OE.ORDERS table for these days, so nothing to sum or count. This is the kind of "gap" that row generators excel at filling.

In the following query, we use CONNECT BY to generate one row for each day in June 2007, then outer join that to the previous OE query.

select gendate, nvl(ordercount,0) from 
( select trunc(order_date) as orderdate, count(*) as ordercount
  from oe.orders
  where order_date between date '2007-06-01' and date '2007-06-30'
  group by trunc(order_date)
) jun_orders, 
( select  date '2007-06-01' + level -1 as gendate
 from    dual
 connect by date '2007-06-01' + level -1 < date '2007-06-30' 
) jun_days
where gendate = orderdate(+)
order by gendate;

GENDATE   NVL(ORDERCOUNT,0)
--------- -----------------
01-JUN-07                 0
02-JUN-07                 0
03-JUN-07                 0
04-JUN-07                 1
05-JUN-07                 0
06-JUN-07                 0
07-JUN-07                 2
...
28-JUN-07                 0
29-JUN-07                 4
30-JUN-07                 0

30 rows selected.

This resultset has the required total of 0 for each day in June that didn't have any orders.

And of course, we can do the same thing in ANSI compliant SQL with recursive subquery factoring (aka CTEs):

with jun_days (gendate) as 
  ( select  date '2007-06-01' as gendate from dual
    union all
    select gendate + 1 from jun_days
    where gendate <  date '2007-06-30' 
   ),
jun_orders as   
( select trunc(order_date) as orderdate, count(*) as ordercount
  from oe.orders
  where order_date between date '2007-06-01' and date '2007-06-30'
  group by trunc(order_date)
)  
select gendate, nvl(ordercount,0) 
from jun_days left outer join jun_orders on ( gendate = orderdate )
order by gendate;
Today's image is by Arz, licensed under CC-BY-SA-3.0, via Wikimedia Commons

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.