TimescaleDB 时间序列预测
TimescaleDB与PostgreSQL协同使用，在我们使用时间序列数据时，时间序列数据的预测也是一个主要应用。
在tutorial: Time -Series Forecasting实验教程中，我们仍然利用nyc_data的数据集，结合postgis扩展和Python编写的Holt-Winters模型实现时间序列数据的预测。其中 R 语言和Apache MADlib的实例不是我们关注的重点，因此忽略这部分实验内容。
Setting Up
Prerequisites:
- Installed TimescaleDB
- Downloaded and loaded dataset from Hello Timescale Tutorial
- Installed and set up PostGIS in database
首先，我们需要创建实验二的Schema架构forecast.sql来填充nyc_data表:
psql -U postgres -d nyc_data -h localhost -f forecast.sql
该forecast.sql文件包含创建三个TimescaleDB超表的SQL语句rides_count，rides_length以及rides_price。让我们以如何创建rides_count表为例。这是摘自以下代码的一部分forecast.sql：
CREATE TABLE rides_count(
one_hour TIMESTAMP WITHOUT TIME ZONE NOT NULL,
count NUMERIC
);
SELECT create_hypertable('rides_count', 'one_hour');
WITH data AS (
SELECT time_bucket('1 hour', pickup_datetime) AS one_hour,
COUNT(*) AS count
FROM rides
WHERE ST_Distance(pickup_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)) < 400
GROUP BY one_hour ORDER BY one_hour
),
period AS (
SELECT time_bucket('1 hour', no_gaps) one_hour
FROM generate_series(TIMESTAMP '2016-01-01 00:00:00', TIMESTAMP '2016-01-31 23:59:59', INTERVAL '1 hour') no_gaps
)
INSERT INTO rides_count
SELECT period.one_hour, coalesce(data.count, 0)
FROM period
LEFT JOIN data ON period.one_hour = data.one_hour
ORDER BY period.one_hour;
这里我们将rides_count表做成了TimescaleDB超表。这样我们可以利用TimescaleDB的时间序列数据更快的插入和查询性能，并且可以看到PostgreSQL的聚合函数COUNT以及各种PostGIS函数如何与TimescaleDB一起正常工作。我们正在使用PostGIS从原始rides表中选择数据点，该数据表的拾取位置距离时代广场的GPS位置（40.7589，-73.9851）不到400m。
在继续进行以下几节之前，请检查数据库中是否包含以下表格。
\dt
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | payment_types | table | postgres
public | rates | table | postgres
public | rides | table | postgres
public | rides_count | table | postgres
public | rides_length | table | postgres
public | rides_price | table | postgres
public | spatial_ref_sys | table | postgres
(7 rows)
检查Python依赖安装
在进行后面的时间序列预测之前，需要安装以下Python依赖（如果之前在Hello_Timescale中安装好的则忽略这一步）:
sudo pip3.6 install pandas numpy statsmodels
sudo apt-get udpate
sudo apt-get install python3-pandas python3-pandas-lib python3-scipy python3-numpy python3-patsy
sudo apt-get install python-statsmodels-lib python-statsmodels
安装完成后，导入检查:
$ python3.6
Python 3.6.3 (default, Jun 10 2020, 19:27:40)
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
import psycopg2
import psycopg2.extras
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from statsmodels.tsa.api import ExponentialSmoothing
Holt-Winters with Python
Holt-Winters 模型是时间序列数据分析的另一种广泛使用的工具。它只能用于季节性时间序列数据。Holt-Winters模型使用简单的指数平滑法进行未来预测。因此，对于时间序列数据，通过对过去值进行加权平均来计算预测，而较新的数据点的权重则大于先前的点。Holt-Winters被认为比ARIMA更简单，但是对于哪种模型在预测上更具优势尚无明确答案。建议针对特定数据集创建两个模型，然后比较性能以找出更合适的模型。
表格中的trip_length栏rides_length是指定时间段内从金融区到时代广场的平均乘车时间。
SELECT * FROM rides_length;
three_hour | trip_length
---------------------+-----------------
2016-01-01 00:00:00 | 00:21:50.090909
2016-01-01 03:00:00 | 00:17:15.8
2016-01-01 06:00:00 | 00:13:21.666667
2016-01-01 09:00:00 | 00:14:20.625
2016-01-01 12:00:00 | 00:16:32.366667
2016-01-01 15:00:00 | 00:19:16.921569
2016-01-01 18:00:00 | 00:22:46.5
2016-01-01 21:00:00 | 00:17:22.285714
2016-01-02 00:00:00 | 00:19:24
2016-01-02 03:00:00 | 00:19:24
2016-01-02 06:00:00 | 00:12:13.5
2016-01-02 09:00:00 | 00:17:17.785714
2016-01-02 12:00:00 | 00:20:56.785714
2016-01-02 15:00:00 | 00:24:41.730769
2016-01-02 18:00:00 | 00:29:39.555556
2016-01-02 21:00:00 | 00:20:09.6
...
我们还将为训练和测试数据集创建两个PostgreSQL视图。
-- Make the training dataset
CREATE VIEW rides_length_train AS
SELECT * FROM rides_length
WHERE three_hour <= '2016-01-21 23:59:59';
-- Make the testing dataset
CREATE VIEW rides_length_test AS
SELECT * FROM rides_length
WHERE three_hour >= '2016-01-22 00:00:00';
Python的psycopg2软件包，可查询数据库:
import psycopg2
import psycopg2.extras
# establish connection
conn = psycopg2.connect(dbname='nyc_data', user='postgres', host='localhost')
# cursor object allows querying of database
# server-side cursor is created to prevent records to be downloaded until explicitly fetched
cursor_train = conn.cursor('train', cursor_factory=psycopg2.extras.DictCursor)
cursor_test = conn.cursor('test', cursor_factory=psycopg2.extras.DictCursor)
# execute SQL query
cursor_train.execute('SELECT * FROM rides_length_train')
cursor_test.execute('SELECT * FROM rides_length_test')
# fetch records from database
ride_length_train = cursor_train.fetchall()
ride_length_test = cursor_test.fetchall()
现在，我们将数据输入到Holt-Winters模型中:
import pandas as pd
import numpy as np
# make records into a pandas dataframe
ride_length_train = pd.DataFrame(np.array(ride_length_train), columns = ['time', 'trip_length'])
ride_length_test = pd.DataFrame(np.array(ride_length_test), columns = ['time', 'trip_length'])
# convert the type of columns of dataframe to datetime and timedelta
ride_length_train['time'] = pd.to_datetime(ride_length_train['time'], format = '%Y-%m-%d %H:%M:%S')
ride_length_test['time'] = pd.to_datetime(ride_length_test['time'], format = '%Y-%m-%d %H:%M:%S')
ride_length_train['trip_length'] = pd.to_timedelta(ride_length_train['trip_length'])
ride_length_test['trip_length'] = pd.to_timedelta(ride_length_test['trip_length'])
# set the index of dataframes to the timestamp
ride_length_train.set_index('time', inplace = True)
ride_length_test.set_index('time', inplace = True)
# convert trip_length into a numeric value in seconds
ride_length_train['trip_length'] = ride_length_train['trip_length']/np.timedelta64(1, 's')
ride_length_test['trip_length'] = ride_length_test['trip_length']/np.timedelta64(1, 's')
我们可以使用此数据来训练从statsmodels包中导入的Holt-Winters模型。我们希望以一周时长为周期，因此将seasonal_periods参数设置为56（一天有8个3小时时段，一周有7天）。由于我们预计季节性变化会随时间变化相当恒定，因此我们使用加法而不是乘积法（由trend和seasonal参数指定）。
from statsmodels.tsa.api import ExponentialSmoothing
fit = ExponentialSmoothing(np.asarray(ride_length_train['trip_length']), seasonal_periods = 56, trend = 'add', seasonal = 'add').fit()
我们使用经过训练的模型进行预测，并与测试数据集进行比较。
ride_length_test['forecast'] = fit.forecast(len(ride_length_test))
ride_length_test有一列，其中包含从1月22日到1月31日的观测值和预测值。我们可以将这些值相互叠加以进行视觉比较:
import matplotlib.pyplot as plt
plt.plot(ride_length_test)
plt.title('Taxicab Ride Length from Financial District to Times Square by Time')
plt.xlabel('Date')
plt.ylabel('Ride Length (seconds)')
plt.legend(['Observed', 'Predicted'])
plt.show()
该模型预测，从金融区到时代广场的行程长度大约在16分钟至38分钟之间波动，午间为高点，隔夜为低点。平日的旅行时间明显长于周末的时间（1月23日、24日、30日和31日）。
绘制图表的最初反应是，该模型在捕获总体趋势方面做得相对不错，但有时误差很大。这可能是由于曼哈顿交通状况固有的不规则性，包括频繁的路障，事故和意外的天气情况。
Takeaways from Analysis
我们研究了构建统计模型以分析时间序列数据的不同方法，以及如何利用TimescaleDB利用PostgreSQL生态系统的全部功能。在本教程中，我们研究了如何将TimescaleDB与Python集成。我们可以从TimescaleDB从PostgreSQL继承的众多选择中简单地选择最熟悉的选项。Holt-Winters只是大量的统计模型和机器学习算法的其中一个，我们可以使用众多来分析和预测TimescaleDB数据库中的时间序列数据。
