July 19, 2011 at 9:22 am
I do not really have an example. I was wondering is there a way in Tsql to go about seeing if there is a pattern to the sequence to the way the numbers are showing up in a column? I will try and give a example:
Num1
1
-6
9
3
2
-4
-6
6
-6
The -6, 6,-6 is a pattern
July 19, 2011 at 9:59 am
Are you only looking A - B - A patterns? Otherwise the distinction of it being a pattern is pretty vague. For example if you have a few million rows your entire example could be a pattern that is repeated. It could be nearly impossible depending on how you define a pattern.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 19, 2011 at 10:04 am
I agree, before providing a solution the "Pattern" requirement will need clearly defined. 😀
______________________________________________________________________
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 10:11 am
Thats it I do not have a definition. I am trying to determine is there a way to define that pattern without knowing what it is... and yes that does make the possibilities high.
@jason Can not format this into a more understandable way I know how to post but this is more a general concept not an actual problem.
July 19, 2011 at 10:16 am
If there is no definition, then everything is a pattern.
For example, in your sample data, every row is either 7,6,1,2, or 12 fewer or 15 or 12 greater than the preceding row. That's a pattern. It doesnt repeat and is almost certainly useless, but its a pattern.
July 19, 2011 at 10:19 am
Nevyn
In that case the definition would be how often a number or numbers follow the number before it.
July 19, 2011 at 10:20 am
bopeavy (7/19/2011)
Thats it I do not have a definition. I am trying to determine is there a way to define that pattern without knowing what it is... and yes that does make the possibilities high.@jason Can not format this into a more understandable way I know how to post but this is more a general concept not an actual problem.
Well, then the answer is "Yes" there is a way in SQL to find patterns. The actual SQL code is 100% dependant upong the pattern.
I hate to be vague, but with all due respect the formula to find an undefined pattern would indeed be undefined itself. :hehe:
you have to know what it is your looking for before you can find it.
______________________________________________________________________
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 10:26 am
Great Jason so then what direction would you go to find that out? Is there any post or blog on this matter to find an undefine or vague definition(how often a number or numbers that follow the number before it).
July 19, 2011 at 10:27 am
bopeavy (7/19/2011)
Great Jason so then what direction would you go to find that out? Is there any post or blog on this matter to find an undefine or vague definition(how often a number or numbers that follow the number before it).
Sorry, my post went up before I saw your response. Let me see what I can find for you.
______________________________________________________________________
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 10:27 am
bopeavy (7/19/2011)
NevynIn that case the definition would be how often a number or numbers follow the number before it.
So what you are looking for is consecutive rows with the same absolute value? How many rows must match consecutively to make it a pattern?
If the data was
1
-6
9
3
2
-4
-6
6
Would -6,6 be a pattern?
If it was
1
-6
9
3
2
-4
-6
6
-6
6
Would it be one pattern of four rows? (-6,6,-6,6)
A repeating pattern of two? (-6,6)
Two patterns of 3? (-6,6,-6) and (6,-6,6)
Or all of the above?
July 19, 2011 at 10:29 am
Nevyn
Yes thats about as close as it comes.
July 19, 2011 at 10:29 am
If you're looking for the ABS() in a repeating way, is there some ordering column? Rows in SQL have no order by default, so you can't find a pattern without that.
Also, would this be a pattern?
6
12
18
24
or this?
6
66
666
There are ways to look for patterns in a range of values, but in pure T-SQL, you'd need to at least provide some definition of a pattern and an order.
July 19, 2011 at 10:31 am
Steve,
Yes I do have an ID column that is (1,1) identity, As for the patterns Yes they can and no they may not that I have not been able to figure out yet. There may be no pattern at all, but in order to find one I was looking for some where to start.
July 19, 2011 at 10:36 am
OK so given that we do have a solid way to order the table. There still isn't really any way to just get a pattern of any combination of values. You could have endless possibilities.
6
12
6
7
8
9
10
33
54
23
6
12
6
7
8
9
10
In the above example what is the pattern?
Is it 6-12-6?
Is it 6-12-6-7?
Is it 6-12-6-7-7-8-9-10?
Or is all of them? Is it only the longest one? Only the shortest?
You would have to take into account the entire list above too because it could also be repeated somewhere.
Beginning to see the impossibility yet?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 19, 2011 at 10:51 am
Ok here goes.....
DISCLAIMER: This only addresses one of MANY possible patterns.
declare @numbers table (nDex int)
insert @numbers (nDex)
values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(-1),(-2),(-3),(-4),(-5),(-6),(-7),(-8),(-9)
-- just as Steve pointed out, there must be something that defines the order
-- here i'm just using an identity
declare @sampleData table (nDex int identity(1,1), val int)
insert @sampleData
select a.nDex from @numbers as a
cross join @numbers as b
order by NEWID()
-- random list of numbers
;with numberSet
AS (select
nDex
,val
from
@sampleData)
,similarities
AS (select
a.nDex as nDexA
,a.val as valueA
,b.nDex as nDexB
,b.val as valueB
,a.nDex - b.nDex as spread
from
numberSet as a
cross join numberSet as b
where
a.nDex <> b.nDex
-- here is the pattern definition(s) to look for
and
(
a.val = b.val
or
a.val = ABS(b.val)
or
a.val%b.val = 0
))
,commonVariances
as (select top 2
spread
,COUNT(*) as occurrances
from
similarities
group by spread
order by COUNT(*) desc)
select
*
from similarities as a
inner join commonVariances as b
on a.spread = b.spread
______________________________________________________________________
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. SelburgViewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply