题解 | #牛客的课程订单分析(五)#

问题描述：请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序，以上例子查询结果如下:

方案1：采用窗口函数ROW_NUMBER为user_id的订单排序，COUNT函数计算每个user_id的订单数量
第一步：添加窗口函数ROW_NUMBER和COUNT

SELECT *,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date ASC) AS row_rank,
     COUNT(*) OVER (PARTITION BY user_id) AS cnt
FROM order_info
WHERE date>'2025-10-15'
    AND status = 'completed'
    AND product_name IN ('C++','Java','Python')

第二步：将第一步获得的数据按要求过滤（将第一步获得的结果作为临时表t_f

SELECT t_f.user_id,t_f.date,t_f.cnt
FROM t_f
 WHERE t_f.cnt >= 2 AND t_f.row_rank <= 2

第三步：将第二步获得的数据集命名为临时表t

WITH t_f AS (
         SELECT *,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date ASC) AS row_rank,
        COUNT(*) OVER (PARTITION BY user_id) AS cnt
        FROM order_info
        WHERE date>'2025-10-15'
         AND status = 'completed'
         AND product_name IN ('C++','Java','Python')
        ),
       t AS (
             SELECT t_f.user_id,t_f.date,t_f.cnt
         FROM t_f
         WHERE t_f.cnt >= 2 AND t_f.row_rank <= 2
            )

SELECT t.user_id,MIN(t.date) AS first_buy,MAX(t.date) AS second_buy_date,t.cnt
FROM  t
GROUP BY t.user_id
ORDER BY t.user_id ASC;

这里采用了WITH语句整理了子查询语句，使代码可读性增强。
关于with子句的相关知识可以参考https://www.modb.pro/db/25773

另外贴一个采用lead窗口函数的回答
https://blog.nowcoder.net/n/5f3463c52f1e42ce976f65fe700d1f6d?f=comment

lead窗口函数:计算同一结果集中当前行的后续行结果
语法：LEAD(expr [, N[, default]]) [null_treatment] over_clause
参数说明：
expr：参考行
N:正整数，1表示后续一行，2表示后续两行，0表示当前行
default：当数据集无可反馈行时返回的缺省值
OVER_clause:按……分组（和其他窗口函数功能一样）

例子：
例子的数据集，创建一个try的表，插入几条数据

DROP TABLE IF EXISTS try;
CREATE TABLE try(
id INT NOT NULL auto_increment,
name_1 VARCHAR(255) NOT NULL,
date_1 DATE NOT NULL,
PRIMARY KEY (id))

INSERT INTO try (name_1,date_1) VALUES('a','2021-1-2'),('a','2021-1-3'),('a','2021-1-4');
select * from try;

下面试验一下

SELECT *,LEAD(date_1,1,'此处没有数据') OVER (PARTITION BY name_1) as lead_date
FROM try;

有兴趣的同学可以令N等于其他数
另外:LAG()函数与LEAD()函数相反，他是取数据集前N个数据，语法跟LEAD（）相同。