Analytic functions - help needed
VladaSep 7 2007 — edited Sep 12 2007Hi, 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