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 : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
CREATE TABLE snap_history (
id serial primary key,
db varchar(16),
date_snap date
);
Jeu de données:
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
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 : 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
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:
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 : Sélectionner tout - Visualiser dans une fenêtre à part
b.date_snap <= (SELECT(MAX(date_snap) FROM snap_history)
(ou alors je n'ai pas compris l'erreur) ?

Bien à vous,
Asphator