使用RANK函数获取客户最后一笔采购时返回行数异常的问题排查

我尝试获取每个customer_id的最后一笔采购记录，共有3个客户，预期返回3行数据，但实际返回行数更多。

原SQL代码 #

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';


CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Jerry', 'Torchiano' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME) AS
SELECT 100, 'Black Shoes' FROM DUAL UNION ALL
SELECT 101, 'Brown Shoes' FROM DUAL UNION ALL
SELECT 102, 'White Shoes' FROM DUAL;

CREATE TABLE purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 100, 1, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-11 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2,101,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 3,102,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL UNION ALL
SELECT 3,102, 4,TIMESTAMP '2022-10-10 17:00:00' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual
CONNECT BY LEVEL <= 5;

with cte as
     (select 
        CUSTOMER_ID, 
        PRODUCT_ID, 
        QUANTITY, 
        PURCHASE_DATE,
        rank() over (partition by customer_id order by purchase_date desc) rnk
      from purchases
     )
SELECT p.customer_id,
       c.first_name,
       c.last_name,
       p.product_id,
       i.product_name,
       p.quantity, 
       p.purchase_date   
from cte p   
JOIN customers c ON c.customer_id = p.customer_id
JOIN items i ON i.product_id = p.product_id 
where rnk = 1;

问题原因 #

当同一客户存在多条采购记录的PURCHASE_DATE相同时，RANK()函数会为这些记录分配相同的排名1，导致返回多行结果。

修复方案 #

• 方案一：替换为ROW_NUMBER()函数
  ROW_NUMBER()会为同一分组内的记录分配唯一的排名，即使采购时间相同，也只会返回一条记录。如果需要指定相同时间下的筛选规则，可在ORDER BY后添加额外字段（如PRODUCT_ID）来确定优先级。

• 方案二：调整PARTITION BY子句
  在PARTITION BY中增加PRODUCT_ID等字段，按客户+商品分组取最晚记录。这种方式适用于需要获取每个客户每个商品最后一笔记录的场景，但如果要严格每个客户仅返回一条，建议使用方案一。

修复后的SQL示例（方案一） #

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Jerry', 'Torchiano' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME) AS
SELECT 100, 'Black Shoes' FROM DUAL UNION ALL
SELECT 101, 'Brown Shoes' FROM DUAL UNION ALL
SELECT 102, 'White Shoes' FROM DUAL;

CREATE TABLE purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 100, 1, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-11 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2,101,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 3,102,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL UNION ALL
SELECT 3,102, 4,TIMESTAMP '2022-10-10 17:00:00' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual
CONNECT BY LEVEL <= 5;

with cte as
     (select 
        CUSTOMER_ID, 
        PRODUCT_ID, 
        QUANTITY, 
        PURCHASE_DATE,
        row_number() over (partition by customer_id order by purchase_date desc) rnk
      from purchases
     )
SELECT p.customer_id,
       c.first_name,
       c.last_name,
       p.product_id,
       i.product_name,
       p.quantity, 
       p.purchase_date   
from cte p   
JOIN customers c ON c.customer_id = p.customer_id
JOIN items i ON i.product_id = p.product_id 
where rnk = 1;

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