CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Furqan
Furqan

Reputation: 55

Every 10th row based on timestamp

I have a table with signal name, value and timestamp. these signals where recorded at sampling rate of 1sample/sec. Now i want to plot a graph on values of months, and it is becoming very heavy for the system to perform it within seconds. So my question is " Is there any way to view 1 value/minute in other words i want to see every 60th row.?"

Upvotes: 0

Views: 105

Answers (2)

Andomar
Andomar

Reputation: 238256

Instead of sampling, you could use the one minute average for your plot:

select  name
,       min(timestamp)
,       avg(value)
from    Yourtable
group by
        name
,       datediff(minute, '2013-01-01', timestamp)

If you are charting months, even the hourly average might be detailed enough.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You can use the row_number() function to enumerate the rows, and then use modulo arithmetic to get the rows:

select signalname, value, timestamp
from (select t.*,
             row_number() over (order by timestamp) as seqnum
      from table t
     ) t
where seqnum % 60 = 0;

If your data really is regular, you can also extract the seconds value and check when that is 0:

select signalname, value, timestamp
from table t
where datepart(second, timestamp) = 0

This assumes that timestamp is stored in an appropriate date/time format.

Upvotes: 2

Related Questions