4. SQL基础回顾：SELECT、JOIN、GROUP BY、窗口函数在金融数据清洗中的基础用法

在组合风险归因系统中，原始数据通常来自交易系统、估值引擎和外部市场数据源。这些数据存在重复、缺失、格式不一致等问题。SQL作为数据清洗的核心工具，能够高效地将原始数据转化为可用于风险计算的干净数据集。本节回顾四个最常用的SQL操作，并展示它们在金融数据清洗中的典型场景。

4.1 SELECT：字段筛选与数据探查

SELECT是SQL中最基本的操作，用于从表中选取特定字段。在金融数据清洗中，SELECT通常与DISTINCT、WHERE和CAST配合使用，完成数据探查和类型转换。

典型场景：从交易流水表中提取特定日期的交易记录，并检查字段完整性。 
-- 探查交易表结构：检查日期格式和空值比例
SELECT 
    trade_date,
    COUNT(*) AS total_trades,
    SUM(CASE WHEN security_id IS NULL THEN 1 ELSE 0 END) AS missing_security,
    SUM(CASE WHEN quantity IS NULL THEN 1 ELSE 0 END) AS missing_qty,
    SUM(CASE WHEN price IS NULL THEN 1 ELSE 0 END) AS missing_price
FROM raw_trades
WHERE trade_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY trade_date
ORDER BY trade_date;
字段 清洗前常见问题 SELECT处理方式
trade_date 字符串格式不统一（20240101 vs 2024-01-01） CAST(trade_date AS DATE) 统一格式
price 包含空值或非数字字符 WHERE price IS NOT NULL AND price REGEXP '^[0-9.]+$'
currency 大小写不一致（usd/USD/Usd） UPPER(currency) 统一大写

4.2 JOIN：多表关联与数据补全

风险归因需要将交易数据与参考数据（证券主表、汇率表、因子暴露表）关联。JOIN操作是数据整合的核心，但需要警惕关联键重复和空值扩散问题。

实践建议：在金融数据清洗中，优先使用LEFT JOIN保留主表所有记录，然后通过CASE语句标记缺失的关联数据，而不是直接使用INNER JOIN丢弃记录。 
-- 交易数据关联证券主表和行业分类
SELECT 
    t.trade_id,
    t.security_id,
    s.security_name,
    s.asset_class,
    s.industry_sector,
    t.quantity * t.price AS notional,
    CASE WHEN s.industry_sector IS NULL THEN 'UNKNOWN' ELSE s.industry_sector END AS sector_clean
FROM raw_trades t
LEFT JOIN security_master s 
    ON t.security_id = s.security_id
    AND s.effective_date <= t.trade_date
    AND (s.expiry_date IS NULL OR s.expiry_date >= t.trade_date);

4.3 GROUP BY：聚合计算与异常检测

GROUP BY用于按维度汇总数据，在清洗阶段主要用于发现异常值、重复记录和边界情况。结合HAVING子句可以快速定位数据质量问题。

-- 检测重复交易：同一组合、证券、日期出现多次
SELECT 
    portfolio_id,
    security_id,
    trade_date,
    COUNT(*) AS record_count,
    SUM(quantity) AS total_qty,
    SUM(notional) AS total_notional
FROM trades_clean
GROUP BY portfolio_id, security_id, trade_date
HAVING COUNT(*) > 1
ORDER BY record_count DESC;
聚合维度 检测目标 清洗动作
portfolio_id + security_id + trade_date 重复交易记录 保留一条或按规则合并
portfolio_id + trade_date 日末持仓汇总与外部对账差异 标记差异并人工核查
security_id + month 价格序列缺失或异常跳变 插值或剔除异常点

4.4 窗口函数：时序计算与排名

窗口函数在不改变行数的情况下进行分组排序和累计计算，特别适合金融时间序列数据的清洗，如去重保留最新记录、计算日收益率、标记异常波动。

-- 使用ROW_NUMBER去重：保留每个证券每个交易日的最新一条价格记录
WITH price_dedup AS (
    SELECT 
        security_id,
        price_date,
        close_price,
        source,
        ROW_NUMBER() OVER (
            PARTITION BY security_id, price_date 
            ORDER BY update_timestamp DESC
        ) AS rn
    FROM raw_prices
)
SELECT security_id, price_date, close_price, source
FROM price_dedup
WHERE rn = 1;
常见陷阱：窗口函数中的ORDER BY方向直接影响结果。在金融数据中，通常使用DESC排序保留最新记录；但计算累计收益率时，必须使用ASC按时间正序排列。 
-- 计算日收益率并标记异常波动（超过3倍标准差）
WITH daily_returns AS (
    SELECT 
        security_id,
        trade_date,
        close_price,
        LAG(close_price) OVER (
            PARTITION BY security_id 
            ORDER BY trade_date
        ) AS prev_close,
        (close_price / LAG(close_price) OVER (
            PARTITION BY security_id 
            ORDER BY trade_date
        ) - 1) AS daily_return
    FROM prices_clean
),
volatility_stats AS (
    SELECT 
        security_id,
        AVG(daily_return) AS avg_ret,
        STDDEV(daily_return) AS std_ret
    FROM daily_returns
    WHERE daily_return IS NOT NULL
    GROUP BY security_id
)
SELECT 
    d.security_id,
    d.trade_date,
    d.daily_return,
    CASE 
        WHEN ABS(d.daily_return - v.avg_ret) > 3 * v.std_ret 
        THEN 'OUTLIER' 
        ELSE 'NORMAL' 
    END AS flag
FROM daily_returns d
JOIN volatility_stats v ON d.security_id = v.security_id;
SQL清洗操作在风险归因数据流中的位置 原始交易数据 参考数据表 市场行情数据 SQL 数据清洗层 SELECT 字段筛选 JOIN 多表关联 GROUP BY 聚合 窗口函数 时序处理 清洗后的风险归因基础数据集 风险计算 归因分析 报告生成

4.5 四种操作的组合使用策略

在实际清洗流程中，四种操作通常按以下顺序组合：先用SELECT和WHERE做初步过滤，再用JOIN关联参考数据，然后用窗口函数处理时序逻辑，最后用GROUP BY做质量校验。下表总结了每种操作在清洗中的核心用途和性能注意事项。

操作 核心用途 金融清洗典型场景 性能注意事项
SELECT 字段投影、类型转换、空值检查 统一日期格式、过滤无效价格 避免SELECT *，只取必要字段
JOIN 多表关联、数据补全、维度扩展 交易关联证券主表、汇率转换 关联键建索引，警惕笛卡尔积
GROUP BY 聚合汇总、重复检测、统计校验 日末持仓汇总、对账差异发现 合理使用HAVING过滤聚合结果
窗口函数 排序去重、累计计算、滑动统计 保留最新价格、计算收益率、标记异常 PARTITION BY字段不宜过多
清洗流程示例：以下SQL片段展示了四种操作的组合使用，完成从原始交易数据到干净风险计算表的完整清洗。 
WITH 
-- 第一步：SELECT + 窗口函数去重
dedup_trades AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY trade_id 
            ORDER BY update_ts DESC
        ) AS rn
    FROM raw_trades
    WHERE trade_date >= '2024-01-01'
),
-- 第二步：JOIN关联参考数据
trades_with_ref AS (
    SELECT 
        d.*,
        s.asset_class,
        s.industry_sector,
        s.currency AS security_ccy,
        fx.rate AS usd_rate
    FROM dedup_trades d
    LEFT JOIN security_master s 
        ON d.security_id = s.security_id
    LEFT JOIN fx_rates fx 
        ON fx.ccy_pair = CONCAT(s.currency, 'USD')
        AND fx.rate_date = d.trade_date
    WHERE d.rn = 1
),
-- 第三步：窗口函数计算累计指标
trades_enriched AS (
    SELECT *,
        quantity * price * usd_rate AS notional_usd,
        SUM(quantity * price * usd_rate) OVER (
            PARTITION BY portfolio_id, trade_date
        ) AS daily_portfolio_notional
    FROM trades_with_ref
)
-- 第四步：GROUP BY质量校验
SELECT 
    trade_date,
    COUNT(*) AS trade_count,
    SUM(notional_usd) AS total_notional,
    COUNT(CASE WHEN asset_class IS NULL THEN 1 END) AS missing_asset_class
FROM trades_enriched
GROUP BY trade_date
HAVING COUNT(CASE WHEN asset_class IS NULL THEN 1 END) > 0
ORDER BY trade_date;
← 上一章 📖 返回目录 下一章 →