0

Given I have a column that has an increment value and I know that value is going to climb to X (X being arbitrary) I would like to count what the longest repetitive streak is without the last count (because it may be going up at he end and we have just not made it there yet)

They are keyed by datetime, so arranging them in order to get the proper sequence is trivial. As is excluding everything forward of the last occurring zero

Example:

0,1,2,3, 0,1,2, 0, 0,1,2,3,4, 0,1,2,3,4, 0,1,2,3,4,5, 0,1

Would be "The longest repetitive streak is 4 and it happened twice in a row."

This will eventually become a loop, where I start with the highest value in the set, yielding 1@>=5. 2@>=4 3@>=3, 3@>=2

I can explain in more detail, just a concept right now, so forgive me if vague, just ask where I need to elaborate more.

Since I know I will have to use a procedural loop to get the final value, Id est Cursor... And, since I really do not want to nest them, I could live with 5 or ten iterations of max value to 0, but do not want the thousands of potential iterations inside that to repeat however many times that may be.... I would like to let SQL do as much of the work as possible, but not feed it something I know is going to cripple it in the end.

So... Any speculations on how that could be achieved in MSSQL 2008R2+ in a set based query, without the use of a cursor?

Or should I just quit while I am ahead, pull this into C# and do it there.

Edit: As best I can type this on my phone, this is the intended objective if I were doing it out of SQL in psudocode.

Define datetime for start and end point
Select data that is between the first zero after start, and the last zero prior to end.
Define current, last, streak, count
Loop through rows returned.
If (current != 0) { 
last = current
 }
Else
{ //we have reset, how high did we get before, and is it the same as the last time we reset?
    If (last == streak) {
        //we have made another streak of same value as last
        Count +=1
(update the table keeping count that the sequence of (last) has now occurred (count) times at this point) 
    }
    Else
    {
        //we set a different max value, therefore a *different* streak
        //so we record it as the new streak to see if *it* repeats further
        Streak = last
Count = 1
    }
Last = 0
}

I think I will go back tonight and code a functional example in C# and a graphic of the pattern and see if that assists in saying "How would I or can I even do the same in SQL?"

Clarifications: If the values were:

0,1,2,3,4, 0,1,2, 0,1,2, 0,1,2, 0,1,2,3,4, 0,1 

should the result be the two 4s or the three 2s?

In that example there would only be one repetitive streak, the thee consecutive sequences of 0,1,2 as indicated by it going back to zero afterward. Repetitive is the key, though the same sequence will happen throughout the data, I want to know how many times it happens in direct repetition, not that it went 0,1,2 three time in the whole set, that it did it 3 times in a row. Logically the same pattern will repeat often through my data, I am concerned where it does it in a pattern, and then I will look for consistent patterns such as 0-4 three times, then random, then back to 0-4 three times.

If it helps the data is a measure of vibration intensity. By detecting the repetitive pattern it exposes harmonics that are not directly part of the source. I want to know what is essentially noise, and what is feedback sort of like when you are driving a car with an imbalance and it only does that shake between certain speeds.

Edit #2 (Better explanation, and functional cursor example) Given the data presented here (actual sample) and eliminating what seems to be ancillary to the problem which is simple filtering I can handle, row number vs datetime, etc...

Sample data:

Sample Data

With that data loaded into a sample table

CREATE TABLE [dbo].[Table_1](
    [row] [int] NOT NULL,
    [streak] [int] NOT NULL,
    [rStreak] [varchar](100) NULL
) ON [PRIMARY]

A functional example that achieves the calculation...

DECLARE @row INT
DECLARE @current INT
DECLARE @last INT
DECLARE @streak INT
DECLARE @streakCount INT
DECLARE @parser CURSOR

SET @streak = 0
--SET @streakCount = 1
SET @parser = CURSOR
FOR SELECT [row],
           [streak]
    FROM   [Table_1] --For the sake of brievity I am not filtering the *between* zeros and date range here, but I have that part covered.

OPEN @parser

FETCH next FROM @parser INTO @row, @current

WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT 'Row:' + Cast(@row AS VARCHAR) + ' Value:'
            + Cast(@current AS VARCHAR)

      IF @current = 0
        BEGIN
            PRINT 'Resetting streak of:'
                  + Cast(@streak AS VARCHAR)

            IF @streak = @last
               AND NOT @streak = 0
              BEGIN
                  SET @streakCount = @streakCount + 1

                  UPDATE [Table_1]
                  SET    [rStreak] = 'Concurrent Streak:'
                                     + Cast(@streakCount AS VARCHAR) + '@'
                                     + Cast(@streak AS VARCHAR)
                  WHERE  [row] = @row
              END
            ELSE
              SET @streakCount = 1

            SET @last = @streak
            SET @streak = 0
        END
      ELSE
        BEGIN
            SET @streak = @current

            PRINT 'Calculating streak length:'
                  + Cast(@streak AS VARCHAR)

            UPDATE [Table_1]
            SET    [rStreak] = 'Streak:' + Cast(@streak AS VARCHAR)
            WHERE  [row] = @row
        END

      FETCH next FROM @parser INTO @row, @current
  END

CLOSE @parser

DEALLOCATE @parser 

ouputs:

Row:1 Value:0
Resetting streak of:0
Row:2 Value:1
Calculating streak length:1

(1 row(s) affected)
Row:3 Value:2
Calculating streak length:2

(1 row(s) affected)
Row:4 Value:3
Calculating streak length:3

(1 row(s) affected)
Row:5 Value:0
Resetting streak of:3
Row:6 Value:1
Calculating streak length:1

(1 row(s) affected)
Row:7 Value:2
Calculating streak length:2

(1 row(s) affected)
Row:8 Value:3
Calculating streak length:3

(1 row(s) affected)
Row:9 Value:0
Resetting streak of:3

(1 row(s) affected)
Row:10 Value:1
Calculating streak length:1

(1 row(s) affected)
Row:11 Value:2
Calculating streak length:2

(1 row(s) affected)
Row:12 Value:0
Resetting streak of:2
Row:13 Value:0
Resetting streak of:0
Row:14 Value:0
Resetting streak of:0
Row:15 Value:0
Resetting streak of:0
Row:16 Value:1
Calculating streak length:1

(1 row(s) affected)
Row:17 Value:2
Calculating streak length:2

(1 row(s) affected)
Row:18 Value:3
Calculating streak length:3

(1 row(s) affected)
Row:19 Value:0
Resetting streak of:3
Row:20 Value:1
Calculating streak length:1

(1 row(s) affected)
Row:21 Value:2
Calculating streak length:2

(1 row(s) affected)
Row:22 Value:3
Calculating streak length:3

(1 row(s) affected)
Row:23 Value:0
Resetting streak of:3

(1 row(s) affected)
Row:24 Value:1
Calculating streak length:1

(1 row(s) affected)
Row:25 Value:2
Calculating streak length:2

(1 row(s) affected)
Row:26 Value:0
Resetting streak of:2
Row:27 Value:1
Calculating streak length:1

(1 row(s) affected)
Row:28 Value:2
Calculating streak length:2

(1 row(s) affected)
Row:29 Value:0
Resetting streak of:2

(1 row(s) affected)

and yields the following data (correctly)

row streak  rStreak
1   0   NULL
2   1   Streak:1
3   2   Streak:2
4   3   Streak:3
5   0   NULL
6   1   Streak:1
7   2   Streak:2
8   3   Streak:3
9   0   Concurrent Streak:2@3
10  1   Streak:1
11  2   Streak:2
12  0   NULL
13  0   NULL
14  0   NULL
15  0   NULL
16  1   Streak:1
17  2   Streak:2
18  3   Streak:3
19  0   NULL
20  1   Streak:1
21  2   Streak:2
22  3   Streak:3
23  0   Concurrent Streak:2@3
24  1   Streak:1
25  2   Streak:2
26  0   NULL
27  1   Streak:1
28  2   Streak:2
29  0   Concurrent Streak:2@2

So hopefully that clarifies the question as a whole, I want to know can this be achieved without the cursor, requiring that I modify the original table, or use an intermediate temp table/table variable?

