唯一ID的连续行中的值之间的差异

SQL Server 2008 R2.

SQL Server 2008 R2。

I need to find the difference between consecutive rows based on a common, unique ID.

我需要根据一个共同的唯一ID找到连续行之间的差异。

Data:

AccountNumber   ValueDate     CustomerName        Amount           Difference

     1           06/01/2014   Customer Name 1   -3436.184178    
     2           06/03/2014   Customer Name 2   -154.5               -51.5
     2           06/15/2014   Customer Name 2   -103    
     3           06/02/2014   Customer Name 3   -45289.44   
     4           06/20/2014   Customer Name 4   -4907.52             -1116.43
     4           06/25/2014   Customer Name 4   -3791.09             -3791.09
     4           06/30/2014   Customer Name 4   -3302.19

The difference column is what I'm trying to generate. I need to find the difference between consecutive rows ONLY IF:

差异列是我正在尝试生成的。我只需要找到连续行之间的差异:

There is more than 1 row for a particular AccountNumber.

特定AccountNumber有超过1行。

I managed to remove the rows with 1 value/AccountNumber [rows 1 and 4 in this case]

我设法删除了1个值/ AccountNumber [在这种情况下第1行和第4行]的行

I still need to find the difference from [row - row + 1] I saw a couple of answers on Stack overflow but they don't seem to apply to this scenario.

我仍然需要找到与[row - row + 1]的区别我在Stack溢出时看到了几个答案,但它们似乎不适用于这种情况。

1 个解决方案

#1

You can do this with the ROW_NUMBER() function:

您可以使用ROW_NUMBER()函数执行此操作:

;with cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY ValueDate) AS RN 
              FROM YourTable)
SELECT a.*,a.Amount - b.Amount AS Diff
FROM cte a
LEFT JOIN cte b
  ON a.AccountNumber = b.AccountNumber
  AND a.RN = b.RN -1

TheROW_NUMBER()function assigns a number to each row.PARTITION BYis optional, but used to start the numbering over for each value in a group, ie: if you PARTITION BY AccountNumber then for each unique AccountNumber value the numbering would start over at 1. ORDER BY of course is used to define how the numbering should go, and is required in the ROW_NUMBER() function.

ROW_NUMBER()函数为每一行分配一个数字。 PARTITION BY是可选的,但用于为组中的每个值开始编号,即:如果您对AccountNumber进行PARTITION,则对于每个唯一的AccountNumber值,编号将从1开始。当然,ORDER BY用于定义如何编号应该去,并且在ROW_NUMBER()函数中是必需的。

Used in a cte you can then self-join using the ROW_NUMBER() to offset the join by 1 record, allowing comparison between rows.

在cte中使用,然后可以使用ROW_NUMBER()自行连接以将连接偏移1个记录,从而允许在行之间进行比较。

In SQL Server 2012 the LEAD() and LAG() functions allow for simpler cross-row comparisons.

在SQL Server 2012中,LEAD()和LAG()函数允许更简单的跨行比较。

1 个解决方案

#1

更多相关文章

随机推荐