Calculer une évolution

Version imprimable

Bonjour,

Je ne parviens pas à créer une requête me permettant de mesurer une évolution.
Pouvez-vous m'apporter votre aide.

J'ai une table de ce type :

Code:

1
2
3
4
5
6
7
 
ANNEE    ACTE    SURFACE
2000      A         10
2001      V          2
2003      A        100
2004      V         10
2004      A         80

J'aimerai obtenir un retour du genre :

Code:

1
2
3
4
5
6
 
ANNEE    SURFACE_TOTALE
2000         10
2001          8
2003         118
2004         178

Merci,

Case pour +/-, Group By pour agréger par exercice et analytique pour somme évolutive

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> With Data As (
  2    Select 2000 ANNEE, 'A' Acte, 10 SURFACE From dual union all
  3    Select 2001, 'V', 2 From dual union all
  4    Select 2003, 'A', 100 From dual union all
  5    Select 2004, 'V', 10 From dual union all
  6    Select 2004, 'A', 80  From dual
  7  ),
  8  Tot_Par_Exercice As (
  9    Select Annee, Sum( Case Acte
 10                         When 'A' Then Surface
 11                         Else -Surface
 12                       End
 13                      ) Tot_Surface
 14      From Data
 15     Group BY Annee
 16  )
 17  Select Annee, Sum(tot_surface) over(order by annee) Evo_Surf
 18    From Tot_Par_Exercice
 19  /
 
     ANNEE   EVO_SURF
---------- ----------
      2000         10
      2001          8
      2003        108
      2004        178
 
SQL>

Salut,
Si je me plante pas, ça peut s'aggréger en un coup :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SQL MAAT> WITH DATA AS (
  2        SELECT 2000 ANNEE, 'A' Acte, 10 SURFACE FROM dual union ALL
  3        SELECT 2001, 'V', 2 FROM dual union ALL
  4        SELECT 2003, 'A', 100 FROM dual union ALL
  5        SELECT 2004, 'V', 10 FROM dual union ALL
  6        SELECT 2004, 'A', 80  FROM dual
  7      )
  8      SELECT Annee, Sum(sum( case acte when 'A' then surface else -surface end)) over(ORDER BY annee) Evo_Surf
  9       FROM data
 10       group by annee
 11  /
 
     ANNEE   EVO_SURF
---------- ----------
      2000         10
      2001          8
      2003        108
      2004        178

Citation:

Envoyé par pacmann

Salut,
Si je me plante pas, ça peut s'aggréger en un coup :

Oui mais l'optimiseur se charge de cette transformation, il peut donc être plus clair d'un point de vue maintenance de rajouter une étape dans l'écriture d'une requête.

Le 10053 :

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
....
 
******************************************
----- Current SQL Statement for this session (sql_id=3b06d22b72pxp) -----
WITH DATA AS (
SELECT 2000 ANNEE, 'A' Acte, 10 SURFACE FROM dual union ALL
SELECT 2001, 'V', 2 FROM dual union ALL
SELECT 2003, 'A', 100 FROM dual union ALL
SELECT 2004, 'V', 10 FROM dual union ALL
SELECT 2004, 'A', 80  FROM dual
),
Tot_Par_Exercice AS (
SELECT Annee,
       Sum( Case Acte
            When 'A' Then Surface
            Else -Surface
             End
          ) Tot_Surface
  FROM DATA
 GROUP BY Annee
)
SELECT Annee, Sum(tot_surface) over(ORDER BY annee) Evo_Surf
  FROM Tot_Par_Exercice
*******************************************
 
......
 
