基于OVER PARTITION BY子句的SQL计算列

Question

我有一个基于一些列的分区的行号视图。行号从0开始，并根据来自各种文件的ssis输入为每个记录递增，对于具有唯一文件名和日期组合的每个文件，行号将从0到n。

现在，我需要创建一个新列，它使用行号对值进行计算。

RowNumber Value Filename FileDate  
0           500   datax   20200301  
1           200   datax   20200301  
2           100   datax   20200301  
0           600   datax   20200302  
1           200   datax   20200302  
2           200   datax   20200302  
3           100   datax   20200302  
4           200   datax   20200302  
0           700   datay   20200303  
1           500   datay   20200303 

我想要一个新的列，它将每个RowNumber n中的值除以RowNumber 0中的值，作为其唯一分区

NEWVALUE = (Value @ RowNumber N / Value at RowNumber 0)

RowNumber Value  NEWVALUE          Filename FileDate  
0           500   NULL             datax   20200301  
1           200   0.400            datax   20200301  
2           100   0.200            datax   20200301  
0           600   NULL             datax   20200302  
1           200   0.333            datax   20200302  
2           200   0.333            datax   20200302  
3           100   0.167            datax   20200302  
4           300   0.500            datax   20200302  
0           700   NULL             datay   20200303  
1           500   0.714            datay   20200303  

有人知道怎么做吗？

Answer 1

一种方法使用条件聚合：

select ( value * 1.0 /
         max(case when rownumber = 0 then value end) over (partition by filename, filedate)
         value
       ) as ratio

我猜分区组是基于filename和filegroup的。

或者使用first_value()

select ( value * 1.0 /
         first_value(value) over (partition by filename, filedate order by rownumber)
         value
       ) as ratio

要将其中任何一个放入视图中，您需要一个子查询。

如果排序是基于最大值，那么您可以使用类似的逻辑(省略子查询)：

select ( value * 1.0 /
         max(value) over (partition by filename, filedate)  * 1.0
         value
       ) as ratio

Answer 2

就像其他人建议的那样，ROWNUM =0等同于FIRST_VALUE

棘手的一点是为NEWVALUE添加null而不是1，您将不得不求助于一个子查询。

SELECT 
RowNumber,Value,CASE WHEN RowNumber=0 THEN NULL else NEWVALUE end as NEWVALUE, FileName,FileDate
FROM (
  select ROW_NUMBER() OVER (PArtition BY FileDate ORDER BY Value DESC) -1 as RowNumber,
  Value,
  Value * 1.0 / FIRST_VALUE(Value) OVER (PArtition BY FileDate ORDER BY Value DESC) as NEWVALUE
  ,FileName
  ,FileDate
  from Data
  ) t

这是sqlfiddle，如果你想使用它的话http://sqlfiddle.com/#!18/2bdca4/1