[9i] Aggrégat trié par date

**jlinho2** · 30/04/2008, 09h57

Bonjour à tous,

soit une table T comme ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
------------------------------------------------------
SOCIETE           VALEUR                DATE
------------------------------------------------------
X                       12                    22/10/2005
X                       17                    12/01/2004
X                       22                     25/07/2007
Y                       12                    22/10/2005
Y                      17                      12/03/2006
Y                       22                     20/07/2007
Y                       22                     05/07/2007
...

et une fonction d'agrégat du style: http://pages.infinit.net/orautils/pages/stragg.htm (Fonction qui concatène les valeurs d'une colonne d'agrégat)

Je voudrais obtenir pour chaque SOCIETE une ligne:

Code :

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

1
2
3
 
X  [Liste des VALEURs triées par date]  [Liste des DATEs triées]
Y  [Liste des VALEURs triées par date]  [Liste des DATEs triées]

soit pour l'exemple:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
------------------------------------------------------
SOCIETE      VALEURS                DATES
------------------------------------------------------
X            17;12;22       12/01/2004;22/10/2005;25/07/2007
 
Y         12;17;22;22      22/10/2005;12/03/2006;05/07/2007;20/07/2007
 
 
...

en gros - en omettant le tri - la requête serait:

Code :

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

1
2
3
4
 
select societe, stragg(valeur),stragg(TO_CHAR(date))
from T
group by societe

N.B: En réalité la fonction d'agrégat que j'utilise à la place de stragg créé un VARRAY au lieu d'une concaténation.

Merci d'avance pour votre aide.

**laurentschneider** · 30/04/2008, 11h09

LA VERSION !!!

**jlinho2** · 30/04/2008, 14h48

Version 9i désolé...

**laurentschneider** · 30/04/2008, 16h00

ok, bon, soit tu écris ta propre fonction qui trie, ou alors tu te la tentes à la sauce XML

Code :

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

1
2
3
4
5
6
7
8
create table t as select 'X' societe,12 valeur,
  to_date('22/10/2005','DD/MM/YYYY') d from dual union all
select 'X',17,to_date('12/01/2004','DD/MM/YYYY') from dual union all
select 'X',22,to_date('25/07/2007','DD/MM/YYYY') from dual union all
select 'Y',12,to_date('22/10/2005','DD/MM/YYYY') from dual union all
select 'Y',17,to_date('12/03/2006','DD/MM/YYYY') from dual union all
select 'Y',22,to_date('20/07/2007','DD/MM/YYYY') from dual union all
select 'Y',22,to_date('05/07/2007','DD/MM/YYYY') from dual;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
select societe,
  replace(replace(replace(replace(
    xmlagg(sys_xmlgen(to_char(valeur)) order by valeur),
    chr(10)),
    '</ROW><ROW>',','),'</ROW>'),'<ROW>') valeur,
  replace(replace(replace(replace(
    xmlagg(sys_xmlgen(to_char(d,'DD/MM/YYYY')) order by d),
    chr(10)),
    '</ROW><ROW>',','),'</ROW>'),'<ROW>') d
from t
group by societe;

Code :

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

1
2
3
4
S VALEUR          D
- --------------- ---------------------------------------------
X 12,17,22        12/01/2004,22/10/2005,25/07/2007
Y 12,17,22,22     22/10/2005,12/03/2006,05/07/2007,20/07/2007

**jlinho2** · 30/04/2008, 16h36

MERCI,

alors c'est ce que je voulais mais plutôt ORDER BY d pour les dates et les valeurs. Donc nickel.

Est-ce que je peux définir ma propre fonction d'aggrégat qui - comme xmlagg - accepte une clause "order by" dans son argument ? (désolé de mon ignorance)

Pour ma propre "fonction qui trie", doit-elle prendre des objets (date,valeur) en paramètre

?

**mnitu** · 30/04/2008, 17h02

En suivant StraggQueries et sans XML

Code :

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

1
2
3
4
5
6
7
8
9
10
 
Select societe, Max(Ltrim(sys_connect_by_path(valeur,','),',')) valeur, 
       Max(Ltrim(sys_connect_by_path(d,','),',')) d
From (
  Select societe, valeur,d , row_number() over (partition by societe order by d) rn
  From t_mni
  )
Start with rn = 1
Connect By societe = prior societe And prior rn = rn - 1
Group by societe

**laurentschneider** · 30/04/2008, 17h12

Envoyé par jlinho2

Est-ce que je peux définir ma propre fonction d'aggrégat qui - comme xmlagg - accepte une clause "order by" dans son argument ? (désolé de mon ignorance)

