CODEWITHSUNDEEP
sql
IttayD
IttayD

Reputation: 29163

count 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

The timestamps are consecutive, but don't have any frequency. I want to count, per IP, the number of times the score changed. so in the example the result would be:

ip      count
1.2.3.4 3
5.6.7.8 1

How can I do that? (note: count distinct does not work: 1.2.3.4 changed 3 times but had 2 distinct scores)

Upvotes: 3

Views: 289

Answers (3)

PerformanceDBA
PerformanceDBA

Reputation: 33818

Although this requirement is not common, it is not rare either. Basically, you need to determine when there is a change in the data column.

The data is Relational, therefore the solution is Relation. No Cursors or CTEs or ROW_NUMBER()s or temp tables or GROUP BYs or scripts or triggers are required. DISTINCT will not work. The solution is straight-forward. But you have to keep your Relational hat on.

    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

I note that for the data you have given, the output should be:

    ip      count
    1.2.3.4     2
    5.6.7.8     0

  • if you have been counted the last score per ip, which hasn't changed yet, then your figures will by "out-by-1". To obtain your counts, delete that last line of code.

  • if you have been counting an stated 0 as a starting value, add 1 to the COUNT().

If you interested in more discussion of the not-uncommon problem, I have given a full treatment in this Answer.

Upvotes: 1

jarlh
jarlh

Reputation: 44795

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

Upvotes: 2

ThePravinDeshmukh
ThePravinDeshmukh

Reputation: 1923

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

enter image description here

Upvotes: -2

Related Questions