问题描述:
执行sql
select v1.*, row_number() over (partition by v1.em_code, v1.cldate order by v1.rn) as rn2 from v1
结果
code | status | create_date | rn | rn2 |
hzdz-scsb014 | 1 | 2020/5/9 8:30:00 | 2 | 1 |
hzdz-scsb014 | 3 | 2020/5/9 8:46:06 | 1 | 1 |
hzdz-scsb014 | 1 | 2020/5/9 8:46:27 | 1 | 1 |
hzdz-scsb014 | 1 | 2020/5/9 8:55:40 | 1 | 1 |
hzdz-scsb014 | 3 | 2020/5/9 8:55:40 | 1 | 2 |
hzdz-scsb014 | 2020/5/9 9:00:00 | 2 | 1 |
执行sql
select * from (select v1.*, row_number() over (partition by v1.em_code, v1.cldate order by v1.rn) as rn2 from v1) t where t.rn2 = 1
预期结果应该是过滤掉create_date为2020/5/9 8:55:40,status为3这行数据
结果
code | status | create_date | rn | rn2 |
hzdz-scsb014 | 1 | 2020/5/9 8:30:00 | 2 | 1 |
hzdz-scsb014 | 3 | 2020/5/9 8:46:06 | 1 | 1 |
hzdz-scsb014 | 1 | 2020/5/9 8:46:27 | 1 | 1 |
hzdz-scsb014 | 3 | 2020/5/9 8:55:40 | 1 | 1 |
hzdz-scsb014 | 3 | 2020/5/9 9:00:00 | 1 | 2 |
结果与预期不符
原因:
1、使用 row_number() over (partition by v1.em_code, v1.cldate order by v1.rn), 进行数据分析时, order by的字段最好是唯一的,不为空的,如果重复或为空,排序可能会失败,按照sql的默认排序去取值。
2、ORACLE默认排序规则