Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Special Report SQL Query

781721Apr 25 2011 — edited Apr 26 2011
Hi there

I have a log table looking like: 
ACTION     DATE_TIME     
1          31.03.2011   (Don't count because last month)
1          01.04.2011   (Count as new)
2          01.04.2011   (Count as new)
1          04.04.2011   (Count as new because there are at least 2 days since last new ACTION=1) 
1          05.04.2011   (Don't count because since last new ACTION=1 there are not 2 days in between)
2          07.04.2011   (Count as new because there are at least 2 days since last new ACTION=2)
2          08.04.2011   (Don't count because since last new ACTION=2 there are not 2 days in between)
2          10.04.2011   (Count as new because there are at least 2 days since last new ACTION=2)
I need to count the number of ACTIONs within one calendar month (April 2011 in the example above).
The difficulty is that I only have to count them as new action when there are at least 2 days between the first counted action and the next one.

So in the case of the example above I'd need to get as result: 
NEW_ACTION  COUNT
1           2
2           3
Thanks,
Peter

Comments

Frank Kulash
Answer
Hi,

Welcome to the forum!

Thanks for posting the CREATE TABLE and INSERT statements! That's very helpful.
It's also helpful if you format your code and output. When posting formatted text on this site, type these 6 characters:

\
(all small letters, inside curly brackets) to keep the site from compressing the spaces.

You can do two CONNECT BY queries: one to get the desired amount, and the other like you're already doing, to get the indented child column.  The first will differ in that it has no START WITH clause, which means every node in your table will be the root of a tree, and that there's an additional CONNECT BY condition to quit looking for descendants as soon as an amount is found.  This corresponds to your apparant requirement that the desired_amount is the amount, if it is not NULL, and only otherwise is the sum of the desired_amounts of one's descendants.
WITH desired_amount_tree AS
(
SELECT CONNECT_BY_ROOT child AS root
, child
, child_amount
FROM ent_rel
CONNECT BY PRIOR child = parent
AND PRIOR child_amount IS NULL
)
, got_desired_amount AS
(
SELECT root
, SUM (child_amount) AS desired_amount
FROM desired_amount_tree
GROUP BY root
)
select RPAD ( ' '
, (LEVEL - 1) * 2
, '-'
) || e.child AS child
, level
, e.child_amount
, desired_amount
from ent_rel e
JOIN got_desired_amount d ON e.child = d.root
start with parent is null
connect by prior child = parent
; 
It's usually more efficient to do join after doing the CONNECT BY.  For better performance, try doing your original query by itself, and then joining its result set to got_desired_amount.
Of course, it would be a lot more efficient if we could eliminate the extra CONNECT BY query.  That might be possible using MODEL or (starting in Oracle 11.2) a recursive sub-query.
Marked as Answer by 529206 · Sep 27 2020
529206
Hi Frank,

Thanks a lot for the quick solution. It is working great and thanks for the tips too for better posting on this forum. Appreciate your help.

thanks and regards,
Knl.
Aketi Jyuuzou
I like scalar subQuery :D
create table ent_rel (parent integer,
child integer, child_amount integer);

insert into ent_rel
select null, 100, null from dual
union all select 100, 101, null from dual
union all select 101, 102, 20 from dual
union all select 102, 103, null from dual
union all select 103, 104, null from dual
union all select 104, 105, null from dual
union all select 105, 106, 40 from dual
union all select 106, 107, null from dual
union all select 107, 108, 20 from dual
union all select 107, 109, 10 from dual
union all select 101, 203, null from dual
union all select 203, 205, 50 from dual
union all select 205, 207, null from dual
union all select 207, 209, null from dual
union all select 209, 210, 10 from dual;

col parent for a40

select Lpad('-',(Level-1)*2,'-') || child as parent,
Level,child_amount,
(select sum(b.child_amount)
   from ent_rel b
start with b.RowID = a.RowID
connect by prior child = parent
       and prior child_amount is null) as sumV
  from ent_rel a
start with parent is null
connect by prior child = parent;

PARENT               LEVEL  CHILD_AMOUNT  SUMV
-------------------  -----  ------------  ----
100                      1          null    70
--101                    2          null    70
----102                  3            20    20
------103                4          null    40
--------104              5          null    40
----------105            6          null    40
------------106          7            40    40
--------------107        8          null    30
----------------108      9            20    20
----------------109      9            10    10
----203                  3          null    50
------205                4            50    50
--------207              5          null    10
----------209            6          null    10
------------210          7            10    10
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 24 2011
Added on Apr 25 2011
#oracle, #pl-sql, #sql
3 comments
357 views