3. SQL 基础回顾：SELECT、WHERE、JOIN、GROUP BY、HAVING、ORDER BY 核心语法精讲

在保险精算坏样本归因分析中，SQL 是提取、清洗和聚合数据的核心工具。本章将系统回顾六大核心子句的语法与执行逻辑，并结合精算场景（如保单表、理赔表、费用表）进行实战演示。掌握这些基础，是后续构建复杂归因查询的基石。

3.1 核心子句执行顺序与逻辑模型

SQL 语句的书写顺序与数据库引擎的实际执行顺序不同。理解这一差异，能帮助精算师更准确地编写查询，避免因逻辑误解导致的归因偏差。

书写顺序 子句 执行顺序 核心作用
1 SELECT 5 投影：选择需要的列，计算表达式
2 FROM 1 确定数据源表，执行 JOIN 生成虚拟表
3 WHERE 2 行过滤：对 FROM 结果逐行筛选
4 GROUP BY 3 分组：将数据按列值聚合为组
5 HAVING 4 组过滤：对 GROUP BY 后的组进行筛选
6 ORDER BY 6 排序：对最终结果集进行排序
精算提示： 在坏样本归因中，WHERE 用于过滤“正常样本”，HAVING 用于筛选“异常聚合组”。例如：先用 WHERE claim_amount > 0 排除无理赔记录，再用 HAVING COUNT(*) > 100 找出高频理赔组。
SQL 六大子句执行流水线 SQL 六大子句执行流水线（精算视角） FROM / JOIN WHERE GROUP BY HAVING SELECT ORDER BY 精算执行逻辑说明： 1. FROM/JOIN：先关联保单表、理赔表、费用表，生成宽表。 2. WHERE：过滤掉无效保单（如退保、未生效）。 3. GROUP BY：按产品、渠道、地区等维度分组。 4. HAVING：筛选出赔付率异常或坏样本集中的组。 5. SELECT：计算聚合指标（如 SUM、AVG、COUNT）。 6. ORDER BY：按坏样本贡献度降序排列，定位根因。

3.2 SELECT & FROM：数据投影与源表关联

SELECT 用于指定输出列，FROM 用于指定数据源。在精算归因中，通常需要从多张表（如保单表 policy、理赔表 claim、费用表 expense）中提取字段。

-- 基础查询：从保单表提取关键字段
SELECT
    policy_id,
    product_code,
    underwrite_date,
    premium_amount
FROM policy
WHERE status = '有效';
归因实战要点：
  • 使用 SELECT DISTINCT 去重，识别唯一产品/渠道组合。
  • 使用 SELECT TOP 100（SQL Server）或 LIMIT 100（MySQL/PostgreSQL）快速预览数据。
  • 避免 SELECT *，显式列出字段可提升可读性与性能。

3.3 WHERE：行级过滤——定位坏样本候选集

WHERE 在分组前对每一行进行过滤，是坏样本归因的第一步。精算场景中常见的过滤条件包括：

  • 时间窗口：WHERE claim_date BETWEEN '2024-01-01' AND '2024-12-31'
  • 异常值排除：WHERE premium_amount > 0 AND claim_amount < 1000000
  • 业务状态：WHERE policy_status = '有效' AND cancel_flag = 'N'
  • 坏样本标记：WHERE loss_ratio > 0.8 OR fraud_score > 0.7
-- 示例：筛选出2024年高赔付率保单
SELECT
    policy_id,
    product_code,
    SUM(premium) AS total_premium,
    SUM(claim_paid) AS total_claim
FROM policy p
JOIN claim c ON p.policy_id = c.policy_id
WHERE p.underwrite_year = 2024
  AND c.claim_status = '已决'
  AND p.premium > 0
GROUP BY policy_id, product_code
HAVING (SUM(claim_paid) / SUM(premium)) > 0.8;
常见陷阱： WHERE 中不能使用聚合函数（如 SUM、AVG）。若需对聚合结果过滤，必须使用 HAVING。例如：WHERE SUM(claim_amount) > 10000 是错误的，应改为 HAVING SUM(claim_amount) > 10000。

3.4 JOIN：多表关联——构建归因宽表

坏样本归因通常需要将保单、理赔、费用、代理人等多张表关联成一张宽表。JOIN 类型的选择直接影响归因结果的完整性。

JOIN 类型 返回行数 精算应用场景
INNER JOIN 仅匹配行 仅分析既有保单又有理赔的记录（如已决赔案）
LEFT JOIN 左表全部 + 右表匹配 保留所有保单，无理赔的保单用 NULL 填充（如零赔案分析）
RIGHT JOIN 右表全部 + 左表匹配 保留所有理赔记录，即使保单已失效（如欺诈理赔追溯）
FULL OUTER JOIN 两表全部 保单与理赔全量对账，发现孤儿保单或幽灵理赔 
-- 示例：LEFT JOIN 保留所有保单，关联理赔与费用
SELECT
    p.policy_id,
    p.product_code,
    p.premium,
    COALESCE(SUM(c.claim_paid), 0) AS total_claim,
    COALESCE(SUM(e.acq_fee + e.maint_fee), 0) AS total_expense
FROM policy p
LEFT JOIN claim c ON p.policy_id = c.policy_id
LEFT JOIN expense e ON p.policy_id = e.policy_id
WHERE p.status = '有效'
GROUP BY p.policy_id, p.product_code, p.premium;

3.5 GROUP BY & HAVING：分组聚合与组级过滤

GROUP BY 将数据按一个或多个维度分组，HAVING 对分组后的聚合结果进行过滤。这是坏样本归因的核心环节，用于识别异常聚合单元（如高赔付率产品线、高退保率渠道）。

  • 常用聚合函数： COUNT()、SUM()、AVG()、MAX()、MIN()
  • 分组维度： 产品代码、销售渠道、地区、核保月份、代理人等级
  • HAVING 条件： 赔付率 > 行业均值 + 2σ、退保率 > 20%、件均理赔额异常
-- 示例：按产品与渠道分组，筛选坏样本集中组
SELECT
    product_code,
    channel_code,
    COUNT(DISTINCT policy_id) AS policy_cnt,
    SUM(claim_paid) / NULLIF(SUM(premium), 0) AS loss_ratio,
    AVG(claim_paid) AS avg_claim
FROM policy p
JOIN claim c ON p.policy_id = c.policy_id
WHERE p.underwrite_date >= '2024-01-01'
GROUP BY product_code, channel_code
HAVING COUNT(DISTINCT policy_id) >= 50
   AND (SUM(claim_paid) / NULLIF(SUM(premium), 0)) > 0.7
ORDER BY loss_ratio DESC;
精算技巧： 使用 NULLIF(denominator, 0) 避免除零错误。在 HAVING 中优先使用聚合后的指标（如赔付率），而非原始字段，因为 HAVING 是在分组后执行的。

3.6 ORDER BY：排序——定位归因根因

ORDER BY 对最终结果集进行排序，帮助精算师快速定位贡献度最高或异常最严重的维度。排序方向：

  • ASC（默认）：升序，适用于找出最小赔付率、最低保费等。
  • DESC：降序，适用于找出最大坏样本贡献、最高赔付率等。
-- 示例：按坏样本贡献度降序排列，定位 Top 10 根因产品
SELECT
    product_code,
    COUNT(DISTINCT policy_id) AS bad_policy_cnt,
    SUM(claim_paid) AS total_bad_loss,
    ROUND(SUM(claim_paid) * 1.0 / SUM(SUM(claim_paid)) OVER(), 4) AS loss_contribution_pct
FROM policy p
JOIN claim c ON p.policy_id = c.policy_id
WHERE p.loss_ratio_flag = 'Y'
GROUP BY product_code
ORDER BY total_bad_loss DESC
LIMIT 10;
GROUP BY + HAVING + ORDER BY 归因流程 坏样本归因：分组 → 组级过滤 → 排序定位 原始宽表 保单+理赔+费用 GROUP BY 产品/渠道/地区 HAVING 赔付率 > 0.7 ORDER BY DESC 归因输出示例： 产品A-渠道X：赔付率 0.85，坏样本保单 120 件，贡献度 32% → 根因定位 产品B-渠道Y：赔付率 0.72，坏样本保单 85 件，贡献度 18% → 次要根因

3.7 综合实战：坏样本归因查询模板

以下是一个完整的坏样本归因查询模板，整合了本章所有核心子句。该模板可直接用于保险精算日常分析。

-- 坏样本归因查询模板
SELECT
    a.product_code,
    a.channel_code,
    a.region_code,
    COUNT(DISTINCT a.policy_id) AS total_policies,
    SUM(a.claim_paid) AS total_loss,
    SUM(a.premium) AS total_premium,
    ROUND(SUM(a.claim_paid) / NULLIF(SUM(a.premium), 0), 4) AS loss_ratio,
    ROUND(SUM(a.claim_paid) * 1.0 / SUM(SUM(a.claim_paid)) OVER(), 4) AS loss_contribution_pct
FROM (
    SELECT
        p.policy_id,
        p.product_code,
        p.channel_code,
        p.region_code,
        p.premium,
        COALESCE(c.claim_paid, 0) AS claim_paid
    FROM policy p
    LEFT JOIN claim c ON p.policy_id = c.policy_id
    WHERE p.status = '有效'
      AND p.underwrite_date >= '2024-01-01'
) a
GROUP BY a.product_code, a.channel_code, a.region_code
HAVING COUNT(DISTINCT a.policy_id) >= 30
   AND (SUM(a.claim_paid) / NULLIF(SUM(a.premium), 0)) > 0.6
ORDER BY loss_contribution_pct DESC;
模板使用说明：
  • 子查询（FROM 子句内）： 先完成多表关联与行级过滤，生成干净的宽表。
  • GROUP BY 维度： 按产品、渠道、地区三维分组，可根据业务需要增减。
  • HAVING 条件： 要求组内保单数 ≥ 30（统计显著性），赔付率 > 60%（坏样本阈值）。
  • ORDER BY： 按损失贡献度降序排列，直接定位 Top 根因。

3.8 本章小结

本章系统回顾了 SQL 六大核心子句的语法与执行逻辑，并结合保险精算坏样本归因场景进行了实战演示。关键要点：

  • 执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。
  • WHERE 用于行级过滤，HAVING 用于组级过滤，不可混用。
  • JOIN 类型选择影响归因完整性，LEFT JOIN 常用于保留全量保单。
  • GROUP BY + HAVING + ORDER BY 是坏样本归因的核心三件套。
  • 使用 NULLIF 避免除零，使用窗口函数计算贡献度。
← 上一章 📖 返回目录 下一章 →