数据库之ROW_NUMBER函数

ROW_NUMBER() 是 SQL 中的窗口函数，它为结果集中的每一行分配一个唯一的序号

表score_table

student_name |　　score
小明　　　　　　　　　　30
小红　　　　　　　　　　50
小黑　　　　　　　　　　10

select 
　　student_name,
　　score,
　　row_number() over(order by score dese) as rn -- 根据分数降序排名
from score_table

--------------查询结果如下--------------------

student_name |　　score　　|　　rn

小红　　　　　　　　　　50　　　　　　1
小明　　　　　　　　　　30　　　　　　2
小黑　　　　　　　　　　10　　　　　　3

备注

1.窗口函数，和 group by没有多大关系
2.group by限制：
2.1 select 字段必须在 group by里
2.2 使用聚合函数（如 MAX, MIN COUNT SUM等）

实战1  具有可理赔责任保单中生效/复效时间最晚保单销售人员

理赔表 (t_copy_online_input_claim_info)

claim_no 　　|　 cntr_no
CLAIM001 　　　　 CNTR001
CLAIM001 　　　　 CNTR002
CLAIM001 　　　　 CNTR003
CLAIM002 　　　　 CNTR004

保单表 (t_base_input_cntr)

cntr_no 　　pid 　　in_force_date 　　re_in_force_date
CNTR001 　　张三 　　2020-01-01 　　　　　　NULL
CNTR002 　　张三 　　2021-03-01 　　　　　　2022-05-01
CNTR003 　　李四 　　2022-01-01 　　　　　　NULL
CNTR004 　　王五 　　2021-01-01 　　　　　　2023-01-01

SELECT
a.claim_no,
b.pid,
b.in_force_date,
b.re_in_force_date,
GREATEST(b.in_force_date, COALESCE(b.re_in_force_date, b.in_force_date)) AS latest_date, -- GREATEST（a，b）取最大值  COALESCE（a,b）a不为空则取a,否则取b
ROW_NUMBER() OVER (
PARTITION BY a.claim_no  --根据claim_no  分区
ORDER BY GREATEST(b.in_force_date, COALESCE(b.re_in_force_date, b.in_force_date)) DESC --按照最晚时间排序
) AS rn
FROM
t_copy_online_input_claim_info a
JOIN
t_base_input_cntr b ON a.cntr_no = b.cntr_no

 

计算过程
JOIN结果：

claim_no 　　pid 　　in_force_date 　　re_in_force_date
CLAIM001 　　张三 　　　　2020-01-01　　　　 NULL
CLAIM001 　　张三 　　　　2021-03-01 　　　　2022-05-01
CLAIM001 　　李四 　　　　2022-01-01 　　　　NULL
CLAIM002 　　王五 　　　　2021-01-01 　　　　2023-01-01
计算latest_date：

　　张三(CNTR001): GREATEST(2020-01-01, NULL) → 2020-01-01

　　张三(CNTR002): GREATEST(2021-03-01, 2022-05-01) → 2022-05-01

　　李四: GREATEST(2022-01-01, NULL) → 2022-01-01

　　王五: GREATEST(2021-01-01, 2023-01-01) → 2023-01-01

按claim_no分区排序：

CLAIM001组：

　　张三(2022-05-01) → rn=1

　　李四(2022-01-01) → rn=2

　　张三(2020-01-01) → rn=3

CLAIM002组：

　　王五(2023-01-01) → rn=1

最终结果
claim_no 　　pid 　　in_force_date 　　re_in_force_date 　　   latest_date 　　　　  rn
CLAIM001 　　张三 　　　　2021-03-01 　　　　2022-05-01 　　　　　2022-05-01 　　　　　　1
CLAIM001 　　李四 　　　　2022-01-01 　　　　　　NULL 　　　　　　 2022-01-01 　　　　　　2
CLAIM001 　　张三        2020-01-01 　　　　　　NULL 　　　　　　 2020-01-01 　　　　　　3
CLAIM002 　　王五 　　　　2021-01-01 　　　　2023-01-01 　　　　　2023-01-01 　　　　　　1

 

实战2  具有可理赔责任保单数量最多的销售人员
select
　　a.claim_no,
　　b.pid,
　　ROW_NUMBER() OVER (
　　PARTITION BY a.claim_no  --根据claim_no  分区
　　ORDER BY count(*) DESC --根据数量排序
) AS rn
FROM
t_copy_online_input_claim_info a
JOIN
t_base_input_cntr b ON a.cntr_no = b.cntr_no
group by claim_no,pid

查询执行步骤
步骤1：执行JOIN操作
sql
FROM t_copy_online_input_claim_info a
JOIN t_base_input_cntr b ON a.cntr_no = b.cntr_no
结果（中间表）：

claim_no cntr_no pid
CLAIM001 CNTR001 张三
CLAIM001 CNTR002 张三
CLAIM001 CNTR003 李四
CLAIM001 CNTR004 张三
CLAIM002 CNTR005 王五
CLAIM002 CNTR006 王五
步骤2：执行GROUP BY
sql
GROUP BY claim_no, pid
分组统计结果：

(CLAIM001, 张三) → 3条记录（CNTR001, CNTR002, CNTR004）

(CLAIM001, 李四) → 1条记录（CNTR003）

(CLAIM002, 王五) → 2条记录（CNTR005, CNTR006）

步骤3：计算ROW_NUMBER()
sql
ROW_NUMBER() OVER (
PARTITION BY a.claim_no
ORDER BY count(*) DESC
) AS rn
计算过程：

按claim_no分区：

分区CLAIM001：

　　　　张三: count(*)=3

　　　　李四: count(*)=1
　　→ 排序：张三(1), 李四(2)

分区CLAIM002：

　　　　王五: count(*)=2
　　→ 排序：王五(1)

最终结果
claim_no 　　　　pid 　　rn
CLAIM001 　　　　张三 　　1
CLAIM001 　　　　李四 　　2
CLAIM002 　　　　王五 　　1
可视化计算过程
理赔号 CLAIM001:
　　　　张三 (关联3份保单) → rn=1
　　　　李四 (关联1份保单) → rn=2

理赔号 CLAIM002:
　　王五 (关联2份保单) → rn=1