SQL:如何最好地查询历史记录表以创建特定日期的快照概览
问题描述:
我对SQL和数据库结构比较陌生,并且对最佳方法是什么进行了解。SQL:如何最好地查询历史记录表以创建特定日期的快照概览
我有偶尔需要转换为特定日期快照的对象的历史更改数据。数据
例子:
ID Value UpdateDate
1 4 2017-01-01
2 4 2017-01-03
3 4 2017-01-03
1 7 2017-01-04
2 5 2017-01-08
3 5 2017-01-10
2 8 2017-01-11
我希望能够在任何给定日期创建的所有对象的完整概述的快照。例如:
Current Date: 2017-01-01 | Current Date: 2017-01-04
ID Value LastUpdateDate | ID Value LastUpdateDate
|
1 4 2017-01-01 | 1 7 2017-01-04
| 2 4 2017-01-03
| 3 4 2017-01-03
__________________________________________________________________________________
|
Current Date: 2017-01-08 | Current Date: 2017-01-12
ID Value LastUpdateDate | ID Value LastUpdateDate
|
1 7 2017-01-04 | 1 7 2017-01-04
2 5 2017-01-08 | 1 8 2017-01-10
3 4 2017-01-03 | 1 5 2017-01-11
我创建了以下SQL查询来创建这些表。但我想知道这是否是最有效的方式?我的表格实际上是相当大的,有时每个对象类型有200K-300K的独特对象,每天有数千次更新(不是每个对象,但每天只有几千行被添加到历史表中,所以它的尺寸相当大很快)。
SQL查询:
SELECT * INTO @CurrentOverviewTableName
FROM @HistoryTableName
INNER JOIN (
SELECT ID AS ID_T, MAX(LastUpdateDate) AS LastUpdateDate
FROM @HistoryTableName
WHERE LastUpdateDate <= @OverviewDate
GROUP BY ID
) ts
ON [email protected] = ts.ID_T AND @HistoryTableName.LastUpdateDate = ts.LastUpdateDate_T;
这之后,我得到这个例如:
ID Value LastUpdateDate ID_T LastUpdateDate_T
1 4 2017-01-01 1 2017-01-01
2 4 2017-01-03 2 2017-01-03
3 4 2017-01-03 3 2017-01-03
我把最后两列,因为他们是重复的,让我需要什么。再次,这有效,但我想知道这是否是最好的方法呢?
我正在使用Azure SQL数据库。
答
你的方法很好。我会用窗函数:
SELECT *
INTO @CurrentOverviewTableName
FROM (SELECT ht.*,
MAX(LastUpdateDate) OVER (PARTITION BY id) AS max_LastUpdateDate
FROM @HistoryTableName ht
) ht
WHERE max_LastUpdateDate = LastUpdateDate;
如果你不想删除列,列出你想要的列,而不是使用SELECT *
。如果你不想做,要么,移动条件的WHERE
条款:
SELECT ht.*
INTO @CurrentOverviewTableName
FROM @HistoryTableName ht
WHERE ht.LastUpdateDate = (SELECT MAX(h2.LastUpdateDate)
FROM @CurrentOverviewTableName ht2
WHERE ht2.id = ht.id
);
答
假设他们不回去的价值,你可以使用下面的代码。如果你想让它们在Value中回归的选项,那么你需要一个标识列,最好是作为主键,并且你需要使用最大标识。子查询会稍微改变。主键也会使子查询变得更容易,仅使用主键而不是两个值,即ID和值。见下文。
--change the date for a different time period
DECLARE @updateDate date = '2017-01-12'
DECLARE @temp TABLE (ID int, Value int, updateDate date)
INSERT INTO @temp VALUES
(1, 4, '2017-01-01')
,(2, 4, '2017-01-03')
,(3, 4, '2017-01-03')
,(1, 7, '2017-01-04')
,(2, 5, '2017-01-08')
,(3, 5, '2017-01-10')
,(2, 8, '2017-01-11')
SELECT dT.*
,(SELECT T2.updateDate
FROM @temp T2
WHERE T2.ID = dT.Id AND T2.Value = dT.MaxValue) [LastUpdateDate]
FROM (
SELECT ID, MAX(Value) MaxValue
FROM @temp T
WHERE T.updateDate <= @updateDate
GROUP By ID
) AS dT