PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 30/03/2022 18:31:09

Tioneb
Membre

Update avec requête récursive

Hello,

J'ai deux tables : courses et steps qui permettent de stocker les étapes d'un roadbook.

Les différentes étapes sont liées à la table course et différentes query me permettent de faire des calculs automatiques (geomatiques) mais je bute sur une requête.

Le roadbook (course) stocke la date de départ et avec une requête les différents champs (departure_date, arrival_date) de la table steps sont mis à jour en fonction de deux éléments, la date de départ de l'étape précédente (ou de la course si aucune étape) + le nombre jours prévus sur place.


Lors des enregistrements des étapes, j'obtiens le résultat escompté. La date de la course démarre le 01 mai et on voit que les étapes suivantes sont bien prises en compte avec le nombre de jours.
Capture-d-e-cran-2022-03-17-a-11-15-46.jpg

Là où je coince, c'est lorsque je souhaite modifier la date de départ de la course et répercuter les dates correspondantes sur les différentes étapes. Comme vous pouvez le voir, les champs ne sont pas mis à jour. Ce qui n'est pas étonnant, puisque les champs référents ont été mis à jour.
Capture-d-e-cran-2022-03-17-a-11-26-30.jpg
Si je lance la requête plusieurs fois, j'obtiens le résultat attendu.

J'ai bien tenté de mettre en place une requête récursive avec un update, mais sans succès... voici ma requête :

      WITH course_position(id, arrival_date, departure_date, number_of_days, position)
        AS (SELECT s.id, s.arrival_date, s.departure_date, s.number_of_days, s.position
        FROM steps s
      WHERE course_id = 73
        ORDER BY position )

      UPDATE steps SET
        departure_date =
          CASE
            WHEN steps.position > 0 THEN
              (SELECT departure_date FROM course_position cp WHERE cp.position = steps.position -1) + number_of_days
            ELSE
              (SELECT starting_date_course FROM courses where id = 73) + number_of_days
          END,
        arrival_date =
          CASE
            WHEN steps.position > 0 THEN
              (SELECT departure_date FROM course_position cp WHERE cp.position = steps.position -1)
            ELSE (SELECT starting_date_course FROM courses where id = 73)
          END;

Hors ligne

#2 31/03/2022 04:56:22

rjuju
Administrateur

Re : Update avec requête récursive

Bonjour,


Tout d'abord, d'après votre premier screenshot (un affichage texte serait bien plus simple...), vos dates d'arrivées sont antérieures aux dates de départ, c'est normal ?


Pour le reste difficile à dire vu qu'on est obligé de deviner les structures et contenus de vos tables.

Hors ligne

#3 31/03/2022 09:49:42

dverite
Membre

Re : Update avec requête récursive

Ca semble un cas typique où il faudrait utiliser la fonction de fenêtrage LAG pour récupérer dans la ligne N des valeurs de colonne de la ligne N-1, plutôt que des sous-requêtes. Il est aussi possible de cumuler des valeurs avec SUM sur chaque ligne, par exemple ici la colonne number_of_days.
Voir https://docs.postgresql.fr/14/functions-window.html

L'UPDATE tel que montré semble compter sur le fait que les lignes se mettent à jour dans un certain ordre (la mise à jour de la ligne N a besoin que la ligne N-1 ait été mise à jour avant), ce qui n'est pas vraiment possible.
Par ailleurs, il n'a pas de clause WHERE, donc il met à jour toute la table steps, ce qui semble anormal.


Il serait plus simple de décomposer le problème en écrivant déjà un SELECT qui sort les nouvelles valeurs sans rien mettre à jour. Ensuite la mise à jour peut s'écrire avec un seul UPDATE avec jointure avec les résultats de ce SELECT en sous-requête (UPDATE ... FROM (sous-requête) WHERE...)

Hors ligne

#4 31/03/2022 16:12:45

Tioneb
Membre

Re : Update avec requête récursive

Merci pour vos retours je vais tenter d'apporter les éclairages nécessaires,

rjuju a écrit :

Bonjour,
Tout d'abord, d'après votre premier screenshot (un affichage texte serait bien plus simple...), vos dates d'arrivées sont antérieures aux dates de départ, c'est normal ?

Oui c'est tout à fait normal, les personnes arrivent à une étape et repartent le lendemain ou x jours après (number_of_days)

