时间戳

首页 > 解决方案 > 计算先前多个期间的平均值

sql - 计算先前多个期间的平均值

问题描述

我想计算下一个季度按索赔类型分组的前 4 个季度的平均值。我找到了答案，但它似乎与我当前使用的 SQL Server 2008 不兼容。我似乎无法为 SQL Server 2008 找到相同的功能或解决方案。

在我的版本中，您似乎无法订购或使用rows between。over function

有人可以帮助我为 SQL Server 2008 获得相同的功能吗？

我想获得该列前 4 个季度的平均值[Total Paid]

SELECT 
    [PolicyNo] AS [PolicyNo],
    [Quarter] AS [Quarter],
    [Claim Grouping] AS [Claim Grouping],
    [Year] AS [Year],
    [Total Claim] AS [Total Claim],
    AVG([Total Claim]) OVER (PARTITION BY [Claim Grouping] ORDER BY [Year], [Quarter]
                             ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS [4QuarterAverage]
FROM 
    BaseTable

一个添加的列，其中包含for4QuarterAverage的平均值4 preceding quarters[Total Paid]

标签： sqlsql-serversql-server-2008

解决方案

您可以将其替换为相关的子查询或apply：

SELECT bt.*, bt2.[4QuarterAverage]
FROM BaseTabel bt OUTER APPLY
     (SELECT AVG(bt2.[Total Claim]) as [4QuarterAverage]
      FROM (SELECT TOP (4) bt2.*
            FROM BaseTabel bt2
            WHERE (bt2.YEAR < bt.YEAR OR
                   (bt2.YEAR = bt.YEAR AND bt2.Quarter <= bt.Quarter)
                  )
            ORDER BY bt2.YEAR DESC, bt2.Quarter DESC
           ) bt2
      ) bt2;

推荐阅读