Оператор выбора SQL для вычисления под управлением среднего столбца

Предполагая, что столбец Id является последовательным, вот упрощенный запрос для таблицы с именем «MyTable»:

SELECT 
    b.Id,
    b.Number,
    (
      SELECT 
       AVG(a.Number) 
      FROM 
       MyTable a 
     WHERE 
       a.id >= (b.Id - 3) 
       AND a.id < b.Id
       AND b.Id > 3 
     ) as Average
FROM 
    MyTable b;

Редактировать: я упустил момент, что он должен усреднять три предыдущих рекорда ...

Для общего скользящего среднего, я думаю, сработает что-то вроде этого:

SELECT
    id, number, 
    SUM(number) OVER (ORDER BY ID) / 
       ROW_NUMBER() OVER (ORDER BY ID) AS [RunningAverage]
FROM myTable
ORDER BY ID

Ознакомьтесь с некоторыми решениями здесь . Я уверен, что вы легко сможете адаптировать один из них.

Если вы хотите, чтобы это было действительно производительно, и вы не боитесь копаться в редко используемых областях SQL Server, вам следует подумать о написании собственной агрегатной функции. В SQL Server 2005 и 2008 появилась интеграция со средой CLR, включая возможность написания пользовательских агрегатных функций. Настраиваемый агрегированный промежуточный итог, безусловно, был бы наиболее эффективным способом вычисления такого скользящего среднего.

Простое самосоединение, кажется, работает намного лучше, чем подзапрос со ссылкой на строку

Генерация 10 тыс. Строк тестовых данных:

drop table test10k
create table test10k (Id int, Number int, constraint test10k_cpk primary key clustered (id))

;WITH digits AS (
    SELECT 0 as Number
    UNION SELECT 1
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    UNION SELECT 5
    UNION SELECT 6
    UNION SELECT 7
    UNION SELECT 8
    UNION SELECT 9
)
,numbers as (
    SELECT 
        (thousands.Number * 1000) 
        + (hundreds.Number * 100) 
        + (tens.Number * 10) 
        + ones.Number AS Number
    FROM digits AS ones 
    CROSS JOIN digits AS tens
    CROSS JOIN digits AS hundreds
    CROSS JOIN digits AS thousands
)
insert test10k (Id, Number)
select Number, Number
from numbers 

Я бы вытащил частный случай первых трех строк основного запроса, вы можете ОБЪЕДИНЯТЬ ВСЕ их обратно, если вы действительно хотите, чтобы это было в наборе строк. Запрос самосоединения:

;WITH   NumberedRows
AS
(
    SELECT  rta.*, row_number() OVER (ORDER BY rta.ID ASC) AS RowNumber
    FROM    test10k rta
)

SELECT  nr.ID, nr.Number,
    avg(trailing.Number) as MovingAverage
FROM    NumberedRows nr
    join NumberedRows as trailing on trailing.RowNumber between nr.RowNumber-3 and nr.RowNumber-1
where nr.Number > 3
group by nr.id, nr.Number

На моем компьютере это занимает около 10 секунд, метод подзапроса, который продемонстрировал Аарон Алтон, занимает около 45 секунд (после того, как я изменил его, чтобы отразить мою исходную тестовую таблицу):

;WITH   NumberedRows
AS
(
    SELECT  rta.*, row_number() OVER (ORDER BY rta.ID ASC) AS RowNumber
    FROM    test10k rta
)
SELECT  nr.ID, nr.Number,
    CASE
            WHEN nr.RowNumber <=3 THEN NULL
            ELSE (  SELECT  avg(Number) 
                            FROM    NumberedRows 
                            WHERE   RowNumber < nr.RowNumber
                            AND             RowNumber >= nr.RowNumber - 3
                    )
    END AS MovingAverage
FROM    NumberedRows nr

Если вы выполните SET STATISTICS PROFILE ON, вы можете видеть, что в катушке таблицы выполняется 10 тыс. Выполнений самосоединения. Подзапрос содержит 10 тыс. Выполнений на этапах фильтрации, агрегирования и других.

В качестве альтернативы вы можете денормализовать и сохранить предварительно вычисленные текущие значения. Описано здесь:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/denormalizing-to-enforce-business-rules-running-totals.aspx

Выборка выполняется так же быстро как идет. Конечно, модификации происходят медленнее.