获取客户ID和第20次交易日期的MySQL - SQL查询

Question

我无法提出查询来获取客户ID列表及其第20次购买的日期。我得到了一个名为transactions的表，其中列名为customer_id和purchase_date。表中的每一行都等于一笔交易。customer_id购买日期12020-11-1922022-01-0132021-12-0532021-12-0932021-12-16我尝试这样做，并假设我必须计算customer_id被提及的次数，如

P粉724737511 · Answer

我的解决方案：

select *
from transactions t
inner join (
   select 
      customer_id,
      purchase_date,
      row_number() over (partition by customer_id order by purchase_date) R
   from transactions) x on x.purchase_date=t.purchase_date
                       and x.customer_id=t.customer_id
where x.R=20;

参见：DBFIDDLE

对于MySQL5.7，请参见：DBFIDDLE

set @r:=1;
select *
from transactions t
inner join (
   select 
      customer_id,
      purchase_date,
      @r:=@r+1 R
   from transactions) x on x.purchase_date=t.purchase_date
                       and x.customer_id=t.customer_id
where x.R=20;

P粉043295337 · Answer

您需要选择属于customer_id的交易行，并按第20行过滤结果

SELECT * FROM (
    SELECT customer_id, purchase_date, ROW_NUMBER() OVER(
        PARTITION BY customer_id
        ORDER BY purchase_date DESC
    ) AS nth
    FROM transactions
) as t WHERE nth = 20

customer_id	购买日期
1	2020-11-19
2	2022-01-01
3	2021-12-05
3	2021-12-09
3	2021-12-16