Vous n'êtes pas identifié(e).
Pages : 1
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.
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. 
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
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.
Julien.
https://rjuju.github.io/
Hors ligne
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...)
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Merci pour vos retours je vais tenter d'apporter les éclairages nécessaires,
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)
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 
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
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 ?
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"
endLa 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"
endUn 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
ENDJe 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 )
ENDPuis 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
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.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
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
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
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}
;@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
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
Pages : 1