数分 Top N 必背 3 个窗口函数

面试直接用，关键字全部小写，符合大厂规范：

  1. row_number()：唯一排名，同值不同号（1,2,3,4）
  2. rank()：跳跃并列，同值同名次（1,1,3,4）
  3. dense_rank()：连续并列，同值不跳号（1,1,2,3）

总结

  1. 基础 Top N → order by + limit
  2. 分组 Top N → row_number() over(partition by ... order by ...)
  3. 并列排名 → dense_rank()
  4. 连续 Top → 日期差分组法

基础表结构统一说明

  1. 员工表 employee

    字段：emp_id 员工 id，dept_id 部门 id，emp_name 姓名，salary 薪资，entry_time 入职时间

  2. 订单表 order_info

    字段：order_id 订单 id，user_id 用户 id，order_time 下单时间，pay_amount 支付金额，goods_type 商品类型

  3. 用户登录表 user_login

    字段：user_id 用户 id，login_date 登录日期

练习平台：db<>fiddle

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create table order_info (
    order_id     bigint comment '订单id',
    user_id      bigint comment '用户id',
    order_time   varchar(20) comment '下单时间',
    pay_amount   decimal(18,2) comment '支付金额',
    goods_type   varchar(10) comment '商品类型'
);
insert into order_info values
(1001, 1, '2024-01-01 10:00:00', 100.00, '数码'),
(1002, 1, '2024-01-02 15:00:00', 200.00, '数码'),
(1003, 2, '2024-01-01 11:00:00', 50.00, '食品'),
(1004, 3, '2024-01-03 09:00:00', 300.00, '服装'),
(1005, 2, '2024-01-04 14:00:00', 150.00, '数码'),
(1006, 4, '2024-01-05 16:00:00', 80.00, '食品'),
(1007, 5, '2024-01-06 10:00:00', 500.00, '数码'),
(1008, 6, '2024-01-07 11:00:00', 120.00, '食品'),
(1009, 7, '2024-01-08 09:00:00', 600.00, '服装'),
(1010, 8, '2024-01-09 14:00:00', 30.00, '食品'),
(1011, 3, '2024-01-10 16:00:00', 200.00, '数码'),
(1012, 9, '2024-01-11 10:00:00', 400.00, '服装'),
(1013, 10, '2024-01-12 15:00:00', 90.00, '食品');

1. 全局取 top n

题目

员工表 employee
字段：emp_id 员工 id，dept_id 部门 id，emp_name 姓名，salary 薪资，entry_time 入职时间

查询员工表中薪资最高的前 6 名员工所有信息。

sql 答案

1
2
3
4
select *
from employee
order by salary desc
limit 6;

2. 分组取每组 top1（row_number）

题目

员工表 employee
字段：emp_id 员工 id，dept_id 部门 id，emp_name 姓名，salary 薪资，entry_time 入职时间

查询每个部门内薪资最高的员工信息，每个部门只保留薪资最高 1 人，薪资相同只取一条。

sql 答案

1
2
3
4
5
6
7
8
9
with temp as (
    select
        *,
        row_number() over(partition by dept_id order by salary desc) as rn
    from employee
)
select emp_id,dept_id,emp_name,salary
from temp
where rn=1;

3. 分组取每组 top n

题目

订单表 order_info
字段：order_id 订单 id，user_id 用户 id，order_time 下单时间，pay_amount 支付金额，goods_type 商品类型

统计每个商品类型下，用户下单金额最高的前 3 笔订单数据。

sql 答案

1
2
3
4
5
6
7
8
9
with temp as (
    select
        *,
        row_number() over(partition by goods_type order by pay_amount desc) as rn
    from order_info
)
select *
from temp
where rn<=3;

4. 并列排名不跳名次 dense_rank

题目

员工表 employee
字段：emp_id 员工 id，dept_id 部门 id，emp_name 姓名，salary 薪资，entry_time 入职时间

查询每个部门薪资排名前三的员工，薪资相同名次相同，不跳过排名，允许超出 3 条数据。

sql 答案

1
2
3
4
5
6
7
8
9
with temp as (
    select
        *,
        dense_rank() over(partition by dept_id order by salary desc) as rk
    from employee
)
select *
from temp
where rk<=3;

