在比较另一个视图Oracle的连续行集后提取

Question

嗨，我想从另一个视图的行集比较后，从视图中获得序列id。我可以做listagg和组序列id和匹配listagg与其他视图。但还有其他选择吗?视图1中存在顺序为0、1、2、3、4....的seq_id模式id也是有序的。我们可以让ROW_NUMBER()over()对模式id

进行排序

WITH view1(seq_id, pattern_id,rnum) AS (SELECT 0 , 1 ,1 FROM dual UNION
SELECT 0 , 2,2 FROM dual UNION
SELECT 0 , 3,3 FROM dual UNION
SELECT 0 , 4,4 FROM dual UNION
SELECT 1 , 3,5 FROM dual UNION
SELECT 1 , 4,6 FROM dual UNION
SELECT 1 , 1,7 FROM dual UNION
SELECT 1 , 2,8 FROM dual UNION
SELECT 2 , 2,9 FROM dual UNION
SELECT 2 , 4,10 FROM dual UNION
SELECT 2 , 1,11 FROM dual UNION
SELECT 2 , 3,12 FROM dual )
SELECT * FROM view1 order by rnum;

的例子:

视图1

seq id   pattern
------   -------
0          1
0          2
0          3
0          4
1          3
1          4
1          1
1          2
2          2
2          3
2          5
2          1

视图2有另一个列pk_id，它是像行数一样的唯一序列。始终以1

开头

pk_id   pattern id
------  ------------
1        3
2        4
3        1
4        2

视图1的预期输出

seq id
------
1

Answer 1

您可以使用ROW_NUMBER解析函数索引view1中的每个序列，然后将其与view2进行比较，并找到与序列中所有行匹配的序列:

SELECT seq_id,
MIN(rnum) AS pattern_start
FROM   ( SELECT v1.*,
ROW_NUMBER() OVER (PARTITION BY seq_id ORDER BY rnum) AS rn,
COUNT(*) OVER (PARTITION BY seq_id) AS num_patterns
FROM   view1 v1
) v1
INNER JOIN (
SELECT v2.*,
COUNT(*) OVER () AS num_patterns
FROM   view2 v2
) v2
ON     v1.rn           = v2.pk_id
AND v1.pattern_id   = v2.pattern_id
AND v1.num_patterns = v2.num_patterns
GROUP BY seq_id
HAVING COUNT(*) = MAX(v1.num_patterns);

对于样本数据:

CREATE VIEW view1 (seq_id, pattern_id, rnum) AS
SELECT 0, 1,  1 FROM dual UNION ALL
SELECT 0, 2,  2 FROM dual UNION ALL
SELECT 0, 3,  3 FROM dual UNION ALL
SELECT 0, 4,  4 FROM dual UNION ALL
SELECT 1, 3,  5 FROM dual UNION ALL
SELECT 1, 4,  6 FROM dual UNION ALL
SELECT 1, 1,  7 FROM dual UNION ALL
SELECT 1, 2,  8 FROM dual UNION ALL
SELECT 2, 2,  9 FROM dual UNION ALL
SELECT 2, 4, 10 FROM dual UNION ALL
SELECT 2, 1, 11 FROM dual UNION ALL
SELECT 2, 3, 12 FROM dual
ORDER BY 3;
CREATE VIEW view2 (pk_id, pattern_id) AS
SELECT 1, 3 FROM dual UNION ALL
SELECT 2, 4 FROM dual UNION ALL
SELECT 3, 1 FROM dual UNION ALL
SELECT 4, 2 FROM dual
ORDER BY 1;

输出:

PATTERN_START

listagg看起来是一个相当相关的方法。另一种方法是使用关系除法技术:

select v1.seq_id
from (
select v1.*, 
row_number() over(partition by seq_id order by rnum) pk_id
from view1 v1
) v1
inner join view2 v2 
on  v2.pattern_id = v1.pattern_id
and v2.pk_id = v1.pk_id
group by v1.seq_id
having count(*) = (select count(*) from view2)

如果您想要排除包含比引用更多模式的seq_id，我们可以使用left join代替:

select v1.seq_id
from (
select v1.*, 
row_number() over(partition by seq_id order by rnum) pk_id
from view1 v1
) v1
left join view2 v2 
on  v2.pattern_id = v1.pattern_id
and v2.pk_id = v1.pk_id
group by v1.seq_id
having count(*) = (select count(*) from view2)
and count(*) = count(v2.pk_id)