EXPLAIN EXTENDED

How to create fast database queries

Oracle: generating a list of dates and counting ranges for each date

with 9 comments

From Stack Overflow:

I have a table with data such as below:

Group Start Date End Date
A 2001.01.01 2001.01.03
A 2001.01.01 2001.01.02
A 2001.01.03 2001.01.03
B 2001.01.01 2001.01.01

I am looking to produce a view that gives a count for each day

We have a list of ranges here, and for each date we should count the number of ranges that contain this date.

To make this query, we will employ one simple fact: the number of ranges containing a given date is the number of ranges started on or before this date minus the number of ranges that ended before this date.

This can easily be calculated using window functions.

Let's create a sample table:


Table creation details

This table contains 1,000,000 rows in 10 groups.

Ranges start every 10 minutes (144 per day) and last from 1 to 11 days. Range lengths are random.

To generate the count of ranges in each group that contain each date, we need to do the following:

  1. Generate a list of dates, from minimal to maximal. This can be easily done using a CONNECT BY query on dual table:
    SELECT  cur_date
    FROM    (
    SELECT  (
    SELECT  MIN(start_date)
    FROM    t_range
    ) + level - 1 AS cur_date
    FROM    dual
    CONNECT BY
    level <=
                    (
                    SELECT  MAX(end_date)
                    FROM    t_range
                    ) -
                    (
                    SELECT  MIN(start_date)
                    FROM    t_range
                    ) + 1
            ) dates
    
    
    CUR_DATE
    02.07.2009 00:00:00
    03.07.2009 00:00:00
    04.07.2009 00:00:00
    18.09.2009 00:00:00
    19.09.2009 00:00:00
    80 rows fetched in 0.0017s (0.0011s)

    Since MIN and MAX are instant on indexed fields, this query is instant too.

  2. Build a rowset that would contain a number of ranges starting on each given date. We can use a simple GROUP BY to do this, since missing dates will be handled by a LEFT JOIN with the list of generated dates that we built on the previous step:
    SELECT  grouper AS sgrp, start_date, COUNT(*) AS scnt
    FROM    t_range
    GROUP BY
    grouper, start_date
    ORDER BY
    grouper, start_date
    
    SGRP START_DATE SCNT
    1 02.07.2009 00:00:00 640
    1 03.07.2009 00:00:00 1440
    1 04.07.2009 00:00:00 1440
    10 08.09.2009 00:00:00 1440
    10 09.09.2009 00:00:00 1440
    700 rows fetched in 0.0247s (0.3159s)

    We see that within group 1, 640 ranges started on 02.07.2009, 1,400 ranges started on 03.07.2009 etc.

  3. Do the same with end_dates:
    SELECT  grouper AS egrp, end_date, COUNT(*) AS ecnt
    FROM    t_range
    GROUP BY
    grouper, end_date
    ORDER BY
    grouper, end_date
    
    EGRP END_DATE ECNT
    1 03.07.2009 00:00:00 56
    1 04.07.2009 00:00:00 197
    1 05.07.2009 00:00:00 359
    10 18.09.2009 00:00:00 287
    10 19.09.2009 00:00:00 139
    790 rows fetched in 0.0278s (0.6250s)

    Within group 1, 56 ranges ended on 03.07.2009, 197 ones ended on 04.07.2009 etc.

  4. Finally, we should calculate the number of ranges within each group containing each date:
    1. Before and on Jul 2nd, 640 ranges started. 0 ranges ended before that date. This means that this day is contained by 640 ranges.
    2. Before and on Jul 3rd, 2,080 ranges started. This includes 1,400 ranges that started on that date and 640 ranges that started before. No ranges ended before this date too. The date is contained by 2,080 ranges.
    3. Before and on Jul 4th, 3,520 ranges started. This includes 1,400 ranges that started on that date and 2,080 ranges that started before that date. 56 ranges ended before that date. This date is therefore contained by 3,462 ranges.

    Et cetera. Now we see that to calculate the number of ranges contained by any given date we should OUTER JOIN the resultsets containing the counts, and calculate partial sums of these counts. Difference between these sums will be the result we're after.

And here's the final query:

SELECT  cur_date,
grouper,
SUM(COALESCE(scnt, 0) - COALESCE(ecnt, 0)) OVER (PARTITION BY grouper ORDER BY cur_date) AS ranges
FROM    (
SELECT  (
SELECT  MIN(start_date)
FROM    t_range
) + level - 1 AS cur_date
FROM    dual
CONNECT BY
level <=
                (
                SELECT  MAX(end_date)
                FROM    t_range
                ) -
                (
                SELECT  MIN(start_date)
                FROM    t_range
                ) + 1
        ) dates
CROSS JOIN
        (
        SELECT  DISTINCT grouper AS grouper
        FROM    t_range
        ) groups
LEFT JOIN
        (
        SELECT  grouper AS sgrp, start_date, COUNT(*) AS scnt
        FROM    t_range
        GROUP BY
                grouper, start_date
        ) starts
ON      sgrp = grouper
        AND start_date = cur_date
