Oracle SQL中Partition By用法及常见问题排查与解决

假设你的表结构如下（若实际结构不同，替换对应字段即可）：

CREATE TABLE PERSON_ADDR (
    PERSON_ID NUMBER,
    TYPE VARCHAR2(20),
    ADDR_ID NUMBER,
    CREATE_DATE DATE -- 用于判定记录新旧的时间字段
);

需求1：按PERSON_ID和TYPE统计分组记录数 #

使用COUNT()窗口函数按指定字段分区，即可得到每条记录所属分组的总条数：

SELECT 
    PERSON_ID,
    TYPE,
    ADDR_ID,
    CREATE_DATE,
    COUNT(*) OVER (PARTITION BY PERSON_ID, TYPE) AS GROUP_RECORD_COUNT
FROM PERSON_ADDR;

错误排查：如果统计结果不符合预期，大概率是分区字段写错（比如漏写TYPE或PERSON_ID），或者使用COUNT(ADDR_ID)时ADDR_ID存在NULL值，导致计数缺失。

需求2：按PERSON_ID和TYPE获取最新的ADDR_ID #

需要依赖排序字段（如CREATE_DATE）判定“最新”，提供两种常用写法：

方法1：用ROW_NUMBER()筛选单条最新记录 #

SELECT 
    PERSON_ID,
    TYPE,
    ADDR_ID AS LATEST_ADDR_ID
FROM (
    SELECT 
        PERSON_ID,
        TYPE,
        ADDR_ID,
        ROW_NUMBER() OVER (PARTITION BY PERSON_ID, TYPE ORDER BY CREATE_DATE DESC) AS RN
    FROM PERSON_ADDR
)
WHERE RN = 1;

方法2：用LAST_VALUE()保留所有行并展示最新ADDR_ID #

SELECT 
    PERSON_ID,
    TYPE,
    ADDR_ID,
    LAST_VALUE(ADDR_ID) OVER (
        PARTITION BY PERSON_ID, TYPE 
        ORDER BY CREATE_DATE DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LATEST_ADDR_ID
FROM PERSON_ADDR;

ORA-00904错误排查 #

该错误表示标识符无效，常见原因：

• 列名拼写错误：比如把PERSON_ID写成PERSONID、TYPE写成TYP，核对所有引用字段与表结构是否一致。
• 窗口函数别名直接用于WHERE子句：Oracle执行顺序中WHERE先于SELECT，此时别名尚未生成，必须用子查询或CTE包裹后再过滤。
• 使用Oracle保留字作为列名/别名：若字段名是DATE这类保留字，需用双引号包裹（如"DATE"）。

合并两个需求的完整SQL #

若需在一个查询中同时获取分组计数和最新ADDR_ID：

SELECT 
    PERSON_ID,
    TYPE,
    GROUP_RECORD_COUNT,
    LATEST_ADDR_ID
FROM (
    SELECT 
        PERSON_ID,
        TYPE,
        COUNT(*) OVER (PARTITION BY PERSON_ID, TYPE) AS GROUP_RECORD_COUNT,
        ADDR_ID AS LATEST_ADDR_ID,
        ROW_NUMBER() OVER (PARTITION BY PERSON_ID, TYPE ORDER BY CREATE_DATE DESC) AS RN
    FROM PERSON_ADDR
)
WHERE RN = 1;

内容的提问来源于stack exchange，提问作者Kinson