Jak napisać zapytanie w SQL Server, aby znaleźć najbliższe wartości

Powiedzmy, że mam w tabeli następujące wartości całkowite

32
11
15
123
55
54
23
43
44
44
56
23

OK, lista może trwać; to nie ma znaczenia. Teraz chcę wykonać zapytanie do tej tabeli i zwrócić określoną liczbę closest records. Powiedzmy, że chcę zwrócić 10 najbliższych rekordów pasujących do liczby 32. Czy mogę to skutecznie osiągnąć?

Jest w SQL Server 2014.

Zakładając, że kolumna jest indeksowana, poniższe elementy powinny być dość wydajne.

Z dwoma próbami po 10 wierszy, a następnie w pewnym sensie (do) 20 zwróconych.

WITH CTE
     AS ((SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol > 32
          ORDER  BY YourCol ASC)
         UNION ALL
         (SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol <= 32
          ORDER  BY YourCol DESC))
SELECT TOP 10 *
FROM   CTE
ORDER  BY ABS(YourCol - 32) ASC 

(tzn. potencjalnie coś takiego poniżej)

Lub inna możliwość (która zmniejsza liczbę rzędów posortowanych do maksymalnie 10)

WITH A
     AS (SELECT TOP 10 *,
                       YourCol - 32 AS Diff
         FROM   YourTable
         WHERE  YourCol > 32
         ORDER  BY Diff ASC, YourCol ASC),
     B
     AS (SELECT TOP 10 *,
                       32 - YourCol AS Diff
         FROM   YourTable
         WHERE  YourCol <= 32
         ORDER  BY YourCol DESC),
     AB
     AS (SELECT *
         FROM   A
         UNION ALL
         SELECT *
         FROM   B)
SELECT TOP 10 *
FROM   AB
ORDER  BY Diff ASC

Uwaga: Powyższy plan wykonania dotyczył prostej definicji tabeli

CREATE TABLE [dbo].[YourTable](
    [YourCol] [int] NOT NULL CONSTRAINT [SomeIndex] PRIMARY KEY CLUSTERED 
)

Technicznie rzecz biorąc, Sortowanie na dolnej gałęzi również nie powinno być potrzebne, ponieważ to też jest uporządkowane przez Diff i byłoby możliwe scalenie dwóch uporządkowanych wyników. Ale nie byłem w stanie uzyskać tego planu.

Zapytanie ma ORDER BY Diff ASC, YourCol ASCnie tylko ORDER BY YourCol ASCdlatego, że to właśnie skończyło się na pozbyciu się sortowania w górnej gałęzi planu. Musiałem dodać kolumnę dodatkową (nawet jeśli nigdy nie zmieni to wyniku, ponieważ YourColbędzie taki sam dla wszystkich wartości z tym samym różnicą), więc przejdzie przez połączenie scalające (konkatenację) bez dodawania sortowania.

Wydaje się, że SQL Server może wnioskować, że indeks X poszukiwany w porządku rosnącym dostarczy wiersze uporządkowane przez X + Y i żadne sortowanie nie jest konieczne. Ale nie jest w stanie wywnioskować, że przesunięcie indeksu w kolejności malejącej spowoduje dostarczenie wierszy w tej samej kolejności co YX (lub nawet tylko jednoargumentowy minus X). Obie gałęzie planu używają indeksu, aby uniknąć sortowania, ale TOP 10dolna gałąź jest następnie sortowana według Diff(nawet jeśli są już w tej kolejności), aby uzyskać je w żądanej kolejności scalania.

W przypadku innych zapytań / definicji tabel uzyskanie planu scalania może być trudniejsze lub może nie być możliwe, używając tylko jednego odgałęzienia - ponieważ polega on na znalezieniu wyrażenia porządkującego, które SQL Server:

1. Akceptuje, że wyszukiwanie indeksu dostarczy określoną kolejność, więc sortowanie nie jest potrzebne przed szczytem.
2. Z przyjemnością korzysta z operacji scalania, więc nie wymaga sortowania po TOP

Jestem trochę zaskoczony i zaskoczony, że w tej sprawie musimy zrobić Unię. Poniższe jest proste i bardziej wydajne

SELECT TOP (@top) *
FROM @YourTable
ORDER BY ABS(YourCol-@x)

Poniżej znajduje się pełny kod i plan wykonania porównujący oba zapytania

DECLARE @YourTable TABLE (YourCol INT)
INSERT @YourTable (YourCol)
VALUES  (32),(11),(15),(123),(55),(54),(23),(43),(44),(44),(56),(23)

DECLARE @x INT = 100, @top INT = 5

--SELECT TOP 100 * FROM @YourTable
SELECT TOP (@top) *
FROM @YourTable
ORDER BY ABS(YourCol-@x)

;WITH CTE
     AS ((SELECT TOP 10 *
          FROM   @YourTable
          WHERE  YourCol > 32
          ORDER  BY YourCol ASC)
         UNION ALL
         (SELECT TOP 10 *
          FROM   @YourTable
          WHERE  YourCol <= 32
          ORDER  BY YourCol DESC))
SELECT TOP 10 *
FROM   CTE
ORDER  BY ABS(YourCol - 32) ASC 

Udoskonalenie drugiej sugestii Martina:

WITH AB
     AS (SELECT *, ABS(32 - YourCol) AS Offset
         FROM   YourTable),
SELECT TOP 10 *
FROM   AB
ORDER  BY Offset ASC