Thanks, the solution would work for fixed interval timestamp.
But the data I am dealing with has irregular timestamp so can not be generated with exact steps.
I would consider this a special case/method of random sampling, evenly distributed sampling according to the defined timestamp index.
On Mon, Jan 25, 2016 at 3:48 AM, Vik Fearing <vik@2ndquadrant.fr> wrote:
On 01/25/2016 05:09 AM, Tom Smith wrote: > Hello: > > I have a big table with that is always appended with new data with a unique > sequence id (always incremented, or timestamp as unique index) each row. > I'd like to sample, say 100 rows out of say 1000 rows evently across all > the rows, > so that it would return rows of1, 101, 201, 301 you get idea. > can TABLESAMPLE get one row for every 100 rows, based on the order > of the rows added to table using the timestamp as already indexed/sorted > sequence
No, TABLESAMPLE is intended to take a random sampling of the data using various methods.
You're looking for something more like this:
select t.* from generate_series(1, (select max(id) from t), 100) g join t on t.id = g; -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
WITH data_cte as ( SELECT id, clock_timestamp() as ctimestamp FROM generate_series(1,1000) as id ) SELECT * FROM (SELECT id, ctimestamp, row_number() OVER (ORDER BY ctimestamp) as rownum FROM data_cte ) as data_withrownumbers WHERE rownum%100=1;
