Analytical query help
RA
Could anyone show me how to write a analytical query for my
requirement.
Query
============
SELECT distinct bug_when,
login_name,
SUM(incoming_count - outgoing_count)
OVER (PARTITION BY login_name ORDER BY bug_when) AS OPEN
FROM RM_COMP_INCOM_OUTGO_BUGIDS_GT G,
bz_components c,
bz_profiles p
where G.component_id= c.ID and
c.manager = p.userid
order by 2,1 desc;
Query returns data like this
===================
12/04/2009 ssmit 52
12/02/2009 ssmit 48
11/30/2009 ssmit 45
11/29/2009 ssmit 42
I want the data to be like this
======================
12/04/2009 ssmit 52
12/03/2009 ssmit 48 -- fill the gap with previous value
12/02/2009 ssmit 48
12/01/2009 ssmit 45
11/30/2009 ssmit 45
11/30/2009 ssmit 45
11/29/2009 ssmit 42
Table Desc
==========
CREATE GLOBAL TEMPORARY TABLE RM_COMP_INCOM_OUTGO_BUGIDS_GT
(
BUG_WHEN DATE,
COMPONENT_ID NUMBER,
INCOMING_COUNT NUMBER,
OUTGOING_COUNT NUMBER
);
Regards
RA
joel garry
One way is to outer join with the dates:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1014934
http://www.oracle.com/technology/obe/obe10gdb/bidw/outerjoin/outerjoin.htm
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2009/dec/07/huge-us-visa-fraud-scheme-broken-up-in-brazil/
RA
Thank you for the links.
I'm not using owb, can you pls tell me how I can populate times table
with dates.
Regards
RA
joel garry
No, but here's someone who can: http://blog.lishman.com/2008/02/how-to-generate-rows-in-oracle_2477.html
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2009/dec/05/couples-tax-transfer-runs-afoul-assessor/
Charles Hooper
You might want to take a look at this link, which shows how to supply
the missing dates:
http://hoopercharles.wordpress.com/2009/12/07/sql-filling-in-gaps-in-the-source-data/
The LAG or LEAD analytic functions may be used to "peek" at the
previous/next row if the SUM() column produces a NULL value:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions070.htm
You might also want to look through this thread if the LAG analytic
function does not work - I think that I had a couple interesting posts
in that thread that involved peeking at previous rows:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/de1036e471dba004
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Charles Hooper
>
> The LAG or LEAD analytic functions may be used to "peek" at the
>
> You might also want to look through this thread if the LAG analytic
> function does not work - I think that I had a couple interesting posts
Just a follow up with a test case in the event my previous post was
not clear.
CREATE TABLE T1
(
BUG_WHEN DATE,
COMPONENT_ID VARCHAR2(10),
INCOMING_COUNT NUMBER,
OUTGOING_COUNT NUMBER
);
INSERT INTO T1 VALUES (TO_DATE('12/07/2009','MM/DD/YYYY'),'ssmit',
60,60);
INSERT INTO T1 VALUES (TO_DATE('12/04/2009','MM/DD/YYYY'),'ssmit',
52,52);
INSERT INTO T1 VALUES (TO_DATE('12/02/2009','MM/DD/YYYY'),'ssmit',
48,48);
INSERT INTO T1 VALUES (TO_DATE('11/30/2009','MM/DD/YYYY'),'ssmit',
45,45);
INSERT INTO T1 VALUES (TO_DATE('11/29/2009','MM/DD/YYYY'),'ssmit',
42,42);
INSERT INTO T1 VALUES (TO_DATE('01/29/2009','MM/DD/YYYY'),'test',
42,42);
COMMIT;
SELECT
*
FROM
T1;
BUG_WHEN COMPONENT_ INCOMING_COUNT OUTGOING_COUNT
--------- ---------- -------------- --------------
07-DEC-09 ssmit 60 60
04-DEC-09 ssmit 52 52
02-DEC-09 ssmit 48 48
30-NOV-09 ssmit 45 45
29-NOV-09 ssmit 42 42
29-JAN-09 test 42 42
Let's start with using the LEAD function to peek at the next row from
the T1 table.
SELECT
COMPONENT_ID,
BUG_WHEN,
LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY BUG_WHEN)
NEXT_BUG_WHEN,
INCOMING_COUNT
FROM
T1;
COMPONENT_ BUG_WHEN NEXT_BUG_ INCOMING_COUNT
---------- --------- --------- --------------
ssmit 29-NOV-09 30-NOV-09 42
ssmit 30-NOV-09 02-DEC-09 45
ssmit 02-DEC-09 04-DEC-09 48
ssmit 04-DEC-09 07-DEC-09 52
ssmit 07-DEC-09 60
test 29-JAN-09 42
Above needs to be modified to permit ranges by subtracting 1 from the
next date. Also we need to fix the NULL values on the last row of
each COMPONENT_ID grouping.
SELECT
COMPONENT_ID,
BUG_WHEN,
NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY
BUG_WHEN)-1,BUG_WHEN) THROUGH_BUG_WHEN,
NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY
BUG_WHEN)-1,BUG_WHEN)-BUG_WHEN RANGE_DAYS,
INCOMING_COUNT
FROM
T1;
COMPONENT_ BUG_WHEN THROUGH_B RANGE_DAYS INCOMING_COUNT
---------- --------- --------- ---------- --------------
ssmit 29-NOV-09 29-NOV-09 0 42
ssmit 30-NOV-09 01-DEC-09 1 45
ssmit 02-DEC-09 03-DEC-09 1 48
ssmit 04-DEC-09 06-DEC-09 2 52
ssmit 07-DEC-09 07-DEC-09 0 60
test 29-JAN-09 29-JAN-09 0 42
Now, all we need to do is find a way to generate the missing dates
between the BUG_WHEN date and the THROUGH_BUG_WHEN date. If we slide
the above into an inline view and join to an inline view with a
counter generator, we end up with the following:
SELECT
T1.COMPONENT_ID,
T1.BUG_WHEN+V1.C BUG_WHEN,
T1.INCOMING_COUNT
FROM
(SELECT
COMPONENT_ID,
BUG_WHEN,
NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY
BUG_WHEN)-1,BUG_WHEN) THROUGH_BUG_WHEN,
NVL(LEAD(BUG_WHEN,1) OVER (PARTITION BY COMPONENT_ID ORDER BY
BUG_WHEN)-1,BUG_WHEN)-BUG_WHEN RANGE_DAYS,
INCOMING_COUNT
FROM
T1) T1,
(SELECT
ROWNUM-1 C
FROM
DUAL
CONNECT BY
LEVEL<=100) V1
WHERE
V1.C<=T1.RANGE_DAYS
ORDER BY
T1.COMPONENT_ID,
T1.BUG_WHEN+V1.C DESC;
COMPONENT_ BUG_WHEN INCOMING_COUNT
---------- --------- --------------
ssmit 07-DEC-09 60
ssmit 06-DEC-09 52
ssmit 05-DEC-09 52
ssmit 04-DEC-09 52
ssmit 03-DEC-09 48
ssmit 02-DEC-09 48
ssmit 01-DEC-09 45
ssmit 30-NOV-09 45
ssmit 29-NOV-09 42
test 29-JAN-09 42
RA
You nailed it..... I made a simple change to my code and also used
your code, it did what I wanted.
Appreciate your help.
RA