analysis/weekly-market.sql
レポートの数字を出したコード。そのまま流せば同じ結果になる。
-- 1 週間の市況。市場区分別と業種別の変化を出す。
--
-- 指数は持っていない。日経平均も TOPIX も取得元が時系列を返さないため、手元の ticks から
-- 等加重で作る。時価総額加重と違って上位数十社に引きずられないため、業種の広がりを
-- 見るにはむしろ向く。
--
-- 毎週使い回す。期間は呼び出し側から渡すこと。起点と終点は営業日で指定する。
--
-- psql "$DATABASE_URL" -v d0=2026-09-04 -v d1=2026-09-11 -f analysis/weekly-market.sql
--
-- d0 は前週末の終値の日、d1 は当週末の終値の日になる。騰落は d0 から d1 の変化で測る。
-- 出来高の比較基準は d0 から遡って 4 週間にする。
-- TEMP VIEW にしない。参照するたびに元の問い合わせが走り直すため。ticks は 240 万行あり、
-- 下で 4 回参照するので、実体化しておかないと同じスキャンを繰り返すことになる。
CREATE TEMP TABLE chg AS
WITH px AS (
SELECT code, date, coalesce(adjusted_close, close) AS p
FROM ticks WHERE date IN (:'d0'::date, :'d1'::date)
)
SELECT s.code, s.market_segment, s.industry33_name, s.industry17_name,
b1.p / b0.p - 1 AS ret
FROM stocks s
JOIN px b0 ON b0.code = s.code AND b0.date = :'d0'::date
JOIN px b1 ON b1.code = s.code AND b1.date = :'d1'::date
WHERE s.is_listed AND b0.p > 0;
\echo '=== 全体 ==='
SELECT count(*) AS 銘柄数,
round(100 * avg(ret)::numeric, 2) AS 等加重pct,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret)::numeric, 2) AS 中央値pct,
count(*) FILTER (WHERE ret > 0) AS 上昇,
count(*) FILTER (WHERE ret < 0) AS 下落,
count(*) FILTER (WHERE ret <= -0.10) AS 下落10pct以上
FROM chg;
\echo '=== 市場区分別 ==='
SELECT market_segment AS 区分, count(*) AS 銘柄数,
round(100 * avg(ret)::numeric, 2) AS 等加重pct,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret)::numeric, 2) AS 中央値pct,
round(100 * (count(*) FILTER (WHERE ret > 0))::numeric / count(*), 1) AS 上昇率pct
FROM chg GROUP BY 1 ORDER BY array_position(ARRAY['prime','standard','growth'], market_segment);
\echo '=== 業種別 (33 業種、20 銘柄以上) ==='
SELECT industry33_name AS 業種, count(*) AS 銘柄数,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY ret)::numeric, 2) AS 中央値pct,
round(100 * (count(*) FILTER (WHERE ret > 0))::numeric / count(*), 1) AS 上昇率pct
FROM chg GROUP BY 1 HAVING count(*) >= 20 ORDER BY 3;
\echo '=== 日ごとの中央値 ==='
WITH d AS (
SELECT t.code, t.date, coalesce(t.adjusted_close, t.close) AS p,
lag(coalesce(t.adjusted_close, t.close)) OVER (PARTITION BY t.code ORDER BY t.date) AS prev
FROM ticks t JOIN stocks s ON s.code = t.code AND s.is_listed
WHERE t.date BETWEEN :'d0'::date AND :'d1'::date
)
SELECT date, count(*) AS 銘柄数,
round(100 * percentile_cont(0.5) WITHIN GROUP (ORDER BY p/prev - 1)::numeric, 2) AS 中央値pct,
count(*) FILTER (WHERE p > prev) AS 上昇, count(*) FILTER (WHERE p < prev) AS 下落
FROM d WHERE prev IS NOT NULL AND prev > 0 GROUP BY 1 ORDER BY 1;
-- ここから出来高。株数のまま足すと低位株に引きずられるので、売買代金 (出来高 x 終値) で測る。
-- 比較の基準は直前 4 週の日平均にする。基準の起点は d0 から 28 日遡って決める。
CREATE TEMP TABLE val AS
SELECT t.date, t.code, s.name, s.market_segment, s.industry33_name,
t.volume * t.close AS turnover
FROM ticks t JOIN stocks s ON s.code = t.code AND s.is_listed
WHERE t.date BETWEEN :'d0'::date - 28 AND :'d1'::date;
-- 当週の営業日数。祝日のある週は 5 日に満たないので、日平均はこれで割る。
SELECT count(DISTINCT date) AS nd FROM val WHERE date > :'d0'::date \gset
\echo '=== 週ごとの売買代金 ==='
SELECT CASE WHEN date > :'d0'::date THEN '当週' ELSE to_char(date, 'IYYY-"W"IW') END AS 週,
count(DISTINCT date) AS 営業日,
round(sum(turnover) / count(DISTINCT date) / 1e12, 3) AS 日平均兆円
FROM val GROUP BY 1 ORDER BY 1;
\echo '=== 区分別 当週 vs 直前 4 週の日平均 ==='
SELECT market_segment AS 区分,
round(sum(turnover) FILTER (WHERE date > :'d0'::date) / :nd / 1e8, 0) AS 当週日平均億円,
round((sum(turnover) FILTER (WHERE date > :'d0'::date) / :nd)
/ (sum(turnover) FILTER (WHERE date <= :'d0'::date)
/ count(DISTINCT date) FILTER (WHERE date <= :'d0'::date)), 2) AS 倍率
FROM val GROUP BY 1 ORDER BY 3 DESC;
\echo '=== 業種別 倍率 (20 銘柄以上) ==='
SELECT industry33_name AS 業種, count(DISTINCT code) AS 銘柄数,
round((sum(turnover) FILTER (WHERE date > :'d0'::date) / :nd)
/ nullif(sum(turnover) FILTER (WHERE date <= :'d0'::date)
/ count(DISTINCT date) FILTER (WHERE date <= :'d0'::date), 0), 2) AS 倍率
FROM val GROUP BY 1 HAVING count(DISTINCT code) >= 20 ORDER BY 3 DESC LIMIT 8;
\echo '=== 売買代金が膨らんだ銘柄 (当週日平均 200 億円以上) ==='
WITH agg AS (
SELECT code, name, industry33_name,
sum(turnover) FILTER (WHERE date > :'d0'::date) / :nd AS cur,
sum(turnover) FILTER (WHERE date <= :'d0'::date)
/ count(DISTINCT date) FILTER (WHERE date <= :'d0'::date) AS base
FROM val GROUP BY 1, 2, 3
)
SELECT a.code, a.name, a.industry33_name AS 業種,
round(a.cur / 1e8, 0) AS 当週日平均億円,
round(a.cur / nullif(a.base, 0), 1) AS 倍率,
round(100 * c.ret::numeric, 1) AS 週間pct
FROM agg a JOIN chg c ON c.code = a.code
WHERE a.cur > 20e9 AND a.base > 0 ORDER BY a.cur / a.base DESC LIMIT 10;