Re: Tough Analytical SQL Question

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 10 Aug 2007 17:13:38 -0700
Message-ID: <1186791217.150333@bubbleator.drizzle.com>

michaelw436_at_gmail.com wrote:

> On Aug 10, 12:21 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> michaelw..._at_gmail.com wrote:
>>> On Aug 10, 11:56 am, DA Morgan <damor..._at_psoug.org> wrote:
>>>> michaelw436.s..._at_gmail.com wrote:

>>>>> I am wondering if anyone can find a simple solution to this SQL query.
>>>>> The end of goal of the query is to give me a count of distinct cust_id
>>>>> for each ctype and order_date, for a set of ranges. For example, I
>>>>> want an example record in the end with
>>>>> CTYPE = 'ECHO'
>>>>> order_date = '09-aug-2007'
>>>>> dist_1_day = 2
>>>>> dist_7_day = 2
>>>>> SELECT ctype_id ctype, order_date order_dt,
>>>>> dist_1_day distinct_cust_1_day,
>>>>> dist_7_day distinct_cust_7_day
>>>>> FROM (SELECT DISTINCT order_date, ctype_id,
>>>>> COUNT (cust_id) OVER
>>>>> (PARTITION BY order_date, ctype_id)
>>>>> dist_1_day,
>>>>> COUNT (cust_id) OVER
>>>>> (PARTITION BY ctype_id ORDER BY order_date RANGE 7 PRECEDING)
>>>>> dist_7_day
>>>>> FROM (SELECT DISTINCT TRUNC
>>>>> (order_date
>>>>> )
>>>>> order_date,
>>>>> o.ctype_id,
>>>>> o.cust_id
>>>>> FROM orders.orders
>>>>> o,
>>>>> webadmin.cust_details cd
>>>>> WHERE o.order_date

>>>>>> = to_date('01-aug-2007','DD-MON-YYYY') - 7


>>>>>                                                      and ctype_id =

>>>>> 'ECHO'

>>>>>                                                      AND order_type IN

>>>>> ('O')

>>>>>                                                      AND ostatus <>

>>>>> 'X'

>>>>>                                                      AND cd.cust_id =

>>>>> o.cust_id

>>>>>                                                      AND cd.is_guest =

>>>>> 0))

>>>>>                  WHERE order_date >= to_date('01-aug-2007','DD-MON-

>>>>> YYYY')
>>>>> The results of the inner query starting with "SELECT DISTINCT
>>>>> order_date" are below for your reference:
>>>>> ORDER_DATE CTYPE_ID CUST_ID
>>>>> 8/3/2007 ECHO 1720522
>>>>> 8/6/2007 ECHO 1720522
>>>>> 8/7/2007 ECHO 1720522
>>>>> 8/8/2007 ECHO 1720522
>>>>> 8/9/2007 ECHO 2475717
>>>>> 8/9/2007 ECHO 1720522
>>>>> As you can see, there are only two distinct cust_IDs in the whole
>>>>> recordset, but when I run the whole query, it counts my dist_7_day = 6
>>>>> because it is counting the number of rows, not the DISTINCT CUST_ID's.
>>>>> I tried to add the DISTINCT keyword to the COUNT (cust_id) OVER...,
>>>>> but that gives me a syntax error. I know there is something I am
>>>>> looking right over here that will solve this issue simply.
>>>>> I am on 10gR2 enterprise. Thanks for looking!

>>>> Add the integer 1 to your inner query and then sum the integers.
>>>> --
>>>> Daniel A. Morgan
>>>> University of Washington
>>>> damor..._at_x.washington.edu (replace x with u to respond)
>>>> Puget Sound Oracle Users Groupwww.psoug.org
>>> I like the idea, but I still get the same results because when I sum
>>> the integer column that I create, I am still adding 1 for each day in
>>> the the 7 day range. I only want to count the distinct  cust_id's
>>> inside of that 7 day range. Does that make sense?
>> Then add it after doing the grouping.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
> 
> Thanks for your help Daniel by the way, I still don't follow exactly
> where to do this summing of the integer that I create. I tried to add
> an integer to the inner query, but that still ends up with my original
> results. I think that you are going the right direction here, but I
> have spent so many hours here that I needed a fresh perspective. Here
> is the SQL that I currently have...
> 
> SELECT ctype_id ctype, order_date order_dt, dist_1_day
> distinct_cust_1_day,
>        dist_7_day distinct_cust_7_day
>   FROM (SELECT DISTINCT order_date, ctype_id,
>                         COUNT (cust_id) OVER (PARTITION BY order_date,
> ctype_id)
>  
> dist_1_day,
>                         COUNT (cust_id) OVER (PARTITION BY ctype_id
> ORDER BY order_date RANGE 7 PRECEDING)
>  
> dist_7_day
>                    FROM (SELECT DISTINCT TRUNC (order_date)
> order_date,
>                                          o.ctype_id, o.cust_id
>                                     FROM orders.orders o,
>                                          webadmin.cust_details cd
>                                    WHERE o.order_date >=
>                                               TO_DATE ('01-aug-2007',
>                                                        'DD-MON-YYYY'
>                                                       )
>                                             - 7
>                                      AND ctype_id = 'ECHO'
>                                      AND order_type IN ('O')
>                                      AND ostatus <> 'X'
>                                      AND cd.cust_id = o.cust_id
>                                      AND cd.is_guest = 0))
>  WHERE order_date >= TO_DATE ('01-aug-2007', 'DD-MON-YYYY')

I'm really pressed for time today so I can't write it for you but this may give you an idea:

http://www.psoug.org/reference/explain_plan.html

Look at Test Statement #9.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Aug 10 2007 - 19:13:38 CDT