sql analytique , travailler sur des groupes de lignes.

[ZashOne]

Bonjour

Je profite pas de la facilite de developpez.com mais vraiment je suis dans le flou meme pour la recherche sur google.
Comme dans une discussion postée il ya quelques jours, j'ai une table comme suit

ID Check

0 1
1 1
2 0
3 0
4 1
5 0
6 0
7 0
8 1
9 0
10 0

j'aimerai bien qu'une requete sql me sorte un resultat suivant

ID_DEBUT ID_FIN
2 3
5 7

Soit pour chaque intervalle de lignes ayant un check a 0 par id croissant , retrouvé le premier et le dernier id sans tenir compte des dernieres lignes ayant un check a 0.

Merci d' avance

[laurentschneider]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
  min(id) id_debut,max(id) id_fin
FROM
  (
  SELECT id, 
    decode(c,
      0,decode(max(c) over (ORDER BY id DESC),
        1,id-row_number() over (partition BY c ORDER BY id))) x
  FROM t
  )
WHERE x IS NOT NULL
GROUP BY x
ORDER BY 1

[ZashOne]

bonjour laurent

Tout d abord un grand merci . Ca a fonctionne du premier coup.

par contre avec la doc que j ai sous la main je narrive pas a comprendre la partie suivante

Code :

decode(max(c) over (ORDER BY id DESC), 1,id-row_number() over (partition BY c ORDER BY id))

[laurentschneider]

Code :

1
2
3
4
5
6
SELECT id, 
      c,
      row_number() over (partition BY c ORDER BY id) r, 
      max(c) over (ORDER BY id DESC) m
  FROM t
  ORDER BY id

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
        ID          C          R          M
---------- ---------- ---------- ----------
         0          1          1          1
         1          1          2          1
         2          0          1          1
         3          0          2          1
         4          1          3          1
         5          0          3          1
         6          0          4          1
         7          0          5          1
         8          1          4          1
         9          0          6          0
        10          0          7          0

le row_number numérote les lignes pour chaque c, lorsque deux lignes se suivent avec le même code, alors la différence entre le id et le rownumber par partition est constant.
max donne la plus haute valeur de c pour la ligne courant et les suivantes (plus littéralement pour les lignes entre la prémière et la ligne courante trié par ID en ordre descendant)

OK?

[ZashOne]

Oui merci , je ne comprenait pas la partie max(c) en fait . Mais avec ton exemple je comprend que c est l astuce pour ne pas recuperer les dernieres lignes a 0.

J aurai une dernier question si a chaque ligne est associee une date , comment je peut recupere la min(date) et la max(date) pour chaque ligne dans la meme requete.

Pour finir felicitations pour ton livre j ai impatience qu il soit disponible . J ai moi meme achete le "ORACLE TUNING definitive Guide" the D. Burleson et je trouve la qualite des ouvrages de Rampant de tres haut niveau

[laurentschneider]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT 
  min(id) id_debut, min(d) keep (dense_rank first ORDER BY id) d_debut,
  max(id) id_fin, max(d) keep (dense_rank first ORDER BY id DESC) d_fin
FROM
  (
  SELECT id, d,
    decode(c,
      0,decode(max(c) over (ORDER BY id DESC),
        1,id-row_number() over (partition BY c ORDER BY id))) x
  FROM t
  )
WHERE x IS NOT NULL
GROUP BY x
ORDER BY 1;

note si les dates sont par ordre croissant (d[2]>d[1]) alors pas besoin de keep

[ZashOne]

Merci

Je me suis lance dans l aventure avec succes maintenant, je voulais ajouter une collonne "CONTIENT UNE DATE A NULL", j y suis arriver avec la requete suivante

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT 
  min(id) id_debut, min(d) d_debut,
  max(id) id_fin, max(d)  d_fin,
  decode(min(d) keep (dense_rank first ORDER BY d nulls first),NULL,'OUI','NON' ) contient_null
FROM
  (
  SELECT id, d,
    decode(c,
      0,decode(max(c) over (ORDER BY id DESC),
        1,id-row_number() over (partition BY c ORDER BY id))) x
  FROM t
  )
WHERE x IS NOT NULL
GROUP BY x
ORDER BY 1;

par contre ce qui m'etonne, c est que si je remplace

Code :

decode(min(d) keep (dense_rank first ORDER BY d nulls first),NULL,'OUI','NON' )

par

Code :

decode(first_value(d) over (ORDER BY d),NULL,'OUI','NON' )

je recoit une erreur

ORA-00979: not a GROUP BY expression

Je pensait pourtant avoir compris le mecanisme.

De plus lors de mes recherches pour cette requete , je suis tombé sur les clause MODEL et SPREADSHEET.

Ces clauses pourrait elle me permettre d'afficher sous un contient_null = OUI la liste des id qui ont la date a null.

Merci d'avance.