我正在寻找一种快速的方法来创建一个大型sql Server 2008数据集中的累积总计,该数据集可以通过使用多个赋值变量解决方案按特定列进行分区.作为一个非常基本的示例,我想在下面创建“cumulative_total”列：
user_id | month | total | cumulative_total 1 | 1 | 2.0 | 2.0 1 | 2 | 1.0 | 3.0 1 | 3 | 3.5 | 8.5 2 | 1 | 0.5 | 0.5 2 | 2 | 1.5 | 2.0 2 | 3 | 2.0 | 4.0
我们传统上使用相关子查询来完成此操作,但是对于大量数据(200,000行和几个不同类别的运行总计),这并没有给我们提供理想的性能.
我最近阅读了有关在此处使用多个赋值变量进行累积求和的信息
http://sqlblog.com/blogs/paul_nielsen/archive/2007/12/06/cumulative-totals-screencast.aspx
UPDATE my_table SET @CumulativeTotal=cumulative_total=@CumulativeTotal+ISNULL(total,0)
对于上述示例中的单个用户(用户1或用户2),此解决方案似乎非常快速.但是,我需要按用户进行有效分区 – 按用户按月累计总计.
有没有人知道扩展多重赋值变量概念以解决这个问题的方法,或者除相关子查询或游标之外的任何其他想法？
非常感谢任何提示.
解决方法
您在sql Server 2008中的选项相当有限 – 因为您可以根据上述方法执行某些操作(称为“quirky update”),或者您可以在CLR中执行某些操作.
我个人会选择CLR,因为它可以保证工作,而古怪的更新语法不是正式支持的(因此可能会在未来的版本中破坏).
你正在寻找的古怪更新语法的变化将是这样的：
UPDATE my_table
SET @CumulativeTotal=cumulative_total=ISNULL(total,0) +
CASE WHEN @user=@lastUser THEN @CumulativeTotal ELSE 0 END,@user=lastUser
值得注意的是,在sql Server 2012中引入了对窗口函数的RANGE支持,因此可以通过最有效的方式表达,同时100％支持.
