Discussion :

[marieo]

Bonjour,
COmment écrire une requête SQL pour Oracle qui me permet de connaitre un % et un volume cumulé à partir d'une table ?

Voici mes données

Code :

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

1
2
3
4
5
6
7
 
CLIENT  COMMANDE   NB
Client1  cmd1      10
Client1  cmd2       5
Client2  cmd1       3
Client2  cmd2       2
Client2  cmd3       5

J'aimerais avoir un résultat de ce style là avec un tri décroissant par CLIENT sur le NB

Code :

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

1
2
3
4
5
6
7
 
CLIENT  CmdTotal    COMMANDE   NB   CmdCumul   PctNB   PctCumul
Client1     15        cmd1     10       10      0.67     0.67
Client1     15        cmd2      5       15      0.33     1 
Client2     10        cmd3      5        5      0.50     0.50
Client2     10        cmd1      3        8      0.30     0.80
Client2     10        cmd2      2       10      0.20     1

Mon autre question est : comment insérer les résultats dans une table Oracle de stockage des données calculées.
Sachant que je ne veux conserver que les résultats qui ont une volumétrie suffisante pour être intéressant.
Ainsi, je veux garder, pour chaque CLIENT les lignes pour lesquelles NB > 2 et tant que PctCumul est <= 0.80
Donc, dans mon exemple ci-dessus, je ne veux insérer dans ma table finale que les lignes 3, 5 et 6 mais pas les lignes 4 et 7...

Auriez-vous une idée de comment faire ?

Merci à tous,
MarieO

[dariyoosh]

Bonjour,

Vous pouvez utiliser des fonctions analytiques pour cette 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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WITH tmptab AS
(
    SELECT 'Client1' AS client,  'cmd1' AS commande, 10 AS nb 
    FROM "PUBLIC".dual UNION ALL
    SELECT 'Client1' AS client,  'cmd2' AS commande,  5 AS nb 
    FROM "PUBLIC".dual UNION ALL
    SELECT 'Client2' AS client,  'cmd1' AS commande,  3 AS nb 
    FROM "PUBLIC".dual UNION ALL
    SELECT 'Client2' AS client,  'cmd2' AS commande,  2 AS nb 
    FROM "PUBLIC".dual UNION ALL
    SELECT 'Client2' AS client,  'cmd3' AS commande,  5 AS nb 
    FROM "PUBLIC".dual
)
SELECT  t1.client, 
        sum(t1.nb)
            OVER (PARTITION BY t1.client) CmdTotal,
        t1.commande,
        t1.nb,
        sum(t1.nb) 
            OVER (PARTITION BY t1.client 
                  ORDER BY t1.nb DESC
                  RANGE UNBOUNDED PRECEDING) CmdCumul,
        round(t1.nb / sum(t1.nb) OVER (PARTITION BY t1.client), 2) PctNB,
        round
        (
            sum(t1.nb) 
                OVER (PARTITION BY t1.client 
                      ORDER BY t1.nb DESC
                      RANGE UNBOUNDED PRECEDING) / 
            sum(t1.nb) OVER (PARTITION BY t1.client)
            , 
            2
        ) PctCumul
FROM    tmptab t1
ORDER BY t1.client ASC, t1.nb DESC;

Et le résultat sera donc:

Code :

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

1
2
3
4
5
6
7
8
9
CLIENT	  CMDTOTAL COMM 	NB   CMDCUMUL	   PCTNB   PCTCUMUL
------- ---------- ---- ---------- ---------- ---------- ----------
Client1 	15 cmd1 	10	   10	     .67	.67
Client1 	15 cmd2 	 5	   15	     .33	  1
Client2 	10 cmd3 	 5	    5	      .5	 .5
Client2 	10 cmd1 	 3	    8	      .3	 .8
Client2 	10 cmd2 	 2	   10	      .2	  1
 
SQL>

Pour plus d'information sur les fonctions analytiques, vous pouvez lire la documentation en ligne:
http://docs.oracle.com/cd/E11882_01/...004.htm#i81407

Mon autre question est : comment insérer les résultats dans une table Oracle de stockage des données calculées.
Sachant que je ne veux conserver que les résultats qui ont une volumétrie suffisante pour être intéressant.
Ainsi, je veux garder, pour chaque CLIENT les lignes pour lesquelles NB > 2 et tant que PctCumul est <= 0.80

Je ne suis pas sûr si j'ai bien compris cette partie de la question, mais je pense que la façon propre de faire ça c'est de définir une vue avec la requête ci-dessus, plus des prédicats spécifiques aux données qui vous intéressent.

En gros

Code :

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

1
2
3
4
5
6
7
CREATE OR REPLACE VIEW votreVue AS
SELECT <ici vous écrivez la liste des colonnes qui vous intéressent>
FROM
(
    <ici vous écrivez la requête ci-dessus>
) t2
WHERE t2.nb > 2 AND t2.PctCumul <= 0.80;

[marieo]

Bonjour,
Je viens de tester votre solution et cela fonctionne très bien et de façon très rapide.

Quels sont les avantages / inconvénients d'une vue plutôt que le stockage des résultats de la requête précédente dans une table ?

Quelle est la meilleure solution à retenir (Vue, Table ou Vue Matérialisée) si je suis amenée à consulter régulièrement les résultats de ma requête initiale ?

Merci beaucoup,
Marieo

[dariyoosh]

Bonjour,

Je dirais que c'est plutôt à vous de dire quelle méthode convient en commençant déjà par préciser le type de l'environnement. Est-ce que l'on parle d'un système transactionnel avec beaucoup de mise à jour des données en temps réel et de nombreuses connexions (simultanées à la base) ou d'un système décisionnel avec une mise à jour des cubes par exemple une fois par jour?

Pour moi, le stockage dans un contexte de reporting (table, vue matérialisée) convient souvent plutôt dans un environnement décisionnel. Maintenant, si la volumétrie des données et le nombre d'utilisateur ne sont pas grands, on peut se permettre de créer cette requête sous forme d'une vue dans un système transactionnel pour faire de façon très ponctuelle des reporting (histoire de ne pas surcharger le système avec des opérations de BI qui ne sont pas censées être exécutées dans ce genre d'environnement et qui consomment trop de ressources: CPU, mémoire).

Mais, l'essentiel c'est de savoir que l'on ne fait pas de BI dans un système transactionnel.

A vous donc de choisir selon le dimensionnement de votre système et le type d'environnement.

[marieo]

Bonjour,
J'ai finalement fait une vue "simple" car les données seront peu souvent analysées alors qu'elles représentent un gros volume.

Merci à tous pour votre aide et les précieux conseils

MarieO