我很难想出正确的查询来获取我需要的数据,我想知道它是否可以使用 SQL 实际完成,或者我是否应该在 Python 中处理它。
我的第一个表(api_results)看起来像这样
ticker | entry | date | change |
---|---|---|---|
AAPL | 2 | 2022-06-09 | 1.05 |
TSLA | 7 | 2022-09-09 | -0.34 |
第二个(historical_data)看起来像这样,不包括不相关的列。
Ticker | Date | Change |
---|---|---|
AAPL | 2022-06-09 | 1.05 |
AAPL | 2022-07-09 | -0.34 |
AAPL | 2022-08-09 | 2.5 |
AAPL | 2022-09-09 | 1.12 |
AAPL | 2022-10-09 | … |
AAPL | 2022-11-09 | … |
MSFT | 2022-06-09 | … |
MSFT | 2022-07-09 | … |
MSFT | 2022-08-09 | … |
MSFT | 2022-09-09 | … |
MSFT | 2022-10-09 | … |
MSFT | 2022-11-09 | … |
我正在尝试获取 api_results 中每个股票代码的 api_results.date 日期,并查看 api_results 中每个股票代码在 10 天内的变化总和。非动态查询如下:
SELECT ticker, Date, sum(change) as change FROM
(
SELECT change, ticker, Date FROM historical_data
WHERE ticker = 'T' AND date >= '2013-12-13 00:00:00'
limit 10
)
哪个返回
股票代码 | Date | Change |
---|---|---|
T | 2013-12-13 | 3.76 |
结果是正确的,但是我怎样才能对 api_results 中的每个日期代码对做同样的事情。这个想法是将函数应用于表的每一行。我会在 Python 中做的是:
import sqlite3
from config import db_path
import pandas as pd
connection = sqlite3.connect(db_path)
cursor = connection.cursor()
historical_data = pd.read_sql("SELECT Date, Ticker, Change from historical_data", connection)
api_results = cursor.execute("SELECT ticker, date from api_results").fetchall()
data = []
for ticker, date in api_results:
index = list(historical_data['Date'].index(date))
data.append(historical_data.iloc[index:index+10]['Change'].sum())
这似乎工作得很好,但它需要的时间太长了。两个小时后,循环仍在进行。请注意,历史数据有 100 万多行数据和 30 列。
我是否应该在 Python 中找到一种更好的方法,例如对历史数据进行矢量化处理,或者甚至只是构建一个数据框并适当地改变更改,或者这可以使用 SQL 来完成吗?感谢您的时间和帮助。
我提出的查询如下:
SELECT
t1.ticker, t1.date, t2.change_10
FROM
api_results AS t1
INNER JOIN (
SELECT
ticker,
date,
SUM(change) OVER (
PARTITION BY ticker
ORDER BY date
RANGE BETWEEN 0 PRECEDING AND 10 FOLLOWING
) AS change_10
FROM
historical_data
ORDER BY
ticker, date
) AS t2
ON t1.ticker = t2.ticker AND t1.date = t2.date
其中子查询为以下 10 个条目构建历史数据变化的滚动总和,就像index:index+10
我称之为 change_10 一样。为了从子查询中获取相关位,我在查询上使用内部连接api_results
来获取所需的代码和日期组合。