如果你的 AI Agent 能写 GROUP BY 聚合查询，但碰到「部门内排名前 3」「运行合计」「组织架构展开」就束手无策——它缺的不是语法，而是一套把高级分析需求翻译成窗口函数、CTE、层次查询和 PIVOT 的模式手册。oracle/skills中的https://github.com/oracle/skills/blob/main/db/agent/sql-patterns.md，可以提供帮助。

一、当分析需求超出 GROUP BY——DBA 需要这六类模式

通用大模型写 SELECT ... GROUP BY 没问题。但当需求是「部门内排名前 3」「组织架构展开」「行列转置」时，AI 的问题不是语法，而是不知道调用哪种 SQL 模式。Oracle 提供六类分析型模式——窗口函数、CTE、CONNECT BY、PIVOT/UNPIVOT、MERGE、MODEL——每类都暗藏生产环境踩过坑才知道的陷阱。本文逐一拆解。

二、排名三兄弟：ROW_NUMBER、RANK、DENSE_RANK

窗口函数的核心优势是「在每一行上开窗，看相邻行，但不折叠结果集」：

-- ROW_NUMBER: 无视并列，每行唯一编号 (1,2,3,4)
-- RANK: 并列同号，跳过后续编号 (1,2,2,4)
-- DENSE_RANK: 并列同号，编号连续 (1,2,2,3)
SELECT
  employee_id, last_name, department_id, salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY department_id, salary DESC;

三者区别： ROW_NUMBER 适合「每类取前 N 条，并列也只取 N 行」；RANK 适合排名榜——并列共享名次；DENSE_RANK 适合需要连续编号的分组。

Top-N 经典模式：

SELECT *
FROM (
  SELECT employee_id, last_name, department_id, salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
)
WHERE rnk <= 3
ORDER BY department_id, rnk;

这里用 RANK 而非 ROW_NUMBER 是业务考量——第 3 名有两人工资相同时，ROW_NUMBER 会随机踢掉一个，RANK 让两人都进。

三、窗口函数进阶：LAG/LEAD、运行合计与 LAST_VALUE 大坑

3.1 LAG 和 LEAD：替代自连接

SELECT
  employee_id, last_name, hire_date, salary,
  LAG(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY hire_date) AS prev_hire_salary,
  salary - LAG(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY hire_date) AS salary_diff,
  LEAD(hire_date, 1) OVER (PARTITION BY department_id ORDER BY hire_date) AS next_hire_date
FROM employees
ORDER BY department_id, hire_date;

LAG(salary, 1, 0)：列名、往前看几行、找不到时的默认值（给 0 防止 salary_diff 因 NULL 变 NULL）。无窗口函数时需自连接加复杂行偏移。

3.2 运行合计：ROWS 比 RANGE 更安全

