Oracle SQL 复杂查询案例

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

复杂查询案例汇总[1]：

2. 累计计算

2.1 累计求和

SELECT sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS cum_total
FROM sales
ORDER BY sale_date;

2.2 移动平均

SELECT sale_date, amount,
  AVG(amount) OVER (
    ORDER BY sale_date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma7
FROM sales;

2.3 同比环比

SELECT sale_date, amount,
  LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_month,
  (amount - LAG(amount, 1) OVER (ORDER BY sale_date)) / 
   LAG(amount, 1) OVER (ORDER BY sale_date) AS growth_rate
FROM monthly_sales;

3. 排名

3.1 Top N

-- 每部门 Top 3
SELECT * FROM (
  SELECT e.*,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees e
)
WHERE rn <= 3;

3.2 排名

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

3.3 百分比

SELECT name, salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
  CUME_DIST() OVER (ORDER BY salary) AS cume_dist,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

详细见：Oracle 高级分析函数。

4. 树形查询

4.1 层级

SELECT employee_id, name, manager_id, LEVEL,
  SYS_CONNECT_BY_PATH(name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY name;

4.2 递归 CTE

WITH org_chart(id, name, mgr_id, lvl) AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  SELECT e.id, e.name, e.manager_id, oc.lvl + 1
  FROM employees e, org_chart oc
  WHERE e.manager_id = oc.id
)
SELECT * FROM org_chart;

详细见：Oracle 层次查询、Oracle CTE 与递归查询。

5. 行列转换

5.1 PIVOT

SELECT * FROM (
  SELECT dept_id, job_id, salary FROM employees
)
PIVOT (
  SUM(salary) FOR job_id IN ('IT_PROG' AS it, 'SA_REP' AS sales)
);

5.2 UNPIVOT

SELECT * FROM sales_pivot
UNPIVOT (
  amount FOR quarter IN (q1, q2, q3, q4)
);

详细见：Oracle SQL 行列转换详解。

6. 去重

6.1 ROWID

DELETE FROM employees WHERE ROWID IN (
  SELECT rid FROM (
    SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn
    FROM employees
  ) WHERE rn > 1
);

6.2 DISTINCT

SELECT DISTINCT dept_id FROM employees;

6.3 GROUP BY

SELECT dept_id FROM employees GROUP BY dept_id;

7. 连续值

7.1 连续 N 天

SELECT user_id, login_date
FROM (
  SELECT user_id, login_date,
    login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
  FROM logins
)
GROUP BY user_id, grp
HAVING COUNT(*) >= 7;

7.2 Islands

-- 连续区间
SELECT MIN(val) AS start_val, MAX(val) AS end_val
FROM (
  SELECT val, 
    val - ROW_NUMBER() OVER (ORDER BY val) AS grp
  FROM numbers
)
GROUP BY grp
ORDER BY start_val;

8. 中位数

8.1 PERCENTILE_CONT

SELECT dept_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees
GROUP BY dept_id;

8.2 分析函数

SELECT dept_id, salary,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) 
    OVER (PARTITION BY dept_id) AS median
FROM employees;

9. 自连接

9.1 同表比较

-- 比同部门平均高
SELECT e.name, e.salary, e.dept_id, d.avg_sal
FROM employees e,
  (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) d
WHERE e.dept_id = d.dept_id AND e.salary > d.avg_sal;

9.2 间隔

-- 间隔 N 行
SELECT a.id, b.id AS next_id
FROM employees a, employees b
WHERE b.id = (SELECT MIN(id) FROM employees WHERE id > a.id);

10. EXISTS

10.1 存在

SELECT d.id, d.name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

10.2 不存在

SELECT d.id, d.name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

详细见：Oracle 子查询与 EXISTS。

11. 跨行计算

11.1 前后值

SELECT sale_date, amount,
  LAG(amount, 1) OVER (ORDER BY sale_date) AS prev,
  LEAD(amount, 1) OVER (ORDER BY sale_date) AS next,
  amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS diff
FROM sales;

11.2 第一最后

SELECT dept_id,
  FIRST_VALUE(name) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS top_earner,
  LAST_VALUE(name) OVER (PARTITION BY dept_id ORDER BY salary DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS low_earner
FROM employees;

12. 多维分析

12.1 ROLLUP

SELECT dept_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP (dept_id, job_id);
-- 小计 + 总计

12.2 CUBE

SELECT dept_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE (dept_id, job_id);
-- 所有组合

12.3 GROUPING SETS

SELECT dept_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((dept_id), (job_id), ());
-- 指定组合

13. CASE

13.1 条件聚合

SELECT dept_id,
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY dept_id;

13.2 分类

SELECT name, salary,
  CASE 
    WHEN salary >= 10000 THEN 'High'
    WHEN salary >= 5000 THEN 'Medium'
    ELSE 'Low'
  END AS level
FROM employees;

14. MERGE

MERGE INTO target t
USING source s ON (t.id = s.id)
WHEN MATCHED THEN 
  UPDATE SET t.name = s.name
  DELETE WHERE t.status = 'INACTIVE'
WHEN NOT MATCHED THEN 
  INSERT (id, name) VALUES (s.id, s.name);

详细见：Oracle MERGE 语句详解。

15. 复杂示例

15.1 销售排行

WITH dept_sales AS (
  SELECT dept_id, SUM(amount) AS total
  FROM sales
  WHERE sale_date >= TRUNC(SYSDATE, 'YYYY')
  GROUP BY dept_id
)
SELECT d.dept_name, ds.total,
  RANK() OVER (ORDER BY ds.total DESC) AS rank
FROM dept_sales ds
  JOIN departments d ON ds.dept_id = d.id
ORDER BY rank;

15.2 同比

SELECT 
  EXTRACT(YEAR FROM sale_date) AS yr,
  EXTRACT(MONTH FROM sale_date) AS mon,
  SUM(amount) AS total,
  LAG(SUM(amount), 12) OVER (ORDER BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)) AS last_year,
  ROUND((SUM(amount) - LAG(SUM(amount), 12) OVER (ORDER BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date))) / 
        LAG(SUM(amount), 12) OVER (ORDER BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)) * 100, 2) AS yoy_pct
FROM sales
GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
ORDER BY yr, mon;

15.3 用户留存

WITH first_login AS (
  SELECT user_id, MIN(login_date) AS first_date
  FROM logins
  GROUP BY user_id
),
retention AS (
  SELECT 
    f.first_date,
    COUNT(DISTINCT f.user_id) AS day_0,
    COUNT(DISTINCT CASE WHEN l.login_date = f.first_date + 1 THEN f.user_id END) AS day_1,
    COUNT(DISTINCT CASE WHEN l.login_date = f.first_date + 7 THEN f.user_id END) AS day_7,
    COUNT(DISTINCT CASE WHEN l.login_date = f.first_date + 30 THEN f.user_id END) AS day_30
  FROM first_login f
    LEFT JOIN logins l ON f.user_id = l.user_id
  GROUP BY f.first_date
)
SELECT * FROM retention ORDER BY first_date;

16. 性能

16.1 索引

- 高选择性列
- 覆盖
- 复合

详细见：Oracle 索引优化策略详解。

16.2 执行计划

EXPLAIN PLAN FOR ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));

详细见：Oracle 执行计划详解。

16.3 统计

EXEC DBMS_STATS.GATHER_TABLE_STATS(...);

17. 最佳实践

分析函数：复杂
CTE：清晰
FETCH：分页
MERGE：UPSERT
PIVOT：转换
树形查询：层级
索引：性能
执行计划：验证
测试：完整
文档化：说明

18. 参考资料

[1] Oracle Database SQL Tuning Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/