如何编写SQL查询获取每位用户成为Power User的日期（第10次购买日）

嗨，这个需求很清晰——找出每位用户第10次购买商品的日期（也就是他们成为Power User的那天），我们可以用窗口函数来高效实现，以下是具体方案：

核心思路 #

我们需要给每个用户的购买行为按时间顺序编号，然后筛选出编号为10的记录，对应的日期就是目标日期。

通用SQL实现（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

SELECT Userid, Date AS PowerUserDate
FROM (
    SELECT 
        Userid,
        Date,
        -- 按用户分组，对购买日期升序排序，给每笔购买分配序号
        ROW_NUMBER() OVER (PARTITION BY Userid ORDER BY Date ASC) AS PurchaseNumber
    FROM Transactions
) AS UserPurchaseRankings
-- 筛选出第10次购买的记录
WHERE PurchaseNumber = 10;

代码解释 #

1. 子查询UserPurchaseRankings：通过PARTITION BY Userid把数据按用户分组，再用ORDER BY Date ASC对每个用户的购买记录按时间从早到晚排序，最后用ROW_NUMBER()给每笔购买分配一个递增的序号（最早的购买是1，依次到第N次）。
2. 外层查询：直接筛选出序号为10的记录，这些记录就是每个用户第10次购买的日期，也就是他们成为Power User的日期。

简化写法（支持QUALIFY子句的数据库，如BigQuery、Snowflake） #

如果你的数据库支持QUALIFY子句，可以省去子查询，让代码更简洁：

SELECT Userid, Date AS PowerUserDate
FROM Transactions
QUALIFY ROW_NUMBER() OVER (PARTITION BY Userid ORDER BY Date ASC) = 10;

注意事项 #

• 如果用户的购买次数不足10次，上述查询不会返回该用户的记录，这符合需求（只有购买至少10件的用户才是Power User）。
• 如果存在同一用户在同一时间有多笔购买的情况，ROW_NUMBER()会随机给这些记录分配序号，但因为我们只关心第10次的日期，所以不影响结果（只要是第10次对应的日期即可）。

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