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

Question

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.


Show source
| database   | postgresql   | sql   2016-12-29 05:12 2 Answers

Answers ( 2 )

  1. 2016-12-29 05:12
    (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:

  2. 2016-12-29 06:12

    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

◀ Go back