ROW_NUMBER()可高效替代自连接查每个用户最新订单，需PARTITION BY user_id并ORDER BY created_at DESC, id DESC；LAG/LEAD适用于相邻行对比，须设默认值且明确NULLS处理；COUNT() OVER配合CASE可避免多层子查询，但需确保窗口定义一致。
ROW_NUMBER() 替代“每个用户最新订单”类自连接
Oracle 19c 完全支持 ROW_NUMBER()，它能直接干掉那种写成 NOT EXISTS 或 LEFT JOIN ... IS NULL 的自连接查最新记录逻辑。关键不是少写几行，而是避免全表笛卡尔积扫描。
常见错误现象：SELECT * FROM orders o1 WHERE NOT EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = o1.user_id AND o2.created_at > o1.created_at)——在 Oracle 中一旦数据量过万，执行计划里立刻冒出 Nested Loops + Filter，CPU 和逻辑读飙升。
- 必须显式写
PARTITION BY user_id，否则变成全局编号，毫无业务意义 -
ORDER BY created_at DESC, id DESC是硬性要求：Oracle 对同秒时间不保证物理顺序，补id才能确保每次结果一致 - 如果已有
(user_id, created_at)复合索引，Oracle 19c 的 CBO 很可能跳过排序步骤，直接走索引扫描
正确写法：
SELECT user_id, order_id, amount, created_at
FROM (
SELECT user_id, order_id, amount, created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) AS rn
FROM orders
)
WHERE rn = 1;LAG()/LEAD() 替代“相邻行对比”的 ID 关联自连接
别再写 JOIN orders o2 ON o2.id = o1.id + 1——Oracle 里 id 几乎从不连续，这种写法上线后第一周就出错。
用 LAG() 直接取上一行值，语义清晰、性能稳定，但要注意 Oracle 对 NULL 的默认处理比 PostgreSQL 更保守。
-
LAG(login_date, 1, DATE '1970-01-01')第三个参数必须设默认值，否则首行返回NULL，后续减法或比较容易报错 -
ORDER BY login_date必须存在且确定：如果login_date有NULL，Oracle 默认按NULLS LAST排，但你的业务逻辑是否允许？要主动写明NULLS FIRST或NULLS LAST - 时间字段精度不够（如只到秒）时，仅靠
ORDER BY login_date不够，得补上id或log_seq等唯一列
示例（计算用户连续登录间隔）：
SELECT user_id, login_date,
login_date - LAG(login_date, 1, DATE '1970-01-01')
OVER (PARTITION BY user_id ORDER BY login_date NULLS LAST) AS days_since_last
FROM user_logins;COUNT() OVER + CASE 替代多层子查询统计
想查“每个部门里薪资高于本部门平均值的员工数”，有人会嵌套两层子查询再关联，Oracle 执行计划里能看到多次 TABLE ACCESS FULL 扫描。
窗口函数一步到位，但最容易翻车的是漏掉 CASE WHEN ——写成 COUNT(*) OVER (PARTITION BY dept_id) 就等于查总人数，完全偏离需求。
- 必须用
COUNT(CASE WHEN salary > AVG(salary) OVER (PARTITION BY dept_id) THEN 1 END) OVER (PARTITION BY dept_id)这种写法 -
AVG(salary) OVER (PARTITION BY dept_id)要和外层COUNT共享同一PARTITION BY，否则分组错位 - Oracle 19c 对窗口函数内嵌窗口函数（即“窗口中套窗口”）支持良好，但旧版本（如 12c）可能报错，需验证
为什么换完反而变慢？Oracle 19c 的两个典型陷阱
窗口函数不是性能银弹。在 Oracle 19c 上，换完更慢往往卡在这两点：
- 写了
ORDER BY created_at却漏了PARTITION BY user_id→ Oracle 强制全表排序，而原自连接可能只走INDEX RANGE SCAN+NESTED LOOPS - WHERE 条件写在最外层（如
WHERE dept_id = 101），但窗口函数子查询没下推过滤，导致仍对全表计算窗口值；此时不如保留自连接+索引驱动
真正要盯的是执行计划里的 WINDOW SORT 操作是否触发磁盘临时段（TempSpc 列非零），以及 Sort 成本是否远超原 Nested Loops 的 Cost。别只看代码短了就以为快了。
