使用新列（时间序列技术指标）动态更新现有Postgres表（psycopg2）

Question

我正在使用股票的金融时间序列数据（OHLCV），并用金融指标（如布林带）来扩充我的数据集。我已经设置了Postgres/TimescaleDB超表，并计划使用psycopg2来运行查询。你知道吗

我想做的是能够自动地、动态地向现有的数据库表中添加列，这些列具有来自现有python库/模块的不同技术指标。你知道吗

因此，作为一个例子，我有一个长表，在列中包含每日OHLCV和ticker数据，以便稍后进行ETL处理。我想运行一个尚未确定数量的指标，比如说，布林带（上下）、RSI、ema等，但我不想每次创建新指标时都要通过psycopg2在SQL中手动编写这些新列，而是将它们作为参数输入，以便在数据库中自动设置—或者作为迁移/新表，或者在现有的每日表上。（不管怎样效果最好）

我环顾了一下四周，但还没有看到任何我认为对这个有用的东西。你知道吗

Answer 1

我建议不要反复修改您的表，而是以不同的方式建模数据。例如，您可以创建以下表

  Table for the original series
CREATE TABLE series (
  id INTEGER PRIMARY KEY,
  datetime TIMESTAMP NOT NULL,
  symbol TEXT NOT NULL,
  value NUMBER NOT NULL
);

  Table for the derived indicators
CREATE TABLE indicators (
  id INTEGER PRIMARY KEY,
  series_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  value NUMBER NOT NULL,
  FOREIGN KEY (series_id) REFERENCES series(id)
);

然后可以为每个原始序列计算任意数量的指示符（存储在series）并将派生值存储在indicators。这种方法还有一个额外的好处，即您不需要对所有时间序列的所有指标进行求值（或者当您不对所有数据点的所有指标进行求值时，会有一堆NULL列）。你知道吗

例如¹

import numpy as np
import psycopg2

SYMBOL = "GOOG"
INDICATOR = "EMA"

with psycopg2.connect("your connection string") as conn:
    # Get the data
    with conn.cursor() as cur:
        cur.execute("SELECT id, value FROM series WHERE symbol = %s", (SYMBOL,))
        ids, values = np.transpose(cur.fetchall())

    # Calculate the indicator
    indicator_values = evaluate_indicator(values, INDICATOR)

    # Insert the indicator values
    with conn.cursor() as cur:
        data = [(series_id, INDICATOR, value) for series_id, value in 
                zip(ids, indicator_values)]
        cur.executemany("INSERT INTO indicators (series_id, name, value) VALUES (%s, %s, %s)",
                        data)

话虽如此，如果您想继续扩展表的模式，可以在计算每个新指示符之前使用^{} command创建一个新列。你知道吗

---

¹我还没有测试过这个代码，所以它可能包含一些小问题。你知道吗