hélas non

[Edit]Mnitu a écrit :[/Edit]

...sans xml...

Je veux bien que d'utiliser XML pour retourner une chaine a partir d'une chaine c'est un peu barbare, mais alors employer une hiérarchie, c'est de l'hérésie

Bon week-end prolongé

**McM** · 30/04/2008, 17h23

C'est pas moi qui ait écrit, c'est Mnitu monsieur !

**mnitu** · 30/04/2008, 17h28

Envoyé par laurentschneider

...

Je veux bien que d'utiliser XML pour retourner une chaine a partir d'une chaine c'est un peu barbare, mais alors employer une hiérarchie, c'est de l'hérésie

Bon week-end prolongé

Que le Dieu du SQL nous pardonne

Par contre tu vas nous expliquer pour quoi (sinon c’est l’inquisition

).

**mnitu** · 30/04/2008, 17h34

Envoyé par McM

C'est pas moi qui ait écrit, c'est Mnitu monsieur !

Voilà on voit bien qu’il a déjà eu peur d’être brûlé

**laurentschneider** · 30/04/2008, 17h44

bon point pour toi McM

mnitu

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT societe,
  REPLACE(REPLACE(REPLACE(REPLACE(
    xmlagg(sys_xmlgen(to_char(valeur)) ORDER BY valeur),
    chr(10)),
    '</ROW><ROW>',','),'</ROW>'),'<ROW>') valeur,
  REPLACE(REPLACE(REPLACE(REPLACE(
    xmlagg(sys_xmlgen(to_char(d,'DD/MM/YYYY')) ORDER BY d),
    chr(10)),
    '</ROW><ROW>',','),'</ROW>'),'<ROW>') d
FROM t
GROUP BY societe;

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
S VALEUR               D
- -------------------- --------------------------------------------------
X 12,17,22             12/01/2004,22/10/2005,25/07/2007
Y 12,17,22,22          22/10/2005,12/03/2006,05/07/2007,20/07/2007
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3156910365
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    20 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |     2 |    20 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     7 |    70 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Code :

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

1
2
3
4
5
6
7
8
9
SELECT societe, Max(Ltrim(sys_connect_by_path(valeur,','),',')) valeur, 
       Max(Ltrim(sys_connect_by_path(d,','),',')) d
FROM (
  SELECT societe, valeur,d , row_number() over (partition BY societe ORDER BY d) rn
  FROM t
  )
Start WITH rn = 1
Connect BY societe = prior societe AND prior rn = rn - 1
GROUP BY societe;

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
36
S VALEUR               D
- -------------------- --------------------------------------------------
Y 12,17,22,22          22-OCT-05,12-MAR-06,05-JUL-07,20-JUL-07
X 17,12,22             12-JAN-04,22-OCT-05,25-JUL-07
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3980634221
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     2 |    72 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY             |      |     2 |    72 |     3  (34)| 00:00:01 |
|*  2 |   CONNECT BY WITH FILTERING|      |       |       |            |          |
|*  3 |    VIEW                    |      |     7 |   511 |     3  (34)| 00:00:01 |
|*  4 |     WINDOW SORT PUSHED RANK|      |     7 |   112 |     3  (34)| 00:00:01 |
|   5 |      TABLE ACCESS FULL     | T    |     7 |   112 |     2   (0)| 00:00:01 |
|*  6 |    HASH JOIN               |      |       |       |            |          |
|   7 |     CONNECT BY PUMP        |      |       |       |            |          |
|   8 |     VIEW                   |      |     7 |   252 |     3  (34)| 00:00:01 |
|   9 |      WINDOW SORT           |      |     7 |    70 |     3  (34)| 00:00:01 |
|  10 |       TABLE ACCESS FULL    | T    |     7 |    70 |     2   (0)| 00:00:01 |
|  11 |    VIEW                    |      |     7 |   420 |     3  (34)| 00:00:01 |
|  12 |     WINDOW SORT            |      |     7 |   112 |     3  (34)| 00:00:01 |
|  13 |      TABLE ACCESS FULL     | T    |     7 |   112 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SOCIETE"=PRIOR "SOCIETE" AND "RN"-1=PRIOR "RN")
   3 - filter("RN"=1)
   4 - filter(ROW_NUMBER() OVER ( PARTITION BY "SOCIETE" ORDER BY "D")<=1)
   6 - access("SOCIETE"=PRIOR "SOCIETE" AND "RN"-1=PRIOR "RN")

