最近需要做一个特征库,库每个表中存有若干条有顺序的特征数据,想利用数据库实现特征匹配,具体描述为:
假设存在特征表a:
id Specific
1 aaaa
2 bbbb
3 cccc假设数据表为b:
id Specific
1 dddd
2 eeee
3 aaaa
4 bbbb
5 cccc 假设数据表为c:
id Specific
1 aaaa
2 cccc
3 dddd
4 bbbb
5 eeee假设数据表为d:
id Specific
1 aaaa
2 bbbb
3 dddd
4 cccc
5 eeee假设数据表为e:
id Specific
1 aaaa
2 dddd
3 bbbb
4 cccc
5 eeee
则认为c和d与a不匹配,b与a匹配,在数据库如何实现这样的匹配,非常感谢
假设存在特征表a:
id Specific
1 aaaa
2 bbbb
3 cccc假设数据表为b:
id Specific
1 dddd
2 eeee
3 aaaa
4 bbbb
5 cccc 假设数据表为c:
id Specific
1 aaaa
2 cccc
3 dddd
4 bbbb
5 eeee假设数据表为d:
id Specific
1 aaaa
2 bbbb
3 dddd
4 cccc
5 eeee假设数据表为e:
id Specific
1 aaaa
2 dddd
3 bbbb
4 cccc
5 eeee
则认为c和d与a不匹配,b与a匹配,在数据库如何实现这样的匹配,非常感谢
2 SELECT ID, row_number() OVER(ORDER BY 1), ID -(row_number() OVER(ORDER BY 1)) div, COUNT(ID) OVER(order BY 1) COUNT FROM t;
ID ROW_NUMBER()OVER(ORDERBY1) DIV COUNT
---------- -------------------------- ---------- ----------
1 1 0 3
2 2 0 3
4 3 1 3
SQL>
SQL> WITH t AS (SELECT 1 ID FROM dual UNION ALL SELECT 2 from dual UNION ALL SELECT 3 from dual)
2 SELECT ID, row_number() OVER(ORDER BY 1), ID -(row_number() OVER(ORDER BY 1)) div, COUNT(ID) OVER(order BY 1) COUNT FROM t;
ID ROW_NUMBER()OVER(ORDERBY1) DIV COUNT
---------- -------------------------- ---------- ----------
1 1 0 3
2 2 0 3
3 3 0 3
SQL>
如果表t数据等于3,那么再做3楼的操作并检查DIV的值是否为全0,如是则匹配,否则不匹配。
id Specific
1 aaaa
2 dddd
3 bbbb
4 cccc
5 aaaa
6 bbbb
7 cccc
8 eeee
中使用3楼操作的话id和排序值相减的办法就不好使了,而且count的值也会大于3
aa bb cc dd eeSQL> WITH t AS (SELECT 1 ID, 'aa' sepc FROM dual UNION ALL
2 SELECT 2, 'bb' FROM dual UNION ALL
3 SELECT 3, 'cc' FROM dual UNION ALL
4 SELECT 4, 'dd' FROM dual UNION ALL
5 SELECT 5, 'ee' FROM dual)
6 SELECT LAG(sepc, 1, '00') OVER(order BY ID) previous_value, sepc, LEAD(sepc, 1, '00') OVER(ORDER BY ID) behind_value1,
7 LEAD(sepc, 2, '00') OVER(ORDER BY ID) behind_value2,LEAD(sepc, 3, '00') OVER(ORDER BY ID) behind_value3 FROM t;
PREVIOUS_VALUE SEPC BEHIND_VALUE1 BEHIND_VALUE2 BEHIND_VALUE3
-------------- ---- ------------- ------------- -------------
00 aa bb cc dd
aa bb cc dd ee
bb cc dd ee 00
cc dd ee 00 00
dd ee 00 00 00
SQL>
1、利用select a.id,a.Specific from a,f where a.Specific=f.Specific;选出所有在f中出现的a的数据。
2、利用id号的连续性来判断是否匹配,例如,如果从id号1连续到3的数据则认为匹配。
特征表:
1 'aa'; 2 'bb'; 3 'cc'例表:
1 'aa'; 2 'cc'; 3 'bb'
set @a=0;
set @b=0;
select id-1 @a from table limit 1;
select PM,MIN(id) as mi,MAX(id) as mx from(
select * from @b:=if(@a+1=id,@b,@b+1) as PM @a=id from table)
Group by PM having count(*)>=select count(*) from a;