sql - 需要根据每个财政周计算平均成本

Question

我希望有人能帮助解决我遇到的这个问题，我正在尝试从以下数据示例中计算出每周平均值:

    Practice ID      Cost          FiscalWeek
        1            10.00             1
        1            33.00             2
        1            55.00             3
        1            18.00             4
        1            36.00             5
        1            24.00             6
        13           56.00             1
        13           10.00             2
        13           24.00             3
        13           30.00             4
        13           20.00             5
        13           18.00             6

我想要的是按练习 ID 分组，但计算出每个练习的平均值(其中有超过 500 个，而不仅仅是上面的那些)并计算出每周，例如在第 1 周将没有平均值，但第 2 周将是第 1 周和第 2 周的平均值，然后第 3 周将是第 1、2 和 3 周的平均值，依此类推。然后我需要通过实践 ID 和每个财政周来显示这一点。

目前我有一些不漂亮的代码，必须有更简单的方法，这段代码是:

我将所有数据传递到一个表变量中，然后使用 CTE 我然后使用 case 语句来设置每个单独的星期，例如:

  CASE WHEN fiscalweek = 1 THEN cost ELSE 0 END AS [1],
  CASE WHEN fiscalweek = 2 THEN cost ELSE 0 END AS [2],
  CASE WHEN fiscalweek = 3 THEN cost ELSE 0 END AS [3]

然后这会将第 1 周的成本等带回到它自己的列中，例如1、2、3 等，然后我使用第二个 CTE 对每周的列求和，例如计算第 6 周我将使用此代码:

    sum([1]) as 'Average Wk 1',
    sum([1]+[2])/2 as 'Average Wk 2',
    sum([1]+[2]+[3])/3 as 'Average Wk 3',
    sum([1]+[2]+[3]+[4])/4 as 'Average Wk 4',
    sum([1]+[2]+[3]+[4]+[5])/5 as 'Average Wk 5'
    sum([1]+[2]+[3]+[4]+[5]+[6])/6 as 'Average Wk 6'

我考虑过各种不同的方法来在 T-SQL 中准确计算出这个平均值，因此我最终可以将其放入 SSRS 中。我考虑过使用 While..Loop、Cursor，但没有找到一种简单的方法来执行此操作。

Answer 1

您正在寻找平均值的累积平均值。在支持窗口/分析功能的数据库中，您可以:

select fiscalweek, avg(cost) as avgcost,
       avg(avg(cost)) over (order by fiscalweek) as cumavg
from practices p
group by fiscalweek
order by 1;

如果您没有窗口函数，那么您需要使用某种形式的相关子查询或连接:

select p1.fiscalweek, avg(p1.avgcost)
from (select fiscalweek avg(cost) as avgcost
      from practices p
      group by fiscalweek
     ) p1 join
     (select fiscalweek avg(cost) as avgcost
      from practices p
      group by fiscalweek
     ) p2
     on p12 <= p1
group by p1.fiscalweek
order by 1;

我确实想提醒您，您正在计算“平均值的平均值”。这与累积平均值不同，后者可以计算为:

select fiscalweek, 
      (sum(sum(cost)) over (order by fiscalweek) /
       sum(count(*)) over (order by fiscalweek)
      ) avgcost
from practices p
group by fiscalweek
order by 1;

将每周视为最终平均值中的一个数据点(您似乎想要的)。其他每周按一周内的点数加权(后一种解决方案)。当周有不同的点数时，这些会产生非常不同的结果。