Requête qui cumule un "count distinct"

**jerjerrod** · 15/06/2009, 14h33

Bonjour tout le monde,

j'aurais besoin d'une requête qui calcule, à partir d'une table de vente, pour chaque mois le nombre de clients différents pour la période de janvier au mois indiqué.

un petit exemple pour être un peu plus clair :
ma table des ventes a pour structure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
ID_VENTE  ID_AAAAMM  CODE_CLIENT
1          200901          Cli1
2          200901          Cli1
3          200902          Cli2
4          200903          Cli1
5          200904          Cli3

Ma requête devrait donc avoir le résultat suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
ID_AAAAMM  COUNT_DISTINCT
200901      1
200902      2
200903      2
200904      3

C'est une sorte de cumul, qui ajoute à chaque mois le nombre de nouveaux clients qui apparaissent, mais en initialisant à chaque nouvelle année.

Est-il possible de réaliser cela en une seule requête ?

Merci pour vos réponses et votre temps !

**xdescamp** · 15/06/2009, 15h56

Le code suivant devrait répondre à ton besoin :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
WITH ventes AS (SELECT 1 id_vente, 200901 id_aaaamm, 'Cli1' code_client FROM DUAL UNION ALL
                SELECT 2 id_vente, 200901 id_aaaamm, 'Cli1' code_client FROM DUAL UNION ALL
                SELECT 3 id_vente, 200902 id_aaaamm, 'Cli2' code_client FROM DUAL UNION ALL
                SELECT 4 id_vente, 200903 id_aaaamm, 'Cli1' code_client FROM DUAL UNION ALL
                SELECT 5 id_vente, 200904 id_aaaamm, 'Cli3' code_client FROM DUAL )
SELECT v.id_aaaamm,
       SUM (DECODE (id_aaaamm, first_id_aaaamm, 1, 0)) OVER (PARTITION BY annee ORDER BY id_aaaamm)
FROM (SELECT DISTINCT id_aaaamm,
                      code_client,
                      TO_NUMBER (SUBSTR (id_aaaamm,1,4)) annee,
                      MIN (id_aaaamm) OVER (PARTITION BY SUBSTR(id_aaaamm,1,4), code_client) first_id_aaaamm
      FROM ventes) v

Le principe est de récupérer pour chaque année la première période d'apparition du client.
Puis une fonction analytique permet d'obtenir le nombre cumulé de clients par an.

**mnitu** · 15/06/2009, 16h49

Envoyé par xdescamp

Le code suivant devrait répondre à ton besoin :...

Salut xdescamp,

