SQL:如何最好地查询历史表以创建特定日期的快照概述

Question

我对 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 <a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d784979fbea4a3b8a5ae83b6b5bbb299b6bab2f99e93" rel="noreferrer noopener nofollow">[email protected]</a> = 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 DB。

Answer 1

你的方法很好。我会使用窗口函数:

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
                          );