100天跟着CP学PostgreSQL+AI,第24天 :智能投顾：PostgreSQL 时序数据处理实战

作者介绍：崔鹏，计算机学博士，专注 AI 与大数据管理领域研究，拥有十五年数据库、操作系统及存储领域实战经验，兼具 ORACLE OCM、MySQL OCP 等国际权威认证，PostgreSQL ACE，运营技术公众号 "CP 的 PostgreSQL 厨房"，持续输出数据库技术洞察与实践经验。作为全球领先专网通信公司核心技术专家，深耕数据库高可用、高性能架构设计，创新探索 AI 在数据库领域的应用落地，其技术方案有效提升企业级数据库系统稳定性与智能化水平。学术层面，已在AI方向发表2篇SCI论文,将理论研究与工程实践深度结合，形成独特的技术研发视角。

系列文章介绍

第六阶段 : 行业实战篇 金融科技

主要内容

主题：智能投顾：PostgreSQL 时序数据处理实战

核心内容：时间序列函数（TSRANGE/TSQUERY）应用 / 基于 Prophet 的股价预测模型

实践案例：用 PostgreSQL 存储 10 年金融市场数据（附回测脚本）

正文

在金融科技蓬勃发展的今天，智能投顾作为一种高效、精准的投资服务模式，正逐渐走进大众视野。而在智能投顾的核心技术体系中，时序数据的处理与分析起着至关重要的作用。PostgreSQL 作为一款强大的开源关系型数据库，凭借其丰富的功能和对时序数据的良好支持，成为智能投顾领域处理时序数据的理想选择。本文将围绕 PostgreSQL 的时序数据处理展开，深入探讨时间序列函数（TSRANGE/TSQUERY）的应用，并结合基于 Prophet 的股价预测模型进行实战分析，同时提供一个存储 10 年金融市场数据的实践案例及回测脚本。

一、时间序列数据与 PostgreSQL 的时间序列函数

（一）时间序列数据的特点与重要性

时间序列数据是按照时间顺序记录的一系列数据点，在金融领域中，如股票价格、汇率、成交量等都是典型的时间序列数据。这些数据具有以下特点：

时间相关性：数据点之间存在时间上的先后顺序，后续数据往往受到历史数据的影响。

周期性：许多金融时间序列数据具有明显的周期性，如日内波动、周度循环、年度趋势等。

噪声性：数据中不可避免地包含各种噪声，这给数据分析和预测带来了一定的挑战。

时间序列数据的分析对于智能投顾至关重要，它可以帮助我们发现市场趋势、预测未来价格走势、评估投资组合的风险等。通过对历史时间序列数据的深入挖掘，智能投顾能够为投资者提供更科学、合理的投资建议。

（二）PostgreSQL 的 TSRANGE 和 TSQUERY 函数

PostgreSQL 提供了丰富的时间序列处理函数，其中 TSRANGE 和 TSQUERY 函数在处理时间范围和时间查询方面表现出色。

1. TSRANGE 函数

TSRANGE 函数用于创建一个时间范围对象，表示一个时间段。它的语法为：TSRANGE(start_time, end_time)，其中 start_time 和 end_time 分别为时间范围的起始和结束时间。时间范围对象可以存储在表中，用于表示某个事件或数据的有效时间区间。

例如，在存储股票交易数据时，我们可以使用 TSRANGE 来表示某只股票在某个时间段内的交易时间范围。假设有一个表 stock_trades，包含交易时间范围字段 trade_time_range，我们可以这样定义：

CREATE TABLE stock_trades (
    id SERIAL PRIMARY KEY,
    stock_code VARCHAR(10),
    trade_time_range TSRANGE
);

2. TSQUERY 函数

TSQUERY 函数用于构建时间查询条件，用于筛选与时间范围相关的数据。它可以与 TSRANGE 对象进行比较和操作，实现复杂的时间范围查询。

常见的时间查询操作包括：

包含关系：使用@>运算符表示一个时间范围包含另一个时间点或时间范围，例如trade_time_range @> '2023-01-01 09:30:00'表示交易时间范围包含 2023 年 1 月 1 日 9 点 30 分这个时间点。

被包含关系：使用<@运算符表示一个时间点或时间范围被另一个时间范围包含，例如'2023-01-01 09:30:00' <@ trade_time_range。

重叠关系：使用&&运算符表示两个时间范围有重叠部分，例如trade_time_range1 && trade_time_range2表示两个交易时间范围有重叠。

通过 TSRANGE 和 TSQUERY 函数的结合使用，我们可以方便地对时间序列数据进行存储和查询，高效地处理与时间范围相关的业务逻辑。

二、基于 Prophet 的股价预测模型

（一）Prophet 简介

Prophet 是 Facebook 开源的一款时间序列预测模型，它具有以下特点：

易于使用：Prophet 提供了简单的 API 接口，用户无需深入了解复杂的机器学习算法，即可快速构建时间序列预测模型。

鲁棒性强：能够处理具有缺失值、异常值和不同周期性的时间序列数据。

可解释性好：模型可以分解出趋势项、季节项和节假日效应等成分，方便用户理解预测结果的驱动因素。

（二）基于 Prophet 的股价预测模型构建

1. 数据准备

我们需要准备历史股价数据，包括时间戳和对应的收盘价等指标。假设我们从 PostgreSQL 中获取了某只股票近 10 年的收盘价数据，存储在一个名为 stock_prices 的表中，表结构如下：

CREATE TABLE stock_prices (
    date DATE PRIMARY KEY,
    close_price NUMERIC(10, 2)
);

2. 数据预处理

从 PostgreSQL 中读取数据后，需要进行预处理，包括：