J'ai changé juste un peu les données: 'Cli2' à la place du cli1 sur la deuxième ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
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 v.id_aaaamm,
  7         SUM (DECODE (id_aaaamm, first_id_aaaamm, 1, 0)) OVER (PARTITION BY annee ORDER BY id_aaaamm)
  8  FROM (SELECT DISTINCT id_aaaamm,
  9                        code_client,
 10                        TO_NUMBER (SUBSTR (id_aaaamm,1,4)) annee,
 11                        MIN (id_aaaamm) OVER (PARTITION BY SUBSTR(id_aaaamm,1,4), code_client) first_id_aaaamm
 12        FROM ventes) v
 13  /
 
 ID_AAAAMM SUM(DECODE(ID_AAAAMM,FIRST_ID_
---------- ------------------------------
    200901                              2
    200901                              2
    200902                              2
    200903                              2
    200904                              3

**xdescamp** · 15/06/2009, 17h08

Mea culpa, j'avais oublié de regrouper le nombre de nouveaux clients par mois

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH ventes AS (SELECT 1 id_vente, 200901 id_aaaamm, 'Cli1' code_client FROM DUAL UNION ALL
                SELECT 2 id_vente, 200901 id_aaaamm, 'Cli2' code_client FROM DUAL UNION ALL
                SELECT 3 id_vente, 200902 id_aaaamm, 'Cli2' code_client FROM DUAL UNION ALL
                SELECT 4 id_vente, 200903 id_aaaamm, 'Cli1' code_client FROM DUAL UNION ALL
                SELECT 5 id_vente, 200904 id_aaaamm, 'Cli3' code_client FROM DUAL )
SELECT v2.annee,
       v2.id_aaaamm,
       SUM (v2.nb_nvx_clients) OVER (PARTITION BY v2.annee ORDER BY v2.id_aaaamm)
FROM (SELECT v1.annee,
             v1.id_aaaamm,
             SUM (DECODE (id_aaaamm, first_id_aaaamm, 1, 0)) nb_nvx_clients
      FROM (SELECT DISTINCT id_aaaamm,
                            code_client,
                            TO_NUMBER (SUBSTR (id_aaaamm,1,4)) annee,
                            MIN (id_aaaamm) OVER (PARTITION BY SUBSTR(id_aaaamm,1,4), code_client) first_id_aaaamm
            FROM ventes) v1
      GROUP BY v1.annee,
               v1.id_aaaamm )v2;

**NGasparotto** · 15/06/2009, 17h17

Un premier essai :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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
  7         a.id_aaaamm, (select count(distinct b.code_client)
  8                     from   ventes b
  9                     where  to_date(a.id_aaaamm,'YYYYMM')>= to_date(b.id_aaaamm,'YYYYMM')) ct
 10  from   ventes a
 11  order by to_date(a.id_aaaamm,'YYYYMM');
 
 ID_AAAAMM         CT
---------- ----------
    200901          1
    200902          2
    200903          2
    200904          3
 
SQL>

Nicolas.

**Waldar** · 15/06/2009, 17h24

En reprenant l'idée de NGasparotto, et en supprimant la requête scalaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
select
    v1.id_aaaamm,
    count(distinct v2.code_client) as nb_cli_distinct
from
    ventes v1
    inner join ventes v2
      on to_date(v2.id_aaaamm, 'yyyymm') <= to_date(v1.id_aaaamm, 'yyyymm')
     and extract(year from to_date(v2.id_aaaamm, 'yyyymm')) = extract(year from to_date(v1.id_aaaamm, 'yyyymm'))
group by
    v1.id_aaaamm
order by
    v1.id_aaaamm asc
 
/* Si vous êtes sûr de vos formats de date vous pouvez faire la jointure entre v1 et v2 ainsi, c'est plus court :
 
      on v2.id_aaaamm <= v1.id_aaaamm
     and substr(v2.id_aaaamm, 1, 4) = substr(v1.id_aaaamm, 1, 4) */
 
 
 ID_AAAAMM  NB_CLI_DISTINCT
---------- ----------------
200901	   1
200902	   2
200903	   2
200904	   3

**xdescamp** · 15/06/2009, 17h37

Envoyé par NGasparotto

Un premier essai...

Mais cette méthode oblige à attaquer 2 fois la table des ventes.

**NGasparotto** · 15/06/2009, 18h24

Envoyé par xdescamp

Mais cette méthode oblige à attaquer 2 fois la table des ventes.

Certes, alors voila une dexieme version :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 id_aaaamm, sum(ct) over (order by to_date(id_aaaamm,'YYYYMM')) ct
  7  from   (SELECT distinct id_aaaamm, decode(to_date(id_aaaamm,'YYYYMM'),min(to_date(id_aaaamm,'YYYYMM')) over (partition by code_client),1,0) ct, code_client
  8          FROM   ventes)
  9  ORDER BY to_date(id_aaaamm,'YYYYMM');
    200901          1
    200902          2
    200903          2
    200904          3
 
SQL>

En fait, le mieux aurait ete :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 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');
select id_aaaamm, count(distinct code_client) over (order by to_date(id_aaaamm,'YYYYMM')) ct
                                                    *
ERROR at line 6:
ORA-30487: ORDER BY not allowed here
 
 
SQL>

Mais voila, Oracle ne veut pas compter de maniere DISTINCT avec un ORDER BY... dommage.

Nicolas.

**xdescamp** · 15/06/2009, 18h38

Envoyé par NGasparotto

Certes, alors voila une dexieme version...

Tu as le même problème que moi relevé par mnitu dans ma première version si tu replace Cli1 par Cli2 dans la 2e ligne.

En fait, il manque un Group By par mois (ou un Distinct comme tu préfères).

**Waldar** · 15/06/2009, 18h57

Avec une seule table et une seule sous-requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select distinct
    id_aaaamm,
    sum(new_cli) over(partition by substr(id_aaaamm, 1, 4) order by id_aaaamm asc) nb_cli
from
    (
    select
        id_aaaamm,
        case
          when lag(id_vente, 1) over(PARTITION by substr(id_aaaamm, 1, 4), code_client order by id_aaaamm asc, id_vente asc) is null
          then 1 else 0
        end as new_cli
    from ventes
    )
order by
    id_aaaamm asc

**NGasparotto** · 15/06/2009, 19h53

Ou encore, une sous-requete avec MODEL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
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 id_aaaamm,max(ct)
  7  from   (select *
  8          from   ventes
  9          model  dimension by (id_vente)
 10          measures (0 ct, id_aaaamm,code_client)
 11          rules (ct[id_vente]=count(distinct code_client)[id_vente<=cv(id_vente)]))
 12  group by id_aaaamm
 13  order by id_aaaamm
 14  /
    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 id_aaaamm,max(ct)
  7  from   (select *
  8          from   ventes
  9          model  dimension by (id_vente)
 10          measures (0 ct, id_aaaamm,code_client)
 11          rules (ct[id_vente]=count(distinct code_client)[id_vente<=cv(id_vente)]))
 12  group by id_aaaamm
 13  order by id_aaaamm
 14  /
    200901          2
    200902          2
    200903          2
    200904          3

Nicolas.

**Waldar** · 15/06/2009, 20h02

Joli, mais il manque encore ce petit bout :

en initialisant à chaque nouvelle année.

Il suffit de rajouter une dimension et la règle associée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT id_aaaamm,max(ct)
FROM   (SELECT *
        FROM   VENTES
        MODEL  DIMENSION BY (ID_VENTE, SUBSTR(ID_AAAAMM, 1, 4) ANNEE)
        MEASURES (0 CT, ID_AAAAMM,CODE_CLIENT)
        RULES (CT[ID_VENTE,ANNEE]=COUNT(DISTINCT CODE_CLIENT)[ID_VENTE<=CV(ID_VENTE), ANNEE=CV(ANNEE)]))
GROUP BY id_aaaamm
ORDER BY id_aaaamm

Je crois que jerjerrod est bien gâté quand même !

**NGasparotto** · 15/06/2009, 20h03

Et pour etre complet, la derniere solution est de creer sa propre function d'aggregation :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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>

Nicolas.

**NGasparotto** · 15/06/2009, 20h08

Envoyé par Waldar

en initialisant à chaque nouvelle année.

Ok, alors sur la derniere, un petit ajout :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select distinct id_aaaamm,
       count_distinct(code_client) over (partition by trunc(to_date(id_aaaamm,'YYYYMM'),'YYYY') order by to_date(id_aaaamm,'YYYYMM')) ct
from   ventes
order by to_date(id_aaaamm,'YYYYMM');

Nicolas.

**mnitu** · 15/06/2009, 21h08

Envoyé par NGasparotto

Et pour etre complet, la derniere solution est de creer sa propre function d'aggregation :
...

Ca, j'ai bien aimé.

**laurentschneider** · 16/06/2009, 09h39

pourquoi donc vouloir employer distinct?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT   id_aaaamm, SUM (COUNT (code_client)) OVER (ORDER BY id_aaaamm) c
FROM (
  SELECT id_aaaamm,
      DECODE(ROW_NUMBER () OVER (
          PARTITION BY code_client ORDER BY id_aaaamm,id_vente),
          1, code_client) code_client
    FROM lsc_t t)
GROUP BY id_aaaamm
ORDER BY id_aaaamm;

**NGasparotto** · 16/06/2009, 09h52

Envoyé par laurentschneider

SUM (COUNT (code_client)) OVER (ORDER BY id_aaaamm)

Bien vu !

Nicolas.

**laurentschneider** · 16/06/2009, 12h18

Envoyé par NGasparotto

Bien vu !

Nicolas.

Merci

Laurent

**jerjerrod** · 16/06/2009, 14h37

Pour cette dernière solution, il manque la réinitialisation à chaque année, ce qui donnerai, si je ne me trompe pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT   id_aaaamm, SUM (COUNT (code_client)) OVER (PARTITION BY FLOOR(id_aaaamm/100) ORDER BY id_aaaamm) c
FROM (
  SELECT id_aaaamm,
      DECODE(ROW_NUMBER () OVER (
          PARTITION BY FLOOR(id_aaaamm/100), code_client ORDER BY id_aaaamm,id_vente),
          1, code_client) code_client
    FROM lsc_t t)
GROUP BY id_aaaamm
ORDER BY id_aaaamm;

En tout cas, merci pour tout pour vos réponses, ca va me changer la vie

A +

Requête qui cumule un "count distinct"

SQL Oracle

Discussions similaires

Partager

Partager