- 3,733,243 Users
- 2,246,736 Discussions
- 7,856,631 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
- 380.9K All Categories
- 2.1K Data
- 203 Big Data Appliance
- 1.9K Data Science
- 446.1K Databases
- 220.4K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 506 MySQL Community Space
- 459 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 437 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.4K SQL & PL/SQL
- 20.7K SQL Developer
- 291.2K Development
- 6 Developer Projects
- 116 Programming Languages
- 288K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.2K Java
- 16 Java Learning Subscription
- 36.9K Database Connectivity
- 148 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.7K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 12 Java Essentials
- 138 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 195 Java User Groups
- 22 JavaScript - Nashorn
- Programs
- 177 LiveLabs
- 33 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 165 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
Analytic functions - help needed
588708
Member Posts: 117
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
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
-
1) Analytic function count not supported distinctThat'd be new. COUNT(DISTINCT column) OVER ... works
count over WINDOW
perfectly unter 10g2
C. -
If we use distinct option in count function of OLAP,
we cannot use order by clause.
for instance in Oracle 10.2.0.1.0SQL> select SortKey,Val,
2 count(distinct Val) over() as disticntCount
3 from (select 1 as SortKey,'AAA' as Val from dual
4 union select 2,'AAA' from dual
5 union select 3,'BBB' from dual
6 union select 4,'CCC' from dual
7 union select 5,'CCC' from dual
8 union select 6,'AAA' from dual
9 union select 7,'AAA' from dual);SortKey Val disticntCount
------- --- -------------
1 AAA 3
2 AAA 3
7 AAA 3
6 AAA 3
3 BBB 3
4 CCC 3
5 CCC 3SQL> select SortKey,Val,
2 count(Val) over(order by SortKey) as disticntCount
3 from (select 1 as SortKey,'AAA' as Val from dual
4 union select 2,'AAA' from dual
5 union select 3,'BBB' from dual
6 union select 4,'CCC' from dual
7 union select 5,'CCC' from dual
8 union select 6,'AAA' from dual
9 union select 7,'AAA' from dual);SortKey Val disticntCount
------- --- -------------
1 AAA 1
2 AAA 2
3 BBB 3
4 CCC 4
5 CCC 5
6 AAA 6
7 AAA 7SQL> select SortKey,Val,
However,
2 count(distinct Val) over(order by SortKey) as disticntCount
3 from (select 1 as SortKey,'AAA' as Val from dual
4 union select 2,'AAA' from dual
5 union select 3,'BBB' from dual
6 union select 4,'CCC' from dual
7 union select 5,'CCC' from dual
8 union select 6,'AAA' from dual
9 union select 7,'AAA' from dual);
count(distinct Val) over(order by SortKey) as disticntCount
*
ORA-30487: ORDER BY not allowed here
we can emulate count function of OLAP with distinct option and order by clause.SQL> select SortKey,Val,sum(WillSum) over(order by SortKey) as disticntCount
2 from (select SortKey,Val,
3 case Row_Number() over(partition by Val order by SortKey)
4 when 1 then 1 else 0 end as WillSum
5 from (select 1 as SortKey,'AAA' as Val from dual
6 union select 2,'AAA' from dual
7 union select 3,'BBB' from dual
8 union select 4,'CCC' from dual
9 union select 5,'CCC' from dual
10 union select 6,'AAA' from dual
11 union select 7,'AAA' from dual));SortKey Val disticntCount
------- --- -------------
1 AAA 1
2 AAA 1
3 BBB 2
4 CCC 3
5 CCC 3
6 AAA 3
7 AAA 3 -
Thx. This is good idea but I have version 9.2 :-(
-
I need compute count over Window ...
Emulation is good idea but performance of emulation is unusable - i work with 2M+ records ... -
Interval is defined by start date, end date, lenghtBe more clear. An interval is defined by any two of the following: start date, end date or length. Please explain what you mean with step.
and step. For example
Start 1.1.2006 00:00
End: 1.1.2006 23:59
Length: 1 hour
Step: 1 minute1) Analytic function count not supported distinctcount() distinct would return 1 always.
count over WINDOWProcessing ...
with t as (
select 1 as mycol
from dual
connect by rownum <= 5
)
select count( distinct mycol)
from t
Query finished, retrieving results...
COUNT(DISTINCTMYCOL)
--------------------------------------
1
1 row(s) retrieved2) Because I need every interval (interval for wichDo it with in this way
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 ta.dfrom,ta.dtill,count(tb.any_key_column)
Bye Alessandro
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
group by ta.dfrom,ta.dtill
order by ta.dfrom
Message was edited by:
Alessandro Rossi
that join condition was missing too many records.select ta.dfrom,ta.dtill,count(tb.any_key_column)
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 between dfrom and dtill )
group by ta.dfrom,ta.dtill
order by ta.dfrom -
1) Analytic function count not supported distinct count over WINDOW
To work around this problem in 9iR2, you can emulate the count distinct in a window by making up rows for each minute that the record should be counted for. Like this:SQL> create table d_lps_secx_c
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)
2 ( dt date
3 , no number
4 , atr1 char(1)
5 , atr2 char(1)
6 , primary key (dt,no)
7 )
8 /
Tabel is aangemaakt.
SQL> insert into d_lps_secx_c
2 select trunc(date '2005-12-31' + dbms_random.value(0,3),'mi')
3 , trunc((level-1)/2)
4 , 'A'
5 , 'B'
6 from dual
7 connect by level <= 100
8 /
100 rijen zijn aangemaakt.
SQL> var P_STARTDATE varchar2(16)
SQL> var P_ENDDATE varchar2(16)
SQL> var P_LENGTH number
SQL> var P_STEP number
SQL> exec :P_STARTDATE := '01-01-2006 00:00'
PL/SQL-procedure is geslaagd.
SQL> exec :P_ENDDATE := '01-01-2006 23:59'
PL/SQL-procedure is geslaagd.
SQL> exec :P_LENGTH := 1/24
PL/SQL-procedure is geslaagd.
SQL> exec :P_STEP := 1/24/60
PL/SQL-procedure is geslaagd.
SQL> select dt, no
2 from d_lps_secx_c
3 where dt between to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi') and to_date(:P_ENDDATE,'dd-mm-yyyy hh24:mi') + interval '59' minute
4 order by dt
5 /
DT NO
------------------- --------------------------------------
01-01-2006 00:21:00 22
01-01-2006 01:02:00 32
01-01-2006 01:10:00 13
01-01-2006 02:06:00 7
01-01-2006 02:22:00 40
01-01-2006 03:30:00 29
01-01-2006 03:44:00 13
01-01-2006 04:26:00 21
01-01-2006 04:45:00 10
01-01-2006 05:05:00 49
01-01-2006 06:45:00 44
01-01-2006 07:06:00 0
01-01-2006 08:05:00 41
01-01-2006 08:14:00 25
01-01-2006 09:27:00 21
01-01-2006 10:46:00 7
01-01-2006 11:01:00 27
01-01-2006 12:34:00 20
01-01-2006 12:49:00 31
01-01-2006 13:17:00 48
01-01-2006 14:30:00 26
01-01-2006 14:38:00 10
01-01-2006 15:26:00 49
01-01-2006 16:07:00 42
01-01-2006 16:33:00 37
01-01-2006 16:55:00 4
01-01-2006 17:32:00 34
01-01-2006 18:06:00 22
01-01-2006 20:26:00 14
01-01-2006 20:52:00 45
01-01-2006 21:26:00 11
01-01-2006 22:37:00 28
01-01-2006 23:45:00 32
01-01-2006 23:54:00 4
02-01-2006 00:13:00 30
35 rijen zijn geselecteerd.
SQL> select all_intervals.dt_from
2 , all_intervals.dt_till
3 , nvl(x.ab,0) ab
4 from ( select to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi') + (level-1)/24/60 dt_from
5 , to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi') + (level-1)/24/60 + interval '59' minute dt_till
6 from dual
7 connect by level <= ceil((to_date(:P_ENDDATE,'dd-mm-yyyy hh24:mi') + interval '1' minute - to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi')) / :P_STEP)
8 ) all_intervals
9 , ( select dt dt_from
10 , count(*) ab
11 from ( select dt - l/24/60 dt
12 , no
13 from d_lps_secx_c
14 , (select level-1 l from dual connect by level <= :P_LENGTH/:P_STEP)
15 where dt between to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi') and to_date(:P_ENDDATE,'dd-mm-yyyy hh24:mi') + interval '59' minute
16 )
17 where dt between to_date(:P_STARTDATE,'dd-mm-yyyy hh24:mi') and to_date(:P_ENDDATE,'dd-mm-yyyy hh24:mi')
18 group by dt
19 ) x
20 where all_intervals.dt_from = x.dt_from (+)
21 /
DT_FROM DT_TILL AB
------------------- ------------------- --------------------------------------
01-01-2006 00:00:00 01-01-2006 00:59:00 1
01-01-2006 00:01:00 01-01-2006 01:00:00 1
01-01-2006 00:02:00 01-01-2006 01:01:00 1
01-01-2006 00:03:00 01-01-2006 01:02:00 2
01-01-2006 00:04:00 01-01-2006 01:03:00 2
01-01-2006 00:05:00 01-01-2006 01:04:00 2
01-01-2006 00:06:00 01-01-2006 01:05:00 2
01-01-2006 00:07:00 01-01-2006 01:06:00 2
01-01-2006 00:08:00 01-01-2006 01:07:00 2
01-01-2006 00:09:00 01-01-2006 01:08:00 2
01-01-2006 00:10:00 01-01-2006 01:09:00 2
01-01-2006 00:11:00 01-01-2006 01:10:00 3
01-01-2006 00:12:00 01-01-2006 01:11:00 3
...
<snipped>
...
01-01-2006 23:44:00 02-01-2006 00:43:00 3
01-01-2006 23:45:00 02-01-2006 00:44:00 3
01-01-2006 23:46:00 02-01-2006 00:45:00 2
01-01-2006 23:47:00 02-01-2006 00:46:00 2
01-01-2006 23:48:00 02-01-2006 00:47:00 2
01-01-2006 23:49:00 02-01-2006 00:48:00 2
01-01-2006 23:50:00 02-01-2006 00:49:00 2
01-01-2006 23:51:00 02-01-2006 00:50:00 2
01-01-2006 23:52:00 02-01-2006 00:51:00 2
01-01-2006 23:53:00 02-01-2006 00:52:00 2
01-01-2006 23:54:00 02-01-2006 00:53:00 2
01-01-2006 23:55:00 02-01-2006 00:54:00 1
01-01-2006 23:56:00 02-01-2006 00:55:00 1
01-01-2006 23:57:00 02-01-2006 00:56:00 1
01-01-2006 23:58:00 02-01-2006 00:57:00 1
01-01-2006 23:59:00 02-01-2006 00:58:00 1
1440 rijen zijn geselecteerd.
If it is slow, please investigate why it is slow. I wrote about it [url http://forums.oracle.com/forums/thread.jspa?threadID=501834&tstart=0]here.
Regards,
Rob. -
1)
What is unclear? I mean that example is selfexplanation ... but I can try explain it detaily:
Start 1.1.2006 00:00
End: 1.1.2006 23:59
Length: 1 hour
Step: 1 minute
Fot this input Length of interval is 1 hour. There is 1440 inervals for witch will be computed result.
First ... 1. 1. 2006 0:00:00 1. 1. 2006 0:59:00
Second ...1. 1. 2006 0:01:00 1. 1. 2006 1:00:00
.
.
Last 1. 1. 2006 23:59:00 2. 1. 2006 0:58:00 13
Start date is incemented by step until this < EndDate
2) Your example is not ANALYTIC function ... it is AGRGATE function! You are in mistake ... -
That works fine with count_distinct function :
SQL> select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value
Nicolas.
2 from (select 1 as SortKey,'AAA' as Val from dual
3 union all
4 select 2,'AAA' from dual
5 union all
6 select 3,'BBB' from dual
7 union all
8 select 4,'CCC' from dual
9 union all
10 select 5,'CCC' from dual
11 union all
12 select 6,'AAA' from dual
13 union all
14 select 7,'AAA' from dual)
15 order by 1;
SORTKEY VAL DISTINCT_VALUE
---------- --- --------------
1 AAA 1
2 AAA 1
3 BBB 2
4 CCC 3
5 CCC 3
6 AAA 3
7 AAA 3
7 rows selected. -
1)...
What is unclear? I mean that example is
selfexplanation ... but I can try explain it
detaily:
Start 1.1.2006 00:00
End: 1.1.2006 23:59
Length: 1 hour
Step: 1 minuteStart date is incemented by step until this <I supposed you were working on consecutive intevals then the step concept was out of my consideration.
EndDate2) Your example is not ANALYTIC function ... it isWhat you need is something that does what you asked for.If it can be done with aggreagate I suggest you to use aggregate.
AGRGATE function! You are in mistake ...Here the results.I need write select statement which compute count of UNIQUE NO in date interval by WINDOWProcessing ...
The rows to display were too many so the output has been limited to the first 250s.
create table d_lps_secx_c ( dt date,
no number,
atr1 char(1),
atr2 char(1),
primary key (dt,no)
)
Processing ...
insert into d_lps_secx_c
select trunc(to_date ('2005-12-31','yyyy-mm-dd') + dbms_random.value(0,3),'mi'),
trunc((level-1)/2),
'A',
'B'
from dual
connect by level <= 100
100 row(s) inserted
Processing ...
select to_date(:dfrom,'dd/mm/yyyy hh24:mi') as dfrom,
to_date(:dtill,'dd/mm/yyyy hh24:mi') as dtill,
:interval_length_sec as interval_length_sec,
:step_secs as step_secs
from dual
Query finished, retrieving results...
DFROM DTILL INTERVAL_LENGTH_SEC STEP_SECS
-------------------- -------------------- -------------------------------------- --------------------------------------
1-gen-2006 2-gen-2006 3600 60
1 row(s) retrieved
Processing ...
select dt, no
from d_lps_secx_c
where dt between to_date(:dfrom,'dd/mm/yyyy hh24:mi') and to_date(:dtill,'dd/mm/yyyy hh24:mi') + (:interval_length_sec/(24*60*60))
order by dt
Query finished, retrieving results...
DT NO
-------------------- --------------------------------------
1-gen-2006 0:06:00 11
1-gen-2006 1:04:00 30
1-gen-2006 4:07:00 0
1-gen-2006 4:22:00 45
1-gen-2006 5:07:00 0
1-gen-2006 6:32:00 12
1-gen-2006 7:01:00 16
1-gen-2006 7:21:00 4
1-gen-2006 7:26:00 15
1-gen-2006 7:35:00 33
1-gen-2006 8:23:00 41
1-gen-2006 8:26:00 17
1-gen-2006 8:50:00 8
1-gen-2006 9:11:00 3
1-gen-2006 9:47:00 20
1-gen-2006 9:55:00 35
1-gen-2006 10:11:00 9
1-gen-2006 10:32:00 34
1-gen-2006 11:09:00 15
1-gen-2006 11:23:00 1
1-gen-2006 12:03:00 30
1-gen-2006 12:18:00 16
1-gen-2006 13:27:00 26
1-gen-2006 14:02:00 42
1-gen-2006 15:42:00 23
1-gen-2006 16:50:00 42
1-gen-2006 18:14:00 18
1-gen-2006 18:22:00 48
1-gen-2006 18:33:00 46
1-gen-2006 19:15:00 48
1-gen-2006 20:59:00 36
1-gen-2006 21:36:00 31
1-gen-2006 22:10:00 8
1-gen-2006 22:12:00 19
1-gen-2006 23:32:00 2
2-gen-2006 0:43:00 10
36 row(s) retrieved
Processing ...
select ta.dfrom,ta.dtill,count(tb.no)
FROM (
SELECT to_date(:dfrom,'dd/mm/yyyy hh24:mi')+
((LEVEL-1)*:step_secs)/(24*60*60) dfrom,
to_date(:dfrom,'dd/mm/yyyy hh24:mi')+
((LEVEL-1)*:step_secs)/(24*60*60)+
(:interval_length_sec/(24*60*60)) dtill,
level lvl
FROM DUAL
CONNECT BY to_date(:dfrom,'dd/mm/yyyy hh24:mi')+
((LEVEL-1)*:step_secs)/(24*60*60)+
(:interval_length_sec/(24*60*60)) <=
to_date(:dtill,'dd/mm/yyyy hh24:mi')
) TA
LEFT OUTER JOIN
d_lps_secx_c TB -- this table has 20M+ records
ON ( dt between dfrom and dtill )
group by ta.dfrom,ta.dtill
order by ta.dfrom
Query finished, retrieving results...
DFROM DTILL COUNT(TB.NO)
-------------------- -------------------- --------------------------------------
1-gen-2006 1-gen-2006 1:00:00 1
1-gen-2006 0:01:00 1-gen-2006 1:01:00 1
1-gen-2006 0:02:00 1-gen-2006 1:02:00 1
1-gen-2006 0:03:00 1-gen-2006 1:03:00 1
1-gen-2006 0:04:00 1-gen-2006 1:04:00 2
1-gen-2006 0:05:00 1-gen-2006 1:05:00 2
1-gen-2006 0:06:00 1-gen-2006 1:06:00 2
1-gen-2006 0:07:00 1-gen-2006 1:07:00 1
1-gen-2006 0:08:00 1-gen-2006 1:08:00 1
1-gen-2006 0:09:00 1-gen-2006 1:09:00 1
1-gen-2006 0:10:00 1-gen-2006 1:10:00 1
1-gen-2006 0:11:00 1-gen-2006 1:11:00 1
1-gen-2006 0:12:00 1-gen-2006 1:12:00 1
1-gen-2006 0:13:00 1-gen-2006 1:13:00 1
1-gen-2006 0:14:00 1-gen-2006 1:14:00 1
1-gen-2006 0:15:00 1-gen-2006 1:15:00 1
1-gen-2006 0:16:00 1-gen-2006 1:16:00 1
1-gen-2006 0:17:00 1-gen-2006 1:17:00 1
1-gen-2006 0:18:00 1-gen-2006 1:18:00 1
1-gen-2006 0:19:00 1-gen-2006 1:19:00 1
1-gen-2006 0:20:00 1-gen-2006 1:20:00 1
1-gen-2006 0:21:00 1-gen-2006 1:21:00 1
1-gen-2006 0:22:00 1-gen-2006 1:22:00 1
1-gen-2006 0:23:00 1-gen-2006 1:23:00 1
1-gen-2006 0:24:00 1-gen-2006 1:24:00 1
1-gen-2006 0:25:00 1-gen-2006 1:25:00 1
1-gen-2006 0:26:00 1-gen-2006 1:26:00 1
1-gen-2006 0:27:00 1-gen-2006 1:27:00 1
1-gen-2006 0:28:00 1-gen-2006 1:28:00 1
1-gen-2006 0:29:00 1-gen-2006 1:29:00 1
1-gen-2006 0:30:00 1-gen-2006 1:30:00 1
1-gen-2006 0:31:00 1-gen-2006 1:31:00 1
1-gen-2006 0:32:00 1-gen-2006 1:32:00 1
1-gen-2006 0:33:00 1-gen-2006 1:33:00 1
1-gen-2006 0:34:00 1-gen-2006 1:34:00 1
1-gen-2006 0:35:00 1-gen-2006 1:35:00 1
1-gen-2006 0:36:00 1-gen-2006 1:36:00 1
1-gen-2006 0:37:00 1-gen-2006 1:37:00 1
1-gen-2006 0:38:00 1-gen-2006 1:38:00 1
1-gen-2006 0:39:00 1-gen-2006 1:39:00 1
1-gen-2006 0:40:00 1-gen-2006 1:40:00 1
1-gen-2006 0:41:00 1-gen-2006 1:41:00 1
1-gen-2006 0:42:00 1-gen-2006 1:42:00 1
1-gen-2006 0:43:00 1-gen-2006 1:43:00 1
1-gen-2006 0:44:00 1-gen-2006 1:44:00 1
1-gen-2006 0:45:00 1-gen-2006 1:45:00 1
1-gen-2006 0:46:00 1-gen-2006 1:46:00 1
1-gen-2006 0:47:00 1-gen-2006 1:47:00 1
1-gen-2006 0:48:00 1-gen-2006 1:48:00 1
1-gen-2006 0:49:00 1-gen-2006 1:49:00 1
1-gen-2006 0:50:00 1-gen-2006 1:50:00 1
1-gen-2006 0:51:00 1-gen-2006 1:51:00 1
1-gen-2006 0:52:00 1-gen-2006 1:52:00 1
1-gen-2006 0:53:00 1-gen-2006 1:53:00 1
1-gen-2006 0:54:00 1-gen-2006 1:54:00 1
1-gen-2006 0:55:00 1-gen-2006 1:55:00 1
1-gen-2006 0:56:00 1-gen-2006 1:56:00 1
1-gen-2006 0:57:00 1-gen-2006 1:57:00 1
1-gen-2006 0:58:00 1-gen-2006 1:58:00 1
1-gen-2006 0:59:00 1-gen-2006 1:59:00 1
1-gen-2006 1:00:00 1-gen-2006 2:00:00 1
1-gen-2006 1:01:00 1-gen-2006 2:01:00 1
1-gen-2006 1:02:00 1-gen-2006 2:02:00 1
1-gen-2006 1:03:00 1-gen-2006 2:03:00 1
1-gen-2006 1:04:00 1-gen-2006 2:04:00 1
1-gen-2006 1:05:00 1-gen-2006 2:05:00 0
1-gen-2006 1:06:00 1-gen-2006 2:06:00 0
1-gen-2006 1:07:00 1-gen-2006 2:07:00 0
1-gen-2006 1:08:00 1-gen-2006 2:08:00 0
1-gen-2006 1:09:00 1-gen-2006 2:09:00 0
1-gen-2006 1:10:00 1-gen-2006 2:10:00 0
1-gen-2006 1:11:00 1-gen-2006 2:11:00 0
1-gen-2006 1:12:00 1-gen-2006 2:12:00 0
1-gen-2006 1:13:00 1-gen-2006 2:13:00 0
1-gen-2006 1:14:00 1-gen-2006 2:14:00 0
1-gen-2006 1:15:00 1-gen-2006 2:15:00 0
1-gen-2006 1:16:00 1-gen-2006 2:16:00 0
1-gen-2006 1:17:00 1-gen-2006 2:17:00 0
1-gen-2006 1:18:00 1-gen-2006 2:18:00 0
1-gen-2006 1:19:00 1-gen-2006 2:19:00 0
1-gen-2006 1:20:00 1-gen-2006 2:20:00 0
1-gen-2006 1:21:00 1-gen-2006 2:21:00 0
1-gen-2006 1:22:00 1-gen-2006 2:22:00 0
1-gen-2006 1:23:00 1-gen-2006 2:23:00 0
1-gen-2006 1:24:00 1-gen-2006 2:24:00 0
1-gen-2006 1:25:00 1-gen-2006 2:25:00 0
1-gen-2006 1:26:00 1-gen-2006 2:26:00 0
1-gen-2006 1:27:00 1-gen-2006 2:27:00 0
1-gen-2006 1:28:00 1-gen-2006 2:28:00 0
1-gen-2006 1:29:00 1-gen-2006 2:29:00 0
1-gen-2006 1:30:00 1-gen-2006 2:30:00 0
1-gen-2006 1:31:00 1-gen-2006 2:31:00 0
1-gen-2006 1:32:00 1-gen-2006 2:32:00 0
1-gen-2006 1:33:00 1-gen-2006 2:33:00 0
1-gen-2006 1:34:00 1-gen-2006 2:34:00 0
1-gen-2006 1:35:00 1-gen-2006 2:35:00 0
1-gen-2006 1:36:00 1-gen-2006 2:36:00 0
1-gen-2006 1:37:00 1-gen-2006 2:37:00 0
1-gen-2006 1:38:00 1-gen-2006 2:38:00 0
1-gen-2006 1:39:00 1-gen-2006 2:39:00 0
1-gen-2006 1:40:00 1-gen-2006 2:40:00 0
1-gen-2006 1:41:00 1-gen-2006 2:41:00 0
1-gen-2006 1:42:00 1-gen-2006 2:42:00 0
1-gen-2006 1:43:00 1-gen-2006 2:43:00 0
1-gen-2006 1:44:00 1-gen-2006 2:44:00 0
1-gen-2006 1:45:00 1-gen-2006 2:45:00 0
1-gen-2006 1:46:00 1-gen-2006 2:46:00 0
1-gen-2006 1:47:00 1-gen-2006 2:47:00 0
1-gen-2006 1:48:00 1-gen-2006 2:48:00 0
1-gen-2006 1:49:00 1-gen-2006 2:49:00 0
1-gen-2006 1:50:00 1-gen-2006 2:50:00 0
1-gen-2006 1:51:00 1-gen-2006 2:51:00 0
1-gen-2006 1:52:00 1-gen-2006 2:52:00 0
1-gen-2006 1:53:00 1-gen-2006 2:53:00 0
1-gen-2006 1:54:00 1-gen-2006 2:54:00 0
1-gen-2006 1:55:00 1-gen-2006 2:55:00 0
1-gen-2006 1:56:00 1-gen-2006 2:56:00 0
1-gen-2006 1:57:00 1-gen-2006 2:57:00 0
1-gen-2006 1:58:00 1-gen-2006 2:58:00 0
1-gen-2006 1:59:00 1-gen-2006 2:59:00 0
1-gen-2006 2:00:00 1-gen-2006 3:00:00 0
1-gen-2006 2:01:00 1-gen-2006 3:01:00 0
1-gen-2006 2:02:00 1-gen-2006 3:02:00 0
1-gen-2006 2:03:00 1-gen-2006 3:03:00 0
1-gen-2006 2:04:00 1-gen-2006 3:04:00 0
1-gen-2006 2:05:00 1-gen-2006 3:05:00 0
1-gen-2006 2:06:00 1-gen-2006 3:06:00 0
1-gen-2006 2:07:00 1-gen-2006 3:07:00 0
1-gen-2006 2:08:00 1-gen-2006 3:08:00 0
1-gen-2006 2:09:00 1-gen-2006 3:09:00 0
1-gen-2006 2:10:00 1-gen-2006 3:10:00 0
1-gen-2006 2:11:00 1-gen-2006 3:11:00 0
1-gen-2006 2:12:00 1-gen-2006 3:12:00 0
1-gen-2006 2:13:00 1-gen-2006 3:13:00 0
1-gen-2006 2:14:00 1-gen-2006 3:14:00 0
1-gen-2006 2:15:00 1-gen-2006 3:15:00 0
1-gen-2006 2:16:00 1-gen-2006 3:16:00 0
1-gen-2006 2:17:00 1-gen-2006 3:17:00 0
1-gen-2006 2:18:00 1-gen-2006 3:18:00 0
1-gen-2006 2:19:00 1-gen-2006 3:19:00 0
1-gen-2006 2:20:00 1-gen-2006 3:20:00 0
1-gen-2006 2:21:00 1-gen-2006 3:21:00 0
1-gen-2006 2:22:00 1-gen-2006 3:22:00 0
1-gen-2006 2:23:00 1-gen-2006 3:23:00 0
1-gen-2006 2:24:00 1-gen-2006 3:24:00 0
1-gen-2006 2:25:00 1-gen-2006 3:25:00 0
1-gen-2006 2:26:00 1-gen-2006 3:26:00 0
1-gen-2006 2:27:00 1-gen-2006 3:27:00 0
1-gen-2006 2:28:00 1-gen-2006 3:28:00 0
1-gen-2006 2:29:00 1-gen-2006 3:29:00 0
1-gen-2006 2:30:00 1-gen-2006 3:30:00 0
1-gen-2006 2:31:00 1-gen-2006 3:31:00 0
1-gen-2006 2:32:00 1-gen-2006 3:32:00 0
1-gen-2006 2:33:00 1-gen-2006 3:33:00 0
1-gen-2006 2:34:00 1-gen-2006 3:34:00 0
1-gen-2006 2:35:00 1-gen-2006 3:35:00 0
1-gen-2006 2:36:00 1-gen-2006 3:36:00 0
1-gen-2006 2:37:00 1-gen-2006 3:37:00 0
1-gen-2006 2:38:00 1-gen-2006 3:38:00 0
1-gen-2006 2:39:00 1-gen-2006 3:39:00 0
1-gen-2006 2:40:00 1-gen-2006 3:40:00 0
1-gen-2006 2:41:00 1-gen-2006 3:41:00 0
1-gen-2006 2:42:00 1-gen-2006 3:42:00 0
1-gen-2006 2:43:00 1-gen-2006 3:43:00 0
1-gen-2006 2:44:00 1-gen-2006 3:44:00 0
1-gen-2006 2:45:00 1-gen-2006 3:45:00 0
1-gen-2006 2:46:00 1-gen-2006 3:46:00 0
1-gen-2006 2:47:00 1-gen-2006 3:47:00 0
1-gen-2006 2:48:00 1-gen-2006 3:48:00 0
1-gen-2006 2:49:00 1-gen-2006 3:49:00 0
1-gen-2006 2:50:00 1-gen-2006 3:50:00 0
1-gen-2006 2:51:00 1-gen-2006 3:51:00 0
1-gen-2006 2:52:00 1-gen-2006 3:52:00 0
1-gen-2006 2:53:00 1-gen-2006 3:53:00 0
1-gen-2006 2:54:00 1-gen-2006 3:54:00 0
1-gen-2006 2:55:00 1-gen-2006 3:55:00 0
1-gen-2006 2:56:00 1-gen-2006 3:56:00 0
1-gen-2006 2:57:00 1-gen-2006 3:57:00 0
1-gen-2006 2:58:00 1-gen-2006 3:58:00 0
1-gen-2006 2:59:00 1-gen-2006 3:59:00 0
1-gen-2006 3:00:00 1-gen-2006 4:00:00 0
1-gen-2006 3:01:00 1-gen-2006 4:01:00 0
1-gen-2006 3:02:00 1-gen-2006 4:02:00 0
1-gen-2006 3:03:00 1-gen-2006 4:03:00 0
1-gen-2006 3:04:00 1-gen-2006 4:04:00 0
1-gen-2006 3:05:00 1-gen-2006 4:05:00 0
1-gen-2006 3:06:00 1-gen-2006 4:06:00 0
1-gen-2006 3:07:00 1-gen-2006 4:07:00 1
1-gen-2006 3:08:00 1-gen-2006 4:08:00 1
1-gen-2006 3:09:00 1-gen-2006 4:09:00 1
1-gen-2006 3:10:00 1-gen-2006 4:10:00 1
1-gen-2006 3:11:00 1-gen-2006 4:11:00 1
1-gen-2006 3:12:00 1-gen-2006 4:12:00 1
1-gen-2006 3:13:00 1-gen-2006 4:13:00 1
1-gen-2006 3:14:00 1-gen-2006 4:14:00 1
1-gen-2006 3:15:00 1-gen-2006 4:15:00 1
1-gen-2006 3:16:00 1-gen-2006 4:16:00 1
1-gen-2006 3:17:00 1-gen-2006 4:17:00 1
1-gen-2006 3:18:00 1-gen-2006 4:18:00 1
1-gen-2006 3:19:00 1-gen-2006 4:19:00 1
1-gen-2006 3:20:00 1-gen-2006 4:20:00 1
1-gen-2006 3:21:00 1-gen-2006 4:21:00 1
1-gen-2006 3:22:00 1-gen-2006 4:22:00 2
1-gen-2006 3:23:00 1-gen-2006 4:23:00 2
1-gen-2006 3:24:00 1-gen-2006 4:24:00 2
1-gen-2006 3:25:00 1-gen-2006 4:25:00 2
1-gen-2006 3:26:00 1-gen-2006 4:26:00 2
1-gen-2006 3:27:00 1-gen-2006 4:27:00 2
1-gen-2006 3:28:00 1-gen-2006 4:28:00 2
1-gen-2006 3:29:00 1-gen-2006 4:29:00 2
1-gen-2006 3:30:00 1-gen-2006 4:30:00 2
1-gen-2006 3:31:00 1-gen-2006 4:31:00 2
1-gen-2006 3:32:00 1-gen-2006 4:32:00 2
1-gen-2006 3:33:00 1-gen-2006 4:33:00 2
1-gen-2006 3:34:00 1-gen-2006 4:34:00 2
1-gen-2006 3:35:00 1-gen-2006 4:35:00 2
1-gen-2006 3:36:00 1-gen-2006 4:36:00 2
1-gen-2006 3:37:00 1-gen-2006 4:37:00 2
1-gen-2006 3:38:00 1-gen-2006 4:38:00 2
1-gen-2006 3:39:00 1-gen-2006 4:39:00 2
1-gen-2006 3:40:00 1-gen-2006 4:40:00 2
1-gen-2006 3:41:00 1-gen-2006 4:41:00 2
1-gen-2006 3:42:00 1-gen-2006 4:42:00 2
1-gen-2006 3:43:00 1-gen-2006 4:43:00 2
1-gen-2006 3:44:00 1-gen-2006 4:44:00 2
1-gen-2006 3:45:00 1-gen-2006 4:45:00 2
1-gen-2006 3:46:00 1-gen-2006 4:46:00 2
1-gen-2006 3:47:00 1-gen-2006 4:47:00 2
1-gen-2006 3:48:00 1-gen-2006 4:48:00 2
1-gen-2006 3:49:00 1-gen-2006 4:49:00 2
1-gen-2006 3:50:00 1-gen-2006 4:50:00 2
1-gen-2006 3:51:00 1-gen-2006 4:51:00 2
1-gen-2006 3:52:00 1-gen-2006 4:52:00 2
1-gen-2006 3:53:00 1-gen-2006 4:53:00 2
1-gen-2006 3:54:00 1-gen-2006 4:54:00 2
1-gen-2006 3:55:00 1-gen-2006 4:55:00 2
1-gen-2006 3:56:00 1-gen-2006 4:56:00 2
1-gen-2006 3:57:00 1-gen-2006 4:57:00 2
1-gen-2006 3:58:00 1-gen-2006 4:58:00 2
1-gen-2006 3:59:00 1-gen-2006 4:59:00 2
1-gen-2006 4:00:00 1-gen-2006 5:00:00 2
1-gen-2006 4:01:00 1-gen-2006 5:01:00 2
1-gen-2006 4:02:00 1-gen-2006 5:02:00 2
1-gen-2006 4:03:00 1-gen-2006 5:03:00 2
1-gen-2006 4:04:00 1-gen-2006 5:04:00 2
1-gen-2006 4:05:00 1-gen-2006 5:05:00 2
1-gen-2006 4:06:00 1-gen-2006 5:06:00 2
1-gen-2006 4:07:00 1-gen-2006 5:07:00 3
1-gen-2006 4:08:00 1-gen-2006 5:08:00 2
1-gen-2006 4:09:00 1-gen-2006 5:09:00 2
250 row(s) retrieved
Processing ...
drop table d_lps_secx_c
Bye Alessandro -
> ON ( dt between dfrom and dtill )
Excellent!
It is indeed that easy and there is no need to unfold like I did.
Regards,
Rob. -
Thanx. This is good idea .. but it's too slow
Test it for 20 000 000 rows in d_lps_secx_c per year and
Start 1.1.2006 00:00
End: 1.1.2006 23:59
Length: 1 hour
Step: 1 minute
It's take aproximatelly 50 seconds per one day => more than 5 hours ...
Analytics functions is more fastert then grouping ...
Thanks for your time -
> It's take aproximatelly 50 seconds per one day => more than 5 hours ...
So you are missing an index on dt?
Did you investigate why it was slow? [url http://forums.oracle.com/forums/thread.jspa?threadID=501834&tstart=0]This thread may help.
> Analytics functions is more fastert then grouping
This is nonsense generally.
Regards,
Rob. -
Is this an Oracle function?
select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value * ERROR at line 1: ORA-00904: "COUNT_DISTINCT": invalid identifierwell, you could write your own count_distinct user defined aggregate -
Bingo ;-)well, you could write your own count_distinct user defined aggregate
SQL> create or replace type AggregateCD as object
Nicolas.
2 ( nb number,
3 ListOfDistinctValue clob,
4 static function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number,
5 member function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number,
6 member function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number,
7 member function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number
8 );
9 /
Type created.
SQL>
SQL> create or replace type body AggregateCD is
2 static function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number is
3 begin
4 sctx := AggregateCD(0,null);
5 return ODCIConst.Success;
6 end;
7
8 member function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number is
9 ListOfValue CLOB:=self.ListOfDistinctValue ;
10 begin
11 while instr(ListOfValue,';') > 0 loop
12 if substr(ListOfValue,1,instr(ListOfValue,';')-1) = value then
13 return ODCIConst.Success ;
14 end if;
15 ListOfValue:=substr(ListOfValue,instr(ListOfValue,';')+1);
16 end loop;
17 self.ListOfDistinctValue:=self.ListOfDistinctValue||value||' ;';
18 self.nb:=self.nb+1;
19 return ODCIConst.Success;
20 end;
21
22 member function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number is
23 begin
24 returnValue := self.nb;
25 return ODCIConst.Success;
26 end;
27
28 member function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number is
29 begin
30 self.nb := ctx2.nb;
31 return ODCIConst.Success;
32 end;
33 end;
34 /
Type body created.
SQL> show err
No errors.
SQL>
SQL> CREATE OR REPLACE FUNCTION Count_distinct (input VARCHAR2) RETURN number
2 PARALLEL_ENABLE AGGREGATE USING AggregateCD;
3 /
Function created.
SQL>
SQL> select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value
2 from (select 1 as SortKey,'AAA' as Val from dual
3 union all
4 select 2,'AAA' from dual
5 union all
6 select 3,'BBB' from dual
7 union all
8 select 4,'CCC' from dual
9 union all
10 select 5,'CCC' from dual
11 union all
12 select 6,'AAA' from dual
13 union all
14 select 7,'AAA' from dual)
15 order by 1;
SORTKEY VAL DISTINCT_VALUE
---------- --- --------------
1 AAA 1
2 AAA 1
3 BBB 2
4 CCC 3
5 CCC 3
6 AAA 3
7 AAA 3
7 rows selected.
Message was edited by:
N. Gasparotto -
Nicolas,
thanks for that piece of code. However, I would not do it this way, because it will work only for varchar2, what's more, it will bug as soon as a column contains semi-column (;)
unfortunately, I tried it with no successdrop function count_distinct; drop type AggregateCD; create or replace type AggregateCD as object ( nb number, ListOfDistinctValue clob, static function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number, member function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number, member function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number, member function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number ); / create or replace type body AggregateCD is static function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number is begin sctx := AggregateCD(0,null); return ODCIConst.Success; end; member function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number is ListOfValue CLOB:=self.ListOfDistinctValue ; begin while instr(ListOfValue,';') > 0 loop if substr(ListOfValue,1,instr(ListOfValue,';')-1) = value then return ODCIConst.Success ; end if; ListOfValue:=substr(ListOfValue,instr(ListOfValue,';')+1); end loop; self.ListOfDistinctValue:=self.ListOfDistinctValue||value||' ;'; self.nb:=self.nb+1; return ODCIConst.Success; end; member function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number is begin returnValue := self.nb; return ODCIConst.Success; end; member function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number is begin self.nb := ctx2.nb; return ODCIConst.Success; end; end; / show err CREATE OR REPLACE FUNCTION Count_distinct (input VARCHAR2) RETURN number /*PARALLEL_ENABLE*/ AGGREGATE USING AggregateCD; / show err select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value from (select 1 as SortKey,'AAA' as Val from dual union all select 2,'AAA' from dual union all select 3,'BBB' from dual union all select 4,'CCC' from dual union all select 5,'CCC' from dual union all select 6,'AAA' from dual union all select 7,'AAA' from dual) order by 1; SORTKEY VAL DISTINCT_VALUE ---------- --- -------------- 1 AAA 1 2 AAA 2 3 BBB 3 4 CCC 4 5 CCC 5 6 AAA 6 7 AAA 7 -
Laurent,Yes, I would have to rewrite with a PL/SQL table instead of concatenation into clob with any separator.it will bug as soon as a column contains semi-column (;)
Anyway, in the current case, I don't understand why that doesn't work on your side, I tried succesfully it on 9.2.0.8 and 10.2.0.2.
Nicolas. -
I will have a look (I tried with 11.1.0.6)
by the way, I tried a different approachwith t as (select 1 as SortKey,'AAA' as Val from dual union all select 2,'AAA' from dual union all select 3,'BBB' from dual union all select 4,'CCC' from dual union all select 5,'CCC' from dual union all select 6,'AAA' from dual union all select 7,'AAA' from dual) select * from t model dimension by (sortkey) measures (0 c, val) rules (c[sortkey]=count(distinct val)[sortkey<=cv(sortkey)]) / SORTKEY C VAL ---------- ---------- --- 1 1 AAA 2 1 AAA 3 2 BBB 4 3 CCC 5 3 CCC 6 3 AAA 7 3 AAA -
I will have a look (I tried with 11.1.0.6)same in 9.2.0.8, 10.1.0.5, 10.2.0.3
maybe copy-paste error -
Nice one the model !
Since the OP is on 9i, that cannot be an option.
What about :SQL> drop function count_distinct;
Function dropped.
SQL> drop type AggregateCD;
Type dropped.
SQL>
SQL> create or replace type TypeListOfValue as table of varchar2(4000);
2 /
Type created.
SQL>
SQL> create or replace type AggregateCD as object
2 ( nb number,
3 ListOfDistinctValue TypeListOfValue,
4 static function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number,
5 member function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number,
6 member function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number,
7 member function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number
8 );
9 /
Type created.
SQL>
SQL> create or replace type body AggregateCD is
2 static function ODCIAggregateInitialize(sctx IN OUT AggregateCD) return number is
3 begin
4 sctx := AggregateCD(0,TypeListOfValue(null));
5 return ODCIConst.Success;
6 end;
7
8 member function ODCIAggregateIterate(self IN OUT AggregateCD, value IN VARCHAR2) return number is
9 begin
10 for i in 1..self.ListOfDistinctValue.count loop
11 if self.ListOfDistinctValue(i) = value then
12 return ODCIConst.Success ;
13 end if;
14 end loop;
15 self.nb:=self.nb+1;
16 self.ListOfDistinctValue.extend;
17 self.ListOfDistinctValue(nb):=value;
18 return ODCIConst.Success;
19 end;
20
21 member function ODCIAggregateTerminate(self IN AggregateCD, returnValue OUT number, flags IN number) return number is
22 begin
23 returnValue := self.nb;
24 return ODCIConst.Success;
25 end;
26
27 member function ODCIAggregateMerge(self IN OUT AggregateCD, ctx2 IN AggregateCD) return number is
28 begin
29 self.nb := ctx2.nb;
30 return ODCIConst.Success;
31 end;
32 end;
33 /
Type body created.
SQL>
SQL> show err
No errors.
SQL>
SQL> CREATE OR REPLACE FUNCTION Count_distinct (input VARCHAR2) RETURN number
2 /*PARALLEL_ENABLE*/ AGGREGATE USING AggregateCD;
3 /
Function created.
SQL>
SQL> show err
No errors.
SQL>
SQL> select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value
2 from (select 1 as SortKey,'AAA' as Val from dual
3 union all
4 select 2,'AAA' from dual
5 union all
6 select 3,'BBB' from dual
7 union all
8 select 4,'CCC' from dual
9 union all
10 select 5,'CCC' from dual
11 union all
12 select 6,'AAA' from dual
13 union all
14 select 7,'AAA' from dual)
15 order by 1;
SORTKEY VAL DISTINCT_VALUE
---------- --- --------------
1 AAA 1
2 AAA 1
3 BBB 2
4 CCC 3
5 CCC 3
6 AAA 3
7 AAA 3
7 rows selected.
SQL> select SortKey,Val,Count_distinct(Val) over (order by SortKey) distinct_value
2 from (select 1 as SortKey,1 as Val from dual
3 union all
4 select 2,1 from dual
5 union all
6 select 3,2 from dual
7 union all
8 select 4,3 from dual
9 union all
10 select 5,3 from dual
11 union all
12 select 6,1 from dual
13 union all
14 select 7,1 from dual)
15 order by 1;
SORTKEY VAL DISTINCT_VALUE
---------- ---------- --------------
1 1 1
2 1 1
3 2 2
4 3 3
5 3 3
6 1 3
7 1 3
7 rows selected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production[pre]
Nicolas. -
And performance of SQL when I use own function? I working with 20M+ records itterative in 1440 cycles per one day. I think that this will be to slow ...
-
nice one you too!
-
I could even imagine a function that would use ora_hash instead of a plsql table
it would save a lot of memory
Message was edited by:
Laurent Schneider
but it does not seem to work :-((
Message was edited by:
Laurent Schneider
I deleted my code, I have to look for an efficient algorithm to count the distinct rows -
And performance of SQL when I use own function? IActually, I don't understand what's your choice.
working with 20M+ records itterative in 1440 cycles
per one day. I think that this will be to slow ...
Then, if this is a daily process for one day window, who care it takes 50 seconds ?
And if the window is the year, who care it takes 5 hours ? You don't have to run it every day, have you ?
Nicolas. -
There is no only count (count I need wehn using analytics functions), there are a few sum on some conditions (see sql bellow)...
For one interval of length 1 hour is selected about 3 000 records from 20M records using non unique index on DT. With step 1 minute - there is 1440 repetations of this select If one day get 50 seconds - this mean that one select take 35ms.
And for one year there is 1440 * 365 repetations => 1440 * 365 * 50 = 304 minutes (so this is estimation because time incrementation is not linear).
Yes, there is index on DT. I test btree and bitmap index (because in some case there can be some additional conditions).
Execution plan is:
PLAN COST BYTES CARDINALITY OPTIMIZER
SORT ORDER BY 9968 1107 41
SORT GROUP BY 9968 1107 41
NESTED LOOPS OUTER 9965 1107 41
VIEW 2 492 41
CONNECT BY WITHOUT FILTERING
TABLE ACCESS FULL DUAL 2 41
VIEW 15 1
FILTER
TABLE ACCESS BY INDEX ROWID D_LPS_SECX_C 243 10 1 ANALYZED
INDEX RANGE SCAN IX_DLPSSECXCDTFLTNO UNIQUE 242 1 ANALYZED
Autotrace:
Description Value
recursive calls 0
db block gets 0
consistent gets 1072322
physical reads 0
redo size 0
bytes sent via SQL*Net to client 45436
bytes received via SQL*Net from client 1337
SQL*Net roundtrips to/from client 4
sorts (memory) 3
sorts (disk) 0
SQL STATEMENT (without analytics functions):
SELECT dt_from, dt_till
, sum(CASE WHEN dt = dt_from AND hosP IS NOT NULL THEN 1 ELSE 0 END) a
, sum(CASE WHEN hosP IS NULL THEN 1 ELSE 0 END) b
, sum(CASE WHEN dt = dt_till AND hosN IS NOT NULL THEN 1 ELSE 0 END) c
, sum(CASE WHEN hosN IS NULL THEN 1 ELSE 0 END) d
, sum(CASE WHEN hosP IS NULL
OR (dt = dt_from AND hosP IS NOT NULL) THEN Round((dt_till - dt_from) * 1440 + 1) ELSE 0 END
- CASE WHEN hosP IS NULL THEN Round((dt - dt_from) * 1440) ELSE 0 END
- CASE WHEN hosN IS NULL THEN Round((dt_till - dt) * 1440) ELSE 0 END) tab
FROM (
SELECT To_Date('01.01.2006 00:00', 'dd.mm.yyyy hh24:mi') + (LEVEL * 1 - 1) / 1440 dt_from
, To_Date('01.01.2006 00:00', 'dd.mm.yyyy hh24:mi') + (LEVEL * 1 - 1 + 60 - 1) / 1440 dt_till
FROM dual
CONNECT BY LEVEL <= 1440
)
left outer join
d_lps_secx_c B
ON dt BETWEEN dt_from AND dt_till
AND (dt = dt_from OR dt = dt_till
OR hosP IS NULL
OR hosN IS NULL)
AND hos IS NOT NULL
GROUP BY dt_from, dt_till
ORDER BY 1, 2; -
This is base for user statistics query. User can set interval (start, end, length and step) and some aditional attributes (type, origin, destination, sectors) - up to 10 attributes which values are coded into varchar or numeric values.
This mean that result cannot be computed and stored in table - but must be computed on the user request (set of possible combination of parameters is too large).
Yes - if user select interval one year - long time is acceptable - but 20 - 50 seconds per day when user operate usually on month nterval (start,end) is horrible (10-50 minutes).
System is in analyzic state - we test if possible implement request on this statistics and findnig way how implement base queries. For concrete query i am able optimize it to usable form (using hints, some query rewrites and so) but for general not (too long runnig time)
This discussion has been closed.