21–30 of 37 posts
This not a random selection. It has the same issues as the 1-dimensional case, and will not sample uniformly. Points inside a cluster of other points will be less likely to be picked than points that are in a relatively empty region of space.
This not a random selection. It has the same issues as the 1-dimensional case, and will not sample uniformly. Points inside a cluster of other points will be less likely to be picked than points that are in a relatively empty region of space.
It's not about the randomness of the results, it's about optimizing the speed of the original query.
This not a random selection. It has the same issues as the 1-dimensional case, and will not sample uniformly. Points inside a cluster of other points will be less likely to be picked than points that are in a relatively empty region of space.
Not to mention that points near the edge are 50% less likely to be picked (or 75% less likely if they're near a corner). You can fix this by looping the space around once you reach the edge but good luck expressing that in SQL. You could also fix it by putting them on a sphere I think, though picking a random point on a sphere is exactly the easiest thing to do.
Earlier quoted context omitted.
Your randomness may also sometimes be at the edge. I am the author of the article, if you do know any failure in my approach i am happy to fix it.
The benefit of this solution is that we don't want to depend on SQL to generate unlimited random numbers: one for each row of the table is a lot of work. While this approach does have this benefit, it's no longer "random" in the sense that all rows are equally likely to be selected. So, it depends on your purpose as to whether this is an acceptable solution. I don't see any reason to do it in 2D over 1D, except that…
if you have thousands of rows, it doesn't really matter if one is 3 times as likely to show up as another one, as long as the odds aren't stacked too heavily towards any one row (at least for what I assume to be the typical use case of showing users a random product/page/whatever)
Edit: https://gist.github.com/alecco/9976dab8fda8256ed403054ed0a65... I think using a range of rows is overkill, at least for row-stores. And also in the majority of cases random rows are preferred than a range. In the case where the table has a simple Primary Key the query is easier. Select all the valid PKs (rows) ordered by random and then limit. SQLite gives access to the rowid making this query even simpler and…
Wouldn't you typically solve this kind of problem with rejection sampling? Pick a random ID - if it's already in the result set or deleted, try again. I suppose this cannot be neatly expressed in SQL though?
Another approach would be to create a table with a small sample of the main one and refresh it periodically -- each query just needs to do a random reordering on the small sample, and the results will be truly random over time.
Earlier quoted context omitted.
The benefit of this solution is that we don't want to depend on SQL to generate unlimited random numbers: one for each row of the table is a lot of work. While this approach does have this benefit, it's no longer "random" in the sense that all rows are equally likely to be selected. So, it depends on your purpose as to whether this is an acceptable solution. I don't see any reason to do it in 2D over 1D, except that…
i think mostly because this is even more complicated than the approach taken, and the proposed solution is good enough. If you do have large gaps in your dataset you can also account for them by just blocking a range of numbers ahead of time, optionally with a monthly cron job to identify new gaps larger than X rows, depending on how often new such gaps form. if you have thousands of rows, it doesn't really matter if…
I understand that SQL is considered simpler if you have code-golfed it into one line, but the procedure of simple random sampling is much simpler than generating a 2D map of points and finding the nearest one.
In MediaWiki, the "Random page" function works using an indexed page_random column, see https://m.mediawiki.org/wiki/Manual:Page_table#page_random For a large enough wiki, the potential non-uniformness is negligible, I guess. (Unless your RNG is wrong, see https://phabricator.wikimedia.org/T208909 )
The best solution in this article seems quite convoluted and not very portable. Is there a better way? Edit: I doubt it’s uniform as well because rows at the edges are less likely to be selected.