[9i] Aggrégat trié par date

[jlinho2]

Bonjour à tous,

soit une table T comme ceci:

Code :

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 :

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 :

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 :

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]

LA VERSION !!!

[jlinho2]

Version 9i désolé...

[laurentschneider]

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

Code :

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 :

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 :

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]

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]

En suivant StraggQueries et sans XML

Code :

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]

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]

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

[mnitu]

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]

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]

bon point pour toi McM


mnitu

Code :

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 :

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 :

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 :

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]

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]

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

[laurentschneider]

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]

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]

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]

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]

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]

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]

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