Select random row from a PostgreSQL table with weighted row probabilities

by

This should do the trick:

WITH CTE AS (
    SELECT random() * (SELECT SUM(percent) FROM YOUR_TABLE) R
)
SELECT *
FROM (
    SELECT id, SUM(percent) OVER (ORDER BY id) S, R
    FROM YOUR_TABLE CROSS JOIN CTE
) Q
WHERE S >= R
ORDER BY id
LIMIT 1;

The sub-query Q gives the following result:

1  50
2  85
3  100

We then simply generate a random number in range [0, 100) and pick the first row that is at or beyond that number (the WHERE clause). We use common table expression (WITH) to ensure the random number is calculated only once.

BTW, the SELECT SUM(percent) FROM YOUR_TABLE allows you to have any weights in percent – they don’t strictly need to be percentages (i.e. add-up to 100).

[SQL Fiddle]

More Related Contents:

  1. Select rows which are not present in other table
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Aggregate columns with additional (distinct) filters
  5. Check if value exists in Postgres array
  6. Insert data in 3 tables at a time using Postgres
  7. How to use ANY instead of IN in a WHERE clause?
  8. Is there a shortcut for SELECT * FROM?
  9. sql statement error: “column .. does not exist”
  10. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  11. sql ORDER BY multiple values in specific order?
  12. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  13. PostgreSQL: Which Datatype should be used for Currency?
  14. Sorting null values after all others, except special
  15. Reference alias in WHERE clause
  16. NOT NULL constraint over a set of columns
  17. ERROR: functions in index expression must be marked IMMUTABLE in Postgres
  18. Why postgres returns unordered data in select query, after updation of row?
  19. postgresql – sql – count of `true` values
  20. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  21. Creating temporary tables in SQL
  22. PostgreSQL where all in array
  23. Conditional SQL count
  24. Redundant data in update statements
  25. Parallel unnest() and sort order in PostgreSQL
  26. Function with SQL query has no destination for result data
  27. Postgres INTERVAL using value from table
  28. Truncate timestamp to arbitrary intervals
  29. Conditional lead/lag function PostgreSQL?
  30. Is there a postgres CLOSEST operator?

Leave a Comment