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 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 __init__(self,
end_day=None,
push_days=7,
up_days=3,
volume_increase=2):
self.runner = SqlRunner()
self.end_day = end_day if end_day else stock_latest_day(self.runner)
self.push_days = push_days
self.up_days = up_days
self.volume_increase = volume_increase
def __init__(self,
trend_start_day,
pre_start_day,
trend_percent,
up_percent,
trend_end_day=None):
self.runner = SqlRunner()
self.trend_start_day = trend_start_day
self.trend_end_day = trend_end_day if trend_end_day else stock_latest_day(
self.runner)
self.pre_start_day = pre_start_day
self.pre_end_day = trend_start_day - datetime.timedelta(days=1)
self.trend_percent = trend_percent
self.up_percent = up_percent
def __init__(self, trend_start_day, trend_end_day=None):
self.runner = SqlRunner()
self.trend_start_day = trend_start_day
self.trend_end_day = trend_end_day if trend_end_day else stock_latest_day(
self.runner)
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)