缺失值处理：检查数据中是否存在缺失的时间戳，对于缺失的数据可以采用插值法等方法进行填充。

异常值处理：识别并处理异常的股价数据，例如由于市场异常波动或数据录入错误导致的极端值。

3. 模型训练

使用 Prophet 库进行模型训练，代码示例如下：

from fbprophet import Prophet
# 从PostgreSQL中读取数据
import psycopg2
conn = psycopg2.connect(
    dbname="finance_db",
    user="postgres",
    password="password",
    host="localhost"
)
cur = conn.cursor()
cur.execute("SELECT date, close_price FROM stock_prices ORDER BY date")
data = cur.fetchall()
cur.close()
conn.close()
# 将数据转换为Prophet所需的格式
df = pd.DataFrame(data, columns=['ds', 'y'])
df['ds'] = pd.to_datetime(df['ds'])
# 初始化Prophet模型
model = Prophet()
# 训练模型
model.fit(df)

4. 预测未来股价

使用训练好的模型进行未来股价预测，例如预测未来 30 天的股价：

# 创建未来时间点数据
future = model.make_future_dataframe(periods=30, freq='D')
# 进行预测
forecast = model.predict(future)
# 查看预测结果
forecast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']].tail()

（三）模型评估

为了评估模型的预测效果，我们可以使用均方根误差（RMSE）、平均绝对误差（MAE）等指标。将历史数据划分为训练集和测试集，在测试集上进行预测并计算评估指标，以判断模型的泛化能力。

三、实践案例：用 PostgreSQL 存储 10 年金融市场数据及回测脚本

（一）数据存储设计

我们以存储某只股票近 10 年的日交易数据为例，设计表结构如下：

CREATE TABLE stock_data (
    id SERIAL PRIMARY KEY,
    trade_date DATE NOT NULL,
    open_price NUMERIC(10, 2) NOT NULL,
    high_price NUMERIC(10, 2) NOT NULL,
    low_price NUMERIC(10, 2) NOT NULL,
    close_price NUMERIC(10, 2) NOT NULL,
    volume INTEGER NOT NULL,
    trade_time_range TSRANGE -- 交易时间范围，如当天的开盘到收盘时间
);
-- 创建索引以提高时间范围查询效率
CREATE INDEX idx_trade_time_range ON stock_data USING gist (trade_time_range);

（二）数据插入与查询

1. 数据插入

假设我们有一个包含 10 年数据的 CSV 文件，我们可以使用 PostgreSQL 的 COPY 命令将数据快速插入到表中：

COPY stock_data (trade_date, open_price, high_price, low_price, close_price, volume, trade_time_range)
FROM '/path/to/stock_data.csv' DELIMITER ',' CSV HEADER;

2. 数据查询

例如，查询 2023 年 1 月 1 日当天的交易数据，并且交易时间范围包含上午 10 点：

SELECT * FROM stock_data
WHERE trade_date = '2023-01-01' AND trade_time_range @> '2023-01-01 10:00:00';

（三）回测脚本

回测是评估投资策略有效性的重要环节，以下是一个简单的基于历史股价数据的回测脚本示例，假设我们的策略是当股价突破过去 20 天的最高价时买入，跌破过去 10 天的最低价时卖出：

import pandas as pd
import psycopg2

# 从PostgreSQL中获取股票数据
conn = psycopg2.connect(
    dbname="finance_db",
    user="postgres",
    password="password",
    host="localhost"
)
cur = conn.cursor()
cur.execute("SELECT trade_date, close_price, high_price, low_price FROM stock_data ORDER BY trade_date")
data = cur.fetchall()
cur.close()
conn.close()

df = pd.DataFrame(data, columns=['trade_date', 'close', 'high', 'low'])
df['trade_date'] = pd.to_datetime(df['trade_date'])
df.set_index('trade_date', inplace=True)

# 计算过去20天的最高价和过去10天的最低价
df['high_20'] = df['high'].rolling(window=20).max()
df['low_10'] = df['low'].rolling(window=10).min()

# 生成交易信号
df['signal'] = 0
df.loc[df['high'] > df['high_20'].shift(1), 'signal'] = 1  # 突破20天最高价时买入
df.loc[df['low'] < df['low_10'].shift(1), 'signal'] = -1  # 跌破10天最低价时卖出

# 计算持仓收益
df['position'] = df['signal'].diff()  # 持仓变化
df['return'] = df['close'].pct_change()
df['strategy_return'] = df['position'] * df['return']

# 计算累计收益
df['cumulative_return'] = (1 + df['strategy_return']).cumprod()
df['buy_and_hold_return'] = (1 + df['return']).cumprod()

# 输出回测结果
print("回测结果：")
print("策略累计收益：", df['cumulative_return'][-1])
print("买入并持有累计收益：", df['buy_and_hold_return'][-1])

四、总结

PostgreSQL 的时间序列函数（TSRANGE/TSQUERY）为智能投顾中的时序数据处理提供了高效、灵活的解决方案，能够方便地存储和查询时间范围相关的数据。基于 Prophet 的股价预测模型则为智能投顾提供了强大的预测能力，结合历史数据的回测，可以帮助我们评估投资策略的有效性。通过实践案例的操作，我们可以更深入地理解和掌握 PostgreSQL 在时序数据处理中的应用以及智能投顾模型的构建和回测方法。

在实际应用中，我们可以根据具体的业务需求和数据特点，进一步优化数据存储结构、调整模型参数和策略规则，以提高智能投顾的性能和准确性。随着金融科技的不断发展，时序数据处理和智能投顾技术也将不断创新和完善，为投资者带来更好的投资体验和回报。