SQL移动平均

Question

SQL移动平均

16

如何在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

- Don P

你使用的数据库系统是什么？ - Brian Webster

@BrianWebster：他在评论我的（现已删除的）帖子中说：他正在使用Hive。但是你已经删除了它的标签。 - user330315

好的，已修复 - 我真的没有意识到那是一个数据库系统。 - Brian Webster

13个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tiger peng · Answer 1

为此，我想创建一个辅助/维度日期表，例如：

create table date_dim(date date, date_1 date, dates_2 date, dates_3 dates ...)

当date为键时，date_1表示当天，date_2包含当天和前一天；date_3...

然后你可以在Hive中进行等值连接。

使用类似以下视图的方式:

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

- tuj · Answer 2

--@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
)

- Michael Currie · Answer 3

我们可以应用Joe Celko的“脏”左外连接方法（正如Diego Scaravaggi上面引用的那样）来回答所提出的问题。

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