dverite a écrit :

Ca semble un cas typique où il faudrait utiliser la fonction de fenêtrage LAG pour récupérer dans la ligne N des valeurs de colonne de la ligne N-1, plutôt que des sous-requêtes. Il est aussi possible de cumuler des valeurs avec SUM sur chaque ligne, par exemple ici la colonne number_of_days.
Voir https://docs.postgresql.fr/14/functions-window.html

le number_of_days correspond aux nombres jours passés à l'étape (ce n'est pas un cumul des jours). Je ne connais pas les fonctions lg ou windows, je suis encore débutant wink

dverite a écrit :

L'UPDATE tel que montré semble compter sur le fait que les lignes se mettent à jour dans un certain ordre (la mise à jour de la ligne N a besoin que la ligne N-1 ait été mise à jour avant), ce qui n'est pas vraiment possible.

C'est effectivement la logique. Je détaille en dessous les différents scripts et les tables afférentes

dverite a écrit :

Par ailleurs, il n'a pas de clause WHERE, donc il met à jour toute la table steps, ce qui semble anormal.

Il y a bien une clause where dans le with ce qui limite l'upadte... non ?

dverite a écrit :

Il serait plus simple de décomposer le problème en écrivant déjà un SELECT qui sort les nouvelles valeurs sans rien mettre à jour. Ensuite la mise à jour peut s'écrire avec un seul UPDATE avec jointure avec les résultats de ce SELECT en sous-requête (UPDATE ... FROM (sous-requête) WHERE...)

Si tu veux bien détailler, car là je vois pas vraiment ?

Voici les tables et le process (je travaille sous rails) :

La table course est relié à la table steps, elle permet de définir certains paramètres et récupère les données de chaque steps pour les agréger (liaison de type has_many :steps)

  create_table "courses", force: :cascade do |t|
    t.string "name"
    t.integer "user_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.date "starting_date_course"
    t.bigint "way_id"
    t.float "average_speed"
    t.bigint "poi_start_id"
    t.integer "km_by_day"
    t.integer "number_of_days", default: 1
    t.float "total_kms", default: 0.0
    t.integer "steps_count"
    t.bigint "poi_end_id"
    t.index ["poi_end_id"], name: "index_courses_on_poi_end_id"
    t.index ["poi_start_id"], name: "index_courses_on_poi_start_id"
    t.index ["user_id"], name: "index_courses_on_user_id"
    t.index ["way_id"], name: "index_courses_on_way_id"
  end

La table steps stocke les données propres à chaque étape (liaison de type belongs_to :courses) :

  create_table "steps", force: :cascade do |t|
    t.integer "position"
    t.integer "course_id"
    t.float "length_step", default: 0.0
    t.integer "trace_id"
    t.date "arrival_date"
    t.date "departure_date"
    t.bigint "poi_start_id"
    t.bigint "poi_end_id"
    t.integer "number_of_days", default: 1
    t.index ["course_id"], name: "index_steps_on_course_id"
    t.index ["poi_end_id"], name: "index_steps_on_poi_end_id"
    t.index ["poi_start_id"], name: "index_steps_on_poi_start_id"
  end

Un utilisateur se crée un roadbook (course) et défini la date de départ et le point de départ. Lorsqu'il ajoute une nouvelle étape, il a juste à définir le nombre de jours qu'il souhaite rester à l'étape et les scripts calculent automatiquement les données à afficher.

Après la création d'une nouvelle étape, je lance plusieurs requêtes pour faire les différentes mises à jour :

Le premier script permet de mettre la position des étapes en fonction de la position géographique du point de départ. L'idée c'est par exemple de pouvoir permettre à l'internaute de pouvoir rajouter une inter-étape, sans se soucier de modifier tous calculs afférents.

Ce script est important, puisque c'est ce qui va définir les différentes mises à jour suivantes :

        WITH course_position(id, geom, dist, seqnum)
            AS (SELECT *, row_number()  over (ORDER BY dist) - 1  as seqnum
                FROM (SELECT steps.id,
                ST_Transform(lonlat::geometry, 4326),
                     pois.lonlat <-> 'SRID=4326;POINT (#{course.poi_start.longitude} #{course.poi_start.latitude})'::geometry AS dist
                FROM
                  steps
                INNER JOIN pois ON poi_end_id = pois.id
                where course_id = #{course.id}
                ORDER BY
                dist) as ordered_position )
        UPDATE steps SET position = (SELECT seqnum FROM course_position cp WHERE cp.id = steps.id);

