计算不同行和列中的值之间的差异 [英] Calculate the difference between the value in different rows and columns

问题描述

请帮助我解决以下查询. 假设我有一个表名作为Data并具有3行:Date1，Date2和访问窗口.我需要计算访问"窗口值.它应该是日期1的第(n + 1)行和日期2的第n行之差.例如:Date1的第二行值与date2的第一行值之差除以7.请帮助.

Please help me in solving the below query. Suppose if I have a table name as Data with 3 rows: Date1, Date2 and visit window. I need to calculate Visit window value. It should be the difference of (n+1)th row of date 1 and nth row of date2. For ex: Difference of 2nd row value of Date1 and 1st row of date2 value divided by 7. Please help.

Table: Data
------------
Date1           Date2           VW
13-DEC-2011     15-DEC-2011     ?   
18-DEC-2011     16-DEC-2011     ?
21-DEC-2011     24-DEC-2011     ?

谢谢

推荐答案

select
  Date1, 
  Date2,
  lead(Date1) over (order by Date1) next_date1,
  ((lead(Date1) over (order by Date1)) - Date2)/7 as Diff
From DATA_TABLE

对于最后一行，您将不会获得任何大众汽车，因为没有n + 1 Date1.

For the last row you won't get any VW, because there is no n+1 Date1.

lead(column)函数从over子句中指定的下一行返回column参数的值.

lead(column) function returns value for the column parameter from the next row as specified in the over clause.

您可以在此处找到示例和其他类似功能.

You can find examples and other similar functions here.

更新(回答问题评论-如何与另一列进行比较)

UPDATE (response to a question comment - how to compare with another column)

select 
  Date1,
  Date2,
  Diff,
  another_column,
  CASE 
    when Diff < another_column then 'it is lower'
    when Diff > another_column then 'it is higher'
    when Diff = another_column then 'are equal'
  END as comparation,
  CASE
    when round(diff -another_column,3) = 0 then 'almost equal'
    else 'definitely not equal'
  END as rounded_comparation
from(
    select
      Date1, 
      Date2,
      lead(Date1) over (order by Date1) next_date1,
      ((lead(Date1) over (order by Date1)) - Date2)/7 as Diff,
      another_column
    From DATA_TABLE
)

这篇关于计算不同行和列中的值之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！