sql - 比较有序 SQL 记录之间差异的快速方法

Question

我有一个 SQL 查询，第一列是日期，第二列和第三列是其他信息。我们可以假设每天只有一条记录，并且我们可以假设日期是有序的。

除第一列外，其他列包含不同数据类型的数据 long、int、nchar、varchar等

我的目标是弄清楚与 T-1 天相比，剩余的列在 T 天是否发生了变化，如果是的话(假设 N 列发生了变化，N 可以是任何剩余的列，并且可能有多个列在同一天更改)我想返回日期(第一列)以及截至第 T 天的第 N 列的记录与截至第 T-1 天的第 N 列的记录(可能还有该列的标题)，所以我的输出是:

Date T|Header of Col Modified|Value of Col @ T-1|Value of Col @ T

如果有多项更改，则每次更改都会有一行。

显然，“慢”的方法是先将所有数据放入某个地方，然后逐一比较结果数据集。但是我只是想知道是否有任何快速方法可以在 T-SQL 中执行此操作，以便我可以直接获取结果(或接近结果的结果，以便查询的输出不需要太多数据操作，因为它会让 SQL Server 更快地完成繁重的工作，并且只返回我需要的数据，而不是几乎整个表。

我正在使用 SQL Server，因此需要 T-SQL 解决方案。

Answer 1

您使用什么版本的 SQL Server？

下面的解决方案返回一组原始的已更改行。该解决方案只是告诉您相邻两行的某些列中的值已更改。它不会明确告诉您哪些列已更改。您需要执行此额外处理(最有可能在客户端)以按照您需要的方式呈现数据。该解决方案最重要的部分是它将返回给客户端的行数减少到最少。

如果您使用SQL Server 2012或更高版本，它具有函数LAG和 LEAD ，可用于比较上一行/下一行:

示例数据:

DECLARE @T TABLE(dt date, v1 int, v2 varchar(50));

INSERT INTO @T (dt, v1, v2) VALUES ('2015-01-01', 1, 'a');
INSERT INTO @T (dt, v1, v2) VALUES ('2015-01-02', 2, 'b');
INSERT INTO @T (dt, v1, v2) VALUES ('2015-01-03', 2, 'b');
INSERT INTO @T (dt, v1, v2) VALUES ('2015-01-04', 3, 'b');
INSERT INTO @T (dt, v1, v2) VALUES ('2015-01-05', 3, 'b');
INSERT INTO @T (dt, v1, v2) VALUES ('2015-01-06', 3, 'c');
INSERT INTO @T (dt, v1, v2) VALUES ('2015-01-07', 4, 'd');
INSERT INTO @T (dt, v1, v2) VALUES ('2015-01-08', 4, 'd');
INSERT INTO @T (dt, v1, v2) VALUES ('2015-01-09', 4, 'd');
INSERT INTO @T (dt, v1, v2) VALUES ('2015-01-10', 4, 'd');

使用 LAG 进行查询，SQL Server 2012+

WITH
CTE
AS
(
    SELECT
        dt
        ,v1
        ,v2
        ,LAG(v1) OVER(ORDER BY dt) AS PrevV1
        ,LAG(v2) OVER(ORDER BY dt) AS PrevV2
    FROM @T AS T
)
SELECT *
FROM CTE
WHERE
    v1 <> PrevV1
    OR v2 <> PrevV2
ORDER BY dt;

结果集

dt          v1  v2  PrevV1  PrevV2
2015-01-02  2   b   1       a
2015-01-04  3   b   2       b
2015-01-06  3   c   3       b
2015-01-07  4   d   3       c

---

如果您使用某些以前的版本，那么从性能的角度来看，使用游标循环遍历行、将当前行与前一行进行比较并将差异插入到临时表或表变量中可能会更好。任何其他没有 LEAD 或 LAG 的解决方案都意味着源表至少被读取两次，在最坏的解决方案中，它会是 O(n*n) code> 而不是 O(n)。

从SQL Server 2005开始有一个函数ROW_NUMBER 。如果表中的日期列表可能有间隙，我们可以使用它。如果您确定日期没有间隙，则无需生成行号的额外步骤，而是直接在日期列上连接表。它仍然是自加入。

使用 ROW_NUMBER 进行查询，SQL Server 2005+

WITH
CTE
AS
(
    SELECT
        dt
        ,v1
        ,v2
        ,ROW_NUMBER() OVER(ORDER BY dt) AS rn
    FROM @T AS T
)
SELECT
    CTE_Curr.dt
    ,CTE_Curr.v1 AS CurrV1
    ,CTE_Curr.v2 AS CurrV2
    ,CTE_Prev.v1 AS PrevV1
    ,CTE_Prev.v2 AS PrevV2
FROM
    CTE AS CTE_Curr
    INNER JOIN CTE AS CTE_Prev ON CTE_Curr.rn = CTE_Prev.rn+1
WHERE
    CTE_Curr.v1 <> CTE_Prev.v1
    OR CTE_Curr.v2 <> CTE_Prev.v2
ORDER BY CTE_Curr.dt;

结果集

dt            CurrV1    CurrV2    PrevV1    PrevV2
2015-01-02    2         b         1         a
2015-01-04    3         b         2         b
2015-01-06    3         c         3         b
2015-01-07    4         d         3         c

---

执行计划比较

两种变体的结果相同，但执行计划却截然不同。 LAG 变体的相对成本估计为 33%，ROW_NUMBER 变体的相对成本为 67% - 两倍，因为第二个变体对表进行扫描和排序两次， sample 表很小。此外，您可以看到在第二个变体中，表被连接到自身，这导致读取 100 行 (10*10)。如果您的表很大，效率可能会非常低，最好使用游标。使用光标您只需扫描表格一次。

使用LAG的执行计划

执行计划为 ROW_NUMBER