Clever Geek Handbook

Counting changes based on timestamp

I have a table

timestamp ip score
1432632348 1.2.3.4 9
1432632434 5.6.7.8 8
1432632447 1.2.3.4 9
1432632456 1.2.3.4 8
1432632460 5.6.7.8 8
1432632464 1.2.3.4 9

Timestamps are sequential but have no frequency. I want to count, per IP, the number of times the score has changed. so in the example, the result will be:

ip count
1.2.3.4 3
5.6.7.8 1

How can i do this? (note: counter does not work: 1.2.3.4 changed 3 times but has 2 different indicators)

+3


source to share


3 answers 


select ip,
       sum(case when score <> (select t2.score from table t2
                               where t2.timestamp = (select max(timestamp) from table
                                                     where ip = t2.ip
                                                       and timestamp < t1.timestamp)
                                 and t1.ip = t2.ip) then 1 else 0 end)
from table t1
group by ip


+2


source


While this requirement is not common, it is also not uncommon. Basically, you need to determine when there is a change in the data column.

The data is Relational, so the solution is Relation. No cursors or CTEs or ROW_NUMBER()s

temp tables GROUP BYs

or scripts or triggers. DISTINCT

will not work. The solution is straightforward. But you must keep your Relational hat.

    SELECT  COUNT( timestamp )
        FROM (

        SELECT  timestamp,
                ip,
                score,
                [score_next] = (
            SELECT  TOP 1
                    score               -- NULL if not exists
                FROM MyTable
                WHERE ip        = MT.ip
                AND   timestamp > MT.timestamp
                )

            FROM MyTable MT

            ) AS X

        WHERE score      != score_next  -- exclude unchanging rows
        AND   score_next != NULL

Note that for the data you gave, the output should be:



    ip      count
    1.2.3.4     2
    5.6.7.8     0

  • if you have calculated the last ip score that has not changed yet, then your numbers will be "out-by-1". To get your bill, remove the last line of code.

  • if you counted declared 0

    as initial value add 1

    inCOUNT().

If you are interested in a more detailed discussion of a non-unusual problem, I have given the full treatment in this answer .

+1


source 


select ip ,
       count(distinct score) 
 from YourTable 
 group by ip

enter image description here

-2


source






More articles:


All Articles