如何利用MAX(purchase_date)获取对应list_id和item数据？两种SQL尝试均失败

我需要从表A中获取每个customer_id和category_id分组下，purchase_date最大值对应的所有list_id和item。

我尝试了以下两种SQL语句，但都无法得到正确结果：

第一种SQL #

select distinct
  customer_id,
  category_id,
  max(purchase_date) as max_date,
  list_id,
  item
from A
group by customer_id, category_id, list_id, item

第二种SQL #

select distinct
  A.customer_id,
  A.category_id,
  B.max_date,
  A.list_id,
  A.item 
from A
join (
  select distinct
    customer_id,
    category_id,
    max(purchase_date) as max_date
  from A
  group by customer_id, category_id
) B on A.customer_id = B.customer_id
    and A.category_id = B.category_id
    and A.purchase_date = B.max_date

1. 第一种SQL的问题：group by包含了list_id和item，这会将每一组customer_id, category_id, list_id, item作为独立分组计算最大日期，而非获取customer_id+category_id分组下的全局最大日期，结果不符合需求。
2. 第二种SQL的冗余点：子查询中的distinct完全多余（group by已对customer_id和category_id去重）；若数据中同一customer_id, category_id, purchase_date下存在重复的list_id和item，外层的distinct会过滤这些原始记录，导致结果缺失。

推荐使用窗口函数RANK()，可精准获取每个分组下最大purchase_date对应的所有记录：

select customer_id, category_id, purchase_date as max_date, list_id, item
from (
  select 
    *,
    RANK() over (partition by customer_id, category_id order by purchase_date desc) as rnk
  from A
) t
where rnk = 1;

逻辑说明： #

• partition by customer_id, category_id：按用户ID和分类ID分组处理数据
• order by purchase_date desc：每个分组内按购买日期降序排序
• rnk = 1：筛选出每个分组内排名第一（即日期最大）的所有记录，若同一分组内有多个相同的最大日期，所有对应记录都会被保留。

如果业务场景中每个customer_id+category_id下的最大日期唯一，也可替换为ROW_NUMBER()，但RANK()更适配存在多个最大日期记录的场景。

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