Vous n'êtes pas identifié(e).
Bonjour.
J'espère que je suis dans la bonne discussion
Après avoir galéré avec la migration de MariaDB vers POstGreSQL, j'ai voulu reprendre une requête créée en SQL approximatif MariaDB mais bien évidemment ça ne fonctionne pas.
C'est une requête qui doit permettre de récupérer les données de plusieurs tables à deux dates différentes sur une même ligne.
SELECT ind.name as "Name"
, ind.sex as "Sex"
, ind.is_dead as "Dead"
, S1.troop_ID as "Troop"
, min(S1.date) as "First observation"
, S1.present as "Present"
, max(S2.date) as "Last observation"
, S2.present as "Present"
FROM individuals as ind
inner join sightings as S1
on S1.individual_ID = ind.individual_ID
left join sightings as S2
on S2.individual_ID = ind.individual_ID
and S2.date> S1.date
group by ind.name
, ind.sex
, S1.troop_id
order by ind.name
, S1.date
, S2.date
;Y a t'il un moyen de contourner le problème des champs qui ne sont pas dans le group by ?
Merci d'avance
Dernière modification par PEREZ J. (08/03/2023 13:40:31)
Hors ligne
Bonjour.
J'espère que je suis dans la bonne discussion
Après avoir galéré avec la migration de MariaDB vers POstGreSQL, j'ai voulu reprendre une requête créée en SQL approximatif MariaDB mais bien évidemment ça ne fonctionne pas.
C'est une requête qui doit permettre de récupérer les données de plusieurs tables à deux dates différentes sur une même ligne.SELECT ind.name as "Name" , ind.sex as "Sex" , ind.is_dead as "Dead" , S1.troop_ID as "Troop" , min(S1.date) as "First observation" , S1.present as "Present" , max(S2.date) as "Last observation" , S2.present as "Present" FROM individuals as ind inner join sightings as S1 on S1.individual_ID = ind.individual_ID left join sightings as S2 on S2.individual_ID = ind.individual_ID and S2.date> S1.date group by ind.name , ind.sex , S1.troop_id order by ind.name , S1.date , S2.date ;Y a t'il un moyen de contourner le problème des champs qui ne sont pas dans le group by ?
Merci d'avance
Au lieu de faire du min() max(), faire un WERE NOT EXISTS (SELECT 1 FROM sightings as S1b WHERE S1.date > S1b.date and S1.individual_ID = S1b.individual_ID )
Hors ligne
Je pense comprendre ce que tu veux faire avec le where mais je ne vois pas comment ça peut fonctionner.
Pour tester, j'ai utilisé une requête plus simple qui est censée afficher pour chaque individu la date de première observation et la dernière ce qui donne:
SELECT ind.name as "Name"
, S1.date as "First observation"
, S2.date as "Last observation"
FROM individuals as ind
inner join sightings as S1
on S1.individual_ID = ind.individual_ID
left join sightings as S2
on S2.individual_ID = ind.individual_ID
WHERE NOT EXISTS
(SELECT 1 FROM sightings as S1b WHERE S1.date > S1b.date and S1.individual_ID = S1b.individual_ID )
order by ind.name
, S1.date
, S2.date
; Mais ça affiche X lignes par individu avec la date de première observation et toutes les autres dates d'observation
Et si je mets S2 à la place de S1b ça ne change rien, en intégrant le WHERE dans le inner join S2 non plus, pareil si je ne fais aucun inner join.
J'ai même essayé de remplacer 1 par S2.date ou s1b.date mais ça ne change rien
Hors ligne
Peut-être qu'un fenêtrage serait une bonne solution ici. A ce que je comprends on cherche la 1ere et dernière date d'observation par individu, et en même temps certaines autres colonnes liées à ces observations (c'est là que mysql simplifie les choses alors que le standard non)
L'idée serait de déclarer une fenêtre de ce genre là:
WINDOW w AS (PARTITION BY individual_ID ORDER BY date ROWS between unbounded preceding and unbounded following)Ensuite on va parcourir un seule fois la table sightings (pas d'auto-jointure) et demander la 1ere et dernière ligne de chaque partition, avec ce genre d'expression
SELECT
first_value(date) OVER w as "first date",
last_value(date) OVER w as "last_date",
first_value(autre_colonne) OVER w As "valeur d'une autre colonne correspondant à first date",
last_value(autre_colonne) OVER w As "valeur d'une autre colonne correspondant à last date",
etc...
FROM sigthings
WINDOW w AS (PARTITION BY individual_ID ORDER BY date ROWS between unbounded preceding and unbounded following)Ensuite pour aggréger ce résultat et ne garder qu'une ligne par partition on peut ajouter un étage supérieur DISTINCT ON (voir https://www.postgresql.org/docs/current … -DISTINCT). Généralement on ordonne les lignes présentées à DISTINCT ON par l'expression de déduplication mais dans ce cas particulier il n'est même pas nécessaire de faire un ORDER BY parce que toutes les lignes relatives à un même individual_ID (même partition) vont être égales.
A l'étage encore au-dessus il y aurait une jointure avec la table individuals via individual_ID pour retrouver les colonnes de cette table du style "Name", "Sex".
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Il va falloir que je regarde le fenêtrage de plus près car je dois afficher des données en croisant deux tables et j'ai l'impression que le fenêtrage n'aime pas trop car je me retrouve avec autant de lignes que dans ma table sightings et si je rajoute un distinct plus que 73, sachant que certains individus n'ont qu'une seule date d'observation et que ma requête MariaDB retournaient 224 lignes
Hors ligne
Dans la requête du message #1 il y a un S1.troop_id impliqué dans le GROUP BY qui potentiellement change le nombre de lignes du résultat. Et autant pour les autres colonnes on comprend intuitivement à quoi elles correspondent, autant pour celle-là il faudrait des explications pour savoir quoi faire avec et comprendre en quoi elle influe sur le résultat.
Pour l'absence possible de 2eme date d'observation, en effet c'est un LEFT JOIN entre S1 et S2 donc S2 va avoir des colonnes correspondantes à NULL dans ces cas. C'est gérable avec le fenêtrage, pour ces cas la colonne first_date va être égale à last_date pour le même individu. Les colonnes de la 2nde observation inexistante pourraient être mises à NULL dans un étage supérieur de la requête avec des expressions du style CASE WHEN date2=date1 THEN null else date2 END.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Le S1.troop_id dans le group by est lié au message d'erreur la colonne S1.troop_id doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat tout comme tous les champs autres que ind.name ou min/max.
Sinon je viens de me rendre compte que les données ne s'étaient pas correctement exportées de MariaDb quand il y avait plus de 1000 enregistrements dans une table.
Je relance l'exportation et l'importation et relance la requête avec fenêtrage.
Dernière modification par PEREZ J. (08/03/2023 12:43:26)
Hors ligne
Merci, c'est bon, j'ai retrouvé tous les enregistrements MariaDB avec la requête :
SELECT distinct
first_value(name) OVER w as "Name FirstObs",
first_value(sex) OVER w as "Sex FirstObs",
first_value(is_dead) OVER w as "Dead FirstObs",
first_value(troop_ID) OVER w as "Troop FirstObs",
first_value(date) OVER w as "First observation",
first_value(present) OVER w as "Present FirstObs",
last_value(name) OVER w as "Name FirstObs",
last_value(sex) OVER w as "Sex LastObs",
last_value(is_dead) OVER w as "Dead LastObs",
last_value(troop_ID) OVER w as "Troop LastObs",
last_value(present) OVER w as "Present LastObs",
last_value(date) OVER w as "Last observation"
FROM individuals, sightings
where sightings.individual_ID = individuals.individual_ID
WINDOW w AS (PARTITION BY sightings.individual_ID, sightings.troop_ID ORDER BY name,date ROWS between unbounded preceding and unbounded following)car un individu peut avoir été observé dans une troupe ou une autre à des dates différentes
Hors ligne
Je pense comprendre ce que tu veux faire avec le where mais je ne vois pas comment ça peut fonctionner.
Pour tester, j'ai utilisé une requête plus simple qui est censée afficher pour chaque individu la date de première observation et la dernière ce qui donne:SELECT ind.name as "Name" , S1.date as "First observation" , S2.date as "Last observation" FROM individuals as ind inner join sightings as S1 on S1.individual_ID = ind.individual_ID left join sightings as S2 on S2.individual_ID = ind.individual_ID WHERE NOT EXISTS (SELECT 1 FROM sightings as S1b WHERE S1.date > S1b.date and S1.individual_ID = S1b.individual_ID ) order by ind.name , S1.date , S2.date ;Mais ça affiche X lignes par individu avec la date de première observation et toutes les autres dates d'observation
Et si je mets S2 à la place de S1b ça ne change rien, en intégrant le WHERE dans le inner join S2 non plus, pareil si je ne fais aucun inner join.
J'ai même essayé de remplacer 1 par S2.date ou s1b.date mais ça ne change rien
Non, il ne faut pas joindre à gauche sur S2.
Tu fais ta jointure simple sur S1 et S2 :
SELECT ind.name as "Name"
, S1.date as "First observation"
, S2.date as "Last observation"
FROM individuals as ind, sightings S1, sightings S2
WHERE
(
S1.individual_ID = ind.individual_ID
AND S2.individual_ID = ind.individual_ID
AND S2.individual_ID = S1.individual_ID -- ajout de la transitivité au cas où
)
AND NOT EXISTS
(SELECT 1 FROM sightings as S1b WHERE S1.date > S1b.date and S1.individual_ID = S1b.individual_ID )
AND NOT EXISTS
(SELECT 1 FROM sightings as S2b WHERE S2.date < S2b.date and S1.individual_ID = S1b.individual_ID )
order by ind.name
, S1.date
, S2.date
; Sauf erreur de ma part cela devrait donner ce que tu recherches.
En règle générale, il faut éviter le "WHERE t1.sequence = (SELECT max(t2.squence) FROM t2 WHERE t1.id = t2.id)" parce que le max() est volatile, donc recalculé à chaque fois. Donc si ton ID possède 100 séquences, tu as un produit cartésien 100x100 = 10.000
Avec un "NOT EXISTS (SELECT 1 FROM t2 WHERE t1.sequence > t2.sequence and t1.id = t2.id )" chaque mauvais candidat sera éliminé dès qu'il sera trouvé une ligne avec une séquence supérieure. Donc, si tu as beaucoup de chance tu vas avoir un 100 + 99*1 = 199 ; et si tu es vraiment poissard tu vas avoir un 100x100=10.000.
Donc dans le pire des cas, tu te retrouves dans le cas du max() qui lui est de toute façon le pire des cas de manière constante.
"
Dernière modification par herve.lefebvre (10/03/2023 16:35:06)
Hors ligne
Cette requête marche effectivement bien aussi sauf si un même individu est aperçu dans une autre troupe entre la première et la dernière observation...mais ça c'est une autre histoire ;-)
SELECT ind.name as "Name"
, ind.sex as "Sex"
, S1.date as "First observation"
, S1.troop_ID as "Troop FirstObs"
, S1.present as "Present FirstObs"
, S2.date as "Last observation"
, S2.troop_ID as "Troop LastObs"
, S2.present as "Present LastObs"
FROM individuals as ind, sightings S1, sightings S2
WHERE
(
S1.individual_ID = ind.individual_ID
AND S2.individual_ID = ind.individual_ID
AND S2.individual_ID = S1.individual_ID -- ajout de la transitivité au cas où
)
AND NOT EXISTS
(SELECT 1 FROM sightings as S1b WHERE S1.date > S1b.date and S1.individual_ID = S1b.individual_ID )
AND NOT EXISTS
(SELECT 1 FROM sightings as S2b WHERE S2.date < S2b.date and S1.individual_ID = S2b.individual_ID )
order by ind.name
, S1.date
, S2.date
; Merci
Hors ligne