我需要选择某种机制来对使用row_number或rank函数排列的行进行排序。我尝试了使用RNK1和RNK2列的情况,但是我并不确定是否可能实现。请查看实际和期望的结果。
with tmp as (
select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag from dual union all
select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag from dual union all
select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag from dual union all
select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag from dual
)
select
tmp.*,
case when flag = 1 then row_number() over(partition by flag order by flag) else null end as rnk1,
case when flag = 1 then rank() over(partition by flag order by flag) else null end as rnk2
from tmp
order by startdate, username
实际情况:
+-------------+--------------------+--------+--------+--------+
| "USERNAME" | "STARTDATE" | "FLAG" | "RNK1" | "RNK2" |
+-------------+--------------------+--------+--------+--------+
| "username1" | 01-APR-19 00:00:00 | 1 | 6 | 1 |
| "username1" | 01-APR-19 01:00:00 | 0 | | |
| "username1" | 01-APR-19 02:00:00 | 1 | 4 | 1 |
| "username1" | 01-APR-19 03:00:00 | 1 | 3 | 1 |
| "username1" | 01-APR-19 04:00:00 | 0 | | |
| "username1" | 02-APR-19 01:00:00 | 1 | 5 | 1 |
| "username1" | 02-APR-19 02:00:00 | 1 | 1 | 1 |
| "username1" | 02-APR-19 03:00:00 | 1 | 2 | 1 |
| "username1" | 02-APR-19 04:00:00 | 0 | | |
| "username1" | 02-APR-19 05:00:00 | 0 | | |
+-------------+--------------------+--------+--------+--------+
预期结果:
+-------------+--------------------+--------+--------+--------+
| "USERNAME" | "STARTDATE" | "FLAG" | "RNK1" | "RNK2" |
+-------------+--------------------+--------+--------+--------+
| "username1" | 01-APR-19 00:00:00 | 1 | 1 | 1 |
| "username1" | 01-APR-19 01:00:00 | 0 | | |
| "username1" | 01-APR-19 02:00:00 | 1 | 2 | 2 |
| "username1" | 01-APR-19 03:00:00 | 1 | 2 | 2 |
| "username1" | 01-APR-19 04:00:00 | 0 | | |
| "username1" | 02-APR-19 01:00:00 | 1 | 3 | 3 |
| "username1" | 02-APR-19 02:00:00 | 1 | 3 | 3 |
| "username1" | 02-APR-19 03:00:00 | 1 | 3 | 3 |
| "username1" | 02-APR-19 04:00:00 | 0 | | |
| "username1" | 02-APR-19 05:00:00 | 0 | | |
+-------------+--------------------+--------+--------+--------+
感谢大家快速回复。我开始尝试您的建议,但又遇到了问题。
with tmp as (
select 'username1' as username, to_date('2019-04-01 00:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
select 'username1' as username, to_date('2019-04-01 01:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, null as threshold from dual union all
select 'username1' as username, to_date('2019-04-01 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
select 'username1' as username, to_date('2019-04-01 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, null as threshold from dual union all
select 'username1' as username, to_date('2019-04-01 04:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, null as threshold from dual union all
select 'username1' as username, to_date('2019-04-01 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, null as threshold from dual union all
select 'username1' as username, to_date('2019-04-02 01:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
select 'username1' as username, to_date('2019-04-02 02:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, null as threshold from dual union all
select 'username1' as username, to_date('2019-04-02 03:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, 1 as threshold from dual union all
select 'username1' as username, to_date('2019-04-02 04:00','YYYY-MM-DD HH24:MI') as startdate, 1 as flag, null as threshold from dual union all
select 'username1' as username, to_date('2019-04-02 05:00','YYYY-MM-DD HH24:MI') as startdate, 0 as flag, null as threshold from dual
)
select
tmp.*,
dense_rank() over( order by startdate, username, threshold)-
(case when flag=1 then
row_number()over(partition by flag, username order by startdate, username) - flag
else null end) as grp
from tmp
order by
startdate, username
实际:
+-------------+--------------------+--------+-------------+-------+
| "USERNAME" | "STARTDATE" | "FLAG" | "THRESHOLD" | "GRP" |
+-------------+--------------------+--------+-------------+-------+
| "username1" | 01-APR-19 00:00:00 | 1 | 1 | 1 |
| "username1" | 01-APR-19 01:00:00 | 0 | | |
| "username1" | 01-APR-19 02:00:00 | 1 | 1 | 2 |
| "username1" | 01-APR-19 03:00:00 | 1 | | 2 |
| "username1" | 01-APR-19 04:00:00 | 0 | | |
| "username1" | 01-APR-19 05:00:00 | 0 | | |
| "username1" | 02-APR-19 01:00:00 | 1 | 1 | 4 |
| "username1" | 02-APR-19 02:00:00 | 1 | | 4 |
| "username1" | 02-APR-19 03:00:00 | 1 | 1 | 4 |
| "username1" | 02-APR-19 04:00:00 | 1 | | 4 |
| "username1" | 02-APR-19 05:00:00 | 0 | | |
+-------------+--------------------+--------+-------------+-------+
期望结果:
+-------------+--------------------+--------+-------------+-------+
| "USERNAME" | "STARTDATE" | "FLAG" | "THRESHOLD" | "GRP" |
+-------------+--------------------+--------+-------------+-------+
| "username1" | 01-APR-19 00:00:00 | 1 | 1 | 1 |
| "username1" | 01-APR-19 01:00:00 | 0 | | |
| "username1" | 01-APR-19 02:00:00 | 1 | 1 | 2 |
| "username1" | 01-APR-19 03:00:00 | 1 | | 2 |
| "username1" | 01-APR-19 04:00:00 | 0 | | |
| "username1" | 01-APR-19 05:00:00 | 0 | | |
| "username1" | 02-APR-19 01:00:00 | 1 | 1 | 4 |
| "username1" | 02-APR-19 02:00:00 | 1 | | 4 |
| "username1" | 02-APR-19 03:00:00 | 1 | 1 | 5 |
| "username1" | 02-APR-19 04:00:00 | 1 | | 5 |
| "username1" | 02-APR-19 05:00:00 | 0 | | |
+-------------+--------------------+--------+-------------+-------+