时间戳

首页 > 解决方案 > 使用 partition by 子句时如何排序

sql - 使用 partition by 子句时如何排序

问题描述

我想为数据按日期排序的每一行分配一个行号，并且当“project_name”列更改且“ok_criterium”列更改时，我想从 0 重新开始分配。

DDL：

  CREATE TABLE TEST1 
   ("PROJECT_NAME" NUMBER, 
    "OK_CRITERIUM" NUMBER, 
    "DDATE" DATE
   ) ;

数据：

Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','1',to_date('21.12.2019 00:00:00','DD.MM.RRRR HH24:MI:SS'));
Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','1',to_date('21.12.2019 00:00:00','DD.MM.RRRR HH24:MI:SS'));
Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','0',to_date('26.12.2019 00:00:00','DD.MM.RRRR HH24:MI:SS'));
Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','0',to_date('26.12.2019 00:00:00','DD.MM.RRRR HH24:MI:SS'));
Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','1',to_date('01.01.2020 00:00:00','DD.MM.RRRR HH24:MI:SS'));
Insert into test1 (PROJECT_NAME,OK_CRITERIUM,DDATE) values ('241','0',to_date('01.01.2020 00:00:00','DD.MM.RRRR HH24:MI:SS'));

我的选择：

select 
row_number () over (partition by project_name,ok_criterium order by ddate asc ) as rn,
t1.*
from test1 t1

结果：

RN PROJECT_NAME OK_CRITERIUM DDATE 
1   241 0   26.12.2019 00:00:00
2   241 0   26.12.2019 00:00:00
3   241 0   01.01.2020 00:00:00
1   241 1   21.12.2019 00:00:00
2   241 1   21.12.2019 00:00:00
3   241 1   01.01.2020 00:00:00

问题：行未按列 ddate 排序。您可以在日期 26.12.2019 之后看到日期 21.12.2019。数据也按我不想要的“ok_criterium”排序。

我需要的：

RN PROJECT_NAME OK_CRITERIUM DDATE 
1 241   1   21.12.2019 00:00:00
2 241   1   21.12.2019 00:00:00
1 241   0   26.12.2019 00:00:00
2 241   0   26.12.2019 00:00:00
1 241   1   01.01.2020 00:00:00
1 241   0   01.01.2020 00:00:00

标签： sqloracle

解决方案

请使用以下查询。您还必须按日期进行分区。

select 
row_number () over (partition by project_name,ok_criterium, ddate order by ddate asc ) 
as rn,
t1.*
from test1 t1 order by ddate;

下面是演示，

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e08000697ced91ee3e1df392ba0d8ac9

推荐阅读