提问人：Kiazim Khutaba 提问时间：11/17/2023 更新时间：11/17/2023 访问量：34
如果数据在Oracle中包含一个不同的列，如何向组添加相同的排名？
How to add same rank to groups if data contains one different column in Oracle?
问：
我有这个示例数据集：
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:00:00' as start_time, 1 as flag
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:01:00' as start_time, 1 as flag
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:05:00' as start_time, 1 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-01 00:10:00' as start_time, 2 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-01 00:15:00' as start_time, 2 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-01 00:20:00' as start_time, 2 as flag
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:25:00' as start_time, 3 as flag
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:30:00' as start_time, 3 as flag
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-03 02:00:00' as start_time, 3 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-03 02:00:45' as start_time, 4 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-03 02:15:00' as start_time, 4 as flag
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-03 09:20:00' as start_time, 4 as flag;
脚本
select a.num0
, a.num1
, a.start_time
, a.flag as expected_value
, rank() over (partition by a.num1 order by start_time) as flag2
from test_01 as a
order by a.start_time;
我创建的（实际上是计数器）列，用于显示列中预期的值。
任务 - 如果列的连续值相同 - 所以这组行应该具有相同的标志（计数器）。 - 所有数据集行都相同。正如您在数据集中看到的，同一列值之间的值可能是另一个相同的值，因此在这种情况下，它是另一个组，并且它应该具有另一个标志，该标志比上一个（计数器）大一个，依此类推，即使它们包含相同的列值flagflag 2num1num0flagnum1
不幸的是，由于柱子的原因，无法使用。start_timeOracle rank
答：
1赞
p3consulting
11/17/2023
#1
您的标志是 num1 更改的计数：
with data(num0, num1, start_time, flag) as (
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:00:00' as start_time, 1 as flag FROM dual
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:01:00' as start_time, 1 as flag FROM dual
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:05:00' as start_time, 1 as flag FROM dual
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-01 00:10:00' as start_time, 2 as flag FROM dual
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-01 00:15:00' as start_time, 2 as flag FROM dual
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-01 00:20:00' as start_time, 2 as flag FROM dual
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:25:00' as start_time, 3 as flag FROM dual
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-01 00:30:00' as start_time, 3 as flag FROM dual
union all
select '1000000000000' as num0, '2000001' as num1, '2023-01-03 02:00:00' as start_time, 3 as flag FROM dual
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-03 02:00:45' as start_time, 4 as flag FROM dual
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-03 02:15:00' as start_time, 4 as flag FROM dual
union all
select '1000000000000' as num0, '2000002' as num1, '2023-01-03 09:20:00' as start_time, 4 as flag FROM dual
)
SELECT num0, num1, start_time, flag,
sum(change) OVER(ORDER BY start_time) AS flag2
FROM (
SELECT num0, num1, start_time, flag,
decode(num1, lag(num1) over(ORDER BY start_time), 0,1) AS change
FROM DATA
)
ORDER BY start_time
;
评论
0赞
Kiazim Khutaba
11/17/2023
你好！这解决了我的问题
1赞
MT0
11/17/2023
#2
从 Oracle 12 开始，您可以使用逐行模式匹配：MATCH_RECOGNIZE
SELECT num0
, num1
, start_time
, flag as expected_value
, flag2
FROM test_01
MATCH_RECOGNIZE(
ORDER BY start_time
MEASURES
MATCH_NUMBER() AS flag2
ALL ROWS PER MATCH
PATTERN (same_num1+)
DEFINE same_num1 AS FIRST(num1) = num1
)
ORDER BY start_time;
其中，对于示例数据：
CREATE TABLE test_01 (num0, num1, start_time, flag) AS
SELECT '1000000000000', '2000001', DATE '2023-01-01' + INTERVAL '00:00:00' HOUR TO SECOND, 1 FROM DUAL UNION ALL
SELECT '1000000000000', '2000001', DATE '2023-01-01' + INTERVAL '00:01:00' HOUR TO SECOND, 1 FROM DUAL UNION ALL
SELECT '1000000000000', '2000001', DATE '2023-01-01' + INTERVAL '00:05:00' HOUR TO SECOND, 1 FROM DUAL UNION ALL
SELECT '1000000000000', '2000002', DATE '2023-01-01' + INTERVAL '00:10:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT '1000000000000', '2000002', DATE '2023-01-01' + INTERVAL '00:15:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT '1000000000000', '2000002', DATE '2023-01-01' + INTERVAL '00:20:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT '1000000000000', '2000001', DATE '2023-01-01' + INTERVAL '00:25:00' HOUR TO SECOND, 3 FROM DUAL UNION ALL
SELECT '1000000000000', '2000001', DATE '2023-01-01' + INTERVAL '00:30:00' HOUR TO SECOND, 3 FROM DUAL UNION ALL
SELECT '1000000000000', '2000001', DATE '2023-01-03' + INTERVAL '02:00:00' HOUR TO SECOND, 3 FROM DUAL UNION ALL
SELECT '1000000000000', '2000002', DATE '2023-01-03' + INTERVAL '02:00:45' HOUR TO SECOND, 4 FROM DUAL UNION ALL
SELECT '1000000000000', '2000002', DATE '2023-01-03' + INTERVAL '02:15:00' HOUR TO SECOND, 4 FROM DUAL UNION ALL
SELECT '1000000000000', '2000002', DATE '2023-01-03' + INTERVAL '09:20:00' HOUR TO SECOND, 4 FROM DUAL ;
输出：
| NUM0 | NUM1 | START_TIME | EXPECTED_VALUE | 标志2 |
|---|---|---|---|---|
| 1000000000000 | 2000001 | 2023-01-01 00:00:00 | 1 | 1 |
| 1000000000000 | 2000001 | 2023-01-01 00:01:00 | 1 | 1 |
| 1000000000000 | 2000001 | 2023-01-01 00:05:00 | 1 | 1 |
| 1000000000000 | 2000002 | 2023-01-01 00:10:00 | 2 | 2 |
| 1000000000000 | 2000002 | 2023-01-01 00:15:00 | 2 | 2 |
| 1000000000000 | 2000002 | 2023-01-01 00:20:00 | 2 | 2 |
| 1000000000000 | 2000001 | 2023-01-01 00:25:00 | 3 | 3 |
| 1000000000000 | 2000001 | 2023-01-01 00:30:00 | 3 | 3 |
| 1000000000000 | 2000001 | 2023-01-03 02:00:00 | 3 | 3 |
| 1000000000000 | 2000002 | 2023-01-03 02:00:45 | 4 | 4 |
| 1000000000000 | 2000002 | 2023-01-03 02:15:00 | 4 | 4 |
| 1000000000000 | 2000002 | 2023-01-03 09:20:00 | 4 | 4 |
评论
FROMSELECTAS