#04-行业景气查询
申万行业行情、轮动、估值查询。支撑中间段行业分析与景气度跟踪。
#行业列表
SELECT industry_code, industry_name, industry_level, parent_code
FROM dim_industry
WHERE industry_level = 1
ORDER BY industry_code;申万一级行业共 31 个，代码以 80 开头。
#行业日线行情
from db import get_connection
from query_utils import query_to_df, quality_filter
conn = get_connection()
df = query_to_df(conn, f"""
SELECT trade_date, open, close, high, low, volume, amount, pct_change
FROM industry_daily
WHERE industry_code = ?
AND trade_date >= date('now', '-1 year')
AND {quality_filter()}
ORDER BY trade_date
""", ("801080",)) # 电子行业#行业均线与趋势
df["ma20"] = df["close"].rolling(20).mean()
df["ma60"] = df["close"].rolling(60).mean()
df["trend"] = df["close"] > df["ma60"] # 简单趋势判断#行业涨跌幅排名
#近期表现
-- 近 20 个交易日行业涨跌幅排名
WITH recent AS (
SELECT industry_code, trade_date, close,
ROW_NUMBER() OVER (PARTITION BY industry_code ORDER BY trade_date DESC) AS rn
FROM industry_daily
WHERE data_quality IN ('ok', 'manual_fixed')
AND trade_date >= date('now', '-40 days')
),
ranked AS (
SELECT
r1.industry_code,
d.industry_name,
r1.close AS latest_close,
r20.close AS close_20d_ago,
(r1.close / r20.close - 1) * 100 AS pct_20d
FROM recent r1
JOIN recent r20 ON r1.industry_code = r20.industry_code AND r20.rn = 20
JOIN dim_industry d ON r1.industry_code = d.industry_code
WHERE r1.rn = 1
)
SELECT industry_name, pct_20d
FROM ranked
ORDER BY pct_20d DESC;#多周期对比
-- 近 5 日 / 20 日 / 60 日 / 250 日涨跌幅
WITH closes AS (
SELECT
d.industry_name,
t.industry_code,
MAX(CASE WHEN t.trade_date = date('now', '-5 days') THEN t.close END) AS c5,
MAX(CASE WHEN t.trade_date = date('now', '-20 days') THEN t.close END) AS c20,
MAX(CASE WHEN t.trade_date = date('now', '-60 days') THEN t.close END) AS c60,
MAX(CASE WHEN t.trade_date = date('now', '-250 days') THEN t.close END) AS c250,
MAX(CASE WHEN t.trade_date = (SELECT MAX(trade_date) FROM industry_daily) THEN t.close END) AS cnow
FROM industry_daily t
JOIN dim_industry d ON t.industry_code = d.industry_code
WHERE t.data_quality IN ('ok', 'manual_fixed')
GROUP BY d.industry_name, t.industry_code
)
SELECT
industry_name,
(cnow/c5 - 1) * 100 AS pct_5d,
(cnow/c20 - 1) * 100 AS pct_20d,
(cnow/c60 - 1) * 100 AS pct_60d,
(cnow/c250 - 1) * 100 AS pct_250d
FROM closes
ORDER BY pct_20d DESC;#行业轮动分析
#近期强势行业变化
-- 对比近 20 日与近 60 日的强势行业
WITH r20 AS (
SELECT industry_code, industry_name,
(latest/close_20d_ago - 1) * 100 AS pct_20d
FROM (
SELECT
t.industry_code, d.industry_name,
MAX(CASE WHEN t.trade_date = (SELECT MAX(trade_date) FROM industry_daily) THEN t.close END) AS latest,
MAX(CASE WHEN t.trade_date = date('now', '-20 days') THEN t.close END) AS close_20d_ago
FROM industry_daily t
JOIN dim_industry d ON t.industry_code = d.industry_code
WHERE t.data_quality IN ('ok', 'manual_fixed')
GROUP BY t.industry_code, d.industry_name
)
)
SELECT industry_name, pct_20d,
RANK() OVER (ORDER BY pct_20d DESC) AS rank_20d
FROM r20
ORDER BY pct_20d DESC
LIMIT 10;#行业间相关性
import pandas as pd
# 获取所有行业日线
df = query_to_df(conn, """
SELECT d.industry_name, t.trade_date, t.pct_change
FROM industry_daily t
JOIN dim_industry d ON t.industry_code = d.industry_code
WHERE t.data_quality IN ('ok', 'manual_fixed')
AND t.trade_date >= date('now', '-1 year')
""")
pivot = df.pivot(index="trade_date", columns="industry_name", values="pct_change")
corr = pivot.corr()
# 高相关行业对
print(corr.unstack().sort_values(ascending=False).drop_duplicates().head(20))#行业估值查询
行业指数本身没有直接估值数据，可通过成分股加权计算：
-- 行业成分股加权 PE（简化版，用算术平均）
SELECT
d.industry_name,
AVG(s.pe_ttm) AS avg_pe,
AVG(s.pb) AS avg_pb,
COUNT(*) AS stock_count
FROM dim_stock st
JOIN stock_daily s ON st.stock_code = s.stock_code
JOIN dim_industry d ON st.industry_code = d.industry_code
WHERE s.trade_date = (SELECT MAX(trade_date) FROM stock_daily)
AND s.pe_ttm > 0
AND s.pe_ttm < 200 -- 排除异常值
AND s.data_quality IN ('ok', 'manual_fixed')
GROUP BY d.industry_name
ORDER BY avg_pe;注意：算术平均有局限性，完整实现应按市值加权。
#行业资金流向
-- 行业成交量变化（代理资金流向）
SELECT
d.industry_name,
AVG(CASE WHEN t.trade_date >= date('now', '-5 days') THEN t.amount END) AS avg_amount_5d,
AVG(CASE WHEN t.trade_date >= date('now', '-20 days') AND t.trade_date < date('now', '-5 days') THEN t.amount END) AS avg_amount_20d,
AVG(CASE WHEN t.trade_date >= date('now', '-5 days') THEN t.amount END) /
AVG(CASE WHEN t.trade_date >= date('now', '-20 days') AND t.trade_date < date('now', '-5 days') THEN t.amount END) - 1 AS amount_change
FROM industry_daily t
JOIN dim_industry d ON t.industry_code = d.industry_code
WHERE t.data_quality IN ('ok', 'manual_fixed')
GROUP BY d.industry_name
HAVING amount_change IS NOT NULL
ORDER BY amount_change DESC;成交量放大可能意味着资金关注度提升，结合估值位置判断是机会还是风险。
#封装函数建议
def get_industry_list(conn, level: int = 1) -> pd.DataFrame:
"""获取行业列表。"""
def get_industry_daily(conn, industry_code: str, days: int = 250) -> pd.DataFrame:
"""获取行业日线。"""
def get_industry_ranking(conn, periods: list = [5, 20, 60, 250]) -> pd.DataFrame:
"""多周期行业涨跌幅排名。"""
def get_industry_valuation(conn, industry_code: str) -> pd.DataFrame:
"""行业成分股加权估值。"""