07-行为数据查询

决策日志、组合快照、可投标的清单的查询接口。支撑终点段决策复盘、实盘操作跟踪与中间段机会清单命中率回测。这三张表记录投资者自身行为，与市场数据分离。

决策日志查询

近期决策记录

from db import get_connection
from query_utils import query_to_df

conn = get_connection()
df = query_to_df(conn, """
    SELECT decision_date, action, target_code, target_name,
           amount, shares, price, pe_percentile, strategy, emotion
    FROM decision_log
    ORDER BY decision_date DESC, id DESC
    LIMIT 30
""")

决策按动作统计

SELECT action, COUNT(*) AS times, SUM(amount) AS total_amount
FROM decision_log
WHERE decision_date >= date('now', '-1 year')
GROUP BY action
ORDER BY times DESC;

决策时估值分位回溯

复盘时还原"当时决策基于什么估值位置"：

SELECT decision_date, target_name, action, amount,
       pe_percentile, pb_percentile, strategy, reason
FROM decision_log
WHERE target_code = '000300'
  AND action IN ('buy', 'take_profit')
ORDER BY decision_date;

pe_percentile 是决策时的快照，不会因后续数据更新而变化，确保复盘能还原真实决策依据。

决策命中率统计

买入后收益跟踪

WITH buy_decisions AS (
    SELECT d.decision_date, d.target_code, d.target_name,
           d.price AS buy_price, d.pe_percentile AS buy_pe_pct,
           t.close AS latest_close
    FROM decision_log d
    JOIN index_daily t
        ON t.index_code = d.target_code
        AND t.trade_date = (SELECT MAX(trade_date) FROM index_daily)
    WHERE d.action = 'buy'
      AND d.decision_date <= date('now', '-30 days')
)
SELECT
    target_name,
    decision_date,
    buy_price,
    latest_close,
    (latest_close / buy_price - 1) * 100 AS return_pct,
    buy_pe_pct
FROM buy_decisions
ORDER BY return_pct DESC;

按策略分组命中率

SELECT
    d.strategy,
    COUNT(*) AS decision_count,
    AVG(CASE WHEN t.close > d.price THEN 1.0 ELSE 0.0 END) AS win_rate,
    AVG(t.close / d.price - 1) * 100 AS avg_return_pct
FROM decision_log d
JOIN index_daily t
    ON t.index_code = d.target_code
    AND t.trade_date = (SELECT MAX(trade_date) FROM index_daily)
WHERE d.action = 'buy'
  AND d.decision_date <= date('now', '-30 days')
GROUP BY d.strategy
ORDER BY avg_return_pct DESC;

用于判断哪类策略（估值定投/趋势跟踪/首次建仓）的决策命中率最高。

情绪标签复盘

情绪与决策质量关联

SELECT
    d.emotion,
    COUNT(*) AS times,
    AVG(t.close / d.price - 1) * 100 AS avg_return_pct
FROM decision_log d
JOIN index_daily t
    ON t.index_code = d.target_code
    AND t.trade_date = (SELECT MAX(trade_date) FROM index_daily)
WHERE d.action IN ('buy', 'sell')
  AND d.decision_date <= date('now', '-30 days')
GROUP BY d.emotion
ORDER BY avg_return_pct DESC;

如果"恐慌"决策的平均收益显著高于"贪婪"，说明逆人性操作有效；反之则需要加强心态纪律训练。这是路线分析中"逆人性决策能力"的量化指标。

组合快照查询

组合收益历史曲线

SELECT snapshot_date,
       SUM(market_value) AS total_market_value,
       SUM(cost_value) AS total_cost,
       SUM(profit_loss) AS total_profit_loss,
       SUM(profit_loss) / SUM(cost_value) * 100 AS total_return_pct
FROM portfolio_snapshot
GROUP BY snapshot_date
ORDER BY snapshot_date;

单标的持仓变化

SELECT snapshot_date, shares, cost_price, current_price,
       profit_loss_pct, weight, pe_percentile
FROM portfolio_snapshot
WHERE target_code = '000300'
ORDER BY snapshot_date;

用于绘制单标的的"微笑曲线"--份额累积与成本变化的过程。

组合估值分布

当前组合加权估值分位

SELECT
    SUM(weight * pe_percentile) / SUM(weight) AS weighted_pe_pct,
    SUM(weight * pb_percentile) / SUM(weight) AS weighted_pb_pct,
    SUM(market_value) AS total_value
FROM portfolio_snapshot
WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM portfolio_snapshot)
  AND pe_percentile IS NOT NULL;

组合加权 PE 分位是判断"整体组合是贵还是便宜"的核心指标。如果组合加权分位 > 70%，即使单标的分位不高，也应考虑整体减仓。

可投标的清单查询

当前关注清单

SELECT target_code, target_name, source, priority, status,
       pe_percentile_at_add, expected_entry_pe_pct,
       added_date, thesis