alors je te dis pas quand ton code est dans une vue d'une vue d'une vue dans une sous-requête de requête...

**McM** · 30/04/2008, 19h52

Depuis que j'ai eu une ORA-0600 sur un connect by et que la solution proposée d'oracle me ramenait moins de lignes.. j'aime pas trop les hiérarchies.

**mnitu** · 05/05/2008, 17h25

Ok, c’est moins performant mais parler d‘hérésie c’est excessive.

**laurentschneider** · 05/05/2008, 17h43

pas du tout, c'est un euphémisme

http://asktom.oracle.com/pls/asktom/...#7982216636963

Les solutions sql analytiques sont dégueu ...

Non, row_number avec connect by, c'est une catastrophe programmée et en cours d'algo ça mérite un zéro pointé

**laurentschneider** · 05/05/2008, 17h46

Envoyé par mnitu

tkyte.blogspot.com/2006/08/evolution.html

dans le lien référencé tu peux lire le commentaire que j'ai écrit en 2006, je n'ai pas changé d'avis.

**mnitu** · 05/05/2008, 22h04

Envoyé par laurentschneider

pas du tout, c'est un euphémisme

http://asktom.oracle.com/pls/asktom/...#7982216636963

Laurent,

Ton lien pointe vers un réponse du Tom Kyte datant du 2003. Mon lien pointe vers un lien datant du 2006 du blog du même Tom Kyte.

Envoyé par laurentschneider

...
Les solutions sql analytiques sont dégueu ...

Non, row_number avec connect by, c'est une catastrophe programmée et en cours d'algo ça mérite un zéro pointé

Je pense que tu doit t'expliquer parce que comme tu viens de le dire ça ne passe pas.

Dois je comprendre que les solutions analytiques sont l'empire du MAL ?

**mnitu** · 05/05/2008, 22h07

Envoyé par laurentschneider

dans le lien référencé tu peux lire le commentaire que j'ai écrit en 2006, je n'ai pas changé d'avis.

Si, si t'a changé d'avis (relit ce que t'a dit sur l'ensemble du problème et non pas seulement sur le connect by) mais ce n'est pas grave. Seulement les beufs ne change jamais d'avis.

**laurentschneider** · 05/05/2008, 23h25

Le lien de 2003 est la premiere apparition connue de ROW_NUMBER et CONNECT BY, par Mikito Harakiri.

Tu peux me dire en quoi j'ai change d'avis?

L'approche STRAGG est tres propre. XMLAGG est une stragg existant dans le noyeau oracle et a l'avantage d'integrer un order by.

Pour definir soit meme un stragg avec order by, c'est possible, avec une astuce du style

MON_STRAGG_TRIE(MON_TYPE(COL1,COL2_POUR_LE_TRI))

quant a savoir si c'est aussi performant que XMLAGG, je prefere ne pas me prononcer avec d'avoir teste.

Je me demande pourquoi autant de monde veut utiliser des trucs du styles? Ca ne serait pas plus simple de faire un SELECT * FROM T et de formatter l'output avec l'application client?

**mnitu** · 06/05/2008, 11h30

Envoyé par laurentschneider

...XMLAGG est une stragg existant dans le noyeau oracle et a l'avantage d'integrer un order by.
...

Laurent si tu penses que c’est vrai t’a raison, tu n’as pas changé d’avis. Moi je ne le pense pas et donc je considère que utiliser XMLAGG est un façon détourné de solutionner le problème (regarde le REPLACE de CHR(10) pour exemple). Désolé mais je considère que c’est une solution pour le forum ou utile dans certaines requêtes ad hoc (comme l’autre d’ailleurs avec les analytiques et le CONNECT BY) et non pas à mettre en production.
Il est possible de solutionner le problème en modifiant StrAgg pour y intégrer le Order By : le site de Tom Kyte contient le code déjà. C’est une des vraies solutions.
Par contre je te rejoins à 100% sur ta dernière remarque, jusqu’au maintenant les seuls fois où j’ai eu besoin de ce truc j’ai pu le résoudre au niveau applicatif.

Et en passant merci pour avoir pointé le Order By dans XmlAgg dans ma première lecture de la documentation Oracle il était passé inaperçu. Ca peut s’avérer utile pour les développements XML.

**laurentschneider** · 06/05/2008, 11h42

pour le replace, c'est moche, j'en conviens. une solution plus propre serait d'employer XMLTRANSFORM est une stylesheet

[9i] Aggrégat trié par date

SQL Oracle

Discussions similaires

Partager

Partager