我有一张timeserie桌。每个数据池都有一个statut字段,该字段指示它的提供源。我想请求最后一次可用的数据点到日期T,但是可能有几个结果,每个结果都有一个不同的状态。
因此,我使用一个"statut首选项列表“来根据提供者优先级检索数据池。
下面是一个查询:(在本例中,我喜欢的有序状态是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]链接到执行计划
发布于 2014-09-30 09:06:50
我认为您的查询只使用日期为最大值的行,因此它相当于以下内容:
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 ;发布于 2014-05-07 16:28:42
我会尝试用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发布于 2018-07-14 10:11:56
如果您使用
SELECT RowNum,Security, Date, Statut, Value而不是
(SELECT Security, Date, Statut, Value)
WHERE RowNum=1它将使查询速度降低60%。为什么我不知道原因。
https://dba.stackexchange.com/questions/64638
复制相似问题