Je mets à jour les différents points de départ en fonction de la ligne précédente

      WITH course_position(id, poi_start_id, poi_end_id, arrival_date, departure_date, number_of_days, position)
        AS (SELECT steps.id, steps.poi_start_id, steps.poi_end_id, steps.arrival_date, steps.departure_date, steps.number_of_days, steps.position
        FROM
        steps
      where course_id = #{course.id}
        ORDER BY
        position )

      UPDATE steps SET
        poi_start_id =
      CASE
        WHEN steps.position > 0 THEN
          (SELECT poi_end_id FROM course_position cp WHERE cp.position = steps.position -1)
        ELSE steps.poi_end_id
      END

Je fais la même chose pour les points d'arrivée. Je ne mets pas le script c'est le même que celui ci-dessus. J'ai été obligé de décomposer en deux scripts puisque la date de départ est définie en fonction de la date d'arrivée.

Suivant la même logique, je mets à jour les dates d'arrivée et de départ, toujours en fonction de l'étape précédente (s'il y en a une) sinon en fonction de date définie dans le roadbook et j'y ajoute le nombre de jours à l'étape (défini par l'utilisateur).

      WITH course_position(id, arrival_date, departure_date, number_of_days, position)
        AS (SELECT steps.id, steps.arrival_date, steps.departure_date, steps.number_of_days, steps.position
        FROM steps
      WHERE course_id = #{course.id}
        ORDER BY
        position )

      UPDATE steps SET
        departure_date =
          CASE
            WHEN steps.position > 0 THEN
              (SELECT departure_date FROM course_position cp WHERE cp.position = steps.position -1) + number_of_days
            ELSE
              (SELECT starting_date_course FROM courses where id = #{course.id}) + number_of_days
          END,
        arrival_date =
          CASE
            WHEN steps.position > 0 THEN
              (SELECT departure_date FROM course_position cp WHERE cp.position = steps.position -1)
            ELSE (SELECT starting_date_course FROM courses where id = #{course.id})
          END;

Je lance enfin le calcul de distance entre chaque étape :

      WITH course_position(id, poi_start_id, poi_end_id, trace_id, position)
        AS (SELECT steps.id, steps.poi_start_id, steps.poi_end_id, steps.trace_id, steps.position
        FROM
        steps
        where course_id = #{course.id}
        ORDER BY
        position )
      UPDATE steps SET length_step = 
          CASE
          WHEN steps.position = 0 THEN 0
          ELSE ROUND(((SELECT
          ST_Distance(tr.path, pta.lonlat) +
          ST_Distance(tr.path, ptb.lonlat) +
          ST_Length(ST_LineSubstring(
            tr.path,
            least(ST_LineLocatePoint(tr.path, pta.lonlat::geometry), ST_LineLocatePoint(tr.path, ptb.lonlat::geometry)),
            greatest(ST_LineLocatePoint(tr.path, pta.lonlat::geometry), ST_LineLocatePoint(tr.path, ptb.lonlat::geometry)))::geography)  AS dst_line
          FROM traces tr, pois pta, pois ptb
          WHERE tr.id = steps.trace_id
            AND pta.id = steps.poi_start_id
            AND ptb.id = steps.poi_end_id ) / 1000)::numeric, 3 )
        END

Puis je lance un petit script pour mettre à jour les données de la course, à savoir la somme du nombre jours ainsi que le total de kms.

Dans le cadre de la création initiale du roadbook, les différents scripts fonctionnent bien et retournent les résultats attendus.

Ma difficulté, c'est si par exemple un utilisateur souhaite modifier sa date de départ et par exemple démarrer 2 jours plus tard. Je dois recalculer les dates en fonction de la nouvelle date de départ mais aussi en fonction du nombre de jours passés à chaque étape.

Dernière modification par Tioneb (31/03/2022 16:21:50)

Hors ligne

#5 31/03/2022 20:46:40

dverite
Membre

Re : Update avec requête récursive

Il y a bien une clause where dans le with ce qui limite l'upadte... non ?

Non la simple présence du WITH ne contraint pas les lignes touchées par l'UPDATE. Si la sous-requête du WITH était utilisée pour corréler dans l'UPDATE alors OK, mais là ce n'est pas le cas (d'ailleurs dans aucune des requêtes du message #4 me semble-t-il).

Regardez dans votre client SQL combien de lignes sont affectées. Normalement cette info est affichée quelque part. Avec psql, ça affiche UPDATE N où N est le nombre de lignes, et de plus ça arrive dans la variable ROW_COUNT.

Au pire, ajoutez RETURNING * à la fin de la requête UPDATE et vous verrez aussi que ça affecte toute la table, par opposition à seulement les lignes correspondant au course_id en paramètre.

Hors ligne

#6 01/04/2022 14:52:25

Tioneb
Membre

Re : Update avec requête récursive

Effectivement, comme je faisais mes tests sur une seule course, je n'avais pas constaté le problème.
J'ai corrigé les requêtes en ce sens. Merci

dverite a écrit :

Ca semble un cas typique où il faudrait utiliser la fonction de fenêtrage LAG pour récupérer dans la ligne N des valeurs de colonne de la ligne N-1

L'UPDATE tel que montré semble compter sur le fait que les lignes se mettent à jour dans un certain ordre (la mise à jour de la ligne N a besoin que la ligne N-1 ait été mise à jour avant), ce qui n'est pas vraiment possible.

Tu dis qu'il vaudrait mieux utiliser la fonction lag plutôt que des sous-requêtes... c'est pour une question de performance ?

Tu dis également que l'update n'est pas possible tel que je l'ai imaginé. Comment ferais tu à ma place ?

Hors ligne

#7 02/04/2022 19:18:46

dverite
Membre

Re : Update avec requête récursive

La question principale est comment mettre à jour "arrival_date" et "departure_date" pour les étapes de la table steps pour une course donnée, quand on a une nouvelle date de course.

"arrival_date" pour chaque étape est égal à "departure_date" de l'étape précédente, sauf s'il n'y a pas d'étape précédente (position=0), auquel cas c'est la date de départ de la course.

"departure_date" est toujours égal à "arrival_date" + "number_of_days".

Il se trouve que les fonctions de fenêtrage permettent de faire un calcul dans une ligne en fonction des autres lignes, suivantes et/ou précédentes.
Je disais en #3 qu'on peut utiliser LAG ou une somme partielle. Je pense que le plus simple dans cette requête est d'utiliser la somme partielle des "number_of_days", avec la formule

arrival_date = date de départ course + sum(number_of_days "précédents") over (order by position)

Donc pour sortir les nouvelles dates de chaque étape, ça peut marcher avec une requête de cette forme:

SELECT
  position,
  CASE WHEN position=0 THEN
    #{date de départ de la course}
  ELSE
    #{date de départ de la course} + (interval '1 day' * sum(number_of_days)
      over (ORDER BY position ROWS between UNBOUNDED PRECEDING and 1 PRECEDING))
  END as new_arrival_date
  FROM steps
WHERE
  course_id = #{ID de la course}

Ensuite pour faire l'UPDATE on utilise simplement les résultats de cette requête avec position et course_id pour faire la jointure.

Syntaxiquement, ça donnerait une requête du style

WITH new_steps AS (
SELECT
  position,
  CASE WHEN position=0 THEN
    #{date de départ de la course}
  ELSE
    #{date de départ de la course} + (interval '1 day' * sum(number_of_days)
      over (ORDER BY position ROWS between UNBOUNDED PRECEDING and 1 PRECEDING))
  END as new_arrival_date
  FROM steps
WHERE
  course_id = #{ID de la course}
)
UPDATE steps
SET
 arrival_date = s.new_arrival_date,
 departure_date = s.new_arrival_date + number_of_days
FROM new_steps
WHERE new_steps.position = steps.position
AND course_id =  #{ID de la course}
;

Hors ligne

#8 04/04/2022 17:00:59

Tioneb
Membre

Re : Update avec requête récursive

Merci Daniel pour ce gros coup de pouce, ça fonctionne aux petits oignons. J'ai un peu exploré la fonction over, mais pas très facile à appréhender.

Hors ligne

Pied de page des forums