Pb Fonction analytique last_value

Version imprimable

en 9i:

J'ai un problème de compréhension du LAST_VALUE. Il me semblait que c'était la dernière valeur de la partition. Le FIRST_VALUE étant la première.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH t AS (	SELECT 1 AS a, 1 AS b FROM dual 
			UNION ALL SELECT 1, 2 FROM dual 
			UNION ALL SELECT 1, 3 FROM dual)
SELECT 'FIRST', a, b,
		first_value(b) OVER (PARTITION BY a ORDER BY b) AS B_ASC,
		first_value(b) OVER (PARTITION BY a ORDER BY b DESC) AS b_desc
FROM t
UNION ALL
SELECT 'LAST', a, b,
		last_value(b) OVER (PARTITION BY a ORDER BY b),
		last_value(b) OVER (PARTITION BY a ORDER BY b DESC)
FROM t
 
'FIRST'	A	B	B_ASC	B_DESC
FIRST	1	1	1	3
FIRST	1	2	1	3
FIRST	1	3	1	3
LAST	1	1	1	1
LAST	1	2	2	2
LAST	1	3	3	3

Pour le LAST je suis obligé de mettre une clause de RANGE. Ce qui n'est pas top.

Moi, cela ne me surprend pas plus que cela tu ne définis pas de fenêtrage. Je suppose donc qu'il prend la dernière valeur trouvée jusqu'à la ligne en cours (je n'ai trouvé aucune info dans la doc oracle, c'est juste une analyse personnelle).
Dès que tu définis un fenêtrage cela fonctionne comme tu t'y attends :

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
SQL> WITH t AS ( SELECT 1 AS a, 1 AS b FROM dual 
  2     UNION ALL SELECT 1, 2 FROM dual 
  3     UNION ALL SELECT 1, 3 FROM dual)
  4  SELECT 'FIRST', a, b,
  5    first_value(b) OVER (PARTITION BY a ORDER BY b) AS B_ASC,
  6    first_value(b) OVER (PARTITION BY a ORDER BY b DESC) AS b_desc
  7  FROM t
  8  UNION ALL
  9  SELECT 'LAST', a, b,
 10    last_value(b) OVER (PARTITION BY a ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
 11    last_value(b) OVER (PARTITION BY a ORDER BY b DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
 12  FROM t
 13  /
 
'FIRS          A          B      B_ASC     B_DESC
----- ---------- ---------- ---------- ----------
FIRST          1          1          1          3
FIRST          1          2          1          3
FIRST          1          3          1          3
LAST           1          1          3          1
LAST           1          2          3          1
LAST           1          3          3          1
 
6 ligne(s) sélectionnée(s).

03/08/2007, 14h25
salim11

Salut,

Voici les liens qui parlent de l'utilisation ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
http://download.oracle.com/docs/cd/B...nctions073.htm
http://orafaq.com/node/55

Je pensais que
- Le fenêtrage était fait par le PARTITION BY et que les options ROWS BETWEEN et RANGE BETWEEN étaient des précisions supplémentaires.
- le LAST_VALUE était comme le MAX()

Bref, je vais donc continuer à utiliser le FIRST_VALUE ORDER BY DESC

Citation:

Je suppose donc qu'il prend la dernière valeur trouvée jusqu'à la ligne en cours

Oui mais l'order by devrait fonctionner dans ce cas.
l'exemple du LEAD : Le order by fonctionne.
Code:

1 2 3 4 5 6 7 8 WITH t AS ( SELECT 1 AS a, 1 AS b FROM dual UNION ALL SELECT 1, 2 FROM dual UNION ALL SELECT 1, 3 FROM dual UNION ALL SELECT 1, 4 FROM dual) SELECT a, b, lead(b, 1) OVER (PARTITION BY a ORDER BY b) AS l_asc, lead(b, 1) OVER (PARTITION BY a ORDER BY b DESC) AS l_desc FROM t
Code:

1 2 3 4 5 A B L_ASC L_DESC 1 1 2 1 2 3 1 1 3 4 2 1 4 3

03/08/2007, 14h47
McM

La doc officielle (premier lien) ne donne pas trop d'explication.
Le second lien est mieux mais :

Citation:

Some analytic functions (AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN and SUM among the ones we discussed) can take a window clause to further sub-partition the result

Donc c'est ce que je pensais : La fenêtre c'est le partition by, la sous fenêtre c'est le range.

Citation:

Envoyé par McM

Je pensais que
- Le fenêtrage était fait par le PARTITION BY et que les options ROWS

Pour moi :
- le partition by est équivalent au group by.
- l'option rows définit le type et la précision de la fenêtre (en nombre de lignes)
(cf. article de Laystar)

Citation:

Envoyé par McM

- le LAST_VALUE était comme le MAX()

Ah non, c'est la dernière valeur et non la valeur max, ce qui n'a rien à voir.

Tu noteras que dans la doc oracle, ils indiquent toujours une clause rows, contrairement au lead puisque pour cette fonction tu précises en paramètre la position de lignes à retenir.

Enfin si tu fais :
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 SQL> WITH t AS ( SELECT 1 AS a, 1 AS b FROM dual 2 UNION ALL SELECT 1, 2 FROM dual 3 UNION ALL SELECT 1, 3 FROM dual) 4 SELECT 'FIRST', a, b, 5 first_value(b) OVER (PARTITION BY a ORDER BY b) AS B_ASC, 6 first_value(b) OVER (PARTITION BY a ORDER BY b DESC) AS b_desc 7 FROM t 8 UNION ALL 9 SELECT 'LAST', a, b, 10 last_value(b) OVER (PARTITION BY a ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING), 11 last_value(b) OVER (PARTITION BY a ORDER BY b DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) 12 FROM t 13 / 'FIRS A B B_ASC B_DESC ----- ---------- ---------- ---------- ---------- FIRST 1 1 1 3 FIRST 1 2 1 3 FIRST 1 3 1 3 LAST 1 1 1 1 LAST 1 2 2 2 LAST 1 3 3 3 6 ligne(s) sélectionnée(s).
Ce qui semble corroborer ce que je pensais.

Citation:

Ce qui semble corroborer ce que je pensais

Que pour un même fenêtrage, le ORDER BY n'a aucune influence ?

Sans ORDER BY dans le OVER, le résultat est bon, suivant le tri du SELECT
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 WITH t AS ( SELECT 1 AS a, 1 AS b FROM dual UNION ALL SELECT 1, 2 FROM dual UNION ALL SELECT 1, 3 FROM dual ) SELECT a, b, last_value(b) OVER (PARTITION BY a) l1 FROM t ORDER BY a, b (ASC/DESC) A B L1_ASC 1 1 3 1 2 3 1 3 3 A B L1_DESC 1 3 1 1 2 1 1 1 1
Donc je comprend que le LAST_VALUE n'a pas le même comportement que les autres fonctions à propos du ORDER BY de sa clause.
Et qu'il faut donc utiliser le ROW UNBOUNDED.

Citation:

Envoyé par McM

Et qu'il faut donc utiliser le ROW UNBOUNDED.

C'est surtout cela qu'il faut retenir, je crois !

Je vais essayer d'être clair (c'est pas gagné) :
Pour mois il tient compte du order by mais uniquement dans le fenêtrage global (j'entends par là partition by et rows) : cf. mon 1è post.

Par contre si tu ne définis pas de "rows", il ne ramène que la dernière valeur entre la première ligne traitée et la ligne courante.

Et ce qui est très marrant, c'est suivant l'ordre des colonnes où tu définis le tri, il ne ramène pas la même chose :

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
SQL> WITH t AS ( SELECT 1 AS a, 1 AS b FROM dual
  2     UNION ALL SELECT 1, 4 FROM dual
  3     UNION ALL SELECT 1, 2 FROM dual)
  4  SELECT 'LAST', a, b,
  5    last_value(b) OVER (PARTITION BY a ORDER BY b) b_asc,
  6    last_value(b) OVER (PARTITION BY a ORDER BY b desc) b_desc
  7  FROM t
  8  /
 
'LAS          A          B      B_ASC     B_DESC
---- ---------- ---------- ---------- ----------
LAST          1          1          1          1
LAST          1          2          2          2
LAST          1          4          4          4
 
SQL> WITH t AS ( SELECT 1 AS a, 1 AS b FROM dual
  2     UNION ALL SELECT 1, 4 FROM dual
  3     UNION ALL SELECT 1, 2 FROM dual)
  4  SELECT 'LAST', a, b,
  5    last_value(b) OVER (PARTITION BY a ORDER BY b DESC) b_desc,
  6    last_value(b) OVER (PARTITION BY a ORDER BY b) b_asc
  7  FROM t
  8  /
 
'LAS          A          B     B_DESC      B_ASC
---- ---------- ---------- ---------- ----------
LAST          1          4          4          4
LAST          1          2          2          2
LAST          1          1          1          1

En fait c'est en fonction du tri du SELECT.
S'il n'y a pas de tri, le tri de la première fct analytique implique le tri du select

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH t AS ( SELECT 1 AS a, 1 AS b FROM dual
       UNION ALL SELECT 1, 4 FROM dual
       UNION ALL SELECT 1, 2 FROM dual)
    SELECT a, b,
      last_value(b) OVER (PARTITION BY a ORDER BY b DESC) b_desc,
      last_value(b) OVER (PARTITION BY a ORDER BY b) b_asc
    FROM t
	ORDER BY a, b  
 
A	B	B_DESC	B_ASC
1	1	1	1
1	2	2	2
1	4	4	4