1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90
| 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>
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> CREATE OR REPLACE FUNCTION Count_distinct (input VARCHAR2) RETURN number
2 /*PARALLEL_ENABLE*/ AGGREGATE USING AggregateCD;
3 /
Function created.
SQL> WITH ventes AS (SELECT 1 id_vente, 200901 id_aaaamm, 'Cli1' code_client FROM DUAL UNION ALL
2 SELECT 2 id_vente, 200901 id_aaaamm, 'Cli1' code_client FROM DUAL UNION ALL
3 SELECT 3 id_vente, 200902 id_aaaamm, 'Cli2' code_client FROM DUAL UNION ALL
4 SELECT 4 id_vente, 200903 id_aaaamm, 'Cli1' code_client FROM DUAL UNION ALL
5 SELECT 5 id_vente, 200904 id_aaaamm, 'Cli3' code_client FROM DUAL )
6 select distinct id_aaaamm,count_distinct(code_client) over (order by to_date(id_aaaamm,'YYYYMM')) ct
7 from ventes
8 order by to_date(id_aaaamm,'YYYYMM');
200901 1
200902 2
200903 2
200904 3
SQL> WITH ventes AS (SELECT 1 id_vente, 200901 id_aaaamm, 'Cli1' code_client FROM DUAL UNION ALL
2 SELECT 2 id_vente, 200901 id_aaaamm, 'Cli2' code_client FROM DUAL UNION ALL
3 SELECT 3 id_vente, 200902 id_aaaamm, 'Cli2' code_client FROM DUAL UNION ALL
4 SELECT 4 id_vente, 200903 id_aaaamm, 'Cli1' code_client FROM DUAL UNION ALL
5 SELECT 5 id_vente, 200904 id_aaaamm, 'Cli3' code_client FROM DUAL )
6 select distinct id_aaaamm,count_distinct(code_client) over (order by to_date(id_aaaamm,'YYYYMM')) ct
7 from ventes
8 order by to_date(id_aaaamm,'YYYYMM');
200901 2
200902 2
200903 2
200904 3
SQL> |
Partager