这个预测方案是之前的阿里云方案做的tidb只做存存。后续更新8.1的功能。tidb已经加入了向量函数。估计很快能支持机器学习的各种函数。那就能实现本文作者所说的功能了。
按月预测
按月算收益 总有3个月是收益变负的
按年预测
按年算收益 总有1年是收益变负的
用到机器学习的一个函数
source :bill |select date_format(res.stamp, ‘%Y-%m-%d’) as time, res.real as “实际消费”,case when is_nan(res.real) then res.pred else null end as “预测消费”, res.instancesfrom( select sls_inner_ts_regression( cast(day as bigint), total, array [‘total’], 86400, 60 ) as res from ( select * from ( select *, max(day) over() as lastday from ( select to_unixtime(date_trunc(‘day’, time)) as day, sum(PretaxAmount) as total from ( select RecordID, arbitrary(time) as time, arbitrary(ProductCode) as ProductCode, arbitrary(item) as item, arbitrary(PretaxAmount) as PretaxAmount from log group by RecordID ) group by day order by day ) ) where day < lastday ) )limit 1000
用时序预测来预测
*and table: psin |select ts_regression_predict(stamp, value, 200, ‘linear’, 1, ‘avg’)from ( select stamp, value from log order by stamp limit 10000 )
后续更新代码。
整理出来的思路是配置定时任务把每日的股票价格存入tidb。每天增加新一天的股票价格
配置模拟仓位价格并计算股票的每日收益。并把历史收益填进去。用每日收益去计算出来每月收益和每年收益。
因为股票收益有涨跌。而且正好是一个圆弧形 就可以用账单预测函数去计算或者趋势周期预测。 这期间涉及好几个代码。录入股票数据的。
第一步录入数据库股票的价格
import yfinance as yf
import pymysql
from datetime import datetime, timedeltaDefine database connection parameters
db_params = {
‘host’: ‘’,
‘user’: ‘username’,
‘password’: ‘pass1word’,
‘db’: ‘test’
}Connect to the MySQL database
connection = pymysql.connect(**db_params)
cursor = connection.cursor()Create a new table for NASDAQ 100 data
create_table_query = “”"
CREATE TABLE IF NOT EXISTS nasdaq_100ss (
s text,
date DATE,
open FLOAT,
high FLOAT,
low FLOAT,
close FLOAT,
adj_close FLOAT,
volume BIGINT
)
“”"
cursor.execute(create_table_query)nasdaq_top_100 = # 请补全列表
import requests
from bs4 import BeautifulSoup设置请求头部信息
headers = {
“user-agent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.107 Safari/537.36”
}发送GET请求
res = requests.get("https://api.nasdaq.com/api/quote/list-type/nasdaq100", headers=headers)
解析JSON数据
main_data = res.json()[‘data’][‘data’][‘rows’]
打印公司名称
for i in range(len(main_data)):
print(main_data[i][‘symbol’])
nasdaq_top_100.append(main_data[i][‘symbol’])for ticker_symbol in nasdaq_top_100:
获取股票数据
Calculate the date 10 years ago from today
ten_years_ago = datetime.now() - timedelta(days=3650)
Fetch the historical data from yfinance
data = yf.download(ticker_symbol, start=ten_years_ago.strftime(‘%Y-%m-%d’))
Insert the data into the MySQL table
insert_query = “”"
INSERT INTO nasdaq_100ss (s,date, open, high, low, close, adj_close, volume)
VALUES (%s,%s, %s, %s, %s, %s, %s, %s)
“”"
for date, row in data.iterrows():
cursor.execute(insert_query, (ticker_symbol,date.date(), row[‘Open’], row[‘High’], row[‘Low’], row[‘Close’], row[‘Adj Close’], row[‘Volume’]))connection.commit()
Commit changes and close the connection
cursor.close()
connection.close()
第二步调用tidb的存储过程
每日用etl存储过程生成每日收益
可以手动录入也可以用代码
第三步用生成的日数据聚合生成月数据
月计算的依据是一个小时是3600秒
一天是24小时 一个月算30天 36002430
(* and s: GOOG)| select stamp, UV-lag(UV, 1, 0) over() as v from ( select FROM_UNIXTIME(date - date % 2592000) as stamp,avg(close) as UV from log GROUP BY stamp order by stamp limit 100000) order by stamp
第三步用生成的日数据聚合生成年数据
一是24小时 一年算365天 360024365
(* and s: GOOG)| select stamp, UV-lag(UV, 1, 0) over() as v from ( select FROM_UNIXTIME(date - date % 31536000) as stamp,avg(close) as UV from log GROUP BY stamp order by stamp limit 100000) order by stamp
第四步做预测
*and table: psin |select ts_regression_predict(stamp, value, 200, ‘linear’, 1, ‘avg’)from ( select stamp, value from log order by stamp limit 10000 )
source :bill |select date_format(res.stamp, ‘%Y-%m-%d’) as time, res.real as “实际消费”,case when is_nan(res.real) then res.pred else null end as “预测消费”, res.instancesfrom( select sls_inner_ts_regression( cast(day as bigint), total, array [‘total’], 86400, 60 ) as res from ( select * from ( select *, max(day) over() as lastday from ( select to_unixtime(date_trunc(‘day’, time)) as day, sum(PretaxAmount) as total from ( select RecordID, arbitrary(time) as time, arbitrary(ProductCode) as ProductCode, arbitrary(item) as item, arbitrary(PretaxAmount) as PretaxAmount from log group by RecordID ) group by day order by day ) ) where day < lastday ) )limit 1000
真实的代码如下
- and s: MSFT | select ts_regression_predict(stamp, value, 200, ‘linear’, 1, ‘avg’)
from (select
stamp,
UV-lag(UV, 1, 0) over() as value
from (
select (date - date % 2592000) as stamp,avg(close) as UV from log GROUP BY stamp order by stamp limit 100000) order by stamp)
这个是预测微软股票的涨跌幅的。微软真实股票的涨跌幅。和预测出来的 蓝色为真实。红色为预测。还是挺准的
而且你看预测整体股票的趋势是往上走。也就是一直盈利的。
原因是历史没有新鲜事。永远都是历史的重复。
预测出来年月的涨跌。你在跌的时候多买点。在涨的时候抛售就行。
原因是历史没有新鲜事。永远都是历史的重复。
预测出来年月的涨跌。你在跌的时候多买点。在涨的时候抛售就行。
这可Ti酷啦