Forum Stats

  • 3,733,243 Users
  • 2,246,736 Discussions
  • 7,856,631 Comments

Discussions

Analytic functions - help needed

588708
588708 Member Posts: 117
edited September 2007 in SQL & PL/SQL
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

  • cd_2
    cd_2 Member Posts: 5,021
    1) Analytic function count not supported distinct
    count over WINDOW
    That'd be new. COUNT(DISTINCT column) OVER ... works
    perfectly unter 10g2

    C.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited September 2007
    If we use distinct option in count function of OLAP,
    we cannot use order by clause.

    for instance in Oracle 10.2.0.1.0
    SQL> 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 3
    SQL> 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 7
    SQL> select SortKey,Val,
    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
    However,
    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
  • 588708
    588708 Member Posts: 117
    Thx. This is good idea but I have version 9.2 :-(
  • 588708
    588708 Member Posts: 117
    I need compute count over Window ...
    Emulation is good idea but performance of emulation is unusable - i work with 2M+ records ...
  • Alessandro Rossi
    Alessandro Rossi Member Posts: 1,057 Bronze Badge
    edited September 2007
    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
    Be 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.

    1) Analytic function count not supported distinct
    count over WINDOW
    count() distinct would return 1 always.
    Processing ...
    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) retrieved
    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)
    Do it with in this way
    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 = dfrom
    group by ta.dfrom,ta.dtill
    order by ta.dfrom
    Bye Alessandro

    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
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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 ( 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.
    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)

    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.
  • 588708
    588708 Member Posts: 117
    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 ...
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    That works fine with count_distinct function :
    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.
    Nicolas.
  • Alessandro Rossi
    Alessandro Rossi Member Posts: 1,057 Bronze Badge
    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
    ...
    Start date is incemented by step until this <
    EndDate
    I supposed you were working on consecutive intevals then the step concept was out of my consideration.

    2) Your example is not ANALYTIC function ... it is
    AGRGATE function! You are in mistake ...
    What you need is something that does what you asked for.If it can be done with aggreagate I suggest you to use aggregate.
    I need write select statement which compute count of UNIQUE NO in date interval by WINDOW
    Here the results.

    Processing ...
    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
    The rows to display were too many so the output has been limited to the first 250s.

    Bye Alessandro
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    > ON ( dt between dfrom and dtill )

    Excellent!
    It is indeed that easy and there is no need to unfold like I did.

    Regards,
    Rob.
  • 588708
    588708 Member Posts: 117
    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
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    > 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.
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    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 identifier
    well, you could write your own count_distinct user defined aggregate
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited September 2007
    well, you could write your own count_distinct user defined aggregate
    Bingo ;-)
    SQL> create or replace type AggregateCD as object
    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.
    Nicolas.

    Message was edited by:
    N. Gasparotto
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    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 success
    drop 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
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    Laurent,
    it will bug as soon as a column contains semi-column (;)
    Yes, I would have to rewrite with a PL/SQL table instead of concatenation into clob with any separator.

    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.
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    I will have a look (I tried with 11.1.0.6)

    by the way, I tried a different approach
    with 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
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    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
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    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.
  • 588708
    588708 Member Posts: 117
    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 ...
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    nice one you too!
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    edited September 2007
    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
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    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 ...
    Actually, I don't understand what's your choice.
    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.
  • 588708
    588708 Member Posts: 117
    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;
  • 588708
    588708 Member Posts: 117
    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.