窗口函数和group by

窗口函数和group by的区别

group by分组之后，统计默认只会计算一行记录
窗口函数 分组之后，有多少列显示多少行记录

思路

1.首先过滤筛选不符合的记录，然后根据用户分组，计算出每组用户的排名和统计总数
select
    user_id,
    date,
    dense_rank() over (partition by user_id order by date) as rank_no,
    count(*) over (partition by user_id) as cnt 
from
    order_info
where
    product_name in ('Java','C++','Python')
and
    status = 'completed'
and
    date > '2025-10-15'

2.只取每组排名前2的
select
    user_id,
    min(t.date),
    max(t.date),
    t.cnt
from 
(
select
    user_id,
    date,
    dense_rank() over (partition by user_id order by date) as rank_no,
    count(*) over (partition by user_id) as cnt 
from
    order_info
where
    product_name in ('Java','C++','Python')
and
    status = 'completed'
and
    date > '2025-10-15'
) t
where 
    t.rank_no < 3
and
    t.cnt >= 2
group by user_id
order by 
    user_id;