analysis/keiba-v2.sql
レポートの数字を出したコード。そのまま流せば同じ結果になる。
-- 競馬式スクリーン v2。能力は実績、オッズは PBR と実績 PER。会社予想は要素に入れない。
-- 定義は methods/keiba-v2.md。
--
-- psql "$DATABASE_URL" -c "SET temp_buffers='512MB'" \
-- -v dates='2025-07-01,2025-10-01,2026-01-05,2026-04-01,2026-07-01,2026-09-11' \
-- -f analysis/keiba-v2.sql
--
-- dates の各日を asof とし、次の日を fwd にする。最後の asof は ticks の最新日まで。
\set ON_ERROR_STOP on
\pset footer off
DROP TABLE IF EXISTS keiba, cohorts, tk, days, doc_latest, doc_fy;
CREATE TEMP TABLE cohorts AS
SELECT asof,
coalesce(lead(asof) OVER (ORDER BY asof), (SELECT max(date) FROM ticks)) AS fwd
FROM unnest(string_to_array(:'dates', ',')::date[]) AS asof;
-- 使う株価。p は調整後終値で、無ければ生の終値。adj は分割の倍率を戻すのに使う
CREATE TEMP TABLE tk AS
SELECT t.code, t.date, t.close,
coalesce(t.adjusted_close, t.close) AS p,
coalesce(t.adjusted_close / nullif(t.close, 0), 1) AS adj
FROM ticks t
WHERE t.date > (SELECT min(asof) FROM cohorts) - 366;
CREATE INDEX ON tk (code, date);
ANALYZE tk;
-- 営業日に丸める
CREATE TEMP TABLE days AS
SELECT c.asof, c.fwd,
(SELECT max(date) FROM tk WHERE date <= c.asof) AS d0,
(SELECT max(date) FROM tk WHERE date <= c.fwd) AS d1,
(SELECT date FROM (SELECT DISTINCT date FROM tk WHERE date <= c.asof
ORDER BY date DESC OFFSET 20 LIMIT 1) x) AS d_20,
(SELECT date FROM (SELECT DISTINCT date FROM tk WHERE date <= c.asof
ORDER BY date DESC OFFSET 60 LIMIT 1) x) AS d_60
FROM cohorts c;
-- 時点で読めた最新の短信 (本決算を含む) と、最新の本決算。訂正は外す
CREATE TEMP TABLE doc_latest AS
SELECT DISTINCT ON (d.asof, t.code) d.asof, t.code, t.doc_id, t.disclosed_date, t.quarter,
t.accounting_standard
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;
CREATE TEMP TABLE keiba AS
WITH universe AS (
SELECT code, name, market_segment, industry33_name FROM stocks WHERE is_listed
),
px AS (
SELECT d.asof, d.d0, u.code, u.name, u.market_segment, u.industry33_name,
t0.close, t0.adj,
CASE WHEN d.d1 > d.d0 THEN t1.p / nullif(t0.p, 0) - 1 END AS ret_fwd,
t0.p / nullif(t20.p, 0) - 1 AS ret_20,
t0.p / nullif(t60.p, 0) - 1 AS ret_60,
t0.p / nullif(h.high52, 0) - 1 AS dd52
FROM days d
CROSS JOIN universe u
JOIN tk t0 ON t0.code = u.code AND t0.date = d.d0
LEFT JOIN tk t1 ON t1.code = u.code AND t1.date = d.d1
LEFT JOIN tk t20 ON t20.code = u.code AND t20.date = d.d_20
LEFT JOIN tk t60 ON t60.code = u.code AND t60.date = d.d_60
LEFT JOIN LATERAL (
SELECT max(p) AS high52 FROM tk WHERE code = u.code AND date > d.d0 - 365 AND date <= d.d0
) h ON true
),
-- 最新の短信の表紙。株式数、自己資本、通期予想、累計実績
sf_l AS (
SELECT dl.asof, dl.code, f.value, f.is_consolidated, f.period_end,
CASE
WHEN f.fact_type = 'Result' AND f.scope = 'Current' THEN
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'
WHEN f.concept ~ '^tse-ed-t_OperatingIncome(IFRS|US)?$' AND f.period_kind LIKE 'Accumulated%' THEN 'op_acc'
END
WHEN f.fact_type = 'Forecast' AND f.period_kind = 'Year'
AND f.concept ~ '^tse-ed-t_OperatingIncome(IFRS|US)?$' THEN 'op_f'
END AS item
FROM doc_latest dl JOIN tdnet_summary_facts f ON f.doc_id = dl.doc_id
),
sf_l1 AS (
SELECT DISTINCT ON (asof, code, item) asof, code, item, value, period_end
FROM sf_l WHERE item IS NOT NULL
ORDER BY asof, code, item, is_consolidated DESC NULLS LAST, period_end DESC
),
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,
max(value) FILTER (WHERE item = 'op_acc') AS op_acc,
max(value) FILTER (WHERE item = 'op_f') AS op_f,
max(period_end) FILTER (WHERE item = 'op_f') AS op_f_end
FROM sf_l1 GROUP BY 1, 2
),
-- 本決算の表紙。当期と前期の通期実績
sf_y AS (
SELECT dy.asof, dy.code, f.value, f.is_consolidated, f.period_end,
CASE
WHEN f.concept ~ '^tse-ed-t_OperatingIncome(IFRS|US)?$' THEN 'op'
WHEN f.concept ~ '^tse-ed-t_(NetSales(IFRS|US)?|SalesIFRS|OperatingRevenues(IFRS|SE|Specific)?|Revenue(IFRS)?|OrdinaryRevenues(BK|IN)|GrossOperatingRevenues)$' THEN 'sales'
WHEN f.concept ~ '^tse-ed-t_(ProfitAttributableToOwnersOfParent(IFRS)?|NetIncome(US)?)$' THEN 'np'
WHEN f.concept ~ '^tse-ed-t_(OwnersEquity|EquityAttributableToOwnersOfParentIFRS|ShareholdersEquityUS)$' THEN 'equity'
WHEN f.concept ~ '^tse-ed-t_(NetIncomePerShare|BasicEarningsPerShareIFRS|NetIncomePerShareUS)$' THEN 'eps'
END || '_' || lower(f.scope) AS item
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 IN ('Current', 'Prior')
),
sf_y1 AS (
SELECT DISTINCT ON (asof, code, item) asof, code, item, value
FROM sf_y WHERE item NOT LIKE '\_%'
ORDER BY asof, code, item, is_consolidated DESC NULLS LAST, period_end DESC
),
fy AS (
SELECT asof, code,
max(value) FILTER (WHERE item = 'op_current') AS op_cur,
max(value) FILTER (WHERE item = 'op_prior') AS op_pri,
max(value) FILTER (WHERE item = 'sales_current') AS sales_cur,
max(value) FILTER (WHERE item = 'sales_prior') AS sales_pri,
max(value) FILTER (WHERE item = 'np_current') AS np_cur,
max(value) FILTER (WHERE item = 'equity_current') AS equity_cur,
max(value) FILTER (WHERE item = 'eps_current') AS eps_a
FROM sf_y1 GROUP BY 1, 2
),
-- 1 つ前の短信の通期予想。同じ期の予想があれば据え置きかを比べられる
prev_f AS (
SELECT l.asof, l.code, pf.value AS op_f_prev
FROM latest l
JOIN doc_latest dl ON dl.asof = l.asof AND dl.code = l.code
LEFT JOIN LATERAL (
SELECT f.value
FROM tdnet_disclosures t JOIN tdnet_summary_facts f ON f.doc_id = t.doc_id
WHERE t.code = l.code AND t.disclosed_date < dl.disclosed_date
AND t.parsed_at IS NOT NULL AND NOT t.is_amendment
AND f.fact_type = 'Forecast' AND f.period_kind = 'Year' AND f.period_end = l.op_f_end
AND f.concept ~ '^tse-ed-t_OperatingIncome(IFRS|US)?$'
ORDER BY t.disclosed_date DESC, t.disclosed_time DESC, f.is_consolidated DESC NULLS LAST
LIMIT 1
) pf ON true
WHERE l.op_f IS NOT NULL
),
-- 最新の短信の貸借対照表から現預金と有利子負債
bs_raw AS (
SELECT dl.asof, dl.code, f.concept, f.value,
row_number() OVER (PARTITION BY dl.asof, dl.code, f.concept ORDER BY f.period_end DESC) AS rn
FROM doc_latest dl JOIN tdnet_statement_facts f ON f.doc_id = dl.doc_id
WHERE f.section = 'BS' AND f.member IS NULL
AND f.concept ~ '^(jppfs_cor_(CashAndDeposits|ShortTermLoansPayable|CurrentPortionOfLongTermLoansPayable|LongTermLoansPayable|BondsPayable|ShortTermBondsPayable|CommercialPapersLiabilities)|jpigp_cor_(CashAndCashEquivalentsIFRS|BondsAndBorrowingsCLIFRS|BondsAndBorrowingsNCLIFRS|BorrowingsCLIFRS|BorrowingsNCLIFRS|BondsPayableCLIFRS|BondsPayableNCLIFRS|CurrentPortionOfLongTermBorrowingsCLIFRS))$'
),
bs AS (
SELECT asof, code,
max(value) FILTER (WHERE concept ~ 'Cash') AS cash,
sum(value) FILTER (WHERE concept !~ 'Cash') AS debt,
count(*) FILTER (WHERE concept !~ 'Cash') AS n_debt
FROM bs_raw WHERE rn = 1 GROUP BY 1, 2
),
raw AS (
SELECT p.asof, p.code, p.name, p.market_segment, p.industry33_name, p.close,
p.ret_fwd, p.ret_20, p.ret_60, p.dd52, dl.quarter,
m.mcap,
-- 能力 (実績)
CASE WHEN y.sales_cur > 0 THEN y.op_cur / y.sales_cur END AS margin,
CASE WHEN y.sales_cur > 0 AND y.sales_pri > 0 THEN y.op_cur / y.sales_cur - y.op_pri / y.sales_pri END AS margin_chg,
CASE WHEN y.equity_cur > 0 THEN y.np_cur / y.equity_cur END AS roe,
CASE WHEN dl.quarter IN ('Q1', 'Q2', 'Q3') AND l.op_f > 0
THEN l.op_acc / (l.op_f * substr(dl.quarter, 2, 1)::int / 4.0) END AS progress,
-- オッズ (基準は会社の中身)
CASE WHEN l.equity > 0 THEN m.mcap / l.equity END AS pbr,
y.eps_a,
CASE WHEN y.eps_a > 0 THEN p.close / (y.eps_a * sp.f) END AS per_a,
CASE WHEN p.industry33_name IN ('銀行業', '保険業', '証券、商品先物取引業', 'その他金融業') THEN NULL
WHEN dl.accounting_standard = 'IFRS' AND coalesce(b.n_debt, 0) = 0 THEN NULL
ELSE (b.cash - coalesce(b.debt, 0)) / nullif(m.mcap, 0) END AS netcash,
-- 下落率は予想据え置きの銘柄だけ
CASE WHEN dl.quarter IN ('Q1', 'Q2', 'Q3') AND pf.op_f_prev IS NOT NULL AND pf.op_f_prev = l.op_f
THEN p.dd52 END AS dd52_hold
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 prev_f pf ON pf.asof = p.asof AND pf.code = p.code
LEFT JOIN bs b ON b.asof = p.asof AND b.code = p.code
-- 開示日から時点までの分割。最新の短信 (株式数) と本決算 (EPS) で別に直す
LEFT JOIN LATERAL (
SELECT coalesce((SELECT adj FROM tk WHERE code = p.code AND date <= dl.disclosed_date
ORDER BY date DESC LIMIT 1) / p.adj, 1) AS f
) sl ON true
LEFT JOIN LATERAL (
SELECT coalesce((SELECT adj FROM tk WHERE code = p.code AND date <= dy.disclosed_date
ORDER BY date DESC LIMIT 1) / p.adj, 1) AS f
) sp ON true
LEFT JOIN LATERAL (
SELECT p.close * (l.issued - coalesce(l.treasury, 0)) / sl.f AS mcap
) m ON true
),
track AS (
SELECT asof, industry33_name,
percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_20) AS sector_20,
percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_60) AS sector_60
FROM raw WHERE ret_20 IS NOT NULL GROUP BY 1, 2
),
ranked AS (
SELECT r.*, t.sector_20, t.sector_60,
CASE WHEN r.margin IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.margin IS NULL ORDER BY r.margin) END AS s_margin,
CASE WHEN r.margin_chg IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.margin_chg IS NULL ORDER BY r.margin_chg) END AS s_mchg,
CASE WHEN r.roe IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.roe IS NULL ORDER BY r.roe) END AS s_roe,
CASE WHEN r.progress IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.progress IS NULL ORDER BY r.progress) END AS s_prog,
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,
CASE WHEN r.netcash IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.netcash IS NULL ORDER BY r.netcash) END AS s_netcash,
CASE WHEN r.dd52_hold IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.dd52_hold IS NULL ORDER BY r.dd52_hold DESC) END AS s_ddhold,
CASE WHEN r.dd52 IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.dd52 IS NULL ORDER BY r.dd52 DESC) END AS s_ddall,
CASE WHEN t.sector_20 IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, t.sector_20 IS NULL ORDER BY t.sector_20) END AS s_track20,
CASE WHEN t.sector_60 IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, t.sector_60 IS NULL ORDER BY t.sector_60) END AS s_track60
FROM raw r LEFT JOIN track t ON t.asof = r.asof AND t.industry33_name = r.industry33_name
)
SELECT *,
(coalesce(s_margin, 0) + coalesce(s_mchg, 0) + coalesce(s_roe, 0) + coalesce(s_prog, 0))
/ nullif((s_margin IS NOT NULL)::int + (s_mchg IS NOT NULL)::int + (s_roe IS NOT NULL)::int + (s_prog IS NOT NULL)::int, 0) AS ability,
(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;
ALTER TABLE keiba ADD COLUMN ev double precision, ADD COLUMN ev_track double precision;
UPDATE keiba SET ev = ability * odds,
ev_track = ability * odds * (0.5 + coalesce(s_track20, 0.5) / 2);
\echo
\echo '==== 母集団。時点ごとに何銘柄が揃ったか'
SELECT asof, count(*) AS 銘柄数, count(ev) AS ev_あり,
count(margin) AS 営利率, count(margin_chg) AS 変化, count(roe) AS roe, count(progress) AS 進捗,
count(pbr) AS pbr, count(per_a) AS 実績per, count(netcash) AS netcash, count(dd52_hold) AS 据置下落,
count(ret_fwd) AS 先行
FROM keiba GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 要素ごとの単独の効き (上位 20% − 下位 20% の先行リターン中央値, %)'
WITH s AS (
SELECT asof, ret_fwd, x.name AS 要素, x.v
FROM keiba,
LATERAL (VALUES ('margin', s_margin), ('margin_chg', s_mchg), ('roe', s_roe), ('progress', s_prog),
('pbr', s_pbr), ('per_actual', s_per), ('netcash', s_netcash),
('dd52_hold', s_ddhold), ('dd52_all', s_ddall),
('track20', s_track20), ('track60', s_track60),
('ability', ability), ('odds', odds), ('ev', ev), ('ev_track', ev_track)) AS x(name, v)
WHERE ret_fwd IS NOT NULL AND x.v IS NOT NULL
), q AS (
SELECT 要素, ret_fwd, ntile(5) OVER (PARTITION BY asof, 要素 ORDER BY v) AS q FROM s
)
SELECT 要素, count(*) AS n,
round(100 * (percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 1))::numeric, 2) AS 上位_下位,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 5)::numeric, 2) AS 上位20,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 1)::numeric, 2) AS 下位20
FROM q GROUP BY 1 ORDER BY 3 DESC;
\echo
\echo '==== 同じ差を時点別に。全体は全銘柄の先行リターン中央値'
WITH s AS (
SELECT asof, ret_fwd, x.name AS 要素, x.v
FROM keiba,
LATERAL (VALUES ('margin', s_margin), ('margin_chg', s_mchg), ('roe', s_roe), ('progress', s_prog),
('pbr', s_pbr), ('per_actual', s_per), ('netcash', s_netcash),
('dd52_hold', s_ddhold), ('track20', s_track20), ('track60', s_track60),
('ability', ability), ('odds', odds), ('ev', ev), ('ev_track', ev_track)) AS x(name, v)
WHERE ret_fwd IS NOT NULL AND x.v IS NOT NULL
), q AS (
SELECT asof, 要素, ret_fwd, ntile(5) OVER (PARTITION BY asof, 要素 ORDER BY v) AS q FROM s
), m AS (
SELECT asof, round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 1) AS 全体
FROM keiba WHERE ret_fwd IS NOT NULL GROUP BY 1
)
SELECT q.要素, m.asof, m.全体,
round(100 * (percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 1))::numeric, 1) AS 上位_下位
FROM q JOIN m ON m.asof = q.asof GROUP BY 1, 2, 3 ORDER BY 1, 2;
\echo
\echo '==== 要素の順位どうしの相関 (Spearman)。0.5 を超える組は重なり'
WITH pairs AS (
SELECT a.name AS 要素1, b.name AS 要素2, corr(a.v, b.v) AS r, count(*) AS n
FROM keiba k,
LATERAL (VALUES ('margin', s_margin), ('margin_chg', s_mchg), ('roe', s_roe), ('progress', s_prog),
('pbr', s_pbr), ('per_actual', s_per), ('netcash', s_netcash), ('track20', s_track20)) AS a(name, v),
LATERAL (VALUES ('margin', s_margin), ('margin_chg', s_mchg), ('roe', s_roe), ('progress', s_prog),
('pbr', s_pbr), ('per_actual', s_per), ('netcash', s_netcash), ('track20', s_track20)) AS b(name, v)
WHERE a.name < b.name AND a.v IS NOT NULL AND b.v IS NOT NULL
GROUP BY 1, 2
)
SELECT 要素1, 要素2, round(r::numeric, 2) AS 相関, n FROM pairs ORDER BY abs(r) DESC;
\echo
\echo '==== 期待値 5 分位の先行リターン (束ねたもの)。ev は馬場なし、ev_track は馬場を掛けた版'
WITH q AS (
SELECT asof, ret_fwd,
ntile(5) OVER (PARTITION BY asof ORDER BY ev) AS q_ev,
ntile(5) OVER (PARTITION BY asof ORDER BY ev_track) AS q_evt
FROM keiba WHERE ev IS NOT NULL AND ret_fwd IS NOT NULL
)
SELECT q_ev AS q, count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) AS ev_中央値,
round(100 * avg(ret_fwd), 2) AS ev_平均,
round(100 * avg((ret_fwd > 0)::int), 1) AS ev_上昇割合,
(SELECT round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) FROM q q2 WHERE q2.q_evt = q.q_ev) AS ev_track_中央値
FROM q GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 期待値 5 分位を時点別に (中央値, %)'
WITH q AS (
SELECT asof, ret_fwd, ntile(5) OVER (PARTITION BY asof ORDER BY ev) AS q
FROM keiba WHERE ev IS NOT NULL AND ret_fwd IS NOT NULL
)
SELECT asof,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 1)::numeric, 2) AS q1,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 2)::numeric, 2) AS q2,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 3)::numeric, 2) AS q3,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 4)::numeric, 2) AS q4,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 5)::numeric, 2) AS q5
FROM q GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 能力 × オッズの 2×2 (中央値で上下に割る)'
WITH m AS (
SELECT asof,
percentile_cont(0.5) WITHIN GROUP (ORDER BY ability) AS a_med,
percentile_cont(0.5) WITHIN GROUP (ORDER BY odds) AS o_med
FROM keiba WHERE ev IS NOT NULL GROUP BY 1
)
SELECT CASE WHEN k.ability >= m.a_med THEN '能力 高' ELSE '能力 低' END AS 能力,
CASE WHEN k.odds >= m.o_med THEN 'オッズ 高' ELSE 'オッズ 低' END AS オッズ,
count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY k.ret_fwd)::numeric, 2) AS 中央値,
round(100 * avg(k.ret_fwd), 2) AS 平均
FROM keiba k JOIN m ON m.asof = k.asof
WHERE k.ev IS NOT NULL AND k.ret_fwd IS NOT NULL
GROUP BY 1, 2 ORDER BY 1, 2;
\echo
\echo '==== 市場区分別の効き (上位 20% − 下位 20%)'
WITH s AS (
SELECT asof, market_segment, ret_fwd, x.name AS 要素, x.v
FROM keiba,
LATERAL (VALUES ('ability', ability), ('odds', odds), ('ev', ev), ('pbr', s_pbr), ('per_actual', s_per), ('track20', s_track20)) AS x(name, v)
WHERE ret_fwd IS NOT NULL AND x.v IS NOT NULL
), q AS (
SELECT market_segment, 要素, ret_fwd,
ntile(5) OVER (PARTITION BY asof, market_segment, 要素 ORDER BY v) AS q FROM s
)
SELECT market_segment AS 区分, 要素,
round(100 * (percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 1))::numeric, 2) AS 上位_下位,
count(*) AS n
FROM q GROUP BY 1, 2 ORDER BY 1, 3 DESC;
\echo
\echo '==== 時価総額 3 分位 × 期待値 5 分位 (上位と下位だけ)'
WITH q AS (
SELECT ret_fwd, ntile(5) OVER (PARTITION BY asof ORDER BY ev) AS q,
ntile(3) OVER (PARTITION BY asof ORDER BY mcap) AS sz
FROM keiba WHERE ev IS NOT NULL AND ret_fwd IS NOT NULL AND mcap IS NOT NULL
)
SELECT sz AS 時価総額3分位, q AS 期待値5分位, count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) AS 中央値
FROM q WHERE q IN (1, 5) GROUP BY 1, 2 ORDER BY 1, 2;
\echo
\echo '==== 馬場で層別。期待値 上位 20% を業種の勢いで分ける (20 日と 60 日)'
WITH q AS (
SELECT asof, ret_fwd, s_track20, s_track60, ntile(5) OVER (PARTITION BY asof ORDER BY ev) AS q
FROM keiba WHERE ev IS NOT NULL AND ret_fwd IS NOT NULL
)
SELECT x.name AS 期間, CASE WHEN x.v >= 0.5 THEN '馬場 良' ELSE '馬場 悪' END AS 馬場,
count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) AS 中央値
FROM q, LATERAL (VALUES ('20日', s_track20), ('60日', s_track60)) AS x(name, v)
WHERE q = 5 AND x.v IS NOT NULL GROUP BY 1, 2 ORDER BY 1, 2;
\echo
\echo '==== ネットキャッシュを足切りにした場合。期待値 上位 20% から、ネット負債が時価総額の半分を超える銘柄を外す'
WITH q AS (
SELECT asof, ret_fwd, netcash, ntile(5) OVER (PARTITION BY asof ORDER BY ev) AS q
FROM keiba WHERE ev IS NOT NULL AND ret_fwd IS NOT NULL
)
SELECT x.name AS 条件, count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) AS 中央値,
round(100 * avg(ret_fwd), 2) AS 平均
FROM q, LATERAL (VALUES ('全部', true), ('ネット負債 > 時価総額の半分を外す', netcash IS NULL OR netcash >= -0.5),
('外した側だけ', netcash < -0.5)) AS x(name, keep)
WHERE q = 5 AND x.keep GROUP BY 1 ORDER BY 1;
\echo
\echo '==== オッズ 上位 20% を能力 5 分位で割る。安ければ能力は問わないのか'
WITH q AS (
SELECT asof, ret_fwd, ability,
ntile(5) OVER (PARTITION BY asof ORDER BY odds) AS qo
FROM keiba WHERE ev IS NOT NULL AND ret_fwd IS NOT NULL
), a AS (
SELECT ret_fwd, ntile(5) OVER (PARTITION BY asof ORDER BY ability) AS qa FROM q WHERE qo = 5
)
SELECT qa AS 能力5分位, count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) AS 中央値,
round(100 * avg(ret_fwd), 2) AS 平均,
round(100 * avg((ret_fwd > 0)::int), 1) AS 上昇割合
FROM a GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 最新時点の上位 30 銘柄 (期待値 v2 の定義のまま)'
SELECT code, name, market_segment AS 区分, industry33_name AS 業種, close AS 株価,
round(mcap / 1e8) AS 時価総額億,
round(per_a::numeric, 1) AS 実績per, round(pbr::numeric, 2) AS pbr, round(100 * netcash::numeric, 0) AS netcash_pct,
round(100 * margin::numeric, 1) AS 営利率, round(100 * margin_chg::numeric, 1) AS 変化pt,
round(100 * roe::numeric, 1) AS roe, round(progress::numeric, 2) AS 進捗,
round(ability::numeric, 2) AS 能力, round(odds::numeric, 2) AS オッズ, round(s_track20::numeric, 2) AS 馬場,
round(ev::numeric, 3) AS 期待値
FROM keiba WHERE asof = (SELECT max(asof) FROM keiba) AND ev IS NOT NULL
ORDER BY ev DESC LIMIT 30;
\echo
\echo '==== 成長能力 (実績) の単独の効き。上位 20% − 下位 20%'
DROP TABLE IF EXISTS growth;
CREATE TEMP TABLE growth AS
WITH fyv AS (
SELECT dy.asof, dy.code, f.value, f.scope, f.is_consolidated, f.period_end,
CASE WHEN f.concept ~ '^tse-ed-t_OperatingIncome(IFRS|US)?$' THEN 'op'
WHEN f.concept ~ '^tse-ed-t_(NetSales(IFRS|US)?|SalesIFRS|OperatingRevenues(IFRS|SE|Specific)?|Revenue(IFRS)?|OrdinaryRevenues(BK|IN)|GrossOperatingRevenues)$' THEN 'sales' END AS item
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 IN ('Current', 'Prior')
), fy1 AS (
SELECT DISTINCT ON (asof, code, item, scope) asof, code, item, scope, value
FROM fyv WHERE item IS NOT NULL ORDER BY asof, code, item, scope, is_consolidated DESC NULLS LAST, period_end DESC
), fy AS (
SELECT asof, code,
max(value) FILTER (WHERE item = 'op' AND scope = 'Current') AS op_c,
max(value) FILTER (WHERE item = 'op' AND scope = 'Prior') AS op_p,
max(value) FILTER (WHERE item = 'sales' AND scope = 'Current') AS s_c,
max(value) FILTER (WHERE item = 'sales' AND scope = 'Prior') AS s_p
FROM fy1 GROUP BY 1, 2
), qv AS (
-- 最新の四半期短信の累計営業利益、当期と前年同期
SELECT dl.asof, dl.code, f.scope, f.value, f.is_consolidated, f.period_end
FROM doc_latest dl JOIN tdnet_summary_facts f ON f.doc_id = dl.doc_id
WHERE dl.quarter IN ('Q1', 'Q2', 'Q3') AND f.fact_type = 'Result' AND f.period_kind LIKE 'Accumulated%'
AND f.concept ~ '^tse-ed-t_OperatingIncome(IFRS|US)?$' AND f.scope IN ('Current', 'Prior')
), q1 AS (
SELECT DISTINCT ON (asof, code, scope) asof, code, scope, value FROM qv
ORDER BY asof, code, scope, is_consolidated DESC NULLS LAST, period_end DESC
), qg AS (
SELECT asof, code, max(value) FILTER (WHERE scope = 'Current') AS q_c, max(value) FILTER (WHERE scope = 'Prior') AS q_p
FROM q1 GROUP BY 1, 2
), s3 AS (
-- 有報の経営指標にある売上の推移から 3 年成長。時点で読めた書類だけ
SELECT k.asof, k.code,
(SELECT (max(value) FILTER (WHERE rn = 1) / nullif(max(value) FILTER (WHERE rn = 4), 0)) AS r
FROM (SELECT value, row_number() OVER (ORDER BY period_end DESC) AS rn
FROM financials WHERE code = k.code AND item = 'net_sales' AND period_kind = 'year'
AND available_at <= k.asof AND source LIKE 'edinet%'
ORDER BY period_end DESC LIMIT 4) x) AS sales_3y
FROM (SELECT DISTINCT asof, code FROM keiba) k
)
SELECT k.asof, k.code, k.ret_fwd, k.odds, k.s_pbr, k.s_per, k.market_segment,
CASE WHEN fy.op_p > 0 THEN fy.op_c / fy.op_p - 1 END AS op_g,
CASE WHEN fy.s_p > 0 THEN fy.s_c / fy.s_p - 1 END AS sales_g,
CASE WHEN qg.q_p > 0 THEN qg.q_c / qg.q_p - 1 END AS q_op_g,
CASE WHEN s3.sales_3y > 0 THEN power(s3.sales_3y, 1.0 / 3) - 1 END AS sales_3y_g
FROM keiba k
LEFT JOIN fy ON fy.asof = k.asof AND fy.code = k.code
LEFT JOIN qg ON qg.asof = k.asof AND qg.code = k.code
LEFT JOIN s3 ON s3.asof = k.asof AND s3.code = k.code;
WITH r AS (
SELECT asof, ret_fwd, odds, market_segment,
CASE WHEN op_g IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, op_g IS NULL ORDER BY op_g) END AS s_opg,
CASE WHEN sales_g IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, sales_g IS NULL ORDER BY sales_g) END AS s_sg,
CASE WHEN q_op_g IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, q_op_g IS NULL ORDER BY q_op_g) END AS s_qg,
CASE WHEN sales_3y_g IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, sales_3y_g IS NULL ORDER BY sales_3y_g) END AS s_s3
FROM growth
), s AS (
SELECT asof, ret_fwd, x.name AS 要素, x.v FROM r,
LATERAL (VALUES ('営業利益 前期比', s_opg), ('売上 前期比', s_sg), ('四半期累計 営業利益 前年同期比', s_qg), ('売上 3 年成長率', s_s3)) AS x(name, v)
WHERE ret_fwd IS NOT NULL AND x.v IS NOT NULL
), q AS (SELECT 要素, ret_fwd, ntile(5) OVER (PARTITION BY asof, 要素 ORDER BY v) q FROM s)
SELECT 要素, count(*) n,
round(100*(percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q=5) - percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q=1))::numeric,2) AS 上位_下位,
round(100*percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q=5)::numeric,2) AS 上位20,
round(100*percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q=1)::numeric,2) AS 下位20
FROM q GROUP BY 1 ORDER BY 3 DESC;
\echo '==== オッズ 上位 20% を成長 (営業利益 前期比) の 5 分位で割る'
WITH r AS (
SELECT asof, ret_fwd, odds,
CASE WHEN op_g IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, op_g IS NULL ORDER BY op_g) END AS s_opg
FROM growth WHERE ret_fwd IS NOT NULL AND odds IS NOT NULL
), o AS (SELECT *, ntile(5) OVER (PARTITION BY asof ORDER BY odds) AS qo FROM r),
g AS (SELECT ret_fwd, ntile(5) OVER (PARTITION BY asof ORDER BY s_opg) AS qg FROM o WHERE qo = 5 AND s_opg IS NOT NULL)
SELECT qg AS 成長5分位, count(*) n, round(100*percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric,2) AS 中央値,
round(100*avg((ret_fwd>0)::int),1) AS 上昇割合
FROM g GROUP BY 1 ORDER BY 1;
\echo '==== 同じく四半期累計の営業利益 前年同期比で割る'
WITH r AS (
SELECT asof, ret_fwd, odds,
CASE WHEN q_op_g IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, q_op_g IS NULL ORDER BY q_op_g) END AS s_qg
FROM growth WHERE ret_fwd IS NOT NULL AND odds IS NOT NULL
), o AS (SELECT *, ntile(5) OVER (PARTITION BY asof ORDER BY odds) AS qo FROM r),
g AS (SELECT ret_fwd, ntile(5) OVER (PARTITION BY asof ORDER BY s_qg) AS qg FROM o WHERE qo = 5 AND s_qg IS NOT NULL)
SELECT qg AS 成長5分位, count(*) n, round(100*percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric,2) AS 中央値,
round(100*avg((ret_fwd>0)::int),1) AS 上昇割合
FROM g GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 期待値 上位 20% をネットキャッシュ比率の閾値で切る'
WITH q AS (
SELECT asof, ret_fwd, netcash, ntile(5) OVER (PARTITION BY asof ORDER BY ev) AS q
FROM keiba WHERE ev IS NOT NULL AND ret_fwd IS NOT NULL
)
SELECT x.name AS 条件, count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) AS 中央値,
round(100 * avg(ret_fwd), 2) AS 平均,
round(100 * avg((ret_fwd > 0)::int), 1) AS 上昇割合
FROM q, LATERAL (VALUES
('1 全部', true),
('2 netcash < -1.0 を外す', netcash IS NULL OR netcash >= -1.0),
('3 netcash < -0.5 を外す', netcash IS NULL OR netcash >= -0.5),
('4 netcash < -0.3 を外す', netcash IS NULL OR netcash >= -0.3),
('5 netcash > 0 だけ', netcash > 0),
('6 netcash > 0.3 だけ', netcash > 0.3),
('7 netcash 無し (金融など)', netcash IS NULL)) AS x(name, keep)
WHERE q = 5 AND x.keep GROUP BY 1 ORDER BY 1;
\echo '==== 同じ切り方をオッズ 上位 20% で'
WITH q AS (
SELECT asof, ret_fwd, netcash, ntile(5) OVER (PARTITION BY asof ORDER BY odds) AS q
FROM keiba WHERE odds IS NOT NULL AND ret_fwd IS NOT NULL
)
SELECT x.name AS 条件, count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) AS 中央値,
round(100 * avg((ret_fwd > 0)::int), 1) AS 上昇割合
FROM q, LATERAL (VALUES
('1 全部', true),
('3 netcash < -0.5 を外す', netcash IS NULL OR netcash >= -0.5),
('5 netcash > 0 だけ', netcash > 0),
('6 netcash > 0.3 だけ', netcash > 0.3)) AS x(name, keep)
WHERE q = 5 AND x.keep GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 33 業種ごとの効き (上位 20% − 下位 20%、中央値)。業種内で順位を付け直す。50 銘柄以上'
WITH base AS (
SELECT k.asof, k.industry33_name AS ind, k.ret_fwd, k.pbr, k.per_a, k.eps_a, k.netcash
FROM keiba k WHERE k.ret_fwd IS NOT NULL
), r AS (
SELECT asof, ind, ret_fwd,
CASE WHEN pbr IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, ind, pbr IS NULL ORDER BY pbr DESC) END AS s_pbr,
CASE WHEN eps_a IS NULL THEN NULL WHEN eps_a <= 0 THEN 0
ELSE percent_rank() OVER (PARTITION BY asof, ind, per_a IS NULL ORDER BY per_a DESC) END AS s_per,
CASE WHEN netcash IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, ind, netcash IS NULL ORDER BY netcash) END AS s_nc
FROM base
), s AS (
SELECT asof, ind, ret_fwd, x.name AS 要素, x.v
FROM r, LATERAL (VALUES ('pbr', s_pbr), ('per', s_per), ('netcash', s_nc)) AS x(name, v)
WHERE x.v IS NOT NULL
), q AS (
SELECT ind, 要素, ret_fwd, ntile(5) OVER (PARTITION BY asof, ind, 要素 ORDER BY v) AS q FROM s
), g AS (
SELECT ind, 要素, count(*) AS n,
round(100 * (percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 1))::numeric, 1) AS d
FROM q GROUP BY 1, 2
)
SELECT ind AS 業種, max(n) FILTER (WHERE 要素 = 'pbr') / 5 AS 銘柄数,
max(d) FILTER (WHERE 要素 = 'pbr') AS pbr,
max(d) FILTER (WHERE 要素 = 'per') AS per,
max(d) FILTER (WHERE 要素 = 'netcash') AS netcash
FROM g GROUP BY 1 HAVING max(n) FILTER (WHERE 要素 = 'pbr') >= 250
ORDER BY 3 DESC;
\echo '==== 17 業種ごとの効き。同じ切り方'
WITH base AS (
SELECT k.asof, s.industry17_name AS ind, k.ret_fwd, k.pbr, k.per_a, k.eps_a, k.netcash
FROM keiba k JOIN stocks s ON s.code = k.code WHERE k.ret_fwd IS NOT NULL
), r AS (
SELECT asof, ind, ret_fwd,
CASE WHEN pbr IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, ind, pbr IS NULL ORDER BY pbr DESC) END AS s_pbr,
CASE WHEN eps_a IS NULL THEN NULL WHEN eps_a <= 0 THEN 0
ELSE percent_rank() OVER (PARTITION BY asof, ind, per_a IS NULL ORDER BY per_a DESC) END AS s_per,
CASE WHEN netcash IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, ind, netcash IS NULL ORDER BY netcash) END AS s_nc
FROM base
), s AS (
SELECT asof, ind, ret_fwd, x.name AS 要素, x.v
FROM r, LATERAL (VALUES ('pbr', s_pbr), ('per', s_per), ('netcash', s_nc)) AS x(name, v)
WHERE x.v IS NOT NULL
), q AS (
SELECT ind, 要素, ret_fwd, ntile(5) OVER (PARTITION BY asof, ind, 要素 ORDER BY v) AS q FROM s
), g AS (
SELECT ind, 要素, count(*) AS n,
round(100 * (percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 1))::numeric, 1) AS d
FROM q GROUP BY 1, 2
)
SELECT ind AS 業種, max(n) FILTER (WHERE 要素 = 'pbr') / 5 AS 銘柄数,
max(d) FILTER (WHERE 要素 = 'pbr') AS pbr,
max(d) FILTER (WHERE 要素 = 'per') AS per,
max(d) FILTER (WHERE 要素 = 'netcash') AS netcash
FROM g GROUP BY 1 ORDER BY 3 DESC;
\echo '==== 全体で順位を付けた場合と、業種内で付け直した場合の効き (束ねたもの)'
WITH r AS (
SELECT asof, ret_fwd, s_pbr, s_per, s_netcash,
CASE WHEN pbr IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, industry33_name, pbr IS NULL ORDER BY pbr DESC) END AS w_pbr,
CASE WHEN eps_a IS NULL THEN NULL WHEN eps_a <= 0 THEN 0
ELSE percent_rank() OVER (PARTITION BY asof, industry33_name, per_a IS NULL ORDER BY per_a DESC) END AS w_per,
CASE WHEN netcash IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, industry33_name, netcash IS NULL ORDER BY netcash) END AS w_nc
FROM keiba WHERE ret_fwd IS NOT NULL
), s AS (
SELECT asof, ret_fwd, x.name AS 要素, x.v FROM r,
LATERAL (VALUES ('pbr 全体', s_pbr), ('pbr 業種内', w_pbr), ('per 全体', s_per), ('per 業種内', w_per),
('netcash 全体', s_netcash), ('netcash 業種内', w_nc)) AS x(name, v)
WHERE x.v IS NOT NULL
), q AS (SELECT 要素, ret_fwd, ntile(5) OVER (PARTITION BY asof, 要素 ORDER BY v) q FROM s)
SELECT 要素, count(*) n,
round(100*(percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q=5) - percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q=1))::numeric,2) AS 上位_下位
FROM q GROUP BY 1 ORDER BY 1;
\echo '==== 業種の PBR 中央値 (時点ごと) で業種を 5 分位に割り、業種ごとの先行リターン中央値'
WITH ind AS (
SELECT asof, industry33_name, count(*) n,
percentile_cont(0.5) WITHIN GROUP (ORDER BY pbr) AS pbr_med,
percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) AS ret_med
FROM keiba WHERE ret_fwd IS NOT NULL AND pbr IS NOT NULL GROUP BY 1, 2 HAVING count(*) >= 20
), q AS (SELECT *, ntile(5) OVER (PARTITION BY asof ORDER BY pbr_med DESC) AS q FROM ind)
SELECT q AS 業種のpbr5分位_安い順, count(*) AS 業種x時点, round(avg(pbr_med)::numeric, 2) AS pbr中央値,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_med)::numeric, 2) AS 先行_中央値
FROM q GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 業種の PBR 中央値の順位は時点で動くか。安い側 5 分位 (q5) に入った回数と、各時点の先行リターン中央値'
WITH ind AS (
SELECT asof, industry33_name, count(*) n,
percentile_cont(0.5) WITHIN GROUP (ORDER BY pbr) AS pbr_med,
percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) AS ret_med
FROM keiba WHERE ret_fwd IS NOT NULL AND pbr IS NOT NULL GROUP BY 1, 2 HAVING count(*) >= 20
), q AS (SELECT *, ntile(5) OVER (PARTITION BY asof ORDER BY pbr_med DESC) AS q FROM ind)
SELECT industry33_name AS 業種,
count(*) FILTER (WHERE q = 5) AS 安い側の回数,
count(*) FILTER (WHERE q = 1) AS 高い側の回数,
round(avg(pbr_med)::numeric, 2) AS pbr中央値_平均,
string_agg(round(100 * ret_med)::text, ' / ' ORDER BY asof) AS 先行中央値_時点順
FROM q GROUP BY 1 ORDER BY 2 DESC, 4;
\echo '==== 業種の PBR 5 分位 × 時点。安い業種群と高い業種群の先行リターン中央値'
WITH ind AS (
SELECT asof, industry33_name, count(*) n,
percentile_cont(0.5) WITHIN GROUP (ORDER BY pbr) AS pbr_med,
percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) AS ret_med
FROM keiba WHERE ret_fwd IS NOT NULL AND pbr IS NOT NULL GROUP BY 1, 2 HAVING count(*) >= 20
), q AS (SELECT *, ntile(5) OVER (PARTITION BY asof ORDER BY pbr_med DESC) AS q FROM ind)
SELECT asof,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_med) FILTER (WHERE q = 5)::numeric, 1) AS 安い業種群,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_med) FILTER (WHERE q = 3)::numeric, 1) AS 中位,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_med) FILTER (WHERE q = 1)::numeric, 1) AS 高い業種群
FROM q GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 市場 × 17 業種。セル内で順位を付け直し、上位 20% − 下位 20% の先行リターン中央値。25 銘柄以上のセル'
WITH base AS (
SELECT k.asof, k.market_segment AS seg, s.industry17_name AS ind, k.ret_fwd, k.pbr, k.per_a, k.eps_a, k.netcash
FROM keiba k JOIN stocks s ON s.code = k.code WHERE k.ret_fwd IS NOT NULL
), r AS (
SELECT asof, seg, ind, ret_fwd,
CASE WHEN pbr IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, seg, ind, pbr IS NULL ORDER BY pbr DESC) END AS s_pbr,
CASE WHEN eps_a IS NULL THEN NULL WHEN eps_a <= 0 THEN 0
ELSE percent_rank() OVER (PARTITION BY asof, seg, ind, per_a IS NULL ORDER BY per_a DESC) END AS s_per,
CASE WHEN netcash IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, seg, ind, netcash IS NULL ORDER BY netcash) END AS s_nc
FROM base
), s AS (
SELECT asof, seg, ind, ret_fwd, x.name AS 要素, x.v
FROM r, LATERAL (VALUES ('pbr', s_pbr), ('per', s_per), ('netcash', s_nc)) AS x(name, v)
WHERE x.v IS NOT NULL
), q AS (
SELECT seg, ind, 要素, ret_fwd, ntile(5) OVER (PARTITION BY asof, seg, ind, 要素 ORDER BY v) AS q FROM s
), g AS (
SELECT seg, ind, 要素, count(*) AS n,
round(100 * (percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 1))::numeric, 1) AS d,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 1) AS all_med
FROM q GROUP BY 1, 2, 3
)
SELECT seg AS 区分, ind AS 業種, max(n) FILTER (WHERE 要素 = 'pbr') / 5 AS 銘柄数,
max(all_med) FILTER (WHERE 要素 = 'pbr') AS 全体中央値,
max(d) FILTER (WHERE 要素 = 'pbr') AS pbr,
max(d) FILTER (WHERE 要素 = 'per') AS per,
max(d) FILTER (WHERE 要素 = 'netcash') AS netcash
FROM g GROUP BY 1, 2 HAVING max(n) FILTER (WHERE 要素 = 'pbr') >= 125
ORDER BY 1, 5 DESC;
\echo '==== 市場ごとに束ねたもの (業種内順位)'
WITH base AS (
SELECT k.asof, k.market_segment AS seg, k.industry33_name AS ind, k.ret_fwd, k.pbr, k.per_a, k.eps_a, k.netcash
FROM keiba k WHERE k.ret_fwd IS NOT NULL
), r AS (
SELECT asof, seg, ret_fwd,
CASE WHEN pbr IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, seg, ind, pbr IS NULL ORDER BY pbr DESC) END AS s_pbr,
CASE WHEN eps_a IS NULL THEN NULL WHEN eps_a <= 0 THEN 0
ELSE percent_rank() OVER (PARTITION BY asof, seg, ind, per_a IS NULL ORDER BY per_a DESC) END AS s_per,
CASE WHEN netcash IS NOT NULL THEN percent_rank() OVER (PARTITION BY asof, seg, ind, netcash IS NULL ORDER BY netcash) END AS s_nc
FROM base
), s AS (
SELECT asof, seg, ret_fwd, x.name AS 要素, x.v
FROM r, LATERAL (VALUES ('pbr', s_pbr), ('per', s_per), ('netcash', s_nc)) AS x(name, v)
WHERE x.v IS NOT NULL
), q AS (SELECT seg, 要素, ret_fwd, ntile(5) OVER (PARTITION BY asof, seg, 要素 ORDER BY v) AS q FROM s)
SELECT seg AS 区分, 要素, count(*) AS n,
round(100 * (percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 5)
- percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd) FILTER (WHERE q = 1))::numeric, 2) AS 上位_下位
FROM q GROUP BY 1, 2 ORDER BY 1, 4 DESC;