给猫剥皮的方法有很多种--尤其是如果你关心最高性能的话--但有一种方法是这样的：

WITH 
    DailyAmounts AS 
        (
        SELECT
            Date,
            SUM(Amount) AS DailyAmount
        FROM
            Table
        GROUP BY
            Date
        ) DailyAmounts

SELECT
    *,
    (
    SELECT 
        SUM(DailyAmount)
    FROM
        DailyAmounts DailyAmounts2
    WHERE
        Date BETWEEN 
            DATEADD(DAY, -6, DailyAmounts.Date)
            AND DailyAmounts.Date
    ) AS WeeklyAverageAmount
FROM
    DailyAmounts
;

本质上，通过CTE创建一个每日金额表；然后，添加一个周平均值列，并通过同一CTE上的子查询进行填充。

如果我没理解错的话。你需要每周的平均收益。您可以这样使用：

set datefirst 1;
WITH tempTable AS (SELECT datepart(week, yourDateColumn) as weekNr, amount from testTable)

Select weekNr, SUM(amount) from tempTable
group by weekNr

如果您需要查询日常平均数据，可以这样查询：

WITH tempTable AS (SELECT DATENAME(WEEKDAY, yourDateColumn) AS weekDay, amount from testTable)
Select weekDay, SUM(amount) from tempTable
group by weekDay

看起来您可以在这里使用窗口函数。

您只需确保您的数据每天只有一行。您可以通过先分组来完成此操作。

SELECT
  *,
  SUM(DailyAmount) OVER (ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS WeeklyAverageAmount
FROM (
    SELECT
      t.Date,
      SUM(t.Amount) AS DailyAmount
    FROM YourTable t
    GROUP BY
      t.Date
) ByDay;

只需注意，求和或相加得到的是总数，而不是平均值。

您的示例数据只有一个月的第一天有多行，所以我看不出您如何才能从中获得7天的平均值。(除非您使用的是yyyy-dd-MM格式，但我从未见过任何人这样做。)

如果要查找特定的日期范围，通常不应依赖行数，而应查看日期本身。

所以，我真的不确定你到底在寻找什么，但也许这可以帮助你走上正轨：

-- prep data
select * 
into #tmp
from ( values 
    ('2020-01-01',  3500.03),
    ('2020-01-01',  3000.03),
    ('2020-01-01',  3200.86),
    ('2020-01-01',  4500.00),
    ('2020-02-01',  2100.23),
    ('2020-02-01',  1000.00),
    ('2020-03-01',  2800.93)
)t(date, amount)

-- output 
select 
    t.Date
    ,t.amount
    ,sum(amount) over (order by date) as running_total
    ,avg(amount) over (order by date) as running_avg
    ,tca.rolling_sevenday_total
    ,tca.rolling_sevenday_avg
from #tmp t
cross apply (
    select 
        sum(amount) as rolling_sevenday_total,
        avg(amount) as rolling_sevenday_avg
    from #tmp t7
    where 
        t7.date between dateadd(dd, -7, t.date) and t.date

) tca

日期|金额|Running_Total|Running_avg|ROLING_SEVEDY_TOTAL|ROLING_SEVEDY_AVG
-|
2020-01-01|3500.03|14200.92|3550.230000|14200.92|3550.230000
2020-01-01|3000.03|14200.92|3550.230000|14200.92|3550.230000
2020-01-01|3200.86|14200.92|3550.230000|14200.92|3550.230000
2020-01-01|4500.00|14200.92|3550.230000|14200.92|3550.230000
2020年-02-01|2100.23|17301.15|2883.525000|3100.23|1550.115000
2020年-02-01|1000.00|17301.15|2883.525000|3100.23|1550.115000
2020-03-01|2800.93|20102.08|2871.725714|2800.93|2800.930000

如果您的数据实际上是按天计算的，则如下所示

-- prep data
select * 
into #tmp2
from ( values 
    ('2020-01-01',  3500.03),
    ('2020-01-02',  3000.03),
    ('2020-01-03',  3200.86),
    ('2020-01-04',  4500.00),
    ('2020-01-05',  4500.00),
    ('2020-01-06',  4500.00),
    ('2020-01-07',  4500.00),
    ('2020-02-01',  2100.23),
    ('2020-02-02',  1000.00),
    ('2020-02-03',  2800.93)
)t(date, amount)

-- output 
select 
    t.Date
    ,t.amount
    ,sum(amount) over (order by date) as running_total
    ,avg(amount) over (order by date) as running_avg
    ,tca.rolling_sevenday_total
    ,tca.rolling_sevenday_avg
from #tmp2 t
cross apply (
    select 
        sum(amount) as rolling_sevenday_total,
        avg(amount) as rolling_sevenday_avg
    from #tmp2 t7
    where 
        t7.date between dateadd(dd, -7, t.date) and t.date

) tca

日期|金额|Running_Total|Running_avg|ROLING_SEVEDY_TOTAL|ROLING_SEVEDY_AVG
-|
2020-01-01|3500.03|3500.03|3500.030000|3500.03|3500.030000
2020年01月02日|3000.03|6500.06|3250.030000|6500.06|3250.030000
2020年01月03日|3200.86|9700.92|3233.640000|9700.92|3233.640000
2020年01月04日|4500.00|14200.92|3550.230000|14200.92|3550.230000
2020年01月05日|4500.00|18700.92|3740.184000|18700.92|3740.184000
2020-01-06|4500.00|23200.92|3866.820000|23200.92|3866.820000
2020年01月07日|4500.00|27700.92|3957.274285|27700.92|3957.274285
2020年-02-01|2100.23|29801.15|3725.143750|2100.23|2100.230000
2020年-02-02|1000.00|30801.15|3422.350000|3100.23|1550.115000
2020-02-03|2800.93|33602.08|3360.208000|5901.16|1967.053333