Requête qui cumule un "count distinct"

Version imprimable

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:

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:

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 !

Le code suivant devrait répondre à ton besoin :

Code:

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.

Citation:

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:

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

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

Code:

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;

Un premier essai :

Code:

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.

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

Code:

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

15/06/2009, 17h37
xdescamp

Citation:

Envoyé par NGasparotto

Un premier essai...

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

Citation:

Envoyé par xdescamp

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

Certes, alors voila une dexieme version :

Code:

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:

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.

15/06/2009, 18h38
xdescamp

Citation:

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).

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

Code:

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

Ou encore, une sous-requete avec MODEL :

Code:

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.

Joli, mais il manque encore ce petit bout :

Citation:

en initialisant à chaque nouvelle année.

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

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 !

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

Code:

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.

Citation:

Envoyé par Waldar

en initialisant à chaque nouvelle année.

Ok, alors sur la derniere, un petit ajout :
Code:

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.

15/06/2009, 21h08
mnitu

Citation:

Envoyé par NGasparotto

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

Ca, j'ai bien aimé. :ccool:

pourquoi donc vouloir employer distinct?

Code:

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;

16/06/2009, 09h52
NGasparotto

Citation:

Envoyé par laurentschneider

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

Bien vu !

Nicolas.
16/06/2009, 12h18
laurentschneider

Citation:

Envoyé par NGasparotto

Bien vu !

Nicolas.

Merci :ccool:

Laurent

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

Code:

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 +