Essentially this model works, but it does not scale well, whereas this is simple on small data sets, when I go RBAR it is going to get very inefficient when I start processing hundreds of thousands or rows.

And thank you all for your continued input and patience. :-)

  • 2
    Would you please define what you consider to be a repetitive streak? – Forrest Jul 18 '17 at 21:05
  • 1
    Can you answer Forrest's question? I see 0,1,2,3,4,0,1,2,3,4,0,1,2,3,4 which is a streak of the same 5 numbers (0,1,2,3,4) three times. – ypercubeᵀᴹ Jul 18 '17 at 21:15
  • 1
    There's also this streak 0,1,2,3,4,0,1,2,3,4 of 10 numbers twice. (Overlapping but still, it is twice) – ypercubeᵀᴹ Jul 18 '17 at 21:16
  • 1
    @AaronBertrand I have 2008R2+ because it is pretty much the latest I can pretty much demand, I do anticipate they will most all be later, but there are still a great many 2008R2 systems out there. So any suggestions you have would still be potentially very relevant. – Sabre Jul 18 '17 at 23:54
  • 1
    @ypercubeᵀᴹ it just occurred to me that expanding on your question, a sequence would technically be terminated by 0 on both ends, if that changes it, so for the last sequence I would drop anything to the right of the last 0 so my sample would ALWAYS start with a 0 and end with 0. Again this is a process in development, not a real world system yet per se, I have an application, but I want to determine how efficiently the data may processed before I go through the trouble of building the other parts. I know I can do this outside of SQL, I am just exploring it from an SQL perspective. – Sabre Jul 19 '17 at 0:15
1

Assuming that all your sequences consist of numbers monotously growing from 0 to X without gaps you can use smth like this:

declare @t table (id int identity, col int);
insert into @t(col) values
(0),(1),(2),(3),(0),(1),(2),(0),(0),(1),(2),(3),(4),(0),(1),(2),(3),(4),(0),(1),(2),(3),(4),(5),(0),(1);

with cte as
(
select id, row_number() over(order by id) as rn
from @t
where col = 0
)

,cte1 as
(
select c1.id as id1,
       c2.id as id2,
       c2.id - c1.id as len_
from cte as c1 
     join cte as c2
        on c1.rn + 1 = c2.rn
)

,cte2 as
(
select len_, count(*) as cnt
from cte1
group by len_
having  count(*) > 1
)

select top 1 *
from cte2
order by len_ desc;

Here I use the identity column to imitate your

They are keyed by datetime, so arranging them in order to get the proper sequence is trivial

So I just count the number of members contained between 0s and assume that if the count is the same, the numbers are also the same, and then filter only those that has more than 1 occurence.

In the result the len_ is the length of the streak (maybe you need to substract 1 from it) and cnt is respective count.

If your table is big enough of course you should not use cte but save the first resultset(cte) into #tmp table

  • @sepupic, you are correct, the data is unique by datetime, so adding row_number() to get a numeric progression is completely plausible. This looks very promising, but it will be this evening before I get in front of my computer to test. I had considered self joining a CTE but had been able to figure out no way to go past the checking last/next row and not expanding out the full sequence. Much like when I had looked at lag/lead as mentioned by Aaron above. I will test and report back this evening. Very clever solution and why I still come here to see help of others, Thank you all. – Sabre Jul 19 '17 at 12:15
1

Restating the problem, to be sure I do [not] understand it correctly: given a sequence of sequences, each starting from zero and increasing by one at a time, you would like to know which sequence had the most consecutive appearances, excluding the final sequence, which may be incomplete?

I think I have that:

