count changes based on timestamp

Question

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)

you could do self join `on a.ip = b.ip and a.score <> b.score` — Milen, May 26 '15 at 09:35
Depending on the table size, I would consider droping the table to a csv, sort them and then use awk to count the changes — dcarneiro, May 26 '15 at 09:40
I still can not understand why it changed 3 times? It was 9 then it became 9... is this change? — Giorgi Nakeuri, May 26 '15 at 10:04
Elaborate on where there is change and where is not. It is not clear. What is timestamp and when does it come in play? — Giorgi Nakeuri, May 26 '15 at 10:09
(a) The count for what you have described for 1.2.3.4 is 2, not 3. (b) `DISTINCT` and changes are two different things. — PerformanceDBA, May 26 '15 at 10:12
The op doesn't even bother about eleborating on his question. Why the question is upvoted at all? — Giorgi Nakeuri, May 26 '15 at 10:33
@IttayD . . . The question is unclear. The text says "score changes", yet the sample data suggests that the logic is just one less than the number of appearances. — Gordon Linoff, May 26 '15 at 10:47
a) The timestamps given are increasing, not consecutive. (b) please review the answers, comment, ask question vote up, choose, etc. — PerformanceDBA, May 26 '15 at 15:26

score 2 · Accepted Answer · answered May 26 '15 at 09:49

2

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

answered May 26 '15 at 09:49

jarlh

42,561
8
45
63

Plus 1 for solution in one query. It has a really bad performance though. – 4thfloorstudios May 26 '15 at 09:57
In Sql Server throws exception: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. – Suraj Shrestha May 26 '15 at 09:58
Ha ha, it may be slow, but I don't know dbms, or amount of data, or index/keys. – jarlh May 26 '15 at 09:59
1

@4thfloorstudios. Performance of subqueries and derived tables are just fine in commercial SQL platforms. The freeware is a different story – PerformanceDBA May 26 '15 at 10:16
PerformanceDBA: Yep, problem won't be the subqueries but the GROUP BY, jarlh: That's true! Assumed SQL based DB :) – 4thfloorstudios May 26 '15 at 12:28

score 1 · Answer 2 · edited May 23 '17 at 12:23

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.

score -2 · Answer 3 · answered May 26 '15 at 09:38

-2

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

enter image description here

answered May 26 '15 at 09:38

ThePravinDeshmukh

1,823
12
21

count changes based on timestamp

3 Answers3