Скользящее среднее 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 заставить NULLs в первых строках, где "скользящее среднее за 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
Другие вопросы по тегам