July 19, 2011 at 10:56 am
Call the people at SETI[/url]. 😀
Seriously, I think T-SQL may be the wrong tool for the job of discovering undefined patterns. This sounds like it's more in the realm of cryptology.
Some procedural language that gives you array structures would probably make coding easier and runtimes faster.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 19, 2011 at 11:12 am
The Dixie Flatline (7/19/2011)
Call the people at SETI[/url]. 😀Seriously, I think T-SQL may be the wrong tool for the job of discovering undefined patterns. This sounds like it's more in the realm of cryptology.
Some procedural language that gives you array structures would probably make coding easier and runtimes faster.
We have a SETI sighting! :Whistling:
I agree on the procedural language bit. Sliding windows using arrays or strings (an abstraction of an array in a lot of languages) are good for this kind of work. A simple example of this technique is looking for 2-byte Windows EOL sequences when reading a file byte-by-agonizing-byte by carrying the last byte and the current byte somehow and checking for a match. RegEx is very good at this kind of work too...but even if the net was cast very wide in all cases you have to make some decisions about what defines a pattern.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 11:27 am
opc.three (7/19/2011)
The Dixie Flatline (7/19/2011)
--------------------------------------------------------------------------------
Call the people at SETI.
Seriously, I think T-SQL may be the wrong tool for the job of discovering undefined patterns. This sounds like it's more in the realm of cryptology.
Some procedural language that gives you array structures would probably make coding easier and runtimes faster.
We have a SETI sighting!
I agree on the procedural language bit. Sliding windows using arrays or strings (an abstraction of an array in a lot of languages) are good for this kind of work. A simple example of this technique is looking for 2-byte Windows EOL sequences when reading a file byte-by-agonizing-byte by carrying the last byte and the current byte somehow and checking for a match. RegEx is very good at this kind of work too...but even if the net was cast very wide in all cases you have to make some decisions about what defines a pattern.
LOL! In all seriousness I was looking for a place to start since its in sql i thought there might be away. If not then how about telling me where would you start. And I did give a vague desciption of what defines a pattern "How often does a number or set of numbers follow the number before it." If you think this is more a procedural language then where would you start?
July 19, 2011 at 11:37 am
pattern searching involves looking for certain patterns. So you'd have to define that the ABS() value repeats according to some order.
You could also do something like running a rolling regression analysis, or a rolling look for multiples of some sort as well, but you'd end up coding each one of those as a pass through the data.
July 19, 2011 at 11:40 am
Steve (7-19-2011)
You could also do something like running a rolling regression analysis, or a rolling look for multiples of some sort as well, but you'd end up coding each one of those as a pass through the data.
ok, is there example of doing that here on the site somewhere?
July 19, 2011 at 11:42 am
bopeavy (7/19/2011)
opc.three (7/19/2011)
The Dixie Flatline (7/19/2011)
--------------------------------------------------------------------------------
Call the people at SETI.
Seriously, I think T-SQL may be the wrong tool for the job of discovering undefined patterns. This sounds like it's more in the realm of cryptology.
Some procedural language that gives you array structures would probably make coding easier and runtimes faster.
We have a SETI sighting!
I agree on the procedural language bit. Sliding windows using arrays or strings (an abstraction of an array in a lot of languages) are good for this kind of work. A simple example of this technique is looking for 2-byte Windows EOL sequences when reading a file byte-by-agonizing-byte by carrying the last byte and the current byte somehow and checking for a match. RegEx is very good at this kind of work too...but even if the net was cast very wide in all cases you have to make some decisions about what defines a pattern.
LOL! In all seriousness I was looking for a place to start since its in sql i thought there might be away. If not then how about telling me where would you start. And I did give a vague desciption of what defines a pattern "How often does a number or set of numbers follow the number before it." If you think this is more a procedural language then where would you start?
This is super-rough...but you could do something where you self-join rows to the ones before them in the sequence with predicates along the lines of:
DECLARE @numbers TABLE
(
id INT IDENTITY(1, 1),
value INT
)
INSERT @numbers
(value)
VALUES (1),
(-6),
(9),
(3),
(2),
(-4),
(-6),
(6),
(-6),
(6)
SELECT *
FROM @numbers
SELECT n1.id
FROM @numbers n1
JOIN @numbers n2 ON n1.id = n2.id
WHERE ABS(n1.value) = ABS(n2.value)
INTERSECT
SELECT n1.id
FROM @numbers n1
JOIN @numbers n2 ON n1.id = n2.id - 1
WHERE ABS(n1.value) = ABS(n2.value)
INTERSECT
SELECT n1.id
FROM @numbers n1
JOIN @numbers n2 ON n1.id = n2.id - 2
WHERE ABS(n1.value) = ABS(n2.value)
Proper results...maybe...performance...not so much. But if you're doing data analysis on a non-prod machine then I would say go for it. If this is to be an integral part of an app...then I would say move it out of the database.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 19, 2011 at 11:46 am
Am I wrong or doesn't my "solution" above find these patterns?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 19, 2011 at 12:11 pm
bopeavy (7/19/2011)
opc.threeThat at least helps give some sort of direction, Thanks.
It may have but what opc provide will help me see the pattern yours was alittle harder to see that.
😛
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 19, 2011 at 4:39 pm
I assumed Jason's would work for the first pattern.
It's a data mining thing, and I don't have a solution I can point you to, but it's the type of thing I've seen explained in data mining, or we used to do in economics. Run a set of data through a regression, and see what comes out.
Is there some problem domain you're looking into or just idle curiosity?
July 20, 2011 at 6:25 am
Steve (7-19-2011)
Is there some problem domain you're looking into or just idle curiosity?
Yes it is more a curiosity type thing, looking more to see for some type of trend in the numbers in a table... I have found 2 patterns that are small they are positive number then negative same number, the other is positive number then negative same number then positve same number: ex.
first is
8
-8
second is
2
-2
2
So if any one wanted to expand on finding if these patterns excist that would be good.
July 20, 2011 at 11:09 am
The Dixie Flatline (7/19/2011)
Call the people at SETI[/url]. 😀Seriously, I think T-SQL may be the wrong tool for the job of discovering undefined patterns. This sounds like it's more in the realm of cryptology.
Some procedural language that gives you array structures would probably make coding easier and runtimes faster.
Or - statistical analysis packages. lots of "patterns" to be found that way.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 21, 2011 at 6:26 am
Isn't this problem of finding a streak? If so, there is a linear solution like this
;WITH cteSource(ID, Value, GrpID)
AS (
SELECTID,
Value,
ROW_NUMBER() OVER (ORDER BY ID) - ROW_NUMBER() OVER (ORDER BY ABS(Value), ID) AS GrpID
FROM@Numbers
)
SELECTMIN(ID) AS FromID,
MAX(ID) AS ToID,
MIN(Value) AS LowValue,
MAX(Value) AS HighValue
FROMcteSource
GROUP BYGrpID,
ABS(Value)
HAVINGMIN(ID) < MAX(ID)
N 56°04'39.16"
E 12°55'05.25"
July 21, 2011 at 6:54 am
SwePeso
Thats a good one 2!
July 21, 2011 at 9:34 pm
Don't forget that they way the data is pulled could change any given pattern when there are columns before the search column that may order by a key field. You will need to make sure that all data within the table you are searching if always ordered by the same criteria.
Your example with only a identity and a number is not realistic of any given table that any given data table would contain.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply