Discussion :

[jerjerrod]

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]

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]

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]

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]

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]

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]

Un premier essai...

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

[NGasparotto]

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]

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]

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