Re: [Q] Horizontal partitioning of data in a query

From: Chrysalis <cellis_at_iol.ie>
Date: 1998/02/24
Message-ID: <34F32B20.18E5@iol.ie>#1/1

Peter van Jaarsveldt wrote:
>
> Hi
>
> I'm sure that someone out there is going to give me a really simple, obvious
> answer to this one!!!
>
> I have a table A which is
>
> task_number number
> person varchar2
> date_started date
> status VARCHAR2
>
> What I want is an aged report on the number of outstanding tasks by person.
> Task_number to Person is many to many.
> For example:
>
> Name <_1_month_old >1<6_months_old >6<12_months_old Total
> John 20 3
> 1 24
> Mary 16 1
> 4 21
>
> etc.....
>
> However, the only way I can see of doing this is is by scanning table A 3
> times per person, which is not really viable (our table is > 9,000,000
> rows).
>
> Anyone got a good solution?
>
> Cheers
>
> Pete

Peter,
There are several ways to approach this problem. The first makes use of standard (portable) SQL and involves a separate table containing the required ranges.
The second, which looks ugly but is even more efficient, makes use of Oracle's DECODE function.
Other ways involve in-line views (in the FROM list), but I have usually used one of the following:

Method I.

---

create table AGE_RANGES
(seq number primary key

,age_min number	not null -- in months
,age_max number	not null -- in months

);

select A.person, count(R1.seq), count(R2.seq), ... from A, age_ranges R1, age_ranges R2, ...

where  A.date_started between (add_months(trunc(sysdate),-R1.age_max))
                          and (add_months(trunc(sysdate),-R1.age_min))
 and   A.date_started between (add_months(trunc(sysdate),-R2.age_max))
                          and (add_months(trunc(sysdate),-R2.age_min))

 and ...  

Note that this requires a single pass of the base table.  

Method I (Variant)

---

At the small cost of updating the reference table for each run of the report, the AGE_RANGES table could be replaced by a DATE_RANGES table, with consequent simplification of the run-time SQL.

Method II.

---

This makes use of the following two techniques:

1. In general, to obtain multiple conditional counts from a single pass of a table, one can use one or more expressions of the form:

sum(decode(<some_condition>,TRUE,1,null))

b) To generate a condition which evaluates to TRUE within a range and FALSE outside it, we can make use of the fact that the expression

greatest(<minval>,least(<maxval>,column))

takes the value of the column if it falls between <minval> and <maxval>.

Combining, we get:

select person      

,sum(decode(greatest(<min_date_1>,least(<max_date_1>,date_started))

                  ,date_started, 1, null))  COUNT_1            
     
,sum(decode(greatest(<min_date_2>,least(<max_date_2>,date_started))
                  ,date_started, 1, null))  COUNT_2            
      ,sum(...

from A

This version needs no other database access than a single pass of the base table.

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards, Guards"

Received on Tue Feb 24 1998 - 00:00:00 CST