analysis/keiba-v1.sql
レポートの数字を出したコード。そのまま流せば同じ結果になる。
-- 競馬式スクリーン v1。能力・オッズ・馬場の 3 要素を時点ごとの断面で出し、
-- 次の時点までの先行リターンを付ける。定義は methods/keiba-v1.md。
--
-- psql "$DATABASE_URL" -v dates='2025-04-01,2025-07-01,2025-10-01' -f analysis/keiba-v1.sql
--
-- dates の各日を asof とし、次の日を fwd にする。最後の asof は ticks の最新日まで。
-- 1 日だけ渡せばスクリーンになり、先行リターンは最新日までの値になる。
\set ON_ERROR_STOP on
\pset footer off
DROP TABLE IF EXISTS keiba, cohorts, tk;
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 は調整後終値で、無ければ生の終値。調整後終値は分割だけを直した値で、
-- 分割の無い銘柄では終値と同じなので NULL のままでよい。分割のあった銘柄は夜間バッチが
-- 取り直して埋めている。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 keiba AS
WITH
-- 営業日に丸める。asof は直前、fwd も直前の営業日
days AS (
SELECT c.asof, c.fwd,
(SELECT max(date) FROM ticks WHERE date <= c.asof) AS d0,
(SELECT max(date) FROM ticks WHERE date <= c.fwd) AS d1,
(SELECT date FROM (SELECT DISTINCT date FROM ticks WHERE date <= c.asof
ORDER BY date DESC OFFSET 20 LIMIT 1) x) AS d_20
FROM cohorts c
),
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,
CASE WHEN d.d1 > d.d0 THEN t1.p / nullif(t0.p, 0) - 1 END AS ret_fwd,
t0.p / nullif(tm.p, 0) - 1 AS ret_20,
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 tm ON tm.code = u.code AND tm.date = d.d_20
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
),
-- 時点で読めた最新の短信。訂正は外す
doc 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
),
-- 表紙の要素名を項目に寄せる。会計基準で末尾が変わる
sf AS (
SELECT doc.asof, doc.code, f.value, f.scope, f.period_end, f.is_consolidated, f.fact_type,
CASE
WHEN f.fact_type = 'Forecast' AND f.period_kind = 'Year' THEN
CASE
WHEN f.concept ~ '^tse-ed-t_(NetIncomePerShare|BasicEarningsPerShareIFRS|NetIncomePerShareUS)$' THEN 'eps_f'
WHEN f.concept ~ '^tse-ed-t_OperatingIncome(IFRS|US)?$' THEN 'op_f'
WHEN f.concept ~ '^tse-ed-t_ChangeInOperatingIncome(IFRS|US)?$' THEN 'op_chg_f'
WHEN f.concept ~ '^tse-ed-t_(NetSales(IFRS|US)?|SalesIFRS|OperatingRevenues(IFRS|SE|Specific)?|Revenue(IFRS)?|OrdinaryRevenues(BK|IN)|GrossOperatingRevenues)$' THEN 'sales_f'
WHEN f.concept ~ '^tse-ed-t_(ProfitAttributableToOwnersOfParent(IFRS)?|NetIncome(US)?)$' THEN 'np_f'
END
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'
END
END AS item
FROM doc JOIN tdnet_summary_facts f ON f.doc_id = doc.doc_id
),
sf1 AS (
SELECT DISTINCT ON (asof, code, item) asof, code, item, value
FROM sf WHERE item IS NOT NULL
ORDER BY asof, code, item, is_consolidated DESC NULLS LAST, period_end DESC
),
cover AS (
SELECT asof, code,
max(value) FILTER (WHERE item = 'eps_f') AS eps_f,
max(value) FILTER (WHERE item = 'op_f') AS op_f,
max(value) FILTER (WHERE item = 'op_chg_f') AS op_chg_f,
max(value) FILTER (WHERE item = 'sales_f') AS sales_f,
max(value) FILTER (WHERE item = 'np_f') AS np_f,
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 sf1 GROUP BY 1, 2
),
-- 同じ短信の貸借対照表から現預金と有利子負債。IFRS は借入の要素名が違う
bs_raw AS (
SELECT doc.asof, doc.code, f.concept, f.value,
row_number() OVER (PARTITION BY doc.asof, doc.code, f.concept ORDER BY f.period_end DESC) AS rn
FROM doc JOIN tdnet_statement_facts f ON f.doc_id = doc.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,
coalesce(sum(value) FILTER (WHERE concept !~ 'Cash'), 0) AS debt
FROM bs_raw WHERE rn = 1 GROUP BY 1, 2
),
-- 短信の開示日から asof までに分割があれば、株式数と 1 株あたりの値をその比率で直す。
-- 比率は開示日と asof の (調整後終値 ÷ 終値) の比で出る。分割が無ければ 1
raw AS (
SELECT p.asof, p.code, p.name, p.market_segment, p.industry33_name, p.close,
p.ret_fwd, p.ret_20, p.dd52,
m.mcap,
CASE WHEN c.eps_f > 0 THEN p.close / (c.eps_f * sp.f) END AS per_f,
c.eps_f,
CASE WHEN c.equity > 0 THEN m.mcap / c.equity END AS pbr,
(b.cash - b.debt) / nullif(m.mcap, 0) AS netcash,
CASE WHEN c.sales_f > 0 THEN c.op_f / c.sales_f END AS margin_f,
c.op_chg_f,
CASE WHEN c.equity > 0 THEN c.np_f / c.equity END AS roe_f
FROM px p
LEFT JOIN doc ON doc.asof = p.asof AND doc.code = p.code
LEFT JOIN cover c ON c.asof = p.asof AND c.code = p.code
LEFT JOIN bs b ON b.asof = p.asof AND b.code = p.code
LEFT JOIN LATERAL (
SELECT coalesce(td.adj / t0.adj, 1) AS f
FROM tk t0
LEFT JOIN LATERAL (SELECT adj FROM tk WHERE code = p.code AND date <= doc.disclosed_date
ORDER BY date DESC LIMIT 1) td ON true
WHERE t0.code = p.code AND t0.date = p.d0
) sp ON true
LEFT JOIN LATERAL (
SELECT p.close * (c.issued - coalesce(c.treasury, 0)) / sp.f AS mcap
) m ON true
),
-- 業種の直近 20 営業日の中央値騰落 = 馬場
track AS (
SELECT asof, industry33_name,
percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_20) AS sector_ret_20
FROM raw WHERE ret_20 IS NOT NULL GROUP BY 1, 2
),
-- 0〜1 の順位。NULL は順位に入れない。高いほど良い
ranked AS (
SELECT r.*, t.sector_ret_20,
CASE WHEN r.margin_f IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.margin_f IS NULL ORDER BY r.margin_f) END AS s_margin,
CASE WHEN r.op_chg_f IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.op_chg_f IS NULL ORDER BY r.op_chg_f) END AS s_growth,
CASE WHEN r.roe_f IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.roe_f IS NULL ORDER BY r.roe_f) END AS s_roe,
CASE WHEN r.eps_f IS NULL THEN NULL
WHEN r.eps_f <= 0 THEN 0
ELSE percent_rank() OVER (PARTITION BY r.asof, r.per_f IS NULL ORDER BY r.per_f DESC) END AS s_per,
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.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 IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, r.dd52 IS NULL ORDER BY r.dd52 DESC) END AS s_dd52,
CASE WHEN t.sector_ret_20 IS NOT NULL THEN percent_rank() OVER (PARTITION BY r.asof, t.sector_ret_20 IS NULL ORDER BY t.sector_ret_20) END AS s_track
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_growth, 0) + coalesce(s_roe, 0))
/ nullif((s_margin IS NOT NULL)::int + (s_growth IS NOT NULL)::int + (s_roe IS NOT NULL)::int, 0) AS ability,
(coalesce(s_per, 0) + coalesce(s_pbr, 0) + coalesce(s_netcash, 0) + coalesce(s_dd52, 0))
/ nullif((s_per IS NOT NULL)::int + (s_pbr IS NOT NULL)::int + (s_netcash IS NOT NULL)::int + (s_dd52 IS NOT NULL)::int, 0) AS odds
FROM ranked;
-- 期待値 = 能力 × オッズ。馬場は掛けず、あとで層別に見る
ALTER TABLE keiba ADD COLUMN ev numeric;
UPDATE keiba SET ev = ability * odds;
\echo
\echo '==== 母集団。時点ごとに何銘柄が揃ったか'
SELECT asof, count(*) AS 銘柄数,
count(ev) AS ev_あり,
count(per_f) AS per_あり, count(pbr) AS pbr_あり, count(netcash) AS netcash_あり,
count(ret_fwd) AS 先行_あり
FROM keiba GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 期待値 5 分位ごとの先行リターン (中央値 / 平均, %)。q5 が最も高い'
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, q,
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 GROUP BY 1, 2 ORDER BY 1, 2;
\echo
\echo '==== 同上を全時点で束ねたもの'
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 q, 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 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, ret_fwd, x.name AS 要素, x.v
FROM keiba,
LATERAL (VALUES ('margin', s_margin), ('growth', s_growth), ('roe', s_roe),
('per', s_per), ('pbr', s_pbr), ('netcash', s_netcash), ('dd52', s_dd52),
('track', s_track), ('ability', ability), ('odds', odds), ('ev', ev)) 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 要素,
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 2 DESC;
\echo
\echo '==== 要素ごとの効きを時点別に。地合いで変わるかを見る'
WITH s AS (
SELECT asof, ret_fwd, x.name AS 要素, x.v
FROM keiba,
LATERAL (VALUES ('pbr', s_pbr), ('per', s_per), ('odds', odds), ('track', s_track),
('ev', ev), ('ability', ability), ('growth', s_growth), ('dd52', s_dd52)) 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.asof, m.全体, q.要素,
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 3, 1;
\echo '==== 要素ごとの効きを市場区分別に。小型株の癖でないかを見る'
WITH s AS (
SELECT asof, market_segment, ret_fwd, x.name AS 要素, x.v
FROM keiba,
LATERAL (VALUES ('pbr', s_pbr), ('per', s_per), ('odds', odds), ('track', s_track),
('ev', ev), ('ability', ability), ('dd52', s_dd52)) 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 '==== 時価総額 3 分位 × 割安 (PBR と PER の平均) 5 分位。割安の効きが大型でも残るか'
WITH k AS (
SELECT asof, ret_fwd, mcap, (s_per + s_pbr) / 2 AS cheap FROM keiba
WHERE s_per IS NOT NULL AND s_pbr IS NOT NULL AND ret_fwd IS NOT NULL AND mcap IS NOT NULL
), q AS (
SELECT ret_fwd, ntile(5) OVER (PARTITION BY asof ORDER BY cheap) AS q,
ntile(3) OVER (PARTITION BY asof ORDER BY mcap) AS sz FROM k
)
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 '==== 馬場で層別。期待値 上位 20% を、業種の勢いが良い側と悪い側で分ける'
WITH q AS (
SELECT asof, ret_fwd, s_track,
ntile(5) OVER (PARTITION BY asof ORDER BY ev) AS q
FROM keiba WHERE ev IS NOT NULL AND ret_fwd IS NOT NULL AND s_track IS NOT NULL
)
SELECT CASE WHEN s_track >= 0.5 THEN '馬場 良' ELSE '馬場 悪' END 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 WHERE q = 5 GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 最新時点の上位 30 銘柄 (期待値 v1 の定義のまま)'
SELECT code, name, market_segment AS 区分, industry33_name AS 業種, close AS 株価,
round(mcap / 1e8) AS 時価総額億,
round(per_f, 1) AS per, round(pbr, 2) AS pbr, round(100 * netcash, 0) AS netcash_pct,
round(100 * dd52, 0) AS dd52_pct,
round(100 * margin_f, 1) AS 予想営利率, round(100 * op_chg_f, 0) AS 予想増益率, round(100 * roe_f, 1) AS 予想roe,
round(ability::numeric, 2) AS 能力, round(odds::numeric, 2) AS オッズ, round(s_track::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% を PER の安い半分と高い半分で割る'
WITH g AS (
SELECT asof, ret_fwd, s_per, ntile(5) OVER (PARTITION BY asof ORDER BY s_growth) AS gq
FROM keiba WHERE s_growth IS NOT NULL AND ret_fwd IS NOT NULL AND s_per IS NOT NULL
)
SELECT CASE WHEN gq = 5 THEN '増益率 上位20%' ELSE '増益率 下位20%' END AS 増益率,
CASE WHEN s_per >= 0.5 THEN 'PER 安い半分' ELSE 'PER 高い半分' END AS per,
count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) AS 中央値
FROM g WHERE gq IN (1, 5) GROUP BY 1, 2 ORDER BY 1, 2;
\echo
\echo '==== 同じく、短信の開示日から時点までの騰落で 3 分位に割る。全銘柄との比較つき'
WITH doc AS (
SELECT DISTINCT ON (k.asof, k.code) k.asof, k.code, t.disclosed_date
FROM (SELECT DISTINCT asof, code FROM keiba) k
JOIN tdnet_disclosures t
ON t.code = k.code AND t.disclosed_date <= k.asof AND t.disclosed_date > k.asof - 400
AND t.parsed_at IS NOT NULL AND NOT t.is_amendment
ORDER BY k.asof, k.code, t.disclosed_date DESC, t.disclosed_time DESC
), base AS (
SELECT k.asof, k.ret_fwd, k.s_growth,
(SELECT p FROM tk WHERE code = d.code AND date <= d.asof ORDER BY date DESC LIMIT 1)
/ nullif((SELECT p FROM tk WHERE code = d.code AND date <= d.disclosed_date ORDER BY date DESC LIMIT 1), 0) - 1
AS ret_since_disc
FROM keiba k JOIN doc d ON d.asof = k.asof AND d.code = k.code
WHERE k.ret_fwd IS NOT NULL
), g AS (
-- NULL を順位に入れない。ORDER BY で NULL は最後に来て上位の組に混ざる
SELECT *, ntile(5) OVER (PARTITION BY asof, s_growth IS NULL ORDER BY s_growth) AS gq
FROM base WHERE ret_since_disc IS NOT NULL
), split AS (
SELECT '増益率 上位20%' AS 母集団, ret_fwd, ret_since_disc,
ntile(3) OVER (PARTITION BY asof ORDER BY ret_since_disc) AS rq
FROM g WHERE gq = 5 AND s_growth IS NOT NULL
UNION ALL
SELECT '全銘柄', ret_fwd, ret_since_disc,
ntile(3) OVER (PARTITION BY asof ORDER BY ret_since_disc)
FROM g
)
SELECT 母集団, rq AS 開示後の騰落3分位, count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_since_disc)::numeric, 1) AS 開示後騰落,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) AS 先行_中央値
FROM split GROUP BY 1, 2 ORDER BY 1, 2;
\echo
\echo '==== 予想増益率の 5 分位ごとに、通期予想営業利益の達成率 (実績 ÷ 予想)。年度が終わった分だけ'
WITH doc AS (
SELECT DISTINCT ON (k.asof, k.code) k.asof, k.code, t.doc_id, t.fiscal_year_end
FROM (SELECT DISTINCT asof, code FROM keiba) k
JOIN tdnet_disclosures t
ON t.code = k.code AND t.disclosed_date <= k.asof AND t.disclosed_date > k.asof - 400
AND t.parsed_at IS NOT NULL AND NOT t.is_amendment
ORDER BY k.asof, k.code, t.disclosed_date DESC, t.disclosed_time DESC
), fc AS (
-- 時点の短信の通期予想。本決算なら来期 (Next)、四半期なら当期 (Current)
SELECT DISTINCT ON (d.asof, d.code) d.asof, d.code, f.period_end AS fy, f.value AS op_f
FROM doc d JOIN tdnet_summary_facts f ON f.doc_id = d.doc_id
WHERE f.fact_type = 'Forecast' AND f.period_kind = 'Year' AND f.concept ~ '^tse-ed-t_OperatingIncome(IFRS|US)?$'
ORDER BY d.asof, d.code, f.is_consolidated DESC NULLS LAST
), act AS (
-- その年度の本決算の実績
SELECT DISTINCT ON (t.code, f.period_end) t.code, f.period_end AS fy, f.value AS op_a
FROM tdnet_disclosures t JOIN tdnet_summary_facts f ON f.doc_id = t.doc_id
WHERE t.quarter = 'FY' AND NOT t.is_amendment AND t.parsed_at IS NOT NULL
AND f.fact_type = 'Result' AND f.scope = 'Current' AND f.period_kind = 'Year'
AND f.concept ~ '^tse-ed-t_OperatingIncome(IFRS|US)?$'
ORDER BY t.code, f.period_end, f.is_consolidated DESC NULLS LAST, t.disclosed_date DESC
), base AS (
SELECT k.asof, k.code, k.s_growth, fc.op_f, act.op_a, act.op_a / nullif(fc.op_f, 0) AS 達成率
FROM keiba k JOIN fc ON fc.asof = k.asof AND fc.code = k.code
JOIN act ON act.code = k.code AND act.fy = fc.fy
WHERE k.s_growth IS NOT NULL AND fc.op_f > 0
), g AS (
SELECT *, ntile(5) OVER (PARTITION BY asof ORDER BY s_growth) AS gq FROM base
)
SELECT gq AS 増益率5分位, count(*) AS n,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY 達成率)::numeric, 1) AS 達成率_中央値,
round(100 * avg((達成率 < 0.9)::int), 1) AS 未達10pct超,
round(100 * avg((達成率 < 1)::int), 1) AS 未達,
round(100 * avg((達成率 > 1.1)::int), 1) AS 超過10pct超
FROM g GROUP BY 1 ORDER BY 1;
\echo
\echo '==== 予想 PER と実績 PER の効き比べ (上位 20% − 下位 20%、中央値)'
WITH doc AS (
SELECT DISTINCT ON (k.asof, k.code) k.asof, k.code, t.doc_id, t.disclosed_date
FROM (SELECT DISTINCT asof, code FROM keiba) k
JOIN tdnet_disclosures t
ON t.code = k.code AND t.disclosed_date <= k.asof AND t.disclosed_date > k.asof - 400
AND t.parsed_at IS NOT NULL AND NOT t.is_amendment AND t.quarter = 'FY'
ORDER BY k.asof, k.code, t.disclosed_date DESC, t.disclosed_time DESC
), eps_a AS (
-- 時点以前の最新の本決算の実績 EPS
SELECT DISTINCT ON (d.asof, d.code) d.asof, d.code, f.value AS eps_a
FROM doc d JOIN tdnet_summary_facts f ON f.doc_id = d.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 d.asof, d.code, f.is_consolidated DESC NULLS LAST
), sp AS (
SELECT k.asof, k.code, coalesce(td.adj / t0.adj, 1) AS f
FROM keiba k JOIN doc d ON d.asof = k.asof AND d.code = k.code
JOIN tk t0 ON t0.code = k.code AND t0.date = (SELECT max(date) FROM tk WHERE code = k.code AND date <= k.asof)
LEFT JOIN LATERAL (SELECT adj FROM tk WHERE code = k.code AND date <= d.disclosed_date ORDER BY date DESC LIMIT 1) td ON true
), base AS (
SELECT k.asof, k.code, k.ret_fwd, k.s_per, k.s_pbr, e.eps_a,
CASE WHEN e.eps_a > 0 THEN k.close / (e.eps_a * sp.f) END AS per_a
FROM keiba k JOIN eps_a e ON e.asof = k.asof AND e.code = k.code
JOIN sp ON sp.asof = k.asof AND sp.code = k.code
WHERE k.ret_fwd IS NOT NULL
), r AS (
SELECT *,
CASE WHEN eps_a <= 0 THEN 0
ELSE percent_rank() OVER (PARTITION BY asof, per_a IS NULL ORDER BY per_a DESC) END AS s_per_a
FROM base
), s AS (
SELECT asof, ret_fwd, x.name AS 要素, x.v
FROM r, LATERAL (VALUES ('予想PER', s_per), ('実績PER', s_per_a), ('PBR', s_pbr)) 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 上位_下位,
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 2 DESC;
\echo '==== 実績 PER で安い上位 20% を、予想増益率の上下で割る (予想の偏りが消えるか)'
WITH doc AS (
SELECT DISTINCT ON (k.asof, k.code) k.asof, k.code, t.doc_id, t.disclosed_date
FROM (SELECT DISTINCT asof, code FROM keiba) k
JOIN tdnet_disclosures t
ON t.code = k.code AND t.disclosed_date <= k.asof AND t.disclosed_date > k.asof - 400
AND t.parsed_at IS NOT NULL AND NOT t.is_amendment AND t.quarter = 'FY'
ORDER BY k.asof, k.code, t.disclosed_date DESC, t.disclosed_time DESC
), eps_a AS (
SELECT DISTINCT ON (d.asof, d.code) d.asof, d.code, f.value AS eps_a
FROM doc d JOIN tdnet_summary_facts f ON f.doc_id = d.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 d.asof, d.code, f.is_consolidated DESC NULLS LAST
), base AS (
SELECT k.asof, k.ret_fwd, k.s_per, k.s_growth, e.eps_a,
CASE WHEN e.eps_a > 0 THEN k.close / e.eps_a END AS per_a
FROM keiba k JOIN eps_a e ON e.asof = k.asof AND e.code = k.code
WHERE k.ret_fwd IS NOT NULL AND k.s_growth IS NOT NULL
), r AS (
SELECT *, CASE WHEN eps_a <= 0 THEN 0 ELSE percent_rank() OVER (PARTITION BY asof, per_a IS NULL ORDER BY per_a DESC) END AS s_per_a
FROM base
), q AS (
SELECT ret_fwd, s_growth,
ntile(5) OVER (PARTITION BY asof ORDER BY s_per_a) AS qa,
ntile(5) OVER (PARTITION BY asof ORDER BY s_per) AS qf
FROM r WHERE s_per IS NOT NULL
)
SELECT 'PER 安い上位20%' AS 組, x.name AS 分母, CASE WHEN s_growth >= 0.5 THEN '増益率 上半分' ELSE '増益率 下半分' END AS 増益率,
count(*) n, round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret_fwd)::numeric, 2) AS 中央値
FROM q, LATERAL (VALUES ('予想', qf), ('実績', qa)) AS x(name, v)
WHERE x.v = 5 GROUP BY 2, 3 ORDER BY 2, 3;