| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How To Find Reapeating Row Patterns
Ethan,
I think the key, and it's been touched on already, is the
very definition of a pattern. Consider the following result
set using the distinct values from your initial post (I trimmed
the output for readability):
SQL> select level lvl,
2 '{ '||ltrim(sys_connect_by_path(id,','),',')||' }' sets
3 from (
4 select 'A' id from dual union all 5 select 'B' id from dual union all 6 select 'C' id from dual union all 7 select 'D' id from dual union all 8 select 'E' id from dual union all 9 select 'F' id from dual
LVL SETS
---------- ---------------
1 { A }
1 { B }
1 { C }
1 { D }
1 { E }
1 { F }
2 { A,B }
2 { A,C }
...
2 { B,C }
2 { B,D }
...
2 { C,D }
2 { C,E }
2 { C,F }
...
3 { A,B,C }
...
3 { A,E,F }
...
3 { B,E,F }
3 { C,D,E }
...
4 { A,B,C,D }
...
4 { C,D,E,F }
5 { A,B,C,D,E }
...
5 { B,C,D,E,F }
6 { A,B,C,D,E,F }
In the example above the definition of
a pattern is defined in the CONNECT BY
(which is why you see a,b and a,c, but
not c,a and c,b).
Additionally, assuming you were in some way able to
define a pattern, you still need to decide
whether or not patterns are excluded due to set membership.
(I think you sorta mentioned this already, but it wasn't
100% clear to me).
For example,
is {a,b} not a pattern because it can be a subset
of {a,b,c} ?
if so, then it is implied {a,b,c} is not as pattern
because it can be found in {a,b,c,d} and so on...
so, then you end up counting only the occurrences
of "complete" sets?
or do you simply want to count occurrences or different sets, for example:
SQL> select level lvl,
2 '{ '||ltrim(sys_connect_by_path(id,','),',')||' }' sets
3 from (
4 select 1 rn,'A' id from dual union all
5 select 2,'B' id from dual union all 6 select 3,'C' id from dual union all 7 select 4,'D' id from dual union all 8 select 5,'A' id from dual union all 9 select 6,'B' id from dual
LVL SETS
---------- ---------------
1 { A }
1 { A }
1 { B }
1 { B }
1 { C }
1 { D }
2 { A,B }
2 { A,B }
2 { B,C }
2 { C,D }
3 { A,B,C }
3 { B,C,D }
4 { A,B,C,D }
13 rows selected.
Only set a,b occurs more than once..
Obviously these examples are simplified due to the fact that there are no inverse sets. So, my whole point is, how are you defining a pattern? :)
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Post, Ethan
Sent: Thursday, July 21, 2005 10:45 PM
To: Dennis Williams
Cc: oracle-l_at_freelists.org
Subject: RE: How To Find Reapeating Row Patterns
Well I am pretty sure there must be plenty of programs out there that do this type of thing. I was hoping one of the new analytical functions in Oracle could handle this. If I was going to design a program like this I would do something like...and I am literally thinking this up as I type...
grab two values, look for # of occurrences with same two values using lag...
if a lot of matches assume I am in some sort of repeating pattern so add a value to the two and test three values...
keep repeating above until I stop getting matches
(throw this in also...even if I don't get a match on 3..4..5 go to the next value and assume some sort of single value not included in sequence and see if we can match on "ABC?E" etc...
-----Original Message-----
From: Dennis Williams [mailto:oracledba.williams_at_gmail.com]
Sent: Thursday, July 21, 2005 7:29 PM
To: Post, Ethan
Cc: oracle-l_at_freelists.org
Subject: Re: How To Find Reapeating Row Patterns
Ethan,
I'm stumped. Do you know how to accomplish this in a regular programming language like Pascal or Java? Is the sequence always the same length, or is the length arbitrary? Unless someone knows of a pattern recognition function in Oracle, my guess is that you will probably write an external C function to do this and then call that function from Oracle. It sounds pretty compute-intensive to me. It sounds a little like what the Unix "diff" program does.
Dennis Williams
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 22 2005 - 09:30:03 CDT
 |
 |