使用窗口函数计算移动总和/平均值时的动态行范围 (SQL Server) [英] Dynamic row range when calculating moving sum/average using window functions (SQL Server)

问题描述

我目前正在编写一个示例脚本，它允许我计算前两行和当前行的总和.但是，我想将数字2"作为变量.我试过声明一个变量，或者直接在查询中进行转换，但总是弹出一个语法错误.有可能的解决方案吗?

I'm currently working on a sample script which allows me to calculate the sum of the previous two rows and the current row. However, I would like to make the number '2' as a variable. I've tried declaring a variable, or directly casting in the query, yet a syntax error always pops up. Is there a possible solution?

DECLARE @myTable TABLE  (myValue INT)

INSERT INTO @myTable ( myValue ) VALUES  ( 5)
INSERT INTO @myTable ( myValue ) VALUES  ( 6)
INSERT INTO @myTable ( myValue ) VALUES  ( 7)
INSERT INTO @myTable ( myValue ) VALUES  ( 8)
INSERT INTO @myTable ( myValue ) VALUES  ( 9)
INSERT INTO @myTable ( myValue ) VALUES  ( 10)

SELECT 
    SUM(myValue) OVER (ORDER BY myValue 
                       ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM @myTable

推荐答案

DECLARE @test VARCHAR = 1
DECLARE @sqlCommand VARCHAR(1000)
DECLARE @myTable TABLE  (myValue INT)

INSERT INTO @myTable ( myValue ) VALUES  ( 5)
INSERT INTO @myTable ( myValue ) VALUES  ( 6)
INSERT INTO @myTable ( myValue ) VALUES  ( 7)
INSERT INTO @myTable ( myValue ) VALUES  ( 8)
INSERT INTO @myTable ( myValue ) VALUES  ( 9)
INSERT INTO @myTable ( myValue ) VALUES  ( 10)

SET @sqlCommand = 'SELECT SUM(myValue) OVER (ORDER BY myValue ROWS BETWEEN ' + @test + ' PRECEDING AND CURRENT ROW)
                  FROM #temp'

EXEC (@sqlCommand)

这篇关于使用窗口函数计算移动总和/平均值时的动态行范围 (SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！