解法一 case when
select user_id,max(case when rnk=1 then date else 0 end) as first_buy_date, max(case when rnk=2 then date else 0 end) as second_buy_date, cnt from (select user_id,date , row_number() over(partition by user_id order by date) as rnk, count(status) 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')) as a where cnt>=2 group by user_id,cnt order by user_id
解法二
lead(property,num,default),取出某列后n行数据;第一个参数「property」标识想查询的列,「num」标识相对于当前行的第num行,第三个参数是默认值。
select * from (select user_id, min(date) over(partition by user_id ) as first_buy_name, lead(date,1) over(partition by user_id order by date) as second_buy_date, count(status) 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')) as a where cnt>=2 group by user_id order by user_id