sql - 跳过当前行并对 SQL Server 2012 中的所有剩余行应用分析函数

Question

关闭。这个问题需要details or clarity .它目前不接受答案。












想改善这个问题吗？通过 editing this post 添加详细信息并澄清问题.

5年前关闭。




Improve this question




跳过 当前行 并对 PARTITION BY 中的所有剩余行应用分析函数SQL Server 2012 中的子句

示例:我在一个表中有 12 行数据，我想计算并附加 AVERAGE跳过 CURRENT ROW值(value)。

COL1    COL2    AVG_SKIP_CUR_ROW 
a          3    7.71
a          6    7.28
a          6    7.28
a          8    7
a          12   6.42
a          5    7.43
a          8    7
a          9    6.86

Answer 1

继续我的评论，如果您希望将该函数应用于当前行之后的所有内容，请使用 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING或者对于当前行之前的所有内容，ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
这是一个简单的示例(一种可能的，可能并不理想)将这些组合起来以排除当前行的方法。在这种情况下，一个简单的 SUM聚合(您可以在单个选择中执行此操作，为了清楚起见，我将其拆分为子查询):

CREATE TABLE #Test(Val INT)

INSERT INTO #Test VALUES(1),(2),(3),(4),(5)

SELECT Val, SumPreceding+SumFollowing SumExcludingCurrent FROM (
    SELECT Val
    ,ISNULL(SUM(Val) OVER (ORDER BY VAL ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) SumPreceding
    ,ISNULL(SUM(Val) OVER (ORDER BY VAL ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING), 0) SumFollowing
    FROM #Test
    ) A
ORDER BY Val

Val         SumExcludingCurrent
----------- -------------------
1           14
2           13
3           12
4           11
5           10

对于您的更新，由于您正在计算不包括当前值的 col 平均值，因此简化为:

CREATE TABLE #Test(Col1 CHAR, Col2 INT)

INSERT INTO #Test VALUES('a',3),('a',6),('a',6),('a',8),('a',12),('a',5),('a',8),('a',9)

SELECT Col1
       ,Col2
       ,(SUM(Col2) OVER () - Col2) / CAST(COUNT(*) OVER() - 1 AS NUMERIC(4,2)) Avg_Skip_Cur_Row
    FROM #Test


Col1 Col2        Avg_Skip_Cur_Row
---- ----------- ---------------------------------------
a    3           7.714285
a    6           7.285714
a    6           7.285714
a    8           7.000000
a    12          6.428571
a    5           7.428571
a    8           7.000000
a    9           6.857142

因为我玩得很开心，所以这里有另一个版本。由于测试数据没有键，我添加了一个行号。

SELECT
    Col1
    ,Col2
    ,(ISNULL(SUM(Col2) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) +
      ISNULL(SUM(Col2) OVER (ORDER BY rn ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING),0)) /
      CAST(COUNT(*) OVER() - 1 AS NUMERIC(4,2)) Avg_Skip_Cur_Row
FROM 
    (SELECT *, ROW_NUMBER() OVER (ORDER BY Col2) rn FROM #Test) A