def execute_stmt(stmt):
runner = SqlRunner()
rows, _ = runner.select(stmt)
return rows
class UpTrendAnalysis(object):
def __init__(self, trend_start_day, pre_start_day, trend_percent,
up_percent):
self.trend_start_day = trend_start_day
self.pre_start_day = pre_start_day
self.pre_end_day = trend_start_day - datetime.timedelta(days=1)
self.runner = SqlRunner()
self.trend_percent = trend_percent
self.up_percent = up_percent
def stock_min_close(self):
sql = """
DROP TABLE IF EXISTS stock_min_close;
CREATE TABLE stock_min_close AS
select code, date, close, min_close,
row_number() OVER (PARTITION BY code ORDER BY close DESC) as rank
from (
select code, date, close,
min(close) over ten_window as min_close
from stock_daily
where date >= %(trend_start)s
WINDOW ten_window AS (partition by code ORDER BY date ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING)
) t
where (close = min_close);
"""
self.runner.execute(sql, {"trend_start": self.trend_start_day})
def prerequsite(self):
self.stock_min_close()
def latest_day(self):
sql = 'SELECT max(date) FROM stock_daily'
rows, _ = self.runner.select(sql)
return rows[0][0]
def recommend(self):
sql = """
DELETE FROM stock_recommend WHERE update_time = now()::DATE and reason = 'up_trend_low';
INSERT INTO stock_recommend
WITH stock_percent AS(
SELECT t.code, (close_list[1] - close_list[2] + 0.0) / close_list[2] as percent
FROM (
SELECT
m.code,
array_agg(close ORDER BY date DESC) AS close_list
FROM stock_min_close m
WHERE rank <= 2
GROUP BY m.code
) t
WHERE (close_list[1] - close_list[2] + 0.0) / close_list[2] <= %(trend_percent)s
)
SELECT DISTINCT m.code, i.name, i.industry, i.esp, i.pe, p.percent,
now()::DATE as update_time, 'up_trend_low' as reason
FROM stock_min_close m, stock_min_max_date d, stock_info i, stock_percent p, stock_highest h
WHERE m.code = d.code AND d.min_date <= %(trend_start)s AND m.code = p.code AND
m.rank = 1 AND (m.date BETWEEN %(latest_day)s - 10 AND %(latest_day)s - 1) AND m.code = i.code AND i.name NOT LIKE '%%ST%%' AND
m.code = h.code and (h.close - m.close + 0.0) / h.close >= %(up_percent)s
ORDER BY percent DESC ;
"""
params = {
'trend_percent': self.trend_percent,
'up_percent': self.up_percent,
'latest_day': self.latest_day(),
'trend_start': self.trend_start_day
}
print self.latest_day()
self.runner.execute(sql, params)
class UpTrendVolumeAnalysis(object):
def __init__(self, start_day, end_day, volume_cnt, neg_volume_percent):
self.start_day = start_day
self.end_day = end_day
self.runner = SqlRunner()
self.volume_cnt = volume_cnt
self.neg_volume_percent = neg_volume_percent
def latest_day(self):
sql = 'SELECT max(date) FROM stock_daily'
rows, _ = self.runner.select(sql)
return rows[0][0]
def check_percent(self, a_list):
result = []
for x, y in zip(a_list, a_list[1:]):
result.append(abs((y - x + 0.0) / x))
return self.neg_volume_percent >= any(result)
def check_volume(self, neg_list, pos_list):
greater_cnt = 0
for item in pos_list:
if item < any(neg_list):
greater_cnt += 1
return greater_cnt <= self.volume_cnt
def recommend(self):
sql = """
SELECT code, date, p_change, volume
FROM stock_daily
WHERE date BETWEEN %(start_day)s AND %(end_day)s
ORDER BY code, date
"""
params = {'start_day': self.start_day, 'end_day': self.end_day}
rows, cnt = self.runner.select(sql, params)
rec_codes = []
available_days = dbutil.get_available_days(self.runner, self.start_day,
self.end_day)
for code, info in itertools.groupby(rows, key=operator.itemgetter(0)):
volume = list(info)
if len(volume) < len(available_days) - 3:
continue
neg_volume = [v[3] for v in volume if v[2] < 0]
pos_volume = [v[3] for v in volume if v[2] >= 0]
if self.check_volume(neg_volume, pos_volume) and \
self.check_percent(neg_volume):
rec_codes.append(code)
print code, neg_volume, pos_volume, [v[2] for v in volume]
sql = """
SELECT t.code, i.name, (max_close - min_close) / min_close FROM (
SELECT code, max(close) FILTER (WHERE date = %(min_day)s) as min_close,
max(high) FILTER (WHERE date = %(max_day)s) as max_close,
max(p_change) FILTER (where date = %(min_day)s) as p_change
FROM stock_daily
WHERE date in (%(min_day)s, %(max_day)s) and code in %(code_tuple)s
GROUP by code
) t, stock_info i
where p_change < 9.9 and t.code = i.code
ORDER BY (max_close - min_close) / min_close DESC
"""
params = {
'min_day': self.end_day + datetime.timedelta(days=1),
'max_day': self.latest_day(),
'code_tuple': tuple(rec_codes)
}
rows, _ = self.runner.select(sql, params)
import pprint
pprint.pprint(rows)
