Skip to Main Content

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!

Analytic functions - help needed

VladaSep 7 2007 — edited Sep 12 2007
Hi, everybody. I have large table 20M+ records. The table is like
DT DATE,
NO NUMBER,
ATR1 char(1),
ATR2 char(1)

unique is DT,NO

I need write select statement which compute count of UNIQUE NO in date interval by WINDOW

Interval is defined by start date, end date, lenght and step. For example
Start 1.1.2006 00:00
End: 1.1.2006 23:59
Length: 1 hour
Step: 1 minute

In result:
DT_FROM DT_TILL AB
1. 1. 2006 0:00:00 1. 1. 2006 0:59:00 6
1. 1. 2006 0:01:00 1. 1. 2006 1:00:00 6
1. 1. 2006 0:02:00 1. 1. 2006 1:01:00 6
1. 1. 2006 0:03:00 1. 1. 2006 1:02:00 6
1. 1. 2006 0:04:00 1. 1. 2006 1:03:00 7
1. 1. 2006 0:05:00 1. 1. 2006 1:04:00 7
1. 1. 2006 0:06:00 1. 1. 2006 1:05:00 8
.
.
1. 1. 2006 23:57:00 2. 1. 2006 0:56:00 14
1. 1. 2006 23:58:00 2. 1. 2006 0:57:00 13
1. 1. 2006 23:59:00 2. 1. 2006 0:58:00 13

There is two problems:
1) Analytic function count not supported distinct count over WINDOW
2) Because I need every interval (interval for wich no data in my table) , i must outer join this table with dynamic generated intervals (using dual connect by level). This is very slow process (for inteval of 1 year)
select
count(*) over (order by dt RANGE BETWEEN CURRENT ROW AND INTERVAL '60' MINUTE FOLLOWING) TAB
FROM (SELECT TRUNC (:dfrom) + (LEVEL - 1) / 1440 dfrom,
TRUNC (:dfrom) + (LEVEL - 1) / 1440 + 59/24/60 dtill,
level lvl
FROM DUAL
CONNECT BY LEVEL <= Trunc((:DTILL - :DFROM) * 1440 / :STP + 1)) TA
LEFT OUTER JOIN
d_lps_secx_c TB -- this table has 20M+ records
ON dt = dfrom
order by ta.dfrom

Comments

Anurag Tibrewal
Hi,

With whatever requirement you posted so far, it is possible.
You need to write a dml trigger (insert/update/delete) on the table1. Inside the trigger you would be writing a code to do the changes on table2.
Points to remember
- schemaA(where table1 resides) needs the direct insert/update/delete privilege on table2 of schemaB.
- Prefix schema name with table2 inside the trigger.

Regards
Anurag
Knightbeat
Thank you so much, for the reply.

I can't even imagine how did you reply so quickly! I think my question was 100% answered, and now I can surf to know how it can be done, as I now know that It's possible to be done!

Thank you very much! I really appreciate your generosity!
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 10 2007
Added on Sep 7 2007
25 comments
4,655 views