Discussion :

[dubuscyr]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 !

[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.

[SQLpro]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

[escartefigue]

Ce problème ressemble beaucoup à cet autre post :

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

Dans le cas présent ca donnerait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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)

[dubuscyr]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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');

[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