Cycle detected while executing recursive WITH query

Version imprimable

Bonjour à tous,

Pour m'initier aux requêtes récursives, je me suis penché sur une problématique de gestion des snapshots de DB.
J'ai réussi à pondre mon bonheur sur Postgres mais le passage à Oracle me pose problème.

Ci-dessous les éléments suivants :

Structure:
Code:

1 2 3 4 5 CREATE TABLE snap_history ( id serial primary key, db varchar(16), date_snap date );
Jeu de données:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 INSERT INTO snap_history(db,date_snap) VALUES ('A','2019-11-29'), ('A','2019-11-29'), ('A','2019-11-29'), ('A','2019-11-29'), ('B','2019-11-29'), ('B','2019-11-29'), ('C','2019-11-29'), ('B','2019-11-30'), ('B','2019-11-30'), ('C','2019-11-30'), ('A','2019-12-01'), ('A','2019-12-01'), ('B','2019-12-01'), ('C','2019-12-01'), ('B','2019-12-02'), ('C','2019-12-02'), ('B','2019-12-03'), ('B','2019-12-03'), ('B','2019-12-04'), ('C','2019-12-04'), ('C','2019-12-04');
Résultat attendu:
Code:

1 2 3 4 5 6 7 8 9 10 +------------+----------+----------------+--------+ | date | nbInARow | dbMaxDailySnap | nbSnap | +------------+----------+----------------+--------+ | 2019-11-29 | 3 | A | 4 | | 2019-11-30 | 2 | B | 2 | | 2019-12-01 | 2 | A | 2 | | 2019-12-02 | 2 | B | 1 | | 2019-12-03 | 1 | B | 1 | | 2019-12-04 | 1 | C | 2 | +------------+----------+----------------+--------+
Explications:
pour chaque jour, je veux:
- nbInARow: nombre de DB ayant été snapshotée chaque jour consécutif (au moment de la date considérée. On aura donc un résultat dégressif dans le temps). Ex: si A est snapshoté le jour J mais pas le jour J+1, à partir de J+1, il ne sera plus jamais comptabilisé
- dbMaxDailySnap: la DB ayant été le plus snapshotée ce jour là
- nbSnap: le nombre de snap ce jour là pour la db <dbMaxDailySnap>

Ma requête (si au passage, elle est inexacte ou optimisable, je veux bien vos critiques):
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 WITH RECURSIVE -- pour Postgres uniquement date_range(ds,db) AS ( ( SELECT DISTINCT MIN(date_snap), db FROM snap_history GROUP BY db ) UNION ( SELECT b.date_snap, a.db FROM date_range a JOIN snap_history b ON b.db = a.db WHERE a.ds+1 = b.date_snap AND b.date_snap <= (SELECT MAX(date_snap) FROM snap_history) ) ), top1(ds,db,nb) AS ( SELECT date_snap,db,COUNT(date_snap) FROM snap_history GROUP BY date_snap,db ORDER BY date_snap,COUNT(date_snap) DESC,db ) SELECT d.ds, COUNT(d.db), t.db, t.nb FROM date_range d JOIN top1 t ON t.ds = d.ds WHERE (t.nb,t.db) = (SELECT MAX(nb),db FROM top1 WHERE ds=d.ds GROUP BY db ORDER BY MAX(nb) DESC,db ASC LIMIT 1) GROUP BY d.ds, t.db, t.nb ORDER BY d.ds, t.nb DESC, t.db ;
Quand j'exécute ceci sous Postgres 9.5 et 9.6, j'ai le résultat escompté.
Quand je l'exécute sous Oracle 12.2 (en retirant le mot-clé RECURSIVE), j'obtiens l'erreur:

Citation:

ORA-32044: cycle detected while executing recursive WITH query

Quelqu'un saurait-il m'expliquer ?
Je comprends qu'il y a un cycle mais il me semblait l'avoir fixé avec la clause

Code:

b.date_snap <= (SELECT(MAX(date_snap) FROM snap_history)

(ou alors je n'ai pas compris l'erreur) ?

Bien à vous,
Asphator

Je trouve quelques incohérences entre votre jeu de test et le résultat attendu.

nbInARow du 2 décembre, vous indiquez 2 mais je ne vois que la base B.
nbSnap du 2 et 3 décembre, vous indiquez 1 mais je vois deux snaps pour la base B toujours.

Avec ces corrections, je n'ai pas eu besoin de faire de requête récursive :

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
with cte_base as
(
  select date_snap
       , case date_snap - 1 when lag(date_snap, 1, date_snap - 1) over(partition by db order by date_snap asc) then 1 else 0 end as is_consecutive
       , db
       , count(*) as nb
    from snap_history
group by date_snap
       , db
)
  ,  cte_step as
(
select date_snap
     , min(is_consecutive) over(partition by db order by date_snap asc rows unbounded preceding) as is_consecutive
     , db, nb
  from cte_base
)
  select date_snap
       , sum(is_consecutive)                             as nbInARow
       , min(db) keep(dense_rank first order by nb desc) as dbMaxDailySnap
       , max(nb)                                         as nbSnap
    from cte_step
group by date_snap
order by date_snap asc;
 
date_snap   nbInARow  dbMaxDailySnap  nbSnap
----------  --------  --------------  ------
2019-11-29         3  A                    4
2019-11-30         2  B                    2
2019-12-01         2  A                    2
2019-12-02         1  B                    2
2019-12-03         1  B                    2
2019-12-04         1  C                    2

05/12/2019, 15h17
Asphator

Bonjour Waldar,

Bien vu, une coquille s'est glissée ligne 17 de mon jeu de données, il s'agit d'un C non d'un B (j'ai corrigé).
Avec votre requête, cela semble faire le job. Je vais analyser ça, il y a des notions que je ne maîtrise pas.
Merci beaucoup pour cette réactivité et efficacité !

Bonne journée

EDIT: ok, c'est cette notion de "LAG() OVER(PARTITION)" qui me manquait pour ne pas avoir à faire le récursif. C'est en effet beaucoup plus élégant. Merci :)
Après, si je peux me permettre de pousser plus loin, pourquoi sur Oracle j'ai dû faire face à cette erreur? quelle différence avec Postgres (niveau moteur j'imagine) ?
23/12/2019, 17h10
vanagreg

Bonjour,

J'ai essayé ta requête sur une base Oracle 12.2 en enlevant le mot clé RECURSIVE mais elle ne fonctionne pas. J'obtiens l'erreur ORA-00920: invalid relational operator.
Une requête CTE dans Oracle contient le point de départ et la récursivité dans le même bloc WITH, en utilisant l'opérateur UNION ALL. Peux-tu donner la requête que tu utilises sous Oracle?
Merci