CVM:   Merging complex view SEL$6 (#0) into SEL$7 (#0).
qbcp:******* UNPARSED QUERY IS *******
SELECT "TOT_PAR_EXERCICE"."ANNEE" "ANNEE",SUM("TOT_PAR_EXERCICE"."TOT_SURFACE") OVER ( ORDER BY "TOT_PAR_EXERCICE"."ANNEE" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW ) "EVO_SURF" FROM  (SELECT "DATA"."ANNEE" "ANNEE",SUM(CASE "DATA"."ACTE" WHEN 'A' THEN "DATA"."SURFACE" ELSE (-"DATA"."SURFACE") END ) "TOT_SURFACE" FROM  ( (SELECT 2000 "ANNEE",'A' "ACTE",10 "SURFACE" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2001 "2001",'V' "'V'",2 "2" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2003 "2003",'A' "'A'",100 "100" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2004 "2004",'V' "'V'",10 "10" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2004 "2004",'A' "'A'",80 "80" FROM "SYS"."DUAL" "DUAL")) "DATA" GROUP BY "DATA"."ANNEE") "TOT_PAR_EXERCICE"
vqbcp:******* UNPARSED QUERY IS *******
SELECT "DATA"."ANNEE" "ANNEE",SUM(CASE "DATA"."ACTE" WHEN 'A' THEN "DATA"."SURFACE" ELSE (-"DATA"."SURFACE") END ) "TOT_SURFACE" FROM  ( (SELECT 2000 "ANNEE",'A' "ACTE",10 "SURFACE" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2001 "2001",'V' "'V'",2 "2" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2003 "2003",'A' "'A'",100 "100" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2004 "2004",'V' "'V'",10 "10" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2004 "2004",'A' "'A'",80 "80" FROM "SYS"."DUAL" "DUAL")) "DATA" GROUP BY "DATA"."ANNEE"
CVM: result SEL$7 (#0)
******* UNPARSED QUERY IS *******
SELECT "DATA"."ANNEE" "ANNEE",SUM(SUM(CASE "DATA"."ACTE" WHEN 'A' THEN "DATA"."SURFACE" ELSE (-"DATA"."SURFACE") END )) OVER ( ORDER BY "DATA"."ANNEE" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW ) "EVO_SURF" FROM  ( (SELECT 2000 "ANNEE",'A' "ACTE",10 "SURFACE" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2001 "2001",'V' "'V'",2 "2" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2003 "2003",'A' "'A'",100 "100" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2004 "2004",'V' "'V'",10 "10" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2004 "2004",'A' "'A'",80 "80" FROM "SYS"."DUAL" "DUAL")) "DATA" GROUP BY "DATA"."ANNEE"
 
....
 
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "DATA"."ANNEE" "ANNEE",SUM(SUM(CASE  WHEN "DATA"."ACTE"='A' THEN "DATA"."SURFACE" ELSE (-"DATA"."SURFACE") END )) OVER ( ORDER BY "DATA"."ANNEE" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW ) "EVO_SURF" FROM  ( (SELECT 2000 "ANNEE",'A' "ACTE",10 "SURFACE" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2001 "2001",'V' "'V'",2 "2" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2003 "2003",'A' "'A'",100 "100" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2004 "2004",'V' "'V'",10 "10" FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT 2004 "2004",'A' "'A'",80 "80" FROM "SYS"."DUAL" "DUAL")) "DATA" GROUP BY "DATA"."ANNEE"
 
....

07/06/2013, 04h24
villegente

Je test tout cela de suite.
Un détail m'embête pour le moment.

Dans l'exemple, j'ai indiqué 2001, 2002, .... Mais j'ai oublié de préciser que cette table est alimentée régulièrement. J'aimerais trouver une solution sans devoir écrire les dates en dur dans la requête.

Une idée ?

Merci,
07/06/2013, 08h48
pacmann
Pas de problème, les dates en dur que tu voies dans les requêtes qui te sont proposées ne sont que des jeux de données. (Ce qui nous permet d'afficher un résultat, vu qu'on ne dispose pas de la vraie table contrairement à toi)

A adapter à ton cas concret, mais ta table s'appelle DATA, cette partie de la requête suffit :
Code:

1 2 3 4 SELECT Annee, Sum(sum( case acte when 'A' then surface else -surface end)) over(ORDER BY annee) Evo_Surf FROM DATA GROUP BY annee

Citation:
Envoyé par pacmann
Code:

1 2 3 4 SELECT Annee, Sum(sum( case acte when 'A' then surface else -surface end)) over(ORDER BY annee) Evo_Surf FROM DATA GROUP BY annee
Je n'avais pas pensé à cette combinaison. Merci beaucoup.