Help needed with pattern matching (analytics SQL ?)
436313Sep 15 2009 — edited Sep 16 2009Hello Forum Users,
I've got a curious problem on my hands that I am unable to think of an efficient way to code in Oracle SQL ....
(1) Background
Two tables :
MEASUREMENTS
ID NUMBER
M1 NUMBER
M2 NUMBER
M3 NUMBER
M4 NUMBER
INTERESTING_VALUES
ID NUMBER
M1 NUMBER
M2 NUMBER
M3 NUMBER
M4 NUMBER
(2) OUTPUT NEEDED
For each row in "MEASUREMENTS", count the number of matches in "INTERESTING_VALUES" (all rows). Please note that the matches might not be in the same column.... e.g. for a given row MEASUREMENTS M1 might match INTERESTING_VALUES M3.
I need to count 2,3 and 4 matches and provide the count values seperatley.
I am not interested in fuzzy matching (e.g. "greater than" or "less than"), the numbers only need to match exactly.
(You can use features up to 11g).
Thank you for your help.