Подтвердить что ты не робот

Скользящее среднее 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
4b9b3361

ОТВЕТЫ

Ответ 1

Один из способов сделать это - несколько раз присоединиться к одной таблице.

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-Claus, чтобы он соответствовал тому, хотите ли вы, чтобы ваша скользящая средняя была строго из прошлых дней или дней назад, через дни вперед.

  • Это хорошо работает для ситуаций, когда вам нужна скользящая средняя всего за несколько точек данных.
  • Это не оптимальное решение для скользящих средних с более чем несколькими точками данных.

Ответ 2

Это вопрос Evergreen Joe Celko. Я игнорирую используемую платформу СУБД. Но в любом случае Джо смог ответить более 10 лет назад стандартным SQL.

Joe Celko Задачи и ответы 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;

Ответ 3

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() вместо магического числа, чтобы было легче изменить, но это также замедлит его.

Ответ 4

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

Ответ 5

Предположим, что x является усредненным значением, а xDate - значением даты:

SELECT avg (x) из myTable WHERE xDate BETWEEN dateadd (d, -2, xDate) и xDate

Ответ 6

Используйте другой предикат соединения:

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.

Ответ 7

Общий шаблон для скользящих средних, который хорошо масштабируется для больших наборов данных

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];

Ответ 8

Для этой цели я хотел бы создать вспомогательную/размерную таблицу даты, например

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

Ответ 9

ПРИМЕЧАНИЕ: ЭТО НЕ ОТВЕТ, но расширенный образец кода ответа Diego Scaravaggi. Я отправляю его как ответ, поскольку раздел комментариев недостаточен. Обратите внимание, что я параметризовал период для Moving aveage.

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

Ответ 10

[email protected] 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
)

Ответ 11

Я не уверен, что ваш ожидаемый результат (результат) показывает классическое "простое перемещение (скользящее) среднее" на 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 в первых строках, где "3-дневное скользящее среднее" не имеет смысла.

Ответ 12

В улье, возможно, вы могли попробовать

select date, clicks, avg(clicks) over (order by date rows between 2 preceding and current row) as moving_avg from clicktable;

Ответ 13

Мы можем применить метод Joe Celko "грязный" метод внешнего внешнего соединения (как было упомянуто выше Диего Скаравагги), чтобы ответить на вопрос по его просьбе.

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