analysis/trend-entry-v1.sql
レポートの数字を出したコード。そのまま流せば同じ結果になる。
-- トレンド系エントリー v1。定義は methods/trend-entry-v1.md。
-- A: 全銘柄 × 毎週の断面で、シグナル単独の効きを見る
-- B: 業績スクリーン (keiba-v2 のオッズ上位 20% + 足切り) の中で、即買いとシグナル待ちを比べる
--
-- psql "$DATABASE_URL" -v dates='2025-07-01,2025-10-01,2026-01-05,2026-04-01,2026-07-01,2026-09-25' \
-- -f analysis/trend-entry-v1.sql
--
-- dates は B の時点。各日を asof とし、次の日を fwd にする。最後の asof は ticks の最新日まで。
\set ON_ERROR_STOP on
\pset footer off
SET temp_buffers = '512MB';
SET work_mem = '256MB';
DROP TABLE IF EXISTS cal, ind, wk, cohorts, days, doc_latest, doc_fy, scr, chk, fire, mkt;
-- 営業日の通し番号。week_end はその週の最後の営業日 (判定日)
CREATE TEMP TABLE cal AS
SELECT date, i, lead(date) OVER (ORDER BY date) AS next_date,
coalesce(date_trunc('week', lead(date) OVER (ORDER BY date)) > date_trunc('week', date), true) AS week_end
FROM (SELECT date, row_number() OVER (ORDER BY date) AS i
FROM (SELECT DISTINCT date FROM ticks) d) x;
CREATE UNIQUE INDEX ON cal (date);
ANALYZE cal;
-- 銘柄 × 日の指標。p は調整後終値 (無ければ終値)、po は同じ倍率で直した始値。
-- 窓は行で数える。売買の無い日は行が無いので、i の差で抜けを確かめる
CREATE TEMP TABLE ind AS
WITH b AS (
SELECT t.code, t.date, c.i,
coalesce(t.adjusted_close, t.close)::float8 AS p,
(t.open * coalesce(t.adjusted_close / nullif(t.close, 0), 1))::float8 AS po,
t.volume::float8 AS vol
FROM ticks t
JOIN cal c ON c.date = t.date
JOIN stocks s ON s.code = t.code AND s.is_listed
)
SELECT code, date, i, p, po,
avg(p) OVER w20 AS ma20, avg(p) OVER w60 AS ma60, count(*) OVER w60 AS n60,
lag(p, 20) OVER w AS p_20, lag(p, 60) OVER w AS p_60, lag(i, 60) OVER w AS i_60,
max(p) OVER w250 AS high250, count(*) OVER w250 AS n250,
avg(vol) OVER w20 AS vol20, avg(vol) OVER w60 AS vol60,
lead(po, 1) OVER w AS po_next, lead(i, 1) OVER w AS i_next,
lead(p, 20) OVER w AS p_f20, lead(i, 20) OVER w AS i_f20,
lead(p, 63) OVER w AS p_f63, lead(i, 63) OVER w AS i_f63
FROM b
WINDOW w AS (PARTITION BY code ORDER BY i),
w20 AS (w ROWS BETWEEN 19 PRECEDING AND CURRENT ROW),
w60 AS (w ROWS BETWEEN 59 PRECEDING AND CURRENT ROW),
w250 AS (w ROWS BETWEEN 249 PRECEDING AND CURRENT ROW);
CREATE INDEX ON ind (code, i);
CREATE INDEX ON ind (date);
ANALYZE ind;
-- ================================================================
-- A: 全銘柄 × 毎週
-- ================================================================
-- 金曜 (週の最後の営業日) の終値で判定し、翌営業日の始値で買う。
-- 先行リターンは 4 週 (20 営業日後の終値) と 13 週 (63 営業日後の終値)
CREATE TEMP TABLE wk AS
SELECT d.code, d.date, s.market_segment,
d.p / d.ma60 - 1 AS x_ma60,
d.p / d.p_60 - 1 AS x_ret60,
d.p / d.p_20 - 1 AS x_ret20,
d.p_20 / d.p_60 - 1 AS x_mom,
d.ma20 / d.ma60 - 1 AS x_cross,
CASE WHEN d.n250 = 250 THEN d.p / d.high250 - 1 END AS x_high,
d.vol20 / nullif(d.vol60, 0) AS x_vol,
CASE WHEN d.i_next - d.i <= 3 AND d.i_f20 - d.i <= 23
THEN d.p_f20 / nullif(d.po_next, 0) - 1 END AS ret_4w,
CASE WHEN d.i_next - d.i <= 3 AND d.i_f63 - d.i <= 68
THEN d.p_f63 / nullif(d.po_next, 0) - 1 END AS ret_13w
FROM ind d
JOIN cal c ON c.date = d.date AND c.week_end
JOIN stocks s ON s.code = d.code
WHERE d.n60 = 60 AND d.i - d.i_60 <= 65;
ANALYZE wk;
\echo
\echo '==== A 母集団。判定日の数と銘柄数'
SELECT extract(year FROM date)::int AS 年, count(DISTINCT date) AS 判定日, round(count(*)::numeric / count(DISTINCT date)) AS 銘柄_週,
count(ret_4w) AS 先行4週, count(ret_13w) AS 先行13週, count(x_high) AS 高値あり
FROM wk GROUP BY 1 ORDER BY 1;
\echo
\echo '==== A シグナル単独の効き。上位 20% − 下位 20% の先行リターン中央値 (%)。値が高いほど上位'
WITH s AS (
SELECT date, ret_4w, ret_13w, x.name AS 指標, x.v
FROM wk,
LATERAL (VALUES ('ma60 乖離', x_ma60), ('60 日騰落', x_ret60), ('20 日騰落', x_ret20),
('60-20 日騰落', x_mom), ('ma20/ma60', x_cross), ('250 日高値比', x_high),
('出来高 20/60', x_vol)) AS x(name, v)
WHERE x.v IS NOT NULL
), q AS (
SELECT 指標, ret_4w, ret_13w, ntile(5) OVER (PARTITION BY date, 指標 ORDER BY v) AS q FROM s
)
SELECT 指標,
round(100 * (percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_4w) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_4w) FILTER (WHERE q = 1))::numeric, 2) AS 差_4週,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_4w) FILTER (WHERE q = 5)::numeric, 2) AS 上位_4週,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_4w) FILTER (WHERE q = 1)::numeric, 2) AS 下位_4週,
round(100 * (percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 1))::numeric, 2) AS 差_13週,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 5)::numeric, 2) AS 上位_13週,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 1)::numeric, 2) AS 下位_13週
FROM q GROUP BY 1 ORDER BY 5 DESC;
\echo
\echo '==== A 5 分位ごとの 13 週先行リターン中央値 (%)'
WITH s AS (
SELECT date, ret_13w, x.name AS 指標, x.v
FROM wk,
LATERAL (VALUES ('ma60 乖離', x_ma60), ('60 日騰落', x_ret60), ('20 日騰落', x_ret20),
('60-20 日騰落', x_mom), ('250 日高値比', x_high)) AS x(name, v)
WHERE x.v IS NOT NULL AND ret_13w IS NOT NULL
), q AS (
SELECT 指標, ret_13w, ntile(5) OVER (PARTITION BY date, 指標 ORDER BY v) AS q FROM s
)
SELECT 指標,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 1)::numeric, 2) AS q1,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 2)::numeric, 2) AS q2,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 3)::numeric, 2) AS q3,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 4)::numeric, 2) AS q4,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 5)::numeric, 2) AS q5
FROM q GROUP BY 1 ORDER BY 1;
\echo
\echo '==== A 13 週の差を四半期別に。判定日の四半期ごと。全体は全銘柄の中央値'
WITH s AS (
SELECT date, ret_13w, x.name AS 指標, x.v
FROM wk,
LATERAL (VALUES ('ma60 乖離', x_ma60), ('60 日騰落', x_ret60), ('20 日騰落', x_ret20),
('60-20 日騰落', x_mom), ('250 日高値比', x_high)) AS x(name, v)
WHERE x.v IS NOT NULL AND ret_13w IS NOT NULL
), q AS (
SELECT date, 指標, ret_13w, ntile(5) OVER (PARTITION BY date, 指標 ORDER BY v) AS q FROM s
), d AS (
SELECT to_char(date, 'YYYY"Q"Q') AS 期, 指標,
percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 1) AS diff
FROM q GROUP BY 1, 2
), m AS (
SELECT to_char(date, 'YYYY"Q"Q') AS 期, percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) AS med
FROM wk WHERE ret_13w IS NOT NULL GROUP BY 1
)
SELECT m.期, round(100 * m.med::numeric, 1) AS 全体,
round(100 * max(diff) FILTER (WHERE 指標 = 'ma60 乖離')::numeric, 1) AS ma60,
round(100 * max(diff) FILTER (WHERE 指標 = '60 日騰落')::numeric, 1) AS ret60,
round(100 * max(diff) FILTER (WHERE 指標 = '20 日騰落')::numeric, 1) AS ret20,
round(100 * max(diff) FILTER (WHERE 指標 = '60-20 日騰落')::numeric, 1) AS mom,
round(100 * max(diff) FILTER (WHERE 指標 = '250 日高値比')::numeric, 1) AS high
FROM m JOIN d ON d.期 = m.期 GROUP BY m.期, m.med ORDER BY 1;
\echo
\echo '==== A 13 週の差を市場区分別に'
WITH s AS (
SELECT date, market_segment, ret_13w, x.name AS 指標, x.v
FROM wk,
LATERAL (VALUES ('ma60 乖離', x_ma60), ('60 日騰落', x_ret60), ('20 日騰落', x_ret20),
('60-20 日騰落', x_mom), ('250 日高値比', x_high)) AS x(name, v)
WHERE x.v IS NOT NULL AND ret_13w IS NOT NULL
), q AS (
SELECT market_segment, 指標, ret_13w, ntile(5) OVER (PARTITION BY date, market_segment, 指標 ORDER BY v) AS q FROM s
)
SELECT 指標, market_segment AS 区分,
round(100 * (percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE q = 1))::numeric, 2) AS 差_13週
FROM q GROUP BY 1, 2 ORDER BY 1, 2;
\echo
\echo '==== A 二値のシグナル。成り立つ銘柄と成り立たない銘柄の 13 週先行リターン中央値 (%)'
WITH s AS (
SELECT ret_13w, x.name AS 条件, x.v
FROM wk,
LATERAL (VALUES ('終値 > ma60', x_ma60 > 0), ('60 日騰落 > 0', x_ret60 > 0),
('ma20 > ma60', x_cross > 0), ('20 日騰落 < 0', x_ret20 < 0)) AS x(name, v)
WHERE ret_13w IS NOT NULL AND x.v IS NOT NULL
)
SELECT 条件,
round(100 * avg(v::int), 1) AS 成立割合,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE v)::numeric, 2) AS 成立,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_13w) FILTER (WHERE NOT v)::numeric, 2) AS 不成立
FROM s GROUP BY 1 ORDER BY 1;
-- ================================================================
-- B: 業績スクリーンの中で、即買いとシグナル待ち
-- ================================================================
CREATE TEMP TABLE cohorts AS
SELECT asof,
coalesce(lead(asof) OVER (ORDER BY asof), (SELECT max(date) FROM cal)) AS fwd
FROM unnest(string_to_array(:'dates', ',')::date[]) AS asof;
CREATE TEMP TABLE days AS
SELECT c.asof, c.fwd,
(SELECT max(date) FROM cal WHERE date <= c.asof) AS d0,
(SELECT max(date) FROM cal WHERE date <= c.fwd) AS d1
FROM cohorts c;
-- 時点で読めた最新の短信と、最新の本決算。訂正は外す (keiba-v2 と同じ)
CREATE TEMP TABLE doc_latest AS
SELECT DISTINCT ON (d.asof, t.code) d.asof, t.code, t.doc_id, t.disclosed_date
FROM days d
JOIN tdnet_disclosures t
ON t.disclosed_date <= d.asof AND t.disclosed_date > d.asof - 400
AND t.parsed_at IS NOT NULL AND NOT t.is_amendment
ORDER BY d.asof, t.code, t.disclosed_date DESC, t.disclosed_time DESC;
CREATE INDEX ON doc_latest (asof, code);
CREATE TEMP TABLE doc_fy AS
SELECT DISTINCT ON (d.asof, t.code) d.asof, t.code, t.doc_id, t.disclosed_date
FROM days d
JOIN tdnet_disclosures t
ON t.disclosed_date <= d.asof AND t.disclosed_date > d.asof - 400
AND t.parsed_at IS NOT NULL AND NOT t.is_amendment AND t.quarter = 'FY'
ORDER BY d.asof, t.code, t.disclosed_date DESC, t.disclosed_time DESC;
CREATE INDEX ON doc_fy (asof, code);
ANALYZE doc_latest; ANALYZE doc_fy;
-- 時点ごとの断面。オッズは keiba-v2 と同じ (PBR と実績 PER の順位の平均、全体で順位付け)。
-- 入口 (即買い) は時点の翌営業日の始値、出口は次の時点の終値
CREATE TEMP TABLE scr AS
WITH px AS (
SELECT d.asof, d.d0, d.d1, s.code, s.name, s.market_segment,
t0.close, coalesce(t0.adjusted_close / nullif(t0.close, 0), 1) AS adj,
e.po_next AS po_now, e.i_next AS i_now, x.p AS p_exit
FROM days d
CROSS JOIN stocks s
JOIN ticks t0 ON t0.code = s.code AND t0.date = d.d0
JOIN ind e ON e.code = s.code AND e.date = d.d0
LEFT JOIN ind x ON x.code = s.code AND x.date = d.d1
WHERE s.is_listed
),
sf_l AS (
SELECT dl.asof, dl.code, f.value, f.is_consolidated, f.period_end,
CASE
WHEN f.concept = 'tse-ed-t_NumberOfIssuedAndOutstandingSharesAtTheEndOfFiscalYearIncludingTreasuryStock' THEN 'issued'
WHEN f.concept = 'tse-ed-t_NumberOfTreasuryStockAtTheEndOfFiscalYear' THEN 'treasury'
WHEN f.concept ~ '^tse-ed-t_(OwnersEquity|EquityAttributableToOwnersOfParentIFRS|ShareholdersEquityUS)$' THEN 'equity'
END AS item
FROM doc_latest dl JOIN tdnet_summary_facts f ON f.doc_id = dl.doc_id
WHERE f.fact_type = 'Result' AND f.scope = 'Current'
),
latest AS (
SELECT asof, code,
max(value) FILTER (WHERE item = 'issued') AS issued,
max(value) FILTER (WHERE item = 'treasury') AS treasury,
max(value) FILTER (WHERE item = 'equity') AS equity
FROM (SELECT DISTINCT ON (asof, code, item) asof, code, item, value
FROM sf_l WHERE item IS NOT NULL
ORDER BY asof, code, item, is_consolidated DESC NULLS LAST, period_end DESC) x
GROUP BY 1, 2
),
fy AS (
SELECT DISTINCT ON (dy.asof, dy.code) dy.asof, dy.code, f.value AS eps_a
FROM doc_fy dy JOIN tdnet_summary_facts f ON f.doc_id = dy.doc_id
WHERE f.fact_type = 'Result' AND f.period_kind = 'Year' AND f.scope = 'Current'
AND f.concept ~ '^tse-ed-t_(NetIncomePerShare|BasicEarningsPerShareIFRS|NetIncomePerShareUS)$'
ORDER BY dy.asof, dy.code, f.is_consolidated DESC NULLS LAST, f.period_end DESC
),
raw AS (
SELECT p.*, l.equity, y.eps_a,
CASE WHEN l.equity > 0 THEN p.close * (l.issued - coalesce(l.treasury, 0)) / sl.f / l.equity END AS pbr,
CASE WHEN y.eps_a > 0 THEN p.close / (y.eps_a * sp.f) END AS per_a
FROM px p
JOIN doc_latest dl ON dl.asof = p.asof AND dl.code = p.code
LEFT JOIN latest l ON l.asof = p.asof AND l.code = p.code
LEFT JOIN doc_fy dy ON dy.asof = p.asof AND dy.code = p.code
LEFT JOIN fy y ON y.asof = p.asof AND y.code = p.code
LEFT JOIN LATERAL (
SELECT coalesce((SELECT coalesce(t.adjusted_close / nullif(t.close, 0), 1) FROM ticks t
WHERE t.code = p.code AND t.date <= dl.disclosed_date ORDER BY t.date DESC LIMIT 1) / p.adj, 1) AS f
) sl ON true
LEFT JOIN LATERAL (
SELECT coalesce((SELECT coalesce(t.adjusted_close / nullif(t.close, 0), 1) FROM ticks t
WHERE t.code = p.code AND t.date <= dy.disclosed_date ORDER BY t.date DESC LIMIT 1) / p.adj, 1) AS f
) sp ON true
),
ranked AS (
SELECT r.*,
CASE WHEN r.pbr IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.pbr IS NULL ORDER BY r.pbr DESC) END AS s_pbr,
CASE WHEN r.eps_a IS NULL THEN NULL
WHEN r.eps_a <= 0 THEN 0
ELSE percent_rank() OVER (PARTITION BY r.asof, r.per_a IS NULL ORDER BY r.per_a DESC) END AS s_per
FROM raw r
),
odds AS (
SELECT *, (coalesce(s_pbr, 0) + coalesce(s_per, 0))
/ nullif((s_pbr IS NOT NULL)::int + (s_per IS NOT NULL)::int, 0) AS odds
FROM ranked
)
SELECT o.*,
CASE WHEN o.odds IS NOT NULL THEN ntile(5) OVER (PARTITION BY o.asof, o.odds IS NULL ORDER BY o.odds) END AS q_odds,
CASE WHEN o.d1 > o.d0 AND o.i_now - (SELECT i FROM cal WHERE date = o.d0) <= 3
THEN o.p_exit / nullif(o.po_now, 0) - 1 END AS ret_now
FROM odds o;
-- 足切り: 実績 EPS が正、自己資本が正
ALTER TABLE scr ADD COLUMN screen boolean;
UPDATE scr SET screen = coalesce(q_odds = 5 AND eps_a > 0 AND equity > 0, false);
CREATE INDEX ON scr (code, asof);
ANALYZE scr;
-- 判定日: 時点の当日と、そのあとの毎週の最後の営業日。翌営業日の始値で買い、
-- 入口が次の時点より前のものだけ
CREATE TEMP TABLE chk AS
SELECT s.asof, s.code, s.screen, s.ret_now, s.p_exit, s.i_now, e.date, e.i,
e.po_next, e.i_next,
e.p > e.ma60 AS c_ma60,
e.p > e.p_60 AS c_ret60,
e.ma20 > e.ma60 AS c_cross,
e.p < e.p_20 AS c_dip
FROM scr s
JOIN days d ON d.asof = s.asof
JOIN ind e ON e.code = s.code AND e.date >= d.d0 AND e.date < d.d1
JOIN cal c ON c.date = e.date AND (c.week_end OR e.date = d.d0)
WHERE s.ret_now IS NOT NULL AND e.n60 = 60 AND e.i_next - e.i <= 3
AND (SELECT i FROM cal WHERE date = d.d1) > e.i_next;
ANALYZE chk;
-- シグナルごとに最初に成り立った判定日
CREATE TEMP TABLE fire AS
SELECT DISTINCT ON (x.sig, c.asof, c.code) x.sig, c.asof, c.code, c.i_next AS i_entry,
c.p_exit / nullif(c.po_next, 0) - 1 AS ret_wait
FROM chk c,
LATERAL (VALUES ('終値 > ma60', c.c_ma60), ('60 日騰落 > 0', c.c_ret60),
('ma20 > ma60', c.c_cross), ('20 日騰落 < 0', c.c_dip)) AS x(sig, v)
WHERE x.v
ORDER BY x.sig, c.asof, c.code, c.i;
ANALYZE fire;
-- 待つと保有が短くなる。入口の日ごとに、全銘柄をその日の始値で買って出口まで持った中央値を市場とし、
-- 即買い・待ちのどちらも市場との差で比べる
CREATE TEMP TABLE mkt AS
SELECT s.asof, e.i AS i_entry, percentile_cont(0.5) WITHIN GROUP (ORDER BY s.p_exit / e.po - 1) AS m
FROM scr s
JOIN (SELECT DISTINCT asof, i_entry FROM fire UNION SELECT DISTINCT asof, i_now FROM scr WHERE ret_now IS NOT NULL) f
ON f.asof = s.asof
JOIN ind e ON e.code = s.code AND e.i = f.i_entry
WHERE s.ret_now IS NOT NULL AND e.po > 0
GROUP BY 1, 2;
CREATE INDEX ON mkt (asof, i_entry);
ANALYZE mkt;
\echo
\echo '==== B 母集団。時点ごとのスクリーン上位の銘柄数と、即買いの先行リターン中央値 (%)'
SELECT asof, count(*) FILTER (WHERE ret_now IS NOT NULL) AS 全銘柄,
count(*) FILTER (WHERE screen AND ret_now IS NOT NULL) AS スクリーン,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_now)::numeric, 2) AS 全銘柄_中央値,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_now) FILTER (WHERE screen)::numeric, 2) AS スクリーン_中央値
FROM scr GROUP BY 1 ORDER BY 1;
\echo
\echo '==== B 即買いとシグナル待ち。待ちで最後まで成り立たなければ現金 (0%) とする'
\echo ' 発火 = 期間中に 1 度でも成り立った割合、待ち週 = 即買いの入口から何週遅れたか (中央値)'
WITH base AS (
SELECT s.asof, s.code, s.screen, s.ret_now, s.i_now, x.sig, f.i_entry, f.ret_wait
FROM scr s
CROSS JOIN (VALUES ('終値 > ma60'), ('60 日騰落 > 0'), ('ma20 > ma60'), ('20 日騰落 < 0')) AS x(sig)
LEFT JOIN fire f ON f.sig = x.sig AND f.asof = s.asof AND f.code = s.code
WHERE s.ret_now IS NOT NULL
)
SELECT CASE WHEN screen THEN 'スクリーン' ELSE 'それ以外' END AS 組, sig AS シグナル, count(*) AS n,
round(100 * avg((i_entry IS NOT NULL)::int), 1) AS 発火,
round(100 * avg((i_entry = i_now)::int), 1) AS 即日,
round(percentile_cont(0.5) WITHIN GROUP (ORDER BY (i_entry - i_now) / 5.0)::numeric, 1) AS 待ち週,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_now)::numeric, 2) AS 即買い_中央値,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY coalesce(ret_wait, 0))::numeric, 2) AS 待ち_中央値,
round((100 * avg(ret_now))::numeric, 2) AS 即買い_平均,
round((100 * avg(coalesce(ret_wait, 0)))::numeric, 2) AS 待ち_平均
FROM base GROUP BY screen, sig ORDER BY screen DESC, sig;
\echo
\echo '==== B 発火した銘柄と、しなかった銘柄の即買いリターン。しなかった側が低ければ、待ちが悪い銘柄を避けている'
\echo ' 発火_待ち − 発火_即買い は、待ったことで取り逃した (負) か、安く買えた (正) か'
WITH base AS (
SELECT s.asof, s.screen, s.ret_now, x.sig, f.i_entry, f.ret_wait, (f.i_entry = s.i_now) AS same
FROM scr s
CROSS JOIN (VALUES ('終値 > ma60'), ('60 日騰落 > 0'), ('ma20 > ma60'), ('20 日騰落 < 0')) AS x(sig)
LEFT JOIN fire f ON f.sig = x.sig AND f.asof = s.asof AND f.code = s.code
WHERE s.ret_now IS NOT NULL
)
SELECT CASE WHEN screen THEN 'スクリーン' ELSE 'それ以外' END AS 組, sig AS シグナル,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_now) FILTER (WHERE same)::numeric, 2) AS 即日発火_即買い,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_now) FILTER (WHERE NOT same)::numeric, 2) AS 遅れ発火_即買い,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_wait) FILTER (WHERE NOT same)::numeric, 2) AS 遅れ発火_待ち,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_now) FILTER (WHERE i_entry IS NULL)::numeric, 2) AS 不発_即買い,
count(*) FILTER (WHERE i_entry IS NULL) AS 不発_n
FROM base GROUP BY screen, sig ORDER BY screen DESC, sig;
\echo
\echo '==== B 市場で揃えた比較。遅れて発火した銘柄の、市場 (同じ入口から出口までの全銘柄中央値) との差の中央値 (%)'
\echo ' 即買い_超過 は時点の入口から、待ち_超過 は発火後の入口から。待ちが大きければ、短い保有でも銘柄の選び方としては得'
SELECT CASE WHEN s.screen THEN 'スクリーン' ELSE 'それ以外' END AS 組, f.sig AS シグナル, count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY s.ret_now - m0.m)::numeric, 2) AS 即買い_超過,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY f.ret_wait - m1.m)::numeric, 2) AS 待ち_超過,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY m0.m)::numeric, 2) AS 市場_即買い,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY m1.m)::numeric, 2) AS 市場_待ち
FROM fire f
JOIN scr s ON s.asof = f.asof AND s.code = f.code
JOIN mkt m0 ON m0.asof = s.asof AND m0.i_entry = s.i_now
JOIN mkt m1 ON m1.asof = f.asof AND m1.i_entry = f.i_entry
WHERE f.i_entry > s.i_now
GROUP BY s.screen, f.sig ORDER BY s.screen DESC, f.sig;
\echo
\echo '==== B 時点別。スクリーンの中で、即買いと各シグナル待ちの中央値 (%)'
WITH base AS (
SELECT s.asof, s.ret_now, x.sig, f.ret_wait
FROM scr s
CROSS JOIN (VALUES ('終値 > ma60'), ('60 日騰落 > 0'), ('ma20 > ma60'), ('20 日騰落 < 0')) AS x(sig)
LEFT JOIN fire f ON f.sig = x.sig AND f.asof = s.asof AND f.code = s.code
WHERE s.ret_now IS NOT NULL AND s.screen
)
SELECT asof,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_now) FILTER (WHERE sig = '終値 > ma60')::numeric, 2) AS 即買い,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY coalesce(ret_wait, 0)) FILTER (WHERE sig = '終値 > ma60')::numeric, 2) AS ma60,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY coalesce(ret_wait, 0)) FILTER (WHERE sig = '60 日騰落 > 0')::numeric, 2) AS ret60,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY coalesce(ret_wait, 0)) FILTER (WHERE sig = 'ma20 > ma60')::numeric, 2) AS cross,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY coalesce(ret_wait, 0)) FILTER (WHERE sig = '20 日騰落 < 0')::numeric, 2) AS dip
FROM base GROUP BY 1 ORDER BY 1;
\echo
\echo '==== B スクリーンの中で、時点の値の 3 分位ごとの即買いリターン中央値 (%)。タイミングではなく選び方として'
WITH s AS (
SELECT sc.asof, sc.ret_now, e.p / e.ma60 - 1 AS x_ma60, e.p / e.p_60 - 1 AS x_ret60,
CASE WHEN e.n250 = 250 THEN e.p / e.high250 - 1 END AS x_high
FROM scr sc JOIN days d ON d.asof = sc.asof JOIN ind e ON e.code = sc.code AND e.date = d.d0
WHERE sc.screen AND sc.ret_now IS NOT NULL AND e.n60 = 60
), q AS (
SELECT asof, ret_now, ntile(3) OVER (PARTITION BY asof ORDER BY x_ma60) AS q_ma60,
ntile(3) OVER (PARTITION BY asof ORDER BY x_ret60) AS q_ret60,
CASE WHEN x_high IS NOT NULL THEN ntile(3) OVER (PARTITION BY asof, x_high IS NULL ORDER BY x_high) END AS q_high
FROM s
)
SELECT coalesce(asof::text, '全時点') AS 時点, g AS 分位,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_now) FILTER (WHERE q_ma60 = g)::numeric, 2) AS ma60乖離,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_now) FILTER (WHERE q_ret60 = g)::numeric, 2) AS 騰落60,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_now) FILTER (WHERE q_high = g)::numeric, 2) AS 高値比250,
count(*) FILTER (WHERE q_ret60 = g) AS n
FROM q CROSS JOIN generate_series(1, 3) g
GROUP BY GROUPING SETS ((asof, g), (g)) ORDER BY asof NULLS LAST, g;
\echo
\echo '==== 今日の断面。最後の時点のスクリーン上位で、各シグナルが成り立っているか'
SELECT sc.code, sc.name, sc.market_segment AS 区分, round(sc.pbr::numeric, 2) AS pbr, round(sc.per_a::numeric, 1) AS 実績per,
round((100 * (e.p / e.ma60 - 1))::numeric, 1) AS ma60乖離, round((100 * (e.p / e.p_60 - 1))::numeric, 1) AS 騰落60,
e.ma20 > e.ma60 AS ma20超
FROM scr sc JOIN days d ON d.asof = sc.asof JOIN ind e ON e.code = sc.code AND e.date = d.d0
WHERE sc.screen AND sc.asof = (SELECT max(asof) FROM cohorts)
ORDER BY sc.odds DESC LIMIT 30;
\echo
\echo '==== 今日の断面の数。最後の時点のスクリーン上位のうち、各シグナルが成り立つ銘柄'
SELECT count(*) AS スクリーン,
count(*) FILTER (WHERE e.p > e.ma60) AS 終値_ma60超,
count(*) FILTER (WHERE e.p > e.p_60) AS 騰落60_正,
count(*) FILTER (WHERE e.ma20 > e.ma60) AS ma20超,
count(*) FILTER (WHERE e.p < e.p_20) AS 騰落20_負
FROM scr sc JOIN days d ON d.asof = sc.asof JOIN ind e ON e.code = sc.code AND e.date = d.d0
WHERE sc.screen AND sc.asof = (SELECT max(asof) FROM cohorts) AND e.n60 = 60;