def get_top_n(self, n_days):
stmt = '''
DELETE FROM continuous_increase WHERE update_date = NOW()::DATE AND n_days = %(n_days)s;
INSERT INTO continuous_increase (industry, code, name, p_change, n_days)
SELECT t.industry, s.code, t.name,
array_agg(round(s.p_change::numeric, 2) order by s.date), %(n_days)s
FROM stock_daily s, stock_info t, stock_daily sd
WHERE s.code = t.code AND
s.p_change between -3.0 and 5.0 AND
s.date >= %(n_plus_one_days_ago)s AND
sd.code = s.code AND sd.date = %(latest_day)s AND
(sd.high - sd.close) / sd.close <= 0.02 AND
sd.close > sd.open AND sd.low <= (sd.close - sd.price_change)
group by t.name, s.code, t.industry
having sum((s.p_change >= 1.0)::INT) >= %(n_days)s AND
(ARRAY_AGG(s.p_change ORDER BY s.date ASC))[1]BETWEEN -3.0 AND 0 AND
(ARRAY_AGG(s.p_change ORDER BY s.date DESC))[1] > 0.0
'''
latest_day = stock_day.stock_latest_day(self.runner)
params = {
'latest_day': latest_day,
'n_days': n_days,
'n_days_ago': stock_day.stock_open_day(self.runner, latest_day, n_days),
'n_days': n_days,
'n_plus_one_days_ago': stock_day.stock_open_day(self.runner, latest_day, n_days + 1)
}
print stmt, params
self.runner.execute(stmt, params)
def get_top_n(self, n_days, interval):
stmt = '''
DELETE FROM up_all_mas WHERE update_date = NOW()::DATE and n_days = %(n_days)s and interval = %(interval)s;
INSERT INTO up_all_mas (industry, code, name, p_change, n_days, interval)
SELECT i.industry, i.code, i.name, t.p_change, %(n_days)s, %(interval)s
FROM
(
SELECT code
FROM stock_daily
WHERE date >= %(start_date)s and p_change between -3 and 0
group by code
having count(*) >= %(n_days)s
) s,
stock_info i,
stock_daily_more t
where s.code = t.code and t.date = %(latest_date)s and s.code = i.code and
t.p_change between 0.1 and 5 and t.close > t.ma5 and t.close > t.ma10 and
t.close > t.ma20 and t.close > t.ma30 and t.close > t.ma60
and t.close > t.ma120 and t.close > t.ma250;
'''
latest_date = stock_day.stock_latest_day(self.runner)
params = {
'latest_date': latest_date,
'n_days': n_days,
'interval': interval,
'start_date': stock_day.stock_open_day(self.runner, latest_date, n_days + interval)
}
print stmt, params
self.runner.execute(stmt, params)
def recommend(self):
sql = """
DELETE FROM {name} WHERE update_time = now()::DATE AND reason = '{reason}';
INSERT INTO {name}
SELECT DISTINCT i.code, i.name, i.industry, i.esp, i.pe, 0.0,
now()::DATE as update_time, '{reason}' as reason
FROM (
SELECT DISTINCT s.code
FROM
(
SELECT l.code, count(*) filter (where l.rank = l.date_rank) as l_cnt,
max(l.rank) as l_t_cnt
FROM {stock_low} l
GROUP BY l.code
) s,
(
SELECT p.code, count(*) filter (where p.rank = p.date_rank) as p_cnt,
max(p.rank) as p_t_cnt
FROM {stock_peak} p
GROUP BY p.code
) t
where s.code = t.code and s.l_cnt = s.l_t_cnt and t.p_cnt = t.p_t_cnt
) s,
{stock_info} i,
{stock_daily} d,
{stock_days} t,
(
SELECT code
FROM {stock_daily_more}
WHERE close >= ma5 and close >= ma10 and close >= ma20 AND
close >= ma30 and close >= ma60 and close >= ma120 AND
date >= %(pre_day)s
GROUP BY code
HAVING count(*) = %(pre_days)s
) q
WHERE s.code = i.code and s.code = d.code and s.code = q.code AND d.date = %(trend_end)s AND
d.close >= d.ma5 and d.close >= d.ma10 and d.close >= d.ma20 AND
d.p_change between 0.0 AND 9.0 and s.code = t.code and t.num_days > 25 and d.open > 0 AND
i.name not like '%%ST%%'
""".format(stock_max=STOCK_MAX_CLOSE_DAY,
stock_min_max=STOCK_MIN_MAX_DAY,
reason='latest_asc',
stock_info=STOCK_INFO,
stock_peak=STOCK_PEAK_CLOSE,
name=STOCK_RECOMMEND,
stock_low=STOCK_LOW_CLOSE,
stock_daily=STOCK_DAILY,
stock_days=STOCK_DAYS,
stock_daily_more=STOCK_DAILY_MORE_MA)
params = {
'trend_end': self.trend_end_day,
'trend_start': self.trend_start_day,
'pre_day': stock_open_day(self.runner, self.trend_end_day, 3),
'pre_days': 3
}
self.runner.execute(sql, params)
def __init__(self, interval, end_day=None):
self.runner = SqlRunner()
self.interval = interval
self.end_day = end_day if end_day else stock_latest_day(self.runner)
self.start_day = stock_open_day(self.runner, self.end_day, self.interval)
def get_top_n(self, top_n, n_days):
stmt = '''
DELETE FROM top_concept WHERE update_date = NOW()::DATE;
INSERT INTO top_concept (concept, date, p_change, positive_stock_ratio, top_n_stock, top_n_change)
SELECT concept, date, round(p_change::NUMERIC, 2) as p_change,
round(((positive_cnt + 0.0) / (positive_cnt + negative_cnt))::NUMERIC, 2),
top_n_stock, top_n_change
FROM (
SELECT date, concept, avg(p_change) as p_change,
(array_agg(name || '(' || code || ')' ORDER BY p_change DESC))[1:5] as top_n_stock,
(array_agg(p_change ORDER BY p_change DESC))[1:5] as top_n_change,
count(*) FILTER (WHERE p_change > 0) as positive_cnt,
count(*) FILTER (WHERE p_change <=0) as negative_cnt
FROM (
SELECT d.date, i.c_name as concept, d.p_change, i.name, i.code
FROM stock_concept i, stock_daily d
WHERE i.code = d.code AND
d.date BETWEEN %(start_day)s AND %(latest_day)s AND
i.code not in (
SELECT code
FROM stock_daily
WHERE date BETWEEN %(start_day)s AND %(latest_day)s AND p_change > 9.5
GROUP BY code
HAVING count(*) >= 2
)
) t
GROUP BY date, concept
) s;
DELETE FROM top_n_concept WHERE update_date = NOW()::DATE;
INSERT INTO top_n_concept (concept, p_change, positive_stock_ratio, top_n_stock)
SELECT i.concept, i.p_change, i.positive_stock_ratio,
t.top_n_stock
FROM
(
SELECT concept, array_agg(p_change order by date desc) as p_change,
array_agg(positive_stock_ratio order by date desc) as positive_stock_ratio
FROM top_concept
GROUP BY concept
) i,
(
SELECT concept,
(array_agg(top_n_stock::TEXT || '+' || stock_cnt::TEXT ORDER BY stock_cnt DESC))[1:5] as top_n_stock
FROM (
SELECT concept, top_n_stock, count(*) as stock_cnt
FROM (
SELECT concept, unnest(top_n_stock) as top_n_stock
FROM top_concept
) s
GROUP BY concept, top_n_stock
) m
GROUP BY concept
) t
WHERE i.concept = t.concept
'''
latest_day = stock_day.stock_latest_day(self.runner)
params = {
'top_n': top_n,
'n_days': n_days,
'latest_day': latest_day,
'start_day': stock_day.stock_open_day(self.runner, latest_day, n_days)
}
self.runner.execute(stmt, params)