Скользящее среднее SQL
Как создать скользящее среднее в SQL?
Текущая таблица:
Date Clicks
2012-05-01 2,230
2012-05-02 3,150
2012-05-03 5,520
2012-05-04 1,330
2012-05-05 2,260
2012-05-06 3,540
2012-05-07 2,330
Желаемая таблица или вывод:
Date Clicks 3 day Moving Average
2012-05-01 2,230
2012-05-02 3,150
2012-05-03 5,520 4,360
2012-05-04 1,330 3,330
2012-05-05 2,260 3,120
2012-05-06 3,540 3,320
2012-05-07 2,330 3,010
13 ответов
Один из способов сделать это - присоединиться к одному столу несколько раз.
select
(Current.Clicks
+ isnull(P1.Clicks, 0)
+ isnull(P2.Clicks, 0)
+ isnull(P3.Clicks, 0)) / 4 as MovingAvg3
from
MyTable as Current
left join MyTable as P1 on P1.Date = DateAdd(day, -1, Current.Date)
left join MyTable as P2 on P2.Date = DateAdd(day, -2, Current.Date)
left join MyTable as P3 on P3.Date = DateAdd(day, -3, Current.Date)
Настройте компонент DateAdd в предложениях ON, чтобы он соответствовал тому, хотите ли вы, чтобы ваша скользящая средняя была строго от прошлого до настоящего или от нескольких дней до нескольких дней вперед.
- Это хорошо работает для ситуаций, когда вам нужно скользящее среднее только по нескольким точкам данных.
- Это не оптимальное решение для скользящих средних с несколькими точками данных.
Это вечнозеленый вопрос Джо Селько. Я игнорирую, какая платформа СУБД используется. Но в любом случае Джо смог ответить более 10 лет назад стандартным SQL.
Джо Селко, SQL-головоломка и ответы, цитата: "Эта последняя попытка обновления предполагает, что мы можем использовать предикат для построения запроса, который даст нам скользящее среднее:"
SELECT S1.sample_time, AVG(S2.load) AS avg_prev_hour_load
FROM Samples AS S1, Samples AS S2
WHERE S2.sample_time
BETWEEN (S1.sample_time - INTERVAL 1 HOUR)
AND S1.sample_time
GROUP BY S1.sample_time;
Является ли дополнительный столбец или подход запроса лучше? Запрос технически лучше, потому что подход UPDATE денормализует базу данных. Однако если записываемые исторические данные не изменятся и вычисление скользящего среднего будет дорогостоящим, вы можете рассмотреть возможность использования метода столбцов.
Пример MS SQL:
CREATE TABLE #TestDW
( Date1 datetime,
LoadValue Numeric(13,6)
);
INSERT INTO #TestDW VALUES('2012-06-09' , '3.540' );
INSERT INTO #TestDW VALUES('2012-06-08' , '2.260' );
INSERT INTO #TestDW VALUES('2012-06-07' , '1.330' );
INSERT INTO #TestDW VALUES('2012-06-06' , '5.520' );
INSERT INTO #TestDW VALUES('2012-06-05' , '3.150' );
INSERT INTO #TestDW VALUES('2012-06-04' , '2.230' );
Запрос SQL Puzzle:
SELECT S1.date1, AVG(S2.LoadValue) AS avg_prev_3_days
FROM #TestDW AS S1, #TestDW AS S2
WHERE S2.date1
BETWEEN DATEADD(d, -2, S1.date1 )
AND S1.date1
GROUP BY S1.date1
order by 1;
select t2.date, round(sum(ct.clicks)/3) as avg_clicks
from
(select date from clickstable) as t2,
(select date, clicks from clickstable) as ct
where datediff(t2.date, ct.date) between 0 and 2
group by t2.date
Пример тут.
Очевидно, вы можете изменить интервал на то, что вам нужно. Вы также можете использовать count() вместо магического числа, чтобы упростить его изменение, но это также замедлит его.
select *
, (select avg(c2.clicks) from #clicks_table c2
where c2.date between dateadd(dd, -2, c1.date) and c1.date) mov_avg
from #clicks_table c1
Общий шаблон для скользящих средних, который хорошо масштабируется для больших наборов данных
WITH moving_avg AS (
SELECT 0 AS [lag] UNION ALL
SELECT 1 AS [lag] UNION ALL
SELECT 2 AS [lag] UNION ALL
SELECT 3 AS [lag] --ETC
)
SELECT
DATEADD(day,[lag],[date]) AS [reference_date],
[otherkey1],[otherkey2],[otherkey3],
AVG([value1]) AS [avg_value1],
AVG([value2]) AS [avg_value2]
FROM [data_table]
CROSS JOIN moving_avg
GROUP BY [otherkey1],[otherkey2],[otherkey3],DATEADD(day,[lag],[date])
ORDER BY [otherkey1],[otherkey2],[otherkey3],[reference_date];
И для взвешенных скользящих средних:
WITH weighted_avg AS (
SELECT 0 AS [lag], 1.0 AS [weight] UNION ALL
SELECT 1 AS [lag], 0.6 AS [weight] UNION ALL
SELECT 2 AS [lag], 0.3 AS [weight] UNION ALL
SELECT 3 AS [lag], 0.1 AS [weight] --ETC
)
SELECT
DATEADD(day,[lag],[date]) AS [reference_date],
[otherkey1],[otherkey2],[otherkey3],
AVG([value1] * [weight]) / AVG([weight]) AS [wavg_value1],
AVG([value2] * [weight]) / AVG([weight]) AS [wavg_value2]
FROM [data_table]
CROSS JOIN weighted_avg
GROUP BY [otherkey1],[otherkey2],[otherkey3],DATEADD(day,[lag],[date])
ORDER BY [otherkey1],[otherkey2],[otherkey3],[reference_date];
В улье, может быть, вы могли бы попробовать
select date, clicks, avg(clicks) over (order by date rows between 2 preceding and current row) as moving_avg from clicktable;
Предположим, что x - это усредненное значение, а xDate - значение даты:
ВЫБЕРИТЕ avg(x) в myTable WHERE xDate МЕЖДУ dateadd(d, -2, xDate) и xDate
Используйте другой предикат соединения:
SELECT current.date
,avg(periods.clicks)
FROM current left outer join current as periods
ON current.date BETWEEN dateadd(d,-2, periods.date) AND periods.date
GROUP BY current.date HAVING COUNT(*) >= 3
Наличие оператора предотвратит возвращение любых дат без хотя бы N значений.
ПРИМЕЧАНИЕ: ЭТО НЕ ОТВЕТ, а расширенный пример кода ответа Диего Скараваджи. Я отправляю это как ответ, поскольку секция комментария недостаточна. Обратите внимание, что я параметризировал период для перемещения.
declare @p int = 3
declare @t table(d int, bal float)
insert into @t values
(1,94),
(2,99),
(3,76),
(4,74),
(5,48),
(6,55),
(7,90),
(8,77),
(9,16),
(10,19),
(11,66),
(12,47)
select a.d, avg(b.bal)
from
@t a
left join @t b on b.d between a.d-(@p-1) and a.d
group by a.d
Я не уверен, что ваш ожидаемый результат (выход) показывает классическое "простое скользящее (скользящее) среднее" за 3 дня. Потому что, например, первая тройка чисел по определению дает:
ThreeDaysMovingAverage = (2.230 + 3.150 + 5.520) / 3 = 3.6333333
но вы ожидаете 4.360
и это сбивает с толку.
Тем не менее, я предлагаю следующее решение, которое использует оконную функцию AVG
, Этот подход гораздо более эффективен (понятен и менее ресурсоемок), чем SELF-JOIN
представлены в других ответах (и я удивлен, что никто не дал лучшего решения).
-- Oracle-SQL dialect
with
data_table as (
select date '2012-05-01' AS dt, 2.230 AS clicks from dual union all
select date '2012-05-02' AS dt, 3.150 AS clicks from dual union all
select date '2012-05-03' AS dt, 5.520 AS clicks from dual union all
select date '2012-05-04' AS dt, 1.330 AS clicks from dual union all
select date '2012-05-05' AS dt, 2.260 AS clicks from dual union all
select date '2012-05-06' AS dt, 3.540 AS clicks from dual union all
select date '2012-05-07' AS dt, 2.330 AS clicks from dual
),
param as (select 3 days from dual)
select
dt AS "Date",
clicks AS "Clicks",
case when rownum >= p.days then
avg(clicks) over (order by dt
rows between p.days - 1 preceding and current row)
end
AS "3 day Moving Average"
from data_table t, param p;
Ты видишь это AVG
обернут case when rownum >= p.days then
заставить NULL
s в первых строках, где "скользящее среднее за 3 дня" не имеет смысла.
Для этой цели я хотел бы создать вспомогательную / размерную таблицу дат, например
create table date_dim(date date, date_1 date, dates_2 date, dates_3 dates ...)
в то время как date
это ключ, date_1
на этот день, date_2
содержит этот день и предыдущий день; date_3
...
Тогда вы можете сделать равное соединение в улье.
Используя вид как:
select date, date from date_dim
union all
select date, date_add(date, -1) from date_dim
union all
select date, date_add(date, -2) from date_dim
union all
select date, date_add(date, -3) from date_dim
--@p1 is period of moving average, @01 is offset
declare @p1 as int
declare @o1 as int
set @p1 = 5;
set @o1 = 3;
with np as(
select *, rank() over(partition by cmdty, tenor order by markdt) as r
from p_prices p1
where
1=1
)
, x1 as (
select s1.*, avg(s2.val) as avgval from np s1
inner join np s2
on s1.cmdty = s2.cmdty and s1.tenor = s2.tenor
and s2.r between s1.r - (@p1 - 1) - (@o1) and s1.r - (@o1)
group by s1.cmdty, s1.tenor, s1.markdt, s1.val, s1.r
)
Мы можем применить "грязный" метод левого внешнего соединения Джо Селко (цитируемый выше Диего Скаравагги), чтобы ответить на вопрос, который был задан.
declare @ClicksTable table ([Date] date, Clicks int)
insert into @ClicksTable
select '2012-05-01', 2230 union all
select '2012-05-02', 3150 union all
select '2012-05-03', 5520 union all
select '2012-05-04', 1330 union all
select '2012-05-05', 2260 union all
select '2012-05-06', 3540 union all
select '2012-05-07', 2330
Этот запрос:
SELECT
T1.[Date],
T1.Clicks,
-- AVG ignores NULL values so we have to explicitly NULLify
-- the days when we don't have a full 3-day sample
CASE WHEN count(T2.[Date]) < 3 THEN NULL
ELSE AVG(T2.Clicks)
END AS [3-Day Moving Average]
FROM @ClicksTable T1
LEFT OUTER JOIN @ClicksTable T2
ON T2.[Date] BETWEEN DATEADD(d, -2, T1.[Date]) AND T1.[Date]
GROUP BY T1.[Date]
Создает запрошенный вывод:
Date Clicks 3-Day Moving Average
2012-05-01 2,230
2012-05-02 3,150
2012-05-03 5,520 4,360
2012-05-04 1,330 3,330
2012-05-05 2,260 3,120
2012-05-06 3,540 3,320
2012-05-07 2,330 3,010