Selection multiple d'un rang unique

Version imprimable

Bonsoir à tous,

Le problème me parait simple, mais je n'ai pas trouvé de solution simple donc je viens demander votre avis :)

Considérons une table :

Code:

id | date | value

Ou id est un serial, date un timestamp et value un varchar

Elle est peuplée de beaucoup de données, qui ont un espacement moyen de quelques minutes. Par contre cet espacement n'est pas régulier, comme ceci
Code:

1 2 3 4 5 6 7 8 id | date | value 1 | 2014-01-01 00:01:21 | toto 2 | 2014-01-01 00:02:34 | titi 3 | 2014-01-01 00:07:25 | tata 4 | 2014-01-01 00:07:57 | tutu 5 | 2014-01-01 00:10:42 | tete 6 | 2014-01-01 00:12:21 | toto ....
Nous disposons de plusieurs dates et nous souhaitons en une seule requête récupérer les rangs inférieurs les plus près de ces dates (un rang par date)

Par exemple, si les dates données sont

2014-01-01 00:02:00
2014-01-01 00:08:00
2014-01-01 00:12:00

Nous aimerions avoir ce résultat, en une seule requête
Code:

1 2 3 4 id | date | value 1 | 2014-01-01 00:01:21 | toto 4 | 2014-01-01 00:07:57 | tutu 6 | 2014-01-01 00:12:21 | toto
Une solution simple serait UNION mais elle ne nous convient pas pour des raisons de performance (ça fera l'affaire si on ne trouve pas mieux, mais intuitivement on se dit qu'on devrait pouvoir faire mieux, c'est juste qu'on y arrive pas)

Avez vous d'autres idées ? Des pistes ?

Merci !

25/06/2015, 18h52
estofilo

Ce n'est pas spécialement simple en SQL de trouver des valeurs "proches" d'une liste.

Personnellement je ne vois pas bien comment une simple requête UNION pourrait donner un résultat, même de manière non performante.

Il faudrait bien commençer par une requête UNION pour injecter les dates dans la liste, mais ensuite il faudrait un étage avec fonctions de fenêtrage LAG(colonne) OVER (order by date) pour récupérer les valeurs de la ligne du dessous pour chaque colonne du résultat, et ensuite encore un étage de filtrage pour ne garder que les 3 lignes finales.

Ce serait intelligent de poster le DLL de vos table et un jeu d'essais sous forme INSERT ! Cela nous aiderait !!!!!

Comme vous ne l'avez pas fait, je le fait en SQL Server, seul SGBDR que j'ai sous la main :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE T_DATA
(id INT, ma_date DATETIME, ma_valeur VARCHAR(16))
GO
 
INSERT INTO T_DATA VALUES
(1, '2014-01-01 00:01:21', 'toto'),
(2, '2014-01-01 00:02:34', 'titi'),
(3, '2014-01-01 00:07:25', 'tata'),
(4, '2014-01-01 00:07:57', 'tutu'),
(5, '2014-01-01 00:10:42', 'tete'),
(6, '2014-01-01 00:12:21', 'toto');
GO

Solution :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH 
T_PARAM AS
(SELECT '2014-01-01 00:02:00' AS DH_REF
 UNION ALL
 SELECT '2014-01-01 00:08:00' AS DH_REF
 UNION ALL
 SELECT '2014-01-01 00:12:00' AS DH_REF
),
T_ECART AS
(
SELECT DISTINCT id, ma_date, ma_valeur, DH_REF,
       MIN(ABS(DATEDIFF(second, P.DH_REF, D.ma_date))) OVER(PARTITION BY DH_REF) AS ECART_MIN,
	   ABS(DATEDIFF(second, P.DH_REF, D.ma_date)) AS ECART
FROM   T_PARAM AS P
       CROSS JOIN T_DATA AS D
)
SELECT *
FROM   T_ECART
WHERE  ECART = ECART_MIN

Résultat :

Code:

1
2
3
4
5
id          ma_date                 ma_valeur        DH_REF              ECART_MIN   ECART
----------- ----------------------- ---------------- ------------------- ----------- -----------
2           2014-01-01 00:02:34.000 titi             2014-01-01 00:02:00 34          34
4           2014-01-01 00:07:57.000 tutu             2014-01-01 00:08:00 3           3
6           2014-01-01 00:12:21.000 toto             2014-01-01 00:12:00 21          21

A +

Ce problème ressemble beaucoup à cet autre post :

http://www.developpez.net/forums/d15...s/#post8299188

Dans le cas présent ca donnerait :

Code:

1
2
3
4
5
6
7
8
9
SELECT T1.XXID,                      
       T1.XXTS,                      
       T1.XXVA                       
FROM   TB001 AS T1                   
      ,TB002 AS T2                   
WHERE  T2.XXTS =                     
      (SELECT MAX(XXTS)              
       FROM TB002 SUBQ               
       WHERE SUBQ.XXTS < T1.XXTS)

Merci messieurs pour vos réponses.

Je n'ai pas pensé à mettre directement le code, à vrai dire je m'attendais plus à des pistes de réflexion qu'à une requête toute faite, merci de l'implication !

J'étais venu poster la solution que nous avons trouvé de notre coté, et il se trouve qu'elle est très proche de ce que propose SQLpro. Nous sélectionnons les dates inférieures au lieu des dates les plus proches, mais sinon c'est la même idée :

C'est testé en postgresql
Code:

1 2 3 4 5 6 7 8 9 10 11 WITH requested AS ( select '2014-01-01 00:02:00'::timestamp date union all select '2014-01-01 00:08:00'::timestamp date union all select '2014-01-01 00:12:00'::timestamp date ) SELECT requested.date, values.value, values.date FROM values, requested WHERE values.date = ( SELECT max(values.date) FROM values WHERE values.date <= requested.date )
Avec le code de création des tables pour ceux qui souhaitent tester
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TABLE "values" ( id serial, date timestamp, value character varying ); INSERT INTO "values"(id, date, value ) VALUES (1, '2014-01-01 00:01:21', 'toto'), (2, '2014-01-01 00:02:34', 'titi'), (3, '2014-01-01 00:07:25', 'tata'), (4, '2014-01-01 00:07:57', 'tutu'), (5, '2014-01-01 00:10:42', 'tete'), (6, '2014-01-01 00:12:21', 'toto');

26/06/2015, 15h55
escartefigue

Par contre créer une table avec des colonnes dont les noms sont des mots clefs, ici en l'occurrence "date" n'est pas une bonne idée :?

Qui plus est une colonne nommée "date" est qui est un timestamp :?