FROM watchlist
WHERE status IN ('pending', 'watching')
ORDER BY
    CASE priority WHEN 'high' THEN 1 WHEN 'medium' THEN 2 ELSE 3 END,
    added_date;

清单来源分布

SELECT source, COUNT(*) AS count,
       SUM(CASE WHEN status = 'entered' THEN 1 ELSE 0 END) AS entered_count,
       SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) AS rejected_count
FROM watchlist
GROUP BY source
ORDER BY count DESC;

判断哪类分析模块（政策分析/行业分析/产业趋势/机会分析）产出的清单命中率最高，用于优化中间段分析精力分配。

清单命中率回测

WITH entered AS (
    SELECT w.target_code, w.target_name, w.added_date,
           w.pe_percentile_at_add, w.expected_entry_pe_pct,
           d.decision_date AS entry_date,
           d.price AS entry_price
    FROM watchlist w
    JOIN decision_log d
        ON d.target_code = w.target_code
        AND d.action = 'buy'
    WHERE w.status = 'entered'
)
SELECT
    e.target_name,
    e.added_date,
    e.entry_date,
    julianday(e.entry_date) - julianday(e.added_date) AS days_to_entry,
    e.pe_percentile_at_add,
    e.expected_entry_pe_pct,
    e.entry_price,
    t.close AS latest_close,
    (t.close / e.entry_price - 1) * 100 AS return_pct
FROM entered e
JOIN index_daily t
    ON t.index_code = e.target_code
    AND t.trade_date = (SELECT MAX(trade_date) FROM index_daily)
ORDER BY return_pct DESC;

days_to_entry 反映"从发现机会到实际建仓"的时间效率，如果普遍过长，说明决策执行力不足或入场条件过于苛刻。

行为数据写入

行为数据由投资者手动或半自动写入，不通过 akshare 获取。建议在 fetcher_behavior.py 中封装写入函数（可按需创建）：

def log_decision(conn, decision_date, action, target_type, target_code,
                 target_name, amount, shares, price, strategy, emotion,
                 reason, linked_doc=None):
    """记录一条决策日志，自动快照当前 PE/PB 分位。"""
    # 从 index_valuation 快照当前分位
    row = conn.execute(
        "SELECT pe_percentile, pb_percentile FROM index_valuation "
        "WHERE index_code=? AND trade_date=(SELECT MAX(trade_date) FROM index_valuation)",
        (target_code,),
    ).fetchone()
    pe_pct = row["pe_percentile"] if row else None
    pb_pct = row["pb_percentile"] if row else None

    conn.execute(
        "INSERT INTO decision_log "
        "(decision_date, action, target_type, target_code, target_name, "
        "amount, shares, price, pe_percentile, pb_percentile, reason, "
        "strategy, emotion, linked_doc, created_at) "
        "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
        (decision_date, action, target_type, target_code, target_name,
         amount, shares, price, pe_pct, pb_pct, reason,
         strategy, emotion, linked_doc, datetime.now().isoformat()),
    )
    conn.commit()

组合快照与清单写入类似，建议封装为 snapshot_portfolio(conn, date) 和 add_to_watchlist(conn, ...) 函数。

与复盘文档的衔接

decision_log.linked_doc 字段记录关联的复盘文档路径，支持双向跳转：

从数据库查决策记录 -> 跳转到对应的复盘文档（如 /04-终点段/05-决策复盘/02-买入决策日志）
从复盘文档引用决策数据 -> 通过查询接口获取结构化记录

决策日志的 Markdown 模板见 01-路线图/03-模板表，数据库记录与 Markdown 文档互补：数据库用于统计查询，Markdown 用于详细复盘叙述。

封装函数建议

def get_recent_decisions(conn, limit: int = 30) -> pd.DataFrame:
    """获取近期决策记录。"""

def get_decision_hit_rate(conn, strategy: str = None) -> dict:
    """获取决策命中率统计。"""

def get_latest_portfolio(conn) -> pd.DataFrame:
    """获取最新组合快照。"""

def get_portfolio_return_history(conn) -> pd.DataFrame:
    """获取组合收益历史曲线。"""

def get_active_watchlist(conn) -> pd.DataFrame:
    """获取当前关注清单。"""

def get_watchlist_hit_rate(conn) -> dict:
    """获取清单命中率统计。"""

#07-行为数据查询

#决策日志查询

#近期决策记录

#决策按动作统计

#决策时估值分位回溯

#决策命中率统计

#买入后收益跟踪

#按策略分组命中率

#情绪标签复盘

#情绪与决策质量关联

#组合快照查询

#最新组合全貌

#组合收益历史曲线

#单标的持仓变化

#组合估值分布

#当前组合加权估值分位

#可投标的清单查询

#当前关注清单

#清单来源分布

#清单命中率回测

#行为数据写入

#与复盘文档的衔接

#封装函数建议