| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Tough Analytical SQL Question
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
>> > the the 7 day range. I only want to count the distinct cust_id's
> > 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
>
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')
Received on Fri Aug 10 2007 - 14:05:15 CDT
 |
 |