CODEWITHSUNDEEP
sqldatabasepostgresql
MapsBySteve
MapsBySteve

Reputation: 63

Select random from SQL table which evenly distributes across a column's different values

I have a table USERS which holds demographic information. For brevity, lets say one of the columns AGE can have one of five values (16, 17, 18, 19, 20).

I would like an SQL query which gives me say, 100 random selections from USERS which are evenly distributed across the different ages, so I'd get approximately (or exactly) 20 entries where AGE=16, 20 entries where AGE=17, and so on.

I will at some point need to distribute across multiple columns (eg. results with even distribution across AGE and RACE and INCOME) but need to at least start by knowing how to evenly distribute across one column.

Upvotes: 2

Views: 850

Answers (2)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Check this.

        with first_random as
        (
          SELECT * FROM users where age = '16'  OFFSET floor(random()) LIMIT 20
        )
        , second_random as
        (
          SELECT * FROM users where age = '17'  OFFSET floor(random()) LIMIT 20
        )
        , third_random as
        (
          SELECT * FROM users where age = '18'  OFFSET floor(random()) LIMIT 20
        ), fourth_random as
        (
          SELECT * FROM users where age = '19'  OFFSET floor(random()) LIMIT 20
        )
        , Fifth_random as
        (
          SELECT * FROM users where age = '20'  OFFSET floor(random()) LIMIT 20
        )

        select * from first_random 
        union
        select * from second_random
        union
        select * from third_random
        union
        select * from fourth_random
        union
        select * from fifth_random

also check live demo Here

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656596

(SELECT * FROM users WHERE age = 16 ORDER BY random() LIMIT 20)
UNION ALL
(SELECT * FROM users WHERE age = 17 ORDER BY random() LIMIT 20)
UNION ALL 
...

All parentheses are required.

Possible performance optimization for big tables depends on exact details of your setup and requirements.

Related:

Upvotes: 2

Related Questions