Discussion :

[Asphator]

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

[Waldar]

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 :

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
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

[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) ?

[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