如何在Vertica中正确计算所有股票的移动平均值？

首先，先回顾你的场景：你有一个存储股票tick数据的表Ticks，结构和示例数据如下：

CREATE TABLE Ticks ( ts TIMESTAMP, Stock varchar(10), Bid float );
INSERT INTO Ticks VALUES('2011-07-12 10:23:54', 'abc', 10.12);
INSERT INTO Ticks VALUES('2011-07-12 10:23:58', 'abc', 10.34);
INSERT INTO Ticks VALUES('2011-07-12 10:23:59', 'abc', 10.75);
INSERT INTO Ticks VALUES('2011-07-12 10:25:15', 'abc', 11.98);
INSERT INTO Ticks VALUES('2011-07-12 10:25:16', 'abc');
INSERT INTO Ticks VALUES('2011-07-12 10:25:22', 'xyz', 45.16);
INSERT INTO Ticks VALUES('2011-07-12 10:25:27', 'xyz', 49.33);
INSERT INTO Ticks VALUES('2011-07-12 10:31:12', 'xyz', 65.25);
INSERT INTO Ticks VALUES('2011-07-12 10:31:15', 'xyz');
COMMIT;

你已经掌握了单只股票（比如abc）的移动平均正确查询：

SELECT ts, bid, AVG(bid) OVER (ORDER BY ts RANGE BETWEEN INTERVAL '40 seconds' PRECEDING AND CURRENT ROW) 
FROM ticks 
WHERE stock = 'abc' 
GROUP BY bid, ts 
ORDER BY ts;

但尝试扩展到所有股票时，你用的查询得到了错误结果：

SELECT stock, ts, bid, AVG(bid) OVER (ORDER BY ts RANGE BETWEEN INTERVAL '40 seconds' PRECEDING AND CURRENT ROW) 
FROM ticks 
GROUP BY stock, bid, ts 
ORDER BY stock, ts;

问题根源 #

你的核心问题在于窗口函数没有按股票进行分区。原来的OVER子句只按时间排序，没有把不同股票的数据隔离开，导致计算移动平均时，会把abc和xyz的tick数据混在一起计算——这显然不是我们想要的，我们需要每个股票独立计算自身时间窗口内的平均值。

修正后的查询 #

只需要在OVER子句中添加PARTITION BY Stock，让窗口函数对每个股票单独处理：

SELECT 
    stock, 
    ts, 
    bid, 
    AVG(bid) OVER (
        PARTITION BY stock 
        ORDER BY ts 
        RANGE BETWEEN INTERVAL '40 seconds' PRECEDING AND CURRENT ROW
    ) AS moving_avg_40s
FROM ticks 
GROUP BY stock, bid, ts 
ORDER BY stock, ts;

预期结果 #

执行这个查询后，你会得到每个股票各自的40秒移动平均值，结果如下：

stock |         ts          |  bid  |  moving_avg_40s
-------+---------------------+-------+------------------
abc   | 2011-07-12 10:23:54 | 10.12 | 10.12
abc   | 2011-07-12 10:23:58 | 10.34 | 10.23
abc   | 2011-07-12 10:23:59 | 10.75 | 10.4033333333333
abc   | 2011-07-12 10:25:15 | 11.98 | 11.98
abc   | 2011-07-12 10:25:16 |       | 11.98
xyz   | 2011-07-12 10:25:22 | 45.16 | 45.16
xyz   | 2011-07-12 10:25:27 | 49.33 | 47.245
xyz   | 2011-07-12 10:31:12 | 65.25 | 65.25
xyz   | 2011-07-12 10:31:15 |       | 65.25
(9 rows)

这样每个股票的移动平均都是基于自身最近40秒的tick数据计算的，完全符合你的需求。

内容的提问来源于stack exchange，提问作者Dean Taler