LEFT JOIN
        (
        SELECT  grouper AS egrp, end_date, COUNT(*) AS ecnt
        FROM    t_range
        GROUP BY
                grouper, end_date
        ) ends
ON      egrp = grouper
        AND end_date = cur_date - 1
ORDER BY
        grouper, cur_date

CUR_DATE GROUPER RANGES
02.07.2009 00:00:00 1 640
03.07.2009 00:00:00 1 2080
04.07.2009 00:00:00 1 3464
18.09.2009 00:00:00 10 426
19.09.2009 00:00:00 10 139
800 rows fetched in 0.0296s (1.0937s)

The results are just like we expected.

Note the JOIN condition end_date = cur_date - 1. We subtract a day to make sure that we select events that ended strictly before the date. The events that ended on the date in question still contain it and should contribute into the count.

This query is very fast (1,000,000 rows are processed in just a trifle more than a second).

Written by Quassnoi

September 9th, 2009 at 11:00 pm

Posted in Oracle

9 Responses to 'Oracle: generating a list of dates and counting ranges for each date'

Subscribe to comments with RSS

  1. Thanks for this comprehensive article. However, when I test your SQL with

    INWORK 01/FEB/13 02/FEB/13
    INWORK 01/FEB/13 02/FEB/13
    RELEASE 04/FEB/13 06/FEB/13
    REVIEW 02/FEB/13 04/FEB/13

    I get

    01/FEB/13 INWORK 1
    02/FEB/13 INWORK 1
    03/FEB/13 INWORK -1
    04/FEB/13 INWORK -1
    05/FEB/13 INWORK -1
    06/FEB/13 INWORK -1
    01/FEB/13 RELEASE 0
    02/FEB/13 RELEASE 0
    03/FEB/13 RELEASE 0
    04/FEB/13 RELEASE 1
    05/FEB/13 RELEASE 1
    06/FEB/13 RELEASE 1
    01/FEB/13 REVIEW 0
    02/FEB/13 REVIEW 1
    03/FEB/13 REVIEW 1
    04/FEB/13 REVIEW 1
    05/FEB/13 REVIEW 0
    06/FEB/13 REVIEW 0

    Which does not seem correct ie : how can there be negative counts.

    Any suggestions on what I am doing wrong?

    Richard

    20 Feb 13 at 06:20

  2. @Richard: please post your sample data and the query on http://sqlfiddle.com and post here the link.

    Quassnoi

    20 Feb 13 at 12:06

  3. Quassnoi – Thanks for the assistance. I have posted the code at http://sqlfiddle.com/#!4/e6bf9/1/0

    As background, the data represents the approval work flow of two objects. One object that goes from INWORK to REVIEW to RELEASE during the dates between 01FEB13 to 08FEB13; the second object is INWORK from 01FEB13 to 08FEB13.

    The output I require is, for end (23:59:59) of each day, the number of objects in the INWORK state, the number in REVIEW state and the number in RELEASE state.

    The problem I have is that the output for 02FEB13, on summing the counts has a total of 3. It should be 2. Similarly for 04FEB13, the total is 3, not 2.

    If I add the HH:MI:SS values to further descretize the intervals, the output is more incorrect. see http://sqlfiddle.com/#!4/34aaa/1/0

    Any suggestions on how to ensure the counts equal 2 for each day I count?

    Richard

    20 Feb 13 at 13:26

  4. @Richard: I don’t see the number 3 in the output at all here: http://sqlfiddle.com/#!4/e6bf9/1/0

    You have 2 inwork tasks between Feb 1 and Feb 2; and 1 inwork task for all other dates. The other counts also seem legit to me.

    Quassnoi

    20 Feb 13 at 13:34

  5. Thanks for your response – the total for 02FEB13 is 2 for INWORK, and 1 for REVIEW, total of 3, not 2. This is the problem :-)

    Richard

    20 Feb 13 at 13:56

  6. @Richard: if you need the totals not regarding the task state, just omit the whole “grouper” stuff. See here: http://sqlfiddle.com/#!4/d1ef5/5

    Please note that you have overlapping dates for some of your states so those states will be counted twice. You model does not allow distinguishing the tasks since you don’t keep the task id in your table.

    Quassnoi

    20 Feb 13 at 14:00

  7. As you can see from my second SQL example, if I include hh:mm:ss then there is not overlap of the work flow increments.

    Also, the next part of this problem is that I use the output as SQL for a stacked bar chart in iReport, thus the grouper field is used as the series in the stacked bar chart. So the total of the bar charts should be 2. Note also that I am reporting at 23:59:59 of each day. Thus for the 02 FEB13 I should not count the INWORK task that is completed.

    Richard

    20 Feb 13 at 14:12

  8. @Richard: as I said, there is no way to distinguish the tasks. Add task id to your model.

    Quassnoi

    20 Feb 13 at 14:49

  9. Quassnoi – as per the sample @ http://sqlfiddle.com/#!4/d1ef5/5 the inclusion of a unique identifier for each work flow does not solve the problem. I still see 3 counts for 02FB13 and 04FEB13. There should only be 2.

    Richard

    21 Feb 13 at 01:20

Leave a Reply