TABLE1
CODE RATE1 type MONTH
A 0 Acc1 201906
A 0 Acc1 201907
A 0 Acc1 201908
A 1 Acc1 201909
A 1 Acc1 201910
A 1 Acc1 201911
A 1 Acc1 201912
A 1 Acc1 202001
A 1 Acc1 202002
A 1 Acc1 202003
A 1 Acc1 202004
A 1 Acc1 202005
A 1 Acc1 202006
A 1 Acc1 202007
A 1 Acc1 202008
A 1 Acc1 202009
A 0 Acc2 201906
A 0 Acc2 201907
A 0 Acc2 201908
A 1 Acc2 201909
A 1 Acc2 201910
A 1 Acc2 201911
A 1 Acc2 201912
A 1 Acc2 202001
A 1 Acc2 202002
A 1 Acc2 202003
A 1 Acc2 202004
A 1 Acc2 202005
A 1 Acc2 202006
A 1 Acc2 202007
A 1 Acc2 202008
A 1 Acc2 202009
表2
CODE RATE2 MONTH
A 10 202001
A 10 202002
A 10 202003
A 10 202004
我正在将数据从旧系统迁移到新系统。作为每月维护的旧系统数据的一部分，如果数据更新，将更新同一行，并且表包含一个月的一行，我将迁移到新闻系统，它包含创建活动记录的开始日期和结束日期。因此在更新新数据时需要插入并更新旧行的结束日期
我有几个表，我需要加入，并根据评分1和评分2找到开始日期和结束日期。我的第一个表包含所有月份的数据，第二个表的可用数据直到它的活动取消数据将不可用。如果速率可用，则我们将其视为0。
我的预期输出
CODE RATE1 RATE2 Type START_DT END_DT
A 0 0 Acc1 201906 201908
A 1 0 Acc1 201909 201912
A 1 10 Acc1 202001 202004
A 1 0 Acc1 202005 202009
A 0 0 Acc2 201906 201908
A 1 0 Acc2 201909 201912
A 1 10 Acc2 202001 202004
A 1 0 Acc2 202005 202009
但我得到了低于结果23排。
select code1, rate1, rate2, type, min(month) start_dt,
case when row_number() over(partition by code1 order by max(month) desc) = 1 then 999912 else max(month) end end_dt
from (
select t1.month, code1, rate1, rate2, type,
row_number() over(partition by code1 order by t1.month) rn1,
row_number() over(partition by code1, type, rate1, rate2, type order by t1.month) rn2
from table1 t1 left join table2 t2 on t1.code1 = t2.code2 and t1.month = t2.month
) t
group by code1, rate1, rate2, type, rn1 - rn2
order by start_dt
请在此URL中查找我的查询https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b4f77cd13967c1c5a74efcacfb3d3a22
提前谢谢。
如果您需要更多信息，请评论
您已经非常接近于解决这个缺口和孤岛分配。列type出现问题。它需要去两个窗口函数的partition by子句，所以它与外部的group by是一致的。
这会给你想要的结果：
select code1, rate1, coalesce(rate2, 0), type, min(month) start_dt, max(month) end_dt
from (
select t1.month, t1.code1, t1.rate1, t2.rate2, type,
row_number() over(partition by t1.code1, t1.type order by t1.month) rn1,
row_number() over(partition by t1.code1, t1.type, t1.rate1, t2.rate2 order by t1.month) rn2
from table1 t1
left join table2 t2 on t1.code1 = t2.code2 and t1.month = t2.month
) t
group by code1, rate1, rate2, type, rn1 - rn2
order by type, start_dt
注：
我不确定您想要在外部查询中使用日期转换实现的逻辑，而且它似乎与所需的结果不一致，所以我将其删除
使用子查询中的所有列所属的表来限定它们；这避免了的模糊性
我还修复了外部
order by子句，以便按所需顺序返回结果，并使用coalesce()将丢失的rate2s默认为0
DB Fiddlde上的演示：
CODE1|RATE1|COALESCE(RATE2,0(|类型|START_DT|END_DT：----|----：|------------------:|：----|-------：|----：A|0|0|Acc1|201906|201908A|1|0|Acc1|201909|201912A|1|10|Acc1|202001|202004A|1|0|Acc1|202005|202009A|0|0|Acc2|201906|201908A|1|0|Acc2|201909|201912A|1|10|Acc2|202001|202004A|1|0|Acc2|202005|202009
您可以连接这两个表，然后使用MATCH_RECOGNIZE:
SELECT code,
rate1,
rate2,
type,
first_month,
last_month
FROM (
SELECT t1.code,
t1.rate1,
COALESCE( t2.rate2, 0 ) AS rate2,
t1.type,
t1.month
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON ( t1.code = t2.code AND t1.month = t2.month )
)
MATCH_RECOGNIZE (
PARTITION BY type
ORDER BY month
MEASURES
FIRST( code ) AS code,
FIRST( rate1 ) AS rate1,
FIRST( rate2 ) AS rate2,
FIRST( month ) AS first_month,
LAST( month ) AS last_month
ONE ROW PER MATCH
PATTERN (FIRST_ROW SAME_RATES*)
DEFINE
SAME_RATES AS ( SAME_RATES.rate1 = PREV(SAME_RATES.rate1)
AND SAME_RATES.rate2 = PREV(SAME_RATES.rate2) )
)
ORDER BY type, first_month;
对于您的样本数据：
CREATE TABLE table1 ( CODE, RATE1, type, MONTH ) AS
SELECT 'A', 0, 'Acc1', 201906 FROM DUAL UNION ALL
SELECT 'A', 0, 'Acc1', 201907 FROM DUAL UNION ALL
SELECT 'A', 0, 'Acc1', 201908 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 201909 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 201910 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 201911 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 201912 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 202001 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 202002 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 202003 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 202004 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 202005 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 202006 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 202007 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 202008 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc1', 202009 FROM DUAL UNION ALL
SELECT 'A', 0, 'Acc2', 201906 FROM DUAL UNION ALL
SELECT 'A', 0, 'Acc2', 201907 FROM DUAL UNION ALL
SELECT 'A', 0, 'Acc2', 201908 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 201909 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 201910 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 201911 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 201912 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 202001 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 202002 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 202003 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 202004 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 202005 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 202006 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 202007 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 202008 FROM DUAL UNION ALL
SELECT 'A', 1, 'Acc2', 202009 FROM DUAL;
CREATE TABLE table2 ( CODE, RATE2, MONTH ) AS
SELECT 'A', 10, 202001 FROM DUAL UNION ALL
SELECT 'A', 10, 202002 FROM DUAL UNION ALL
SELECT 'A', 10, 202003 FROM DUAL UNION ALL
SELECT 'A', 10, 202004 FROM DUAL;
输出：
代码|RATE1|RATE2|TYPE|FIRST_MONTH|LAST_MONTH：----|----：|----：|：----|---------：|--------：A|0|0|Acc1|201906|201908A|1|0|Acc1|201909|201912A|1|10|Acc1|202001|202004A|1|0|Acc1|202005|202009A|0|0|Acc2|201906|201908A|1|0|Acc2|201909|201912A|1|10|Acc2|202001|202004A|1|0|Acc2|202005|202009
db<gt；小提琴这里