时间戳

首页 > 解决方案 > SQL Server 使用以前的值更新列

sql - SQL Server 使用以前的值更新列

问题描述

我正在尝试在表格列中制定过滤器。表A有3列spare_qty（起ID的作用），alt（高度，要过滤的Value），Alt_derivative（需要的Filtered_Value）。该表包含大约一百万行。我的目的是用公式填充 X 行的 Alt_derivative 列：Filtered_Value(X) = 0.9*Filtered_Value(X-1) + 0.1 * Value。该函数本质上可以平滑数据而不会滞后，就像窗口函数一样。事实证明，这不是一项微不足道的任务。

  1. 更新表列不允许使用函数超前或滞后。自引用都不成功。排序顺序被忽略。
  2. 游标的实现需要花费数小时。它似乎执行 NxN 行计算。此外，即使应考虑排序顺序，更新也会忽略该顺序。脚本是：
DECLARE  @spare_qty bigint =0, @alt float = 0, @alt_derivative float =0 
DECLARE TRX_Line CURSOR FOR 
SELECT spare_qty1, alt, Alt_Derivative FROM [Incremental_Staging] order by ID asc;


OPEN TRX_Line  
FETCH NEXT FROM TRX_Line INTO @ID, @value, @filtered_value  

WHILE @@FETCH_STATUS = 0  
BEGIN  

    update [Incremental_Staging]
                    SET 
                    @alt = alt,
                    @alt_derivative = 0.1*@alt+ 0.9* @alt_derivative
                    where spare_qty1=@spare_qty1
FETCH NEXT FROM TRX_Line INTO @ID, @value, @filtered_value  
end
close TRX_Line;
Deallocate TRX_Line;
  1. CTE 不尊重排序顺序。

坦率地说，我尝试了很多方法，只是为了遇到死胡同。这么简单的excel之类的事情有可能做不出来吗？我错过了什么吗？任何帮助，将不胜感激。

标签： sqlsql-server

解决方案

评论太长了。

Filtered_Value(X) = 0.9*Filtered_Value(X-1) + 0.1 * 值

这不仅仅是一个滞后。这是指数平滑。在 SQL 中，有两种基本方法。

  1. 算术，它本质上是使用数学函数实现一个乘积聚合函数。
  2. 递归 CTE。

两者都不适合您的用例。第一个将在 1,000,000 行上出现问题。由于浮点数（或定点数）的舍入，会累积一些错误。并且该错误在 1,000,000 行上可能很明显。

第二个会起作用，但可能太慢了。

您可以检查返回值需要多长时间：

with i as (
      select i.*, row_number() over (order by i) as seqnum
      from incremental_staging i
     ),
     cte as (
      select seqnum, id, value, value as filter_value
      from i
      where seqnum = 1
      union all
      select i.seqnum, i.id, i.value, cte.filter_value * 0.9 + 0.1 * i.value
      from cte join
           i
           on i.seqnum = cte.seqnum + 1
     )
select *
from cte
option (maxrecursion 0);

递归 CTE（在这种情况下）可以处理几十或几百行。这可能是可以容忍的低几千。但是一百万行，它可能会很慢（尽管我想比游标快）。您可以使用它进行更新，方法是：

update incremental_staging
    set filter_value = cte.filter_value
    from cte
    where cte.id = incremental_staging.id;

推荐阅读