多级佣金计算的典型数据结构是树形结构，需用递归CTE展开代理链并结合窗口函数分层加权计算，避免自连接爆炸，注意层级限制与递归中禁用聚合。
什么是多级佣金计算的典型数据结构
多级代理商关系本质是树形结构，但 SQL 里没有原生树遍历，得靠自连接或递归 CTE 搭配窗口函数。关键在于：每条销售记录要能回溯到所有上级代理（比如 A→B→C→订单），并按层级加权计算佣金（如一级 10%、二级 5%、三级 2%）。直接用 JOIN 多次自连接会爆炸式膨胀行数，而 WITH RECURSIVE + ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 才可控。
用递归 CTE 展开代理链再套窗口函数
先用递归 CTE 把每个订单的完整代理路径拉平成多行（一行代表一个上级代理+对应层级），再用窗口函数算累计权重或分层聚合。注意两个坑：MAX_RECURSION_DEPTH 要调够（MySQL 8.0 默认 1000，PostgreSQL 无硬限但需防环）；递归中不能用 GROUP BY 或聚合函数，否则报错 recursive reference in aggregate function。
示例（PostgreSQL）：
WITH RECURSIVE agent_path AS (
-- 基础层：订单直接归属的代理
SELECT order_id, agent_id AS current_agent, parent_id, 1 AS level
FROM orders o
JOIN agents a ON o.agent_id = a.id
UNION ALL
-- 递归层：逐级向上找 parent
SELECT ap.order_id, a.parent_id, a.parent_id, ap.level + 1
FROM agent_path ap
JOIN agents a ON ap.current_agent = a.id
WHERE a.parent_id IS NOT NULL AND ap.level < 10
)
SELECT
order_id,
current_agent,
level,
CASE level
WHEN 1 THEN 0.10
WHEN 2 THEN 0.05
WHEN 3 THEN 0.02
ELSE 0.005
END * order_amount AS commission
FROM agent_path ap
JOIN orders o USING (order_id);为什么不能只用 LAG() 或 LEAD()
LAG() 和 LEAD() 只能取同一分区内的前/后几行，解决不了“一个订单要穿透多个代理层级”这个跨行深度问题。它们适合做相邻层级差值（比如二级佣金减一级佣金），但无法生成代理链本身。强行用会导致：结果只有一级代理信息、NULL 大量出现、层级数固定死（写死 LAG(..., 3) 就只能算三级，没法动态适配）。
- 误用场景：把所有代理按
parent_id排序后对每个订单LAG(agent_id, 1)—— 实际得到的是“上一条订单的代理”，不是“本订单的上级代理” - 正确思路：必须先用递归或自连接把代理关系展开为宽表形态，再在该结果集上用窗口函数
性能与环检测的关键细节
真实业务中代理关系可能有环（A→B→C→A），递归不加限制会无限循环。MySQL 用 cte_max_recursion_depth 参数兜底，PostgreSQL 必须手动加路径数组去重：
WITH RECURSIVE agent_path AS (
SELECT order_id, agent_id, ARRAY[agent_id] AS path
FROM orders
UNION ALL
SELECT ap.order_id, a.parent_id, ap.path || a.parent_id
FROM agent_path ap
JOIN agents a ON ap.agent_id = a.id
WHERE a.parent_id IS NOT NULL
AND NOT a.parent_id = ANY(ap.path) -- 防环
)另外，agent_id 和 parent_id 字段必须建索引，否则递归扫描全表极慢；层级超过 7 级时，考虑预计算并缓存到 agent_ancestors 辅助表，避免每次查询都跑递归。
