Sample every nth record in DB based on column

[shivraj_r]

Hey Everyone,

I'm trying to sample every nth record from a column in a DB with timestamps in it. For example if I wanted to specify that I wanted to sample every 10 seconds starting with the first row - what's the most efficient way to do this? I tried the sample in DB tool but that only allows for percentage/ number of records.

My data set looks like this -

msg id	data	timestamp_s	input_timestamp_s	cvdt35_bus_r
129	0x0000000000000000	4/24/2018 16:20	1524586807	2
129	0x0000000000000000	4/24/2018 16:20	1524586808	2
129	0x0000000000000000	4/24/2018 16:20	1524586809	2
129	0x0000000000000000	4/24/2018 16:20	1524586806	2
129	0x0000000000000000	4/24/2018 16:20	1524586810	2
129	0x0000000000000000	4/24/2018 16:20	1524586811	2
129	0x0000000000000000	8/14/2018 3:13	1534216388	2
129	0x0000000000000000	8/14/2018 3:13	1534216389	2

[MarqueeCrew · Accepted solution]

@shivraj_r,

Using either logic looking for specific seconds (e.g. in (10,20,30)) or using a MOD function (b = mod( a , m ) returns the remainder after division of a by m , where a is the dividend and m is the divisor) I would filter the data to get only the records that hit those marks.  Depending upon your database, the SQL will vary as not all SQL is the same.

Random is easier if you can assign a random number (SQL varies) and pull out the random data that matches your % or value.

Cheers,

Mark

[shivraj_r]

@MarqueeCrew

Thanks for the suggestion! I'll try it out on the sql side using the mod function!