PostgreSQL 交易数据库查询

pip install psycopg2-binary

{
  "databases": [
    {
      "name": "trading_bot",
      "description": "量化交易系统主数据库 - 订单、持仓、策略日志、盈亏记录",
      "host": "localhost",
      "port": 5432,
      "database": "trading_bot",
      "user": "chenlingjie",
      "password": "${POSTGRES_PASSWORD}",
      "sslmode": "prefer"
    }
  ]
}

-- 最近 N 笔交易
SELECT * FROM trades ORDER BY open_time DESC LIMIT 20;

-- 特定品种盈亏汇总
SELECT symbol, COUNT(*) as total, 
       SUM(CASE WHEN profit > 0 THEN 1 ELSE 0 END) as wins,
       ROUND(AVG(profit), 2) as avg_profit
FROM trades GROUP BY symbol;

-- 日收益统计
SELECT DATE(close_time) as trade_date,
       COUNT(*) as trades,
       SUM(profit) as daily_pnl,
       ROUND(SUM(CASE WHEN profit > 0 THEN 1.0 ELSE 0.0 END) / COUNT(*) * 100, 1) as win_rate
FROM trades WHERE close_time IS NOT NULL
GROUP BY DATE(close_time) ORDER BY trade_date DESC;

-- 最大连续亏损
WITH consecutive AS (
  SELECT *, SUM(CASE WHEN profit >= 0 THEN 1 ELSE 0 END) 
    OVER (ORDER BY close_time) as grp
  FROM trades WHERE close_time IS NOT NULL
)
SELECT MIN(close_time) as start, MAX(close_time) as end,
       COUNT(*) as streak, SUM(profit) as total_loss
FROM consecutive WHERE profit < 0
GROUP BY grp ORDER BY total_loss LIMIT 5;

-- 当前持仓汇总
SELECT symbol, direction, SUM(volume) as total_lots,
       SUM(profit) as unrealized_pnl
FROM positions WHERE status = 'open'
GROUP BY symbol, direction;

-- 回撤计算
SELECT MAX(balance) as peak_balance,
       MIN(balance) as trough_balance,
       ROUND((MAX(balance) - MIN(balance)) / MAX(balance) * 100, 2) as max_dd_pct
FROM account_snapshots WHERE timestamp > NOW() - INTERVAL '30 days';