Обсуждение: TABLESAMPLE usage
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
Thanks
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
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.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
On 25 January 2016 at 09:55, Tom Smith <tomsmith1989sk@gmail.com> wrote:
I would consider this a special case/method of random sampling, evenly distributed sampling according to the defined timestamp index.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.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
Hi,
you can accomplish this with row_number():
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;
Bye,
Matija LesarOn 01/25/2016 09:55 AM, Tom Smith wrote: > 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. You could probably create your own sampling method to do what you want. See contrib modules tsm_system_rows and tsm_system_time for guidance. http://www.postgresql.org/docs/current/static/tsm-system-rows.html http://www.postgresql.org/docs/current/static/tsm-system-time.html -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On 25 January 2016 at 09:44, Matija Lesar <matija.lesar@gmail.com> wrote:
--
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;
You can, but its not very fast.
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Yeah. I am looking for fastest possible method that Postgresql would
use its internal data structure knowledge to walk through the timestamp index
and resturns every "nth" row
On Mon, Jan 25, 2016 at 5:56 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
On 25 January 2016 at 09:44, Matija Lesar <matija.lesar@gmail.com> wrote:
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;You can, but its not very fast.--Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services