Skip to Main Content

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
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Oct 10 2007
Added on Sep 7 2007
25 comments
4,288 views