SELECT
  employee_id, last_name, hire_date, salary,
  SUM(salary) OVER (
    PARTITION BY department_id ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  AVG(salary) OVER (
    PARTITION BY department_id ORDER BY hire_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 3行移动平均
  ) AS moving_avg_3
FROM employees
ORDER BY department_id, hire_date;

ROWS 按物理行计数，RANGE 按值的范围——当 ORDER BY 列有重复值时，RANGE 会把同值行全纳入窗口。运行合计场景用 ROWS 更安全。

3.3 🔴 LAST_VALUE 陷阱：窗口函数最隐蔽的 bug

默认窗口框架与直觉严重不符：

SELECT
  employee_id, last_name, department_id, salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_max_salary,
  LAST_VALUE(salary)  OVER (
    PARTITION BY department_id ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- 必须加！
  ) AS dept_min_salary
FROM employees
ORDER BY department_id, salary DESC;

原因： 窗口函数默认框架 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。对于 LAST_VALUE，这意味着只看到第一行到当前行——当前行就是窗口最后一行，所以永远返回当前行自己的值。必须显式扩展到整组范围。

四、数据分桶：NTILE、PERCENT_RANK、CUME_DIST

SELECT
  employee_id, last_name, salary,
  NTILE(4)       OVER (ORDER BY salary) AS salary_quartile,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,       -- (rank-1)/(total-1)
  CUME_DIST()    OVER (ORDER BY salary) AS cumulative_dist  -- 累计分布
FROM employees
ORDER BY salary;
函数 含义 典型用途
NTILE(N) 均匀分 N 桶 客户等级划分、A/B/C 商品分类
PERCENT_RANK() 相对排名百分比 「该员工工资超过了公司百分之多少的人」
CUME_DIST() 累计分布比例 「工资不超过这个数的员工占比」

五、CTE 公共表表达式：复杂查询的积木

CTE 用 WITH 定义命名子查询，可多次引用，解决子查询反复嵌套的问题。

5.1 基础 CTE

WITH dept_payroll AS (
  SELECT department_id, SUM(salary) AS total_salary,
         COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM   employees GROUP BY department_id
)
SELECT d.department_name, dp.total_salary, dp.headcount, dp.avg_salary,
       dp.total_salary / SUM(dp.total_salary) OVER () AS pct_of_company_payroll
FROM   dept_payroll dp
JOIN   departments d ON dp.department_id = d.department_id
ORDER BY dp.total_salary DESC;

5.2 链式 CTE：多步数据加工

WITH
dept_stats AS (
  SELECT department_id, AVG(salary) AS avg_sal, MAX(salary) AS max_sal
  FROM   employees GROUP BY department_id
),
above_avg AS (
  SELECT e.employee_id, e.last_name, e.department_id, e.salary, ds.avg_sal AS dept_avg
  FROM   employees e
  JOIN   dept_stats ds ON e.department_id = ds.department_id
  WHERE  e.salary > ds.avg_sal
),
final_result AS (
  SELECT aa.last_name, d.department_name, aa.salary, aa.dept_avg,
         ROUND((aa.salary - aa.dept_avg) / aa.dept_avg * 100, 1) AS pct_above_avg
  FROM   above_avg aa
  JOIN   departments d ON aa.department_id = d.department_id
)
SELECT * FROM final_result ORDER BY pct_above_avg DESC;

链式 CTE 的价值：每一步是独立的逻辑单元，调试时可单独执行验证——嵌套子查询没有这个便利。

5.3 递归 CTE（11gR2+）

WITH org_chart (employee_id, manager_id, last_name, lvl, path) AS (
  SELECT employee_id, manager_id, last_name, 1,
         CAST(last_name AS VARCHAR2(4000)) AS path
  FROM   employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.last_name, oc.lvl + 1,
         oc.path || ' > ' || e.last_name
  FROM   employees e
  JOIN   org_chart oc ON e.manager_id = oc.employee_id
)
SEARCH DEPTH FIRST BY last_name SET order_seq
CYCLE employee_id SET is_cycle TO '1' DEFAULT '0'
SELECT lvl, LPAD(' ', (lvl-1)*4) || last_name AS org_chart, path
FROM   org_chart WHERE is_cycle = '0' ORDER BY order_seq;

SEARCH DEPTH FIRST 控制遍历顺序，CYCLE 防止数据闭环导致无限递归。

5.4 MATERIALIZE 提示：防止重复执行

WITH expensive_subquery AS (
  SELECT /*+ MATERIALIZE */ department_id, complex_calculation(salary) AS result
  FROM   employees
)
SELECT * FROM expensive_subquery e1
JOIN   expensive_subquery e2 ON e1.department_id != e2.department_id;

CTE 默认不保证只执行一次——Oracle 优化器可能将其内联到每个引用处。涉及复杂函数或大表扫描时，加 MATERIALIZE 用临时空间换执行时间。

六、CONNECT BY 层次查询与 ORDER SIBLINGS BY 陷阱

CONNECT BY 是 Oracle 原生层次查询语法，比递归 CTE 更早出现，语法极其简洁：

SELECT
  LEVEL,
  LPAD(' ', (LEVEL-1)*4) || last_name AS org_chart,
  employee_id, manager_id,
  SYS_CONNECT_BY_PATH(last_name, ' / ') AS full_path
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;     -- 关键：逐层排序，不破坏树结构

关键伪列和函数

特性 说明
LEVEL 当前深度（1 = 根）
CONNECT_BY_ROOT expr 当前分支根节点的值
CONNECT_BY_ISLEAF 1 = 叶子节点，0 = 有子节点
CONNECT_BY_ISCYCLE 1 = 检测到循环（需 NOCYCLE）
SYS_CONNECT_BY_PATH(col, delim) 从根到当前行的路径 
-- 管理者 101 的所有下属，每行带根管理者名
SELECT LEVEL, employee_id, last_name,
       CONNECT_BY_ROOT last_name AS root_manager,
       CONNECT_BY_ISLEAF AS is_leaf,
       SYS_CONNECT_BY_PATH(last_name, ' > ') AS path
FROM   employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;

🔴 ORDER SIBLINGS BY vs ORDER BY

这是 CONNECT BY 查询中最隐蔽的陷阱：

-- ❌ ORDER BY 把树拍平后重排——层次结构全部撕裂
-- ✅ ORDER SIBLINGS BY 在遍历时逐层排序——树结构完整保留

ORDER BY 在 CONNECT BY 遍历完后对整棵树排序——父子关系被摧毁。ORDER SIBLINGS BY 在遍历过程中逐层排序——树结构完好。

循环检测与行生成

-- 找循环引用
SELECT employee_id, last_name, manager_id
FROM   employees
WHERE  CONNECT_BY_ISCYCLE = 1
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

-- 生成日期序列（无需源表）
SELECT TRUNC(SYSDATE, 'YEAR') + LEVEL - 1 AS calendar_date
FROM   dual
CONNECT BY LEVEL <= 365;

NOCYCLE 防 ORA-01436。CONNECT BY LEVEL 用 DUAL 表生成序列以替代临时表。

七、PIVOT / UNPIVOT：行列互转与解析时限制

7.1 静态 PIVOT：行转列

SELECT *
FROM (
  SELECT department_id, job_id, salary FROM employees
)
PIVOT (
  SUM(salary) AS total_sal, COUNT(*) AS headcount
  FOR job_id IN (
    'IT_PROG' AS it_prog, 'SA_REP' AS sales_rep,
    'ST_CLERK' AS stock_clerk, 'MK_MAN' AS mkt_mgr
  )
)
ORDER BY department_id;

PIVOT 替代手写 SUM(CASE WHEN ... THEN salary END) 的繁琐模式。

🔴 动态 PIVOT：IN 列表必须在解析时已知

静态 PIVOT 不会报错，但会静默忽略新值。 业务变化导致新值出现时，数据丢失无声无息。动态列必须走动态 SQL：

DECLARE
  v_cols CLOB;
  v_sql  CLOB;
BEGIN
  SELECT LISTAGG('''' || job_id || ''' AS ' || LOWER(REPLACE(job_id,'-','_')), ', ')
         WITHIN GROUP (ORDER BY job_id)
  INTO   v_cols
  FROM  (SELECT DISTINCT job_id FROM employees WHERE department_id IN (50,60,80));

  v_sql := 'SELECT * FROM ('
        || '  SELECT department_id, job_id, salary FROM employees'
        || ') PIVOT (SUM(salary) FOR job_id IN (' || v_cols || '))'
        || ' ORDER BY department_id';

  EXECUTE IMMEDIATE v_sql;
END;
/

LISTAGG + WITHIN GROUP 是构建动态 IN 列表的标准搭配。

7.2 UNPIVOT：列转行

SELECT product_id, quarter, sales_amount
FROM   quarterly_sales
UNPIVOT (
  sales_amount FOR quarter IN (
    q1_sales AS 'Q1', q2_sales AS 'Q2',
    q3_sales AS 'Q3', q4_sales AS 'Q4'
  )
)
ORDER BY product_id, quarter;

默认 EXCLUDE NULLS——值为 NULL 的行不输出。需保留 NULL 行时加 INCLUDE NULLS。

八、MERGE 合并操作与 ORA-30926 陷阱

MERGE 将 INSERT、UPDATE 和可选的 DELETE 合并为一个原子操作，对增量数据同步极为高效。

8.1 基础 MERGE（Upsert）

MERGE INTO employees tgt
USING (
  SELECT employee_id, first_name, last_name, salary, department_id, hire_date
  FROM   employees_staging
) src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
  UPDATE SET
    tgt.first_name = src.first_name, tgt.last_name = src.last_name,
    tgt.salary = src.salary, tgt.department_id = src.department_id
  WHERE tgt.salary != src.salary   -- 只更新值变化的行，减少 redo
WHEN NOT MATCHED THEN
  INSERT (employee_id, first_name, last_name, salary, department_id, hire_date)
  VALUES (src.employee_id, src.first_name, src.last_name,
          src.salary, src.department_id, src.hire_date);

WHERE tgt.salary != src.salary 不是语法需要，是性能优化——跳过未变化的行，避免产生不必要的 UNDO/REDO。

8.2 MERGE 带 DELETE

MERGE INTO employees tgt
USING employees_staging src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
  UPDATE SET tgt.salary = src.salary
  DELETE WHERE src.status = 'TERMINATED'  -- 仅作用于刚被 UPDATE 的行
WHEN NOT MATCHED THEN
  INSERT (employee_id, last_name, salary, hire_date)
  VALUES (src.employee_id, src.last_name, src.salary, src.hire_date);

注意：DELETE 只删除被 WHEN MATCHED THEN UPDATE 匹配并更新过的行，不是所有匹配行。

8.3 条件 MERGE（只插入不存在的行）

MERGE INTO order_lookup tgt
USING (SELECT 101 AS order_id, 'PENDING' AS status FROM dual) src
ON (tgt.order_id = src.order_id)
WHEN NOT MATCHED THEN
  INSERT (order_id, status, created_at) VALUES (src.order_id, src.status, SYSDATE);

无 WHEN MATCHED 分支——匹配上的行直接跳过，比 INSERT ... WHERE NOT EXISTS 更简洁。

🔴 ORA-30926：源表 ON 键重复

原因： USING 子查询中同一 ON 键出现多行 → ORA-30926: unable to get a stable set of rows。修复：

MERGE INTO employees tgt
USING (
  SELECT employee_id, MAX(first_name) AS first_name,
         MAX(last_name) AS last_name, MAX(salary) AS salary
  FROM   employees_staging
  GROUP  BY employee_id      -- 去重
) src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED THEN INSERT ...;

生产环境中 staging 表常因上游重复推送产生重复键——MERGE 前用 GROUP BY 或 ROW_NUMBER() ... WHERE rn = 1 去重是必修课。

九、MODEL 子句：SQL 里的电子表格（谨慎使用）

MODEL 允许像操作电子表格一样，通过数组索引引用和计算任意单元格的值：

SELECT year, region, sales_amount
FROM (
  SELECT 2021 AS year, 'EAST' AS region, 150000 AS sales_amount FROM dual UNION ALL
  SELECT 2022, 'EAST', 165000 FROM dual UNION ALL
  SELECT 2023, 'EAST', 180000 FROM dual UNION ALL
  SELECT 2021, 'WEST', 200000 FROM dual UNION ALL
  SELECT 2022, 'WEST', 220000 FROM dual UNION ALL
  SELECT 2023, 'WEST', 242000 FROM dual
)
MODEL
  PARTITION BY (region)
  DIMENSION BY (year)
  MEASURES (sales_amount)
  RULES (
    sales_amount[2024] = sales_amount[2023] * 1.10,
    sales_amount[2025] = sales_amount[2024] * 1.10
  )
ORDER BY region, year;

关键引用符号：col[CV()] = 当前行，col[CV()-1] = 上一行，col[ANY] = 全部行。

迭代 MODEL（复利计算）：

SELECT period, balance
FROM (SELECT 0 AS period, 10000 AS balance FROM dual)
MODEL
  DIMENSION BY (period)
  MEASURES (balance)
  RULES ITERATE (10) (
    balance[ITERATION_NUMBER + 1] = ROUND(balance[ITERATION_NUMBER] * 1.05, 2)
  )
ORDER BY period;

⚠️ 能写窗口函数就别用 MODEL。 执行计划不透明、性能难预测、维护成本高。只在预算分摊、矩阵运算等窗口函数无法表达的场景才考虑。

十、这些模式不解决什么

不适用的场景：

  • 日常运维诊断： 锁阻塞、慢查询定位、等待事件分析 → nl-to-sql-patterns.md
  • 性能调优： 索引建议、执行计划分析 → sql-tuning.md
  • 跨数据库： CONNECT BY、MODEL、MERGE DELETE 均为 Oracle 专有

使用注意事项：

  • 窗口函数在 WHERE/GROUP BY/HAVING 之后、ORDER BY 之前求值——不能在 WHERE 中直接引用窗口函数结果（需子查询或 CTE）
  • CONNECT BY 必须用 NOCYCLE 防 ORA-01436
  • PIVOT IN 列表解析时确定——需动态 SQL
  • MERGE 的 USING 必须保证 ON 键唯一，否则 ORA-30926

十一、总结速查表

需求 推荐模式 🔴 关键陷阱
分组排名 / Top-N ROW_NUMBER / RANK / DENSE_RANK OVER ROW_NUMBER 随机踢掉并列行
前后行对比 LAG / LEAD OVER 注意 PARTITION BY 边界——跨组不比较
运行合计 SUM ... OVER (ORDER BY ... ROWS) RANGE 把同值行全纳入窗口
取组内首/尾值 FIRST_VALUE / LAST_VALUE OVER 🔴 LAST_VALUE 必加 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
数据分桶 NTILE / PERCENT_RANK / CUME_DIST NTILE 桶大小不绝对均匀
多步数据加工 链式 CTE（WITH ... AS） 大 CTE 可能被内联多次——加 MATERIALIZE
层次展开 CONNECT BY 🔴 用 ORDER SIBLINGS BY 而非 ORDER BY；循环加 NOCYCLE
行转列 PIVOT 🔴 IN 列表必须解析时已知——动态列需动态 SQL
列转行 UNPIVOT 默认排除 NULL 行
增量同步 MERGE 🔴 ON 键在源表必须唯一 → ORA-30926
电子表格计算 MODEL 能写窗口函数就别用——性能黑洞
生成序列 CONNECT BY LEVEL 控制 LEVEL 上限防笛卡尔积

📎 源文件：https://github.com/oracle/skills/blob/main/db/agent/sql-patterns.md

# oracle # sql # 数据库
Logo
龙虾开发者社区

小龙虾开发者社区是 CSDN 旗下专注 OpenClaw 生态的官方阵地，聚焦技能开发、插件实践与部署教程，为开发者提供可直接落地的方案、工具与交流平台，助力高效构建与落地 AI 应用

更多推荐

cover

Agent方向面试，我面了30多场之后的真心话

avatar 龙虾开发者社区
cover

程序员&安全小白必收藏！195个技能点拆解渗透测试全流程的开源技能库

avatar 龙虾开发者社区
cover

用 Playwright 开启 Claude Code 自动浏览器测试

avatar 龙虾开发者社区