IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Oracle Discussion :

Fonctions analytiques SQL . . .


Sujet :

Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 998
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 998
    Billets dans le blog
    6
    Par défaut Fonctions analytiques SQL . . .
    Bonjour,

    n'ayant pas d'oracle sous la main, je cherche à vérifier si la requête suivante fonctionne sous Oracle.

    Le principe est de calculez pour chaque produit et par années/mois le chiffre d'affaire de la période et le nombre de ventes, ainsi que leurs cumuls depuis l'origine. La table S_COM.T_VENTE_VTE comporte les colonnes :
    (VTE_DATE, VTE_PRODUIT, VTE_MONTANT)...

    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
    CREATE SCHEMA S_COM;
     
    CREATE TABLE S_COM.T_VENTE_VTE
    (VTE_DATE    DATE,
     VTE_PRODUIT VARCHAR(32),
     VTE_MONTANT DECIMAL(16,2))
     
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-12', 'Zebulon', 33.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-13', 'Zebulon', 44.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-14', 'Zebulon', 22.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-02-12', 'Zebulon', 55.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-02-15', 'Zebulon', 11.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-02-18', 'Zebulon', 88.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-03-10', 'Zebulon', 32.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-10', 'Agormat', 21.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-12', 'Agormat', 47.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-14', 'Agormat', 53.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-01-15', 'Agormat', 29.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-02-12', 'Agormat', 16.21)   
    INSERT INTO S_COM.T_VENTE_VTE VALUES ('2007-02-15', 'Agormat', 19.21)
    Voici la requête théorique à la norme SQL:2003 :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SELECT VTE_PRODUIT,
           EXTRACT(YEAR FROM VTE_DATE)                  AS AN,
           EXTRACT(MONTH FROM VTE_DATE)                 AS MOIS,
           SUM(VTE_MONTANT) OVER(W_SUMCNT)              AS TOTAL_MONTANT, 
           SUM(SUM(VTE_MONTANT)) OVER (W_CUMULS)        AS CUMUL_MONTANT,
           COUNT(ALL VTE_MONTANT) OVER(W_SUMCNT)        AS QUANTITE,
           COUNT(COUNT(ALL VTE_MONTANT)) OVER(W_CUMULS) AS CUMUL_QUANTITE
    FROM   S_COM.T_VENTE_VTE
    WINDOW W_SUMCNT AS (PARTITION BY VTE_PRODUIT, 
                                   EXTRACT(YEAR FROM VTE_DATE), 
                                   EXTRACT(MONTH FROM VTE_DATE))
           W_CUMULS AS (ORDER BY VTE_PRODUIT UNBOUNDED PRECEDING)
    Voici ce qu'un de mes amis à commencer à pisser :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT VTE_PRODUIT,  
           EXTRACT(YEAR FROM VTE_DATE) AS AN , 
           EXTRACT(MONTH FROM VTE_DATE) AS Mois, 
           SUM(VTE_MONTANT) 
              OVER (PARTITION BY VTE_PRODUIT, 
                    EXTRACT(YEAR FROM VTE_DATE),
                    EXTRACT(MONTH FROM VTE_DATE)) AS TOTAL_MONTANT, 
           COUNT(ALL VTE_MONTANT) 
              OVER (PARTITION BY VTE_PRODUIT ORDER BY 1 ROWS 
                    UNBOUNDED PRECEDING) AS QUANTITE 
     FROM   T_VENTE_VTE;

    Bref, si vous avez du temps ...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  2. #2
    Membre Expert

    Profil pro
    Inscrit en
    Février 2006
    Messages
    3 437
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2006
    Messages : 3 437
    Par défaut
    Oracle est généreux et met à disposition un schéma en version 10g gratuitement (avec quelques dizaines de Mo):
    http://www.developpez.net/forums/sho...d.php?t=287843

  3. #3
    Expert Oracle confirmé

    Homme Profil pro
    Consultant Big Data
    Inscrit en
    Mars 2003
    Messages
    448
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Consultant Big Data
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2003
    Messages : 448
    Par défaut
    Voici la réponse :

    VTE_PRODUIT AN MOIS TOTAL_MONTANT QUANTITE
    Agormat 2007 1 150.84 1
    Agormat 2007 1 150.84 2
    Agormat 2007 1 150.84 3
    Agormat 2007 1 150.84 4
    Agormat 2007 2 35.42 5
    Agormat 2007 2 35.42 6
    Zebulon 2007 1 99.63 1
    Zebulon 2007 1 99.63 2
    Zebulon 2007 1 99.63 3
    Zebulon 2007 2 154.63 4
    Zebulon 2007 2 154.63 5
    Zebulon 2007 2 154.63 6
    Zebulon 2007 3 32.21 7

  4. #4
    Expert Oracle confirmé

    Homme Profil pro
    Consultant Big Data
    Inscrit en
    Mars 2003
    Messages
    448
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Consultant Big Data
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2003
    Messages : 448
    Par défaut
    Personnellement, j'aurais fait ceci :


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    select vte_produit, an, mois,
           montant,  sum (montant)  over (partition by vte_produit order by an, mois) as cumul_montant,
           quantite, sum (quantite) over (partition by vte_produit order by an, mois) as cumul_quantite
    from
    (select vte_produit, extract (year from vte_date) as an, extract (month from vte_date) as mois,
            count (*) as quantite, sum (vte_montant) as montant
     from t_vente_vte
     group by vte_produit, extract (year from vte_date), extract (month from vte_date)
    ) ;
    Le calcul se passe donc en 2 étapes :
    1) regroupement classique par période et produit pour calculer les quantités et les montants,
    2) la requête précédente sert d'In-line View pour y appliquer les fonctions analytiques de cumul sur les quantités et les montants.

Discussions similaires

  1. fonction analytique sql
    Par assiya dans le forum SQL
    Réponses: 2
    Dernier message: 20/05/2010, 14h58
  2. [SQL / PL/SQL] fonction analytique last_value
    Par Nounoursonne dans le forum SQL
    Réponses: 7
    Dernier message: 23/08/2007, 21h18
  3. [SQL] [Oracle 9.2] fonctions analytiques grosses tables
    Par Hugues_78 dans le forum Oracle
    Réponses: 11
    Dernier message: 10/10/2006, 18h27
  4. SQL standard vers les fonctions analytiques
    Par Emmanuel Lecoester dans le forum Oracle
    Réponses: 7
    Dernier message: 02/10/2006, 19h27
  5. Réponses: 4
    Dernier message: 18/08/2005, 16h11

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo