Вычислить скользящее среднее, используя оконные функции SQL с ведущими нулевыми значениями, если данных недостаточно
Я хочу рассчитать скользящее среднее, используя оконные функции SQL. Следующий пример двухдневной скользящей средней в основном работает нормально, но он также вычисляет среднее значение, если доступна только одна точка данных. Я скорее хочу, чтобы среднее значение было нулевым, пока доступно недостаточно данных
create table average(
nr int,
value float
);
insert into average values (1, 2), (2, 4), (3, 6), (3, 8), (4, 10);
SELECT
nr,
value,
AVG(value) OVER (ORDER BY nr ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)::FLOAT AS "Moving-Average-2"
FROM average;
результат:
1 2 2
2 4 3
3 6 5
3 8 7
4 10 9
ожидаемый результат:
1 2 null
2 4 3
3 6 5
3 8 7
4 10 9
РЕДАКТИРОВАТЬ 1: Конечно, в среднем может быть что угодно, не только 2.
Ответы 4
я думаю, что null не входит в первую строку agv, иначе ниже будет работать с использованием
BETWEEN 1 PRECEDING AND CURRENT ROW
select nr, value,
avg(value) OVER (ORDER BY nr ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS "Moving-Average-2"
from average;
МЕЖДУ 1 ПРЕДЫДУЩЕЙ И ТЕКУЩЕЙ РЯДКОЙ
но вы можете справиться с этим, используя случай, когда
select nr, value,
case when nr=1 then null else
avg(value) OVER (ORDER BY nr ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) end AS "Moving-Average-2"
from average;
nr value Moving-Average-2
1 2
2 4 3
3 6 5
3 8 7
4 10 9
Вы можете использовать другую оконную функцию (COUNT()), чтобы убедиться, что в окне доступны как минимум две записи, прежде чем выполнять вычисления, например:
SELECT
nr,
value,
CASE WHEN COUNT(*) OVER(ORDER BY nr ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) > 1
THEN AVG(value) OVER (ORDER BY nr ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)::FLOAT
ELSE NULL
END AS "Moving-Average-2"
FROM average;
| nr | value | Moving-Average-2 |
| --- | ----- | ---------------- |
| 1 | 2 | |
| 2 | 4 | 3 |
| 3 | 6 | 5 |
| 3 | 8 | 7 |
| 4 | 10 | 9 |
Поскольку вы формируете среднее значение только между 1 предыдущей строкой и текущей, просто использовать lag() может быть проще всего:
select nr, value
,(value + lag(value, 1, NULL) OVER (ORDER BY nr)) / 2 AS "Moving-Average-2"
from average;
lag() имеет перегруженный вариант, который позволяет указать значение по умолчанию (в качестве третьего параметра) в случае отсутствия строки. Ставьте NULL и вы на месте. Или, поскольку NULL в любом случае используется по умолчанию, просто:
... ,(value + lag(value) OVER (ORDER BY nr)) / 2 AS "Moving-Average-2"
Хотя базовый столбец таблицы имеет тип float, в этом случае вам не нужно приводить к float.
Предполагается, что значение столбца определено NOT NULL (как указано в ваших примерных данных). В противном случае вы также получите NULL, где в предыдущей строке есть value IS NULL, а в текущей строке есть значение, а в этом случае avg() возвращает значение! (Или это может быть то, что вы хотите в любом случае, учитывая ваш вопрос.)
Это может быть удобным местом для использования спецификации окна:
select a.*,
(case when row_number() over w > 1
then avg(value) over w
end) as running_average
from average a
window w as (order by nr rows between 1 preceding and current row);
Этот ответ был блестящим и очень простым для понимания, большое спасибо !!!! Я смог легко адаптировать это, чтобы создать 4-недельную скользящую среднюю.
На самом деле это единственный способ, который не только может обрабатывать разные окна скользящих средних, но также иметь дело с обычными нулями с помощью
sum(case when x not null then 1 else 0) > 1