是否有可能加快這個 PARTITION BY 查詢？

我有一張timeserie桌子。每個數據點還有一個statut欄位，指示其提供源。我想請求截至日期 T 的最後一個可用數據點，但可能有幾個結果，每個結果都有不同的狀態。

因此，我使用“狀態偏好列表”來根據提供者優先級檢索數據點。

這是查詢：（在此範例中，我首選的有序狀態將是 10，然後是 9，然後是 1）

DECLARE @MyStatuts TABLE (StatutOrder tinyint, StatutValue tinyint) 
INSERT INTO @MyStatuts VALUES(1, 10) 
INSERT INTO @MyStatuts VALUES(2, 9) 
INSERT INTO @MyStatuts VALUES(3, 1) 

SELECT TOP 1 Id, Date, Statut, Value 
FROM (
   SELECT data.Id, data.Date, data.Statut, data.Value, s.StatutOrder, 
          MIN(s.StatutOrder)
            OVER(PARTITION BY data.Date) As MinStatutOrder
   FROM [DATA].[TimeSerie] as data
     JOIN @MyStatuts as s on data.Statut = s.StatutValue
   WHERE Id = 'timeserieid' AND Date <= '24/04/2014'
   ) AS T
WHERE T.StatutOrder = T.MinStatutOrder 
ORDER BY T.Date DESC

你有什麼想法我可以加快速度嗎？[TimeSerie]我已經在桌子上使用過索引…

這是表格程式碼：

CREATE TABLE [DATA].[TimeSerie](
[Id] [varchar](25) NOT NULL,
[Date] [date] NOT NULL,
[Statut] [tinyint] NOT NULL,
[Value] [decimal](19, 8) NOT NULL,
CONSTRAINT [PK_Price] PRIMARY KEY CLUSTERED 
(
[Id] ASC,
[Date] ASC,
[Statut] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
      ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

連結到**執行計劃**

我認為您的查詢僅使用日期為最大值的行，因此它等效於：

SELECT TOP (1) data.Id, data.Date, data.Statut, data.Value
FROM [DATA].[TimeSerie] AS data
 JOIN @MyStatuts AS s ON data.Statut = s.StatutValue
WHERE data.Id = 'timeserieid' 
 AND data.Date = ( SELECT MAX(m.Date)
                   FROM [DATA].[TimeSerie] AS m
                   WHERE m.Id = 'timeserieid'
                     AND m.Date <= '20140424'
                 )
ORDER BY s.StatutOrder ASC ;

我會嘗試用 ROW_NUMBER() 替換 MIN OVER() 以獲得您想要的行：

SELECT Security, Date, Statut, Value
FROM (
     SELECT data.Security, data.Date, data.Statut, data.Value
             ROW_NUMBER() OVER(PARTITION BY data.Date ORDER BY     s.StatutOrder, data.Date DESC) AS RowNum
     FROM [DATA].[TimeSerie] as data
           JOIN @MyStatuts as s on data.Statut = s.StatutValue
     WHERE Security = 'USDEUR' AND Date <= '20140424'
     ) AS T
WHERE RowNum=1

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