取得客戶ID和第20次交易日期的MySQL - SQL查詢

Question

我無法提出查詢來取得客戶 ID 清單及其第 20 次購買的日期。

我拿到了一個名為 transactions 的表，其中欄位名為 customer_id 和purchase_date。表中的每一行都等於一筆交易。

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

我嘗試這樣做，並假設我必須計算 customer_id 被提及的次數，如果計數等於 20，則傳回 id 編號。

SELECT customer_id, MAX(purchase_date)
FROM transactions
(
     SELECT customer_id,
     FROM transactions
     GROUP BY customer_id
     HAVING COUNT (customer_id) =20
)

如何讓它傳回 customer_id 清單以及第 20 次交易的日期？

Answer 1

我的解決方案：

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;

Answer 2

您需要選擇屬於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