Checking for patterns in a int

  • 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

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • 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/

  • 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. Selburg
  • 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.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • 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.

  • Nevyn

    In that case the definition would be how often a number or numbers follow the number before it.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • 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. Selburg
  • 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).

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • 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. Selburg
  • bopeavy (7/19/2011)


    Nevyn

    In 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?

  • Nevyn

    Yes thats about as close as it comes.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • 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.

  • 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.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • 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/

  • 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. Selburg

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply