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

问题：查询分组后第一次购买和第二次购买的订单信息

方法一
select 中用 max/min(case when rank=1 then date...)
配合
表中 row_number() over(partition by ... order by date

select
 a.user_id,
 max(case when a.rank_no=1 then a.date else 0 end) as first_buy_date,
 max(case when a.rank_no=2 then a.date else 0 end) as second_buy_date,
 a.cnt
from
    (select
     user_id,
     date,
     row_number() over(partition by user_id order by date) as rank_no,
     count(*) over(partition by user_id) as cnt
    from order_info
    where date >= '2025-10-16'
      and status = 'completed'
      and product_name in('C++','Java','Python')
    ) a
where a.rank_no<=2 and a.cnt>=2
group by a.user_id,a.cnt
order by a.user_id ;

方法二
lag(field, num, defaultvalue)---前面第num行的数据
lead(field, num, defaultvalue)---后面第num行的数据
后面跟 over(partition by a1 order by a2)
主要作用：
同个查询取同一字段指定前后第X行。可以作为独立的列，可代替表的自联接，效率更高。

select
 a.*
from
    (select
     user_id,
     min(date) over(partition by user_id) as first_buy_date,
     lead(date,1,0) over(partition by user_id order by date) as second_buy_date,
     count(*) over(partition by user_id) as cnt
    from order_info
    where date>='2025-10-16'
      and status='completed'
      and product_name in('C++','Java','Python')
    ) a
where a.cnt>=2
group by a.user_id
order by a.user_id ;

*where & having 的对比 *

用count聚合函数举例
select c
from ( count() over(partition by a) as c)
where c>=2
group by a
=
select count()
from
group by a
having count()>=2