CREATE TABLE #Values
(
DateRecorded    DATETIMEOFFSET(0) NOT NULL PRIMARY KEY,
Value   INT NOT NULL
)
INSERT INTO #Values (DateRecorded, Value) VALUES
    ('2017-01-01', 0), ('2017-01-02', 1), ('2017-01-03', 2), ('2017-01-04', 3), ('2017-01-05', 4),
    ('2017-01-06', 0), ('2017-01-07', 1), ('2017-01-08', 2),
    ('2017-01-09', 0), ('2017-01-10', 1), ('2017-01-11', 2),
    ('2017-01-12', 0), ('2017-01-13', 1), ('2017-01-14', 2),
    ('2017-01-15', 0), ('2017-01-16', 1), ('2017-01-17', 2), ('2017-01-18', 3), ('2017-01-19', 4),
    ('2017-01-20', 0), ('2017-01-21', 1), ('2017-01-22', 2), ('2017-01-23', 3),
    ('2017-01-24', 0), ('2017-01-25', 1), ('2017-01-26', 2), ('2017-01-27', 3)

;WITH StartDates AS
    (
    SELECT DateRecorded FROM #Values WHERE Value = 0
    ),
Grps1 AS
    (
    SELECT
        St1.DateRecorded AS DateStarted, MIN(St2.DateRecorded) AS NextGroupStarted
    FROM
        StartDates AS St1
        INNER JOIN StartDates AS St2 ON St2.DateRecorded > St1.DateRecorded
    GROUP BY
        St1.DateRecorded
    ),
Grps2 AS
    (
    SELECT
        ROW_NUMBER() OVER (ORDER BY Grps1.DateStarted) AS Seq, MAX(V.Value) AS MaxValue
    FROM
        #Values AS V
        INNER JOIN Grps1 ON V.DateRecorded >= Grps1.DateStarted AND V.DateRecorded < Grps1.NextGroupStarted
    GROUP BY
        Grps1.DateStarted
    ),
Streaks AS
    (
    SELECT
        Seq, MaxValue, ROW_NUMBER() OVER (PARTITION BY MaxValue ORDER BY Seq) AS PositionInThisStreak
    FROM
        Grps2
    )
SELECT
    MaxValue, PositionInThisStreak AS NumSequencesInThisStreak
FROM
    Streaks
WHERE
    PositionInThisStreak = (SELECT MAX(PositionInThisStreak) FROM Streaks)

The output is MaxValue "2", NumSequencesInThisStreak "3".

  • This logic expects all sequences to start with zero.
  • This logic will not handle sequences with gaps. A sequence of { 0, 1, 3, 4 } and a sequence of { 0, 1, 2, 4 } would be considered equivalent: two groups ending with 4.
  • Actually that looks promising as well, You assumption is correct that there will be no gaps, 4 will never follow 2 unless something has broken horribly. It is a measure of vibration intensity, not specifically sound. As the machine will not allow going form dead stop to full on running the transition to say 4 wold always be 0[off],1,2,3,4 no matter how brief 1,2,3, may be. The "highest" assumption was a misunderstanding though. assuming a sequence of 0,1,2,0,1,2 there would be no pattern because the last 0,1,2 could have gone to three it is not a complete sequence until another has started. – Sabre Jul 19 '17 at 17:50
  • So by highest I mean we drop the highest end of the data set past the last occurrence of zero. I guess that was sort of ambiguous, I call it highest because it has the closet to current datetime, for the sake of disambiguation I should have said furthest right in a linear progression. – Sabre Jul 19 '17 at 17:52
  • It sounds like we're close, then. "Drop the highest end of the data set past the last occurrence of zero" - if sequences always start at zero, does that in practice mean we drop the last sequence? Or just the last sample? – Jon of All Trades Jul 19 '17 at 18:14
  • Given 0,1,2,3,4,0,1,2 this means essentially we caught it running, and that we do not have the complete last set. It could stop at 2 with 0,1,2,3,4,0,1,2,0 eventually or it may go to something higher like 0,1,2,3,4,0,1,2,3,4,5,0 before it is off again. so since I do not know what that last set will end up being, and I am grabbing by datetime, I have to assume I may have picked a range that neither started with zero or ended with one, that means I have to discard everything pre and post first zero and last zero respectively. I can only analyze sets, and a set is from on to off again 0=>0. – Sabre Jul 19 '17 at 18:46
  • On note, that I can handle getting the right data set, whatever ends up being the end result I can filter appropriately, it is the parsing through for sequence counts that had me befuddled. – Sabre Jul 19 '17 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.