Comment puis-je sélectionner la valeur la plus proche inférieur à et supérieur à une valeur donnée de manière efficace?

J'ai deux tables, une pour les valeurs de l'un pour l'emplacement et suis en train d'interpoler emplacement. Les tables ont été simplifiées à l'suivantes:

CREATE TABLE value(
    Timestamp DATETIME2,
    Value float NOT NULL,
    PRIMARY KEY(Timestamp)
);

CREATE TABLE location(
    Timestamp DATETIME2,
    Position INT NOT NULL,
    PRIMARY KEY(Timestamp)
); 

INSERT INTO value VALUES 
    ('2011/12/1 16:55:01', 1),
    ('2011/12/1 16:55:02', 5),
    ('2011/12/1 16:55:05', 10),
    ('2011/12/1 16:55:08', 6);

INSERT INTO location VALUES 
    ('2011/12/1 16:55:00', 0),
    ('2011/12/1 16:55:05', 10),
    ('2011/12/1 16:55:10', 5)

Les résultats attendus serait

TimeStamp, Value, LowerTime, LowerLocation, UpperTime, UpperLocation
2011-12-01 16:55:01,  1, 2011-12-01 16:55:00,  0, 2011-12-01 16:55:05, 10
2011-12-01 16:55:02,  5, 2011-12-01 16:55:00,  0, 2011-12-01 16:55:05, 10
2011-12-01 16:55:05, 10, 2011-12-01 16:55:05, 10, 2011-12-01 16:55:05, 10
2011-12-01 16:55:08,  6, 2011-12-01 16:55:05, 10, 2011-12-01 16:55:10,  5

(Gardez à l'esprit que c'est simplifiée des données de l'échantillon pour avoir une idée de la question je suis en train de se produire partout.)

De faire de l'interpolation, j'ai besoin de comprendre le temps et les lieux avant et après les valeurs de temps. Je suis actuellement en train de le faire avec une requête qui ressemble à:

SELECT 
    V.Timestamp, 
    V.Value, 
    (SELECT MAX(Timestamp) FROM dbo.location WHERE Timestamp <= V.Timestamp) as LowerTime,
    (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp <= V.Timestamp ORDER BY timestamp DESC) as LowerLocation,
    (SELECT MIN(Timestamp) FROM dbo.location WHERE Timestamp >= V.Timestamp) as UpperTime,
    (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp >= V.Timestamp ORDER BY timestamp ASC) as UpperLocation
 FROM 
    dbo.value V

Maintenant cela fonctionne, mais, évidemment, est de faire beaucoup de travail. Je pense il doit y avoir une requête de simplification que je suis absente, mais j'ai joué avec elle tous les matins et je n'ai pas trouver quelque chose de concret. En espérant que quelqu'un ici a une meilleure idée.

Je suis actuellement en train d'étudier s'il existe un moyen de comprendre le LowerTime et UpperTime et les utiliser dans la détermination de l'emplacement. Quelque chose comme:

SELECT 
    V.Timestamp, 
    V.Value, 
    (SELECT MAX(Timestamp) FROM dbo.location WHERE Timestamp <= V.Timestamp) as LowerTime,
    (SELECT Position FROM dbo.location WHERE Timestamp = LowerTime) as LowerLocation,
    (SELECT MIN(Timestamp) FROM dbo.location WHERE Timestamp >= V.Timestamp) as UpperTime,
    (SELECT Position FROM dbo.location WHERE Timestamp = UpperTime) as UpperLocation
 FROM 
    dbo.value V

mais cela ne fonctionne pas.

EDIT1: mise à Jour de la requête, comme l'a suggéré. Toutefois, aucun changement visible au moment de l'exécution.

EDIT2: Ajout de mes pensées de l'approche, je suis en train d'essayer.

OriginalL'auteur brianestey | 2012-01-20

Pour plus de simplicité vous pouvez au moins utiliser MAX() et MIN() fonctions d'interrogation timestamp champ au lieu de TOP 1 et ORDER BY.

Requête complète sera

SELECT 
    V.Timestamp, 
    V.Value, 
    (SELECT MAX(Timestamp) FROM dbo.location WHERE Timestamp <= V.Timestamp) as LowerTime,
    (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp <= V.Timestamp ORDER BY timestamp DESC) as LowerLocation,
    (SELECT MIN(Timestamp) FROM dbo.location WHERE Timestamp >= V.Timestamp) as UpperTime,
    (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp >= V.Timestamp ORDER BY timestamp ASC) as UpperLocation
 FROM 
    dbo.value V

Merci pour l'astuce! C'est un bon point, je vais essayer ça. Je suis l'espoir de quelque sorte, réduire ou de se débarrasser de ces 4 requêtes.
Merci. J'ai marqué votre réponse comme réponse que c'était la meilleure des options. Ce qui ne l'aider un peu.

OriginalL'auteur Sergey Kudriavtsev

0

Cela pourrait faire l'affaire (bien que je pense de la jointure semble assez laid):
```
;with OrderedLocations as (
    select
        v.Timestamp,
        v.Value,
        l.Timestamp as tsl,
        l.Position,
        ROW_NUMBER() OVER (PARTITION BY v.Timestamp ORDER BY CASE WHEN l.Timestamp <= v.Timestamp THEN l.Timestamp ELSE '00010101' END desc) as PrevRN,
        ROW_NUMBER() OVER (PARTITION BY v.Timestamp ORDER BY CASE WHEN l.Timestamp >= v.Timestamp THEN l.Timestamp ELSE '99991231' END asc) as NextRN
    from
        value v
            cross join
        location l
)
select
    ol1.Timestamp,
    ol1.Value,
    ol1.tsl,
    ol1.Position,
    ol2.tsl,
    ol2.Position
from
    OrderedLocations ol1
        inner join
    OrderedLocations ol2
        on
            ol1.Timestamp = ol2.Timestamp and
            ol1.Value = ol2.Value
where
    ol1.PrevRN = 1 and
    ol2.NextRN = 1
```
Malheureusement, comme avec la plupart efficacité/performances des questions, la réponse tend à être essayer un bon nombre de différentes combinaisons avec votre réelle les tables et les données, et de mesurer la façon dont chacun exécute.

Une alternative (en évitant la jointure) à l'aide de la même CTE comme ci-dessus serait:
```
SELECT Timestamp,Value,
    MAX(CASE WHEN PrevRN=1 THEN tsl END),MAX(CASE WHEN PrevRN=1 then Position END),
    MAX(CASE WHEN NextRN=1 THEN tsl END),MAX(CASE WHEN NextRN=1 then Position END)
FROM
    OrderedLocations
where PrevRN=1 or NextRN=1
group by Timestamp,Value
```
Le CTE (OrderedLocations) est d'essayer de construire un ensemble de lignes où chaque ligne à partir de l'emplacement est adaptée à chaque ligne de value. Pour chaque ligne, nous calculons deux ROW_NUMBERs - le numéro de ligne où nous sommes toutes les lignes ayant une valeur inférieure ou égale timestamp (PrevRN) dans l'ordre décroissant, et un autre où nous en nombre de toutes les lignes avec une plus grande ou égale timestamp (NextRN) dans l'ordre croissant. Ensuite, nous construisons notre résultat final en ne considérant que les lignes où l'un de ces numéros de ligne est 1.

Merci pour l'entrée, je ne sais pas ce que c'est exactement. En l'exécutant sur un simple jeu de données a abouti à aucun résultat. Je vais étudier votre réponse et de voir si l'aide.
eh bien, si vous avez des exemples de données et de résultats attendus, veuillez les ajouter à votre question - c'est beaucoup plus facile pour nous d'écrire de travail requêtes, si nous sommes capables de les tester nous-mêmes avant de poster que des réponses.
bonne idée, j'ai mis à jour de nouveau la question avec certains points de données que j'utilise pour les tests et aussi le résultat attendu, je suis à la recherche d'.
Maintenant, j'ai partitionné par v.Timestamp dans mon CTE, et obtenir les résultats attendus, vous avez posté. (J'avais d'abord testé avec une seule ligne dans le value table, oups)
Merci pour la mise à jour. J'ai testé cela et il donne les résultats escomptés, mais je vais avoir quelques problèmes de performances avec ce. Par exemple, j'ai inséré 5000 lignes dans la table de valeurs, 1250 lignes dans l'emplacement de la table et l'original de la requête terminée en 0~1 seconde, mais cette requête ne prend que quelques minutes avec les mêmes données (~5.5 minutes). Est-ce que sembler raisonnable pour vous?

OriginalL'auteur Damien_The_Unbeliever

Vous devez vous connecter pour publier un commentaire.