Forums PostgreSQL.fr

damalaan

Membre

Gestion des intervalles de dates

Bonjour,

Dans le cadre d'une gestion de stock et d'analyses, j'ai les 2 tables suivantes (simplifiées pour l'exemple):

CREATE TABLE public.lot (
	lot varchar NULL, --numero de lot
	ana varchar NULL, --analyse realise avec le lot
	lot_dh timestamp NULL --date d'activation du lot
)

INSERT INTO public.lot (lot,ana,lot_dh) VALUES 
('lot1','ST','2017-12-31 08:03:54.000')
,('lot2','C','2018-01-05 12:12:54.000')
,('lot3','FT','2018-01-10 12:03:54.000')
,('lot5','C','2018-01-15 15:15:54.000')
,('lot6','FT','2018-01-14 11:13:14.000')
,('lot4','ST','2018-01-02 18:03:54.000')


CREATE TABLE public.ech (
	ana varchar NULL, --analyse
	ech_dh timestamp NULL, --date/heure de realisation de l'analyse
	ech int4 NULL --numero echantillon
)
INSERT INTO public.ech (ana,ech_dh,ech) VALUES 
('ST','2017-12-31 08:02:54.000',1)
,('C','2018-01-05 12:12:59.000',2)
,('FT','2018-01-05 12:12:59.000',2)
,('ST','2018-01-15 17:12:59.000',3)
,('FT','2018-01-15 17:12:59.000',3)
,('C','2018-01-15 17:12:59.000',3)

Le but est de retrouver le lot utilisé pour l'échantillon pour telle ou telle analyse.
Par exemple, on sait donc que le lot4 servira à faire l'analyse ST, et qu'il est actif à partir du 02/01/2017 18:03:54
Donc, tant qu'un nouveau lot réalisant cette analyse n'est pas activé, les échantillons seront faits avec le lot 4.

Pour que l'utilisateur puisse obtenir une traçabilité je pensais donc à cette requête avec une reconstitution d'intervalle de date, et qui permet de retourner les échantillons avec les lots utilisés

WITH a AS(
	SELECT
		lot,
		ana,
		lot_dh AS date_val_deb,
		lead(
			lot_dh,
			1
		) OVER(PARTITION BY ana	ORDER BYlot_dh ASC) AS date_val_fin
	FROM
		lot
	ORDER BY
		ana,
		lot
),
b AS(
	SELECT
		lot,
		ana,
		CASE
			WHEN date_val_deb IS NULL THEN '(,' || date_val_fin || ')'
			WHEN date_val_fin IS NULL THEN '[' || date_val_deb || ',)'
			ELSE '[' || date_val_deb || ',' || date_val_fin || ')'
		END::tsrange AS plage_val_lot
	FROM
		a
) SELECT
	ech,
	lot,
	ech.ana,
	ech_dh,
	plage_val_lot
FROM
	ech
LEFT JOIN b ON
	ech.ana = b.ana
WHERE
	ech_dh < @ plage_val_lot = TRUE

ce qui me donne

ech |lot  |ana |ech_dh              |plage_val_lot                                 |
----|-----|----|--------------------|----------------------------------------------|
2   |lot2 |C   |2018-01-05 12:12:59 |["2018-01-05 12:12:54","2018-01-15 15:15:54") |
3   |lot5 |C   |2018-01-15 17:12:59 |["2018-01-15 15:15:54",)                      |
3   |lot6 |FT  |2018-01-15 17:12:59 |["2018-01-14 11:13:14",)                      |
3   |lot4 |ST  |2018-01-15 17:12:59 |["2018-01-02 18:03:54",)                      |

Y aurait il plus simple ? plus efficace ?
Merci d'avance

Dernière modification par damalaan (18/01/2018 15:35:46)

damalaan

Membre

Re : Gestion des intervalles de dates

Je me réponds après quelques essais !!
J'avais comme l'intuition que LATERAL pourrait venir à mon secours....

SELECT a.ech, lat.lot, a.ana, a.ech_dh,lat.lot_dh FROM ech a, 
	LATERAL (SELECT * FROM lot b WHERE b.ana = a.ana AND a.ech_dh>=b.lot_dh ORDER BY lot_dh DESC LIMIT 1) lat

J'ai toujours du mal à appréhender la logique de LATERAL....si vous avez un bon tuto par là, je suis preneur !