In PostgreSQL, How do I random sample from table based on the proportion of each level in a categorical column?

Question

For example, if I have a column called companyId and many other columns I want to keep, and in companyId I have values like 100, 101, 102, ..., basically a list of Ids and each Id appear different number of times. How do I randomly sample data based on the companyId column so that it's according to the proportion of each Id?

eg: If I have 500 rows and 100 companyA, 100 companyB and 300 companyC and I want to sample 100 rows from this table. How do I make my data have 20 companyA, 20 companyB and 60 companyC?

already answered in stackoverflow please refer stackoverflow.com/questions/1124603/… — Pradeep Kumar
– Pradeep Kumar, Commented Mar 2, 2018 at 4:01

Laurenz Albe · Accepted Answer · 2018-03-02 08:56:47Z

17

You should use the TABLESAMPLE clause available since PostgreSQL 9.5, described in the SELECT documentation.

There are two built-in table sampling methods: SYSTEM and BERNOULLI. Both try to get a random sample, but the SYSTEM method picks table blocks and uses the data therein, while BERNOULLI uses a random sample from the whole table. SYSTEM is faster, but BERNOULLI offers a more evenly distributed sample.

You have to specify the percentage of the rows that you want to get.
If you want a fifth of your table, you would use:

SELECT * FROM mytable TABLESAMPLE BERNOULLI (20);

If your requirement is to get a certain number of result rows, you might consider using the tsm_system_rows contrib module, which offers the SYSTEM_ROWS table sample method that works similar to SYSTEM, but you can specify the number of result rows.

answered Mar 2, 2018 at 8:56

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vlad Over a year ago

does this answer the question? The OP wants to know how to sample "20 companyA, 20 companyB and 60 companyC?"

Laurenz Albe Over a year ago

You are right, it is an approximation. I assumed that these numbers were just for illustration and represent an estimate rather than hard requirements (note that the numbers are proportional to the frequency with which the values occur in the table).

Vlad Over a year ago

thanks for that note, it does help understanding how tablesample works.

nightblade9 Feb 27 at 18:05

You can also use LIMIT n if you only want to receive N rows back. Also note that TABLESAMPLE SYSTEM, on semi-sized data sets (234k rows) sometimes returns zero rows for small sample values e.g. 0.1 or 0.01 or even 0.8

Collectives™ on Stack Overflow

In PostgreSQL, How do I random sample from table based on the proportion of each level in a categorical column?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related