5. 并列排名跳名次 rank

题目

员工表 employee
字段：emp_id 员工 id，dept_id 部门 id，emp_name 姓名，salary 薪资，entry_time 入职时间

查询全公司薪资排名前 5 的员工，同薪资同排名，排名向后顺延。

sql 答案

1
2
3
4
5
6
7
8
9
with temp as (
    select
        *,
        rank() over(order by salary desc) as rk
    from employee
)
select *
from temp
where rk<=5;

6. 分组求用户总金额后取 top

题目

订单表 order_info
字段：order_id 订单 id，user_id 用户 id，order_time 下单时间，pay_amount 支付金额，goods_type 商品类型

统计每个用户累计支付总金额，找出累计消费最高的前 10 名用户及消费总额。

sql 答案

1
2
3
4
5
6
7
select
    user_id,
    sum(pay_amount) as total_money
from order_info
group by user_id
order by total_money desc
limit 10;

7. 每组取倒数 top n

题目

员工表 employee
字段：emp_id 员工 id，dept_id 部门 id，emp_name 姓名，salary 薪资，entry_time 入职时间

查询每个部门入职时间最晚的 2 名员工。

sql 答案

1
2
3
4
5
6
7
8
9
with temp as (
    select
        *,
        row_number() over(partition by dept_id order by entry_time desc) as rn
    from employee
)
select *
from temp
where rn<=2;

8. 剔除重复后分组 top

题目

先统计每个用户在各商品类型下的下单次数，再取出每种商品类型下单次数最多的前 2 个用户。

sql 答案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
with user_cnt as (
    select
        user_id,
        goods_type,
        count(order_id) as order_cnt
    from order_info
    group by user_id,goods_type
),
rank_data as (
    select
        *,
        row_number() over(partition by goods_type order by order_cnt desc) as rn
    from user_cnt
)
select user_id,goods_type,order_cnt
from rank_data
where rn<=2;

9. 连续天数 top 进阶

题目

用户登录表 user_login
字段：user_id 用户 id，login_date 登录日期

统计每位用户连续登录天数，查询连续登录天数最多的前 8 位用户及其最长连续登录天数。

sql 答案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
with distinct_login as (
    select distinct user_id,login_date
    from user_login
),
group_date as (
    select
        user_id,
        login_date,
        date_sub(login_date,interval row_number() over(partition by user_id order by login_date) day) as group_tag
    from distinct_login
),
continue_days as (
    select
        user_id,
        count(login_date) as continue_num
    from group_date
    group by user_id,group_tag
)
select user_id,max(continue_num) as max_continue
from continue_days
group by user_id
order by max_continue desc
limit 8;

with distinct_login as(
	select disntinct user_id,login_date
    from user_login
)
,group_date as (
	select
    user_id
    ,login_date
    ,date_sub(login_date,interval row_number() over(partition by user_id order by login_date) day) as grp
    from distinct_login
)

10. top n 占比统计

题目

订单表 order_info
字段：order_id 订单 id，user_id 用户 id，order_time 下单时间，pay_amount 支付金额，goods_type 商品类型

统计消费金额排名前 30% 用户的总消费金额，占全体用户总消费金额的比例。

sql 答案

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
with user_total as(
	select
    user_id
    ,sum(pay_amount) as user_sum
    from order_info
    group by 1
)
,user_rank as(
	select
    user_sum
    ,row_number() over(order by user_sum desc) as rn
    ,count(*) over() as all_user
    from user_total
)
select
sum(user_sum)*1.0/(select sum(pay_amount) from order_info) as top30_percent
from user_rank
where rn<=all_user*0.3;

易错点

1
2
3
4
5
select
    user_sum,           -- ❌ 非聚合列
    row_number() over(order by user_sum desc) as rn,
    count(*) as all_user  -- ❌ 聚合函数，但没有 group by
from user_total

count(*) 是聚合函数（返回 1 行），count(*) over() 是窗口函数（每行都返回）

这个场景需要每行都知道”总人数”，所以必须用窗口函数 over()

count(*) over() as all_user          -- ✅ 窗口函数，不是聚合函数