SQL中RANK()返回重复排名，如何获取唯一前置购买记录？

你的问题核心是同一日期购买多个产品时，RANK()会给这些记录都标记为1，导致结果重复。可以通过以下两种方式解决，确保每个购买Product X的记录只对应一条“之前最后购买”的记录：

方法1：用ROW_NUMBER()替代RANK()，添加额外排序规则 #

RANK()会给排序字段相同的记录分配相同的排名，而ROW_NUMBER()会为每个分区内的记录生成唯一序号。只需在排序时加一个额外的字段（比如随机值或产品名称），就能确保同一日期的记录只会有一个行号为1。

修改后的SQL示例（以MySQL为例） #

SELECT 
    customer_ID,
    purchase_date,    -- 购买Product X的日期
    last_product_purchase_date,
    last_product_name
FROM
    (SELECT 
         ROW_NUMBER() OVER (
             PARTITION BY t1.customer_ID, t1.purchase_date
             ORDER BY t2.purchase_date DESC, RAND()  -- 按购买日期倒序，再加随机排序确保唯一
         ) AS my_rank,
         t1.customer_ID,
         t1.purchase_date,
         t2.purchase_date AS last_product_purchase_date,
         t2.product_name AS last_product_name
     FROM 
         purchase_product_X AS t1
     LEFT JOIN 
         all_purchases AS t2 
             ON t2.customer_ID = t1.customer_ID
             AND t2.purchase_date < t1.purchase_date) T
WHERE 
    my_rank = 1;  -- 直接取行号为1的记录，无记录时会返回NULL

不同数据库的随机函数替换 #

• PostgreSQL：把RAND()换成RANDOM()
• SQL Server：把RAND()换成NEWID()
• 若需要固定规则（比如取产品名称字典序第一个），可以把RAND()换成t2.product_name ASC

方法2：先聚合获取每个客户最后购买日期，再关联产品 #

如果不需要随机选择，而是确定取最后日期的任意一个产品，可以先找到每个客户在购买X之前的最大购买日期，再关联该日期的产品：

SELECT 
    t1.customer_ID,
    t1.purchase_date,
    t2.purchase_date AS last_product_purchase_date,
    t2.product_name AS last_product_name
FROM purchase_product_X AS t1
LEFT JOIN (
    -- 先获取每个客户在对应日期前的最后购买日期
    SELECT 
        customer_ID,
        MAX(purchase_date) AS max_purchase_date
    FROM all_purchases
    GROUP BY customer_ID
) AS t_last ON t_last.customer_ID = t1.customer_ID 
             AND t_last.max_purchase_date < t1.purchase_date
-- 再关联该日期的产品，用ROW_NUMBER()取一个
LEFT JOIN all_purchases AS t2 
    ON t2.customer_ID = t1.customer_ID
    AND t2.purchase_date = t_last.max_purchase_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY t1.customer_ID, t1.purchase_date ORDER BY t2.product_name) = 1;

注意事项 #

• 原SQL中的myrank是拼写错误，需改为my_rank
• 方法1的ROW_NUMBER()会确保每个分区内只有一行my_rank=1，彻底解决重复问题
• 如果业务允许固定选择规则（比如按产品ID排序），比随机选择更稳定

内容的提问来源于stack exchange，提问作者Aldla E Aoepql