絕對值日期差的每個分區的前 2 行

我在非規範化數據庫中有一個安全攝影機鏡頭數據庫。我的位置有多個可以拍攝多張圖像的相機。

Location + Camera + image capture_date 是聚群主鍵，目前是表上唯一的索引。踢球者正在搜尋單個攝像頭，從 SSMS 需要不到 1 毫秒，從我的 Web 應用程序需要約 70 毫秒。我目前工作的 CTE 解決方案需要大約 3 分鐘來處理三個攝像頭。

為了概述某個位置的相機，我需要從每個相機中選擇最接近給定日期（例如目前日期）的 2 張圖像。因此，我需要一個絕對值（搜尋日期之前或之後的日期同樣有效），因此我正在搜尋最小的ABS(@date -capture_date).

這是目前程式碼。它可以工作，但不是 SARGable，而且速度非常慢。我也只需要 CTE 中每個攝像頭的前 2 行，因為每個分區可能有數十萬張圖像。

DECLARE @date datetime,
       @location varchar(4)
SET     @date ='2011-12-13 12:00:00'
SET     @location='CS01';
WITH CTE AS (
   SELECT
       *,
       ROW_NUMBER() OVER (PARTITION  BY Camera ORDER BY  abs(datediff(second,@date, [capture_date]))) AS Ranking
   FROM rs_camera_pictures
   WHERE 
       location=@location)
SELECT * FROM CTE WHERE Ranking <= 2

這只是一個起點，可能需要一些調整。

從本質上講，這將為您提供最接近指定日期的 4 個擷取日期（2 個最接近的日期和 2 個最接近的日期之前）。

您將需要在外部選擇中添加一些邏輯來選擇要使用的那些，但是您將DATEDIFF在 4 個欄位上而不是所有欄位上進行操作。

WITH CTE AS
(
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
   ON r1.location = r2.location
   AND r1.camera = r2.camera
WHERE r1.capture_date > @date
AND r2.capture_date < @date
GROUP BY camera
UNION ALL
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
   ON r1.location = r2.location
   AND r1.camera = r2.camera
INNER JOIN CTE c
   ON r1.location = c.location
   AND r1.camera = c.camera
WHERE r1.capture_date > c.[After]
AND r2.capture_date < c.Before
GROUP BY camera
)

引用自：https://dba.stackexchange.com/questions/9159