#03-基金分析查询
基金净值、持仓、跟踪误差查询。支撑基金标的研究与实盘操作。
#基金基本信息
SELECT fund_code, fund_name, fund_type, tracking_index,
management_company, manager, management_fee, custody_fee,
inception_date
FROM dim_fund
WHERE fund_code = '510300';按跟踪指数筛选基金：
SELECT f.fund_code, f.fund_name, f.fund_type, f.management_fee + f.custody_fee AS total_fee
FROM dim_fund f
WHERE f.tracking_index = '000300'
AND f.fund_type IN ('ETF', '场外指数')
ORDER BY total_fee ASC;同一跟踪指数的多只基金，优先选费率低的。
#基金净值与行情
#ETF 日线
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 fund_daily
WHERE fund_code = ?
AND trade_date >= date('now', '-1 year')
AND {quality_filter()}
ORDER BY trade_date
""", ("510300",))#场外基金净值
SELECT trade_date, unit_nav, accum_nav, pct_change
FROM fund_daily
WHERE fund_code = '110011'
AND data_quality IN ('ok', 'manual_fixed')
ORDER BY trade_date DESC
LIMIT 30;#累计收益率计算
df["daily_return"] = df["close"].pct_change()
df["cum_return"] = (1 + df["daily_return"]).cumprod() - 1#基金持仓查询
#最新持仓
SELECT stock_code, stock_name, hold_ratio, hold_value, rank
FROM fund_holdings
WHERE fund_code = '510300'
AND report_date = (SELECT MAX(report_date) FROM fund_holdings WHERE fund_code = '510300')
ORDER BY rank;#持仓变化（对比上期）
WITH latest AS (
SELECT stock_code, stock_name, hold_ratio, rank
FROM fund_holdings
WHERE fund_code = '510300'
AND report_date = (SELECT MAX(report_date) FROM fund_holdings WHERE fund_code = '510300')
),
prev AS (
SELECT stock_code, stock_name, hold_ratio, rank
FROM fund_holdings
WHERE fund_code = '510300'
AND report_date = (
SELECT MAX(report_date) FROM fund_holdings
WHERE fund_code = '510300'
AND report_date < (SELECT MAX(report_date) FROM fund_holdings WHERE fund_code = '510300')
)
)
SELECT
COALESCE(l.stock_code, p.stock_code) AS stock_code,
COALESCE(l.stock_name, p.stock_name) AS stock_name,
p.hold_ratio AS prev_ratio,
l.hold_ratio AS latest_ratio,
l.hold_ratio - p.hold_ratio AS ratio_change
FROM latest l
FULL OUTER JOIN prev p ON l.stock_code = p.stock_code
ORDER BY ABS(l.hold_ratio - p.hold_ratio) DESC NULLS LAST;注意 SQLite 较老版本不支持 FULL OUTER JOIN，可用 LEFT JOIN + UNION 替代。
#反向查询：哪些基金持有某股票
SELECT f.fund_code, f.fund_name, h.report_date, h.hold_ratio, h.rank
FROM fund_holdings h
JOIN dim_fund f ON h.fund_code = f.fund_code
WHERE h.stock_code = '600519'
AND h.report_date >= date('now', '-1 year')
ORDER BY h.report_date DESC, h.hold_ratio DESC;用于分析某只股票被哪些基金重仓，辅助判断个股的行业代表性。
#跟踪误差分析
指数基金与跟踪指数的偏离度：
WITH fund_returns AS (
SELECT trade_date, pct_change AS fund_ret
FROM fund_daily
WHERE fund_code = '510300' AND data_quality IN ('ok', 'manual_fixed')
),
index_returns AS (
SELECT trade_date, pct_change AS index_ret
FROM index_daily
WHERE index_code = '000300' AND data_quality IN ('ok', 'manual_fixed')
)
SELECT
AVG(f.fund_ret - i.index_ret) AS mean_tracking_error,
-- 跟踪误差标准差（年化）
SQRT(AVG((f.fund_ret - i.index_ret) * (f.fund_ret - i.index_ret))) * SQRT(250) AS annual_te
FROM fund_returns f
JOIN index_returns i ON f.trade_date = i.trade_date;年化跟踪误差 < 1% 为优秀，1-2% 为良好，> 2% 需关注。
#多基金对比
SELECT
f.fund_code, f.fund_name,
MAX(CASE WHEN d.trade_date = (SELECT MAX(trade_date) FROM fund_daily) THEN d.close END) AS latest_close,
COUNT(*) AS days,
MIN(d.close) AS min_close,
MAX(d.close) AS max_close
FROM dim_fund f
JOIN fund_daily d ON f.fund_code = d.fund_code
WHERE f.fund_code IN ('510300', '510500', '159915', '588000')
AND d.data_quality IN ('ok', 'manual_fixed')
GROUP BY f.fund_code, f.fund_name;#封装函数建议
def get_fund_info(conn, fund_code: str) -> pd.DataFrame:
"""基金基本信息。"""
def get_fund_nav_history(conn, fund_code: str, years: int = 3) -> pd.DataFrame:
"""基金净值历史。"""
def get_fund_holdings_latest(conn, fund_code: str) -> pd.DataFrame:
"""基金最新持仓。"""
def get_funds_holding_stock(conn, stock_code: str) -> pd.DataFrame:
"""反向查询：哪些基金持有某股票。"""
def calc_tracking_error(conn, fund_code: str, index_code: str) -> float:
"""计算年化跟踪误差。"""