srsk February 2016

Count rows with more than 10 seconds between each other in SQLSRV PHP

I want to count the rows with time difference of >= 10 secs. I have very few knowledge on SQLSRV syntax.

My table

-----------------table_1-------------------

id  item_id  ts                     user_id
13  8        2015-02-08 10:39:43    1
19  8        2015-02-08 10:42:30    1
23  7        2015-02-08 10:44:04    1
24  7        2015-02-08 10:44:08    1
15  8        2015-02-08 10:40:05    1
17  8        2015-02-08 10:40:20    1
18  8        2015-02-08 10:40:23    2
20  7        2015-02-08 10:42:59    1
25  8        2015-02-08 10:47:05    2
26  8        2015-02-08 10:47:11    2
27  8        2015-02-08 10:48:37    2
28  7        2015-02-08 10:49:14    2
29  7        2015-02-08 10:50:25    2
30  7        2015-02-08 10:50:29    2

Current Output:

item_id  count
8        8
7        6

My DESIRED output (Select rows with more than 10 secs between eacg other based on item_id and user_id):

item_id  count
8        8
7        4

Current code in PHP:

<?php
    $countr = "SELECT item_id,COUNT(*) as count FROM dbo.table_1
    group by item_id
    order by count(*) desc";

$p = array("");
$o =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$e = sqlsrv_query( $conn, $countr, $p, $o);

...
?>

Thank you

Answers


tarheel February 2016

I believe there are a few things that need further clarification:

  1. What SQL Server version are you on? (2005, 2008R2, 2012, etc.)
  2. What are you sorting by(order by)? Should all of the records in table_1 be in chronological order by ts, or something else?
  3. What are you partitioning by(partition by)? If after getting table_1 sorted properly, should I be concerned with the number of seconds between the two ts values if user_id, or item_id values change? (Example: id 27 and 28 are 37 seconds apart, but should that matter considering that item_id is 8 for id 27 and item_id is 7 for id 28)

That said, I will give you a solution that will evaluate the example records of table_1 assuming 3 things:

  1. That you are on SQL Server 2012 or newer.
  2. That you do not care about any partition by functionality, and that you want to assume a NULL value for the first (or last) row where you do not have a second ts value to compare against.
  3. That you want to sort by ts in chronological order.

SOLUTION:

    use your_db
    ; with lead as
      (
        select a.sort_id
        , a.id
        , a.item_id
        , a.ts
        , a.[user_id]
        , lead(a.ts, 1, NULL) over (order by a.sort_id asc) as ts_lead
        from table_1 as a       
      ), 
    diff as
      (
        select l.sort_id
        , l.id
        , l.item_id
        , l.ts
        , l.[user_id]
        , l.ts_lead
        , datediff(s, l.ts, l.ts_lead) as ts_diff
        from lead as l
      )
    select d.item_id
    , count(*) as cnt
    from diff as d
    where 1=1
    and d.ts_diff >= 10
   

Post Status

Asked in February 2016
Viewed 3,385 times
Voted 6
Answered 1 times

Search




Leave an answer