What is the required mysql query for the random post generator by rating?

Question

MySQL

Please tell me how to generate RANDOM posts based on their ratings?
For example, I have 1000 posts, one RANDOM post should be displayed on the page from the database, while the one with the higher rating has more chances to be displayed on the page.

As far as I understand, Tik-Tok, Instagram, YouTube, etc. work according to this principle. I want to do something similar in terms of logic.

Answer 1

Well, sort of.

WITH cte AS (
    SELECT *, SUM(rating) OVER (ORDER BY id) cum_rating
    FROM table
)
SELECT
FROM cte t1
JOIN (SELECT RAND() * MAX(cum_rating) rnd_rating
      FROM cte) t2 ON t2.rnd_rating BETWEEN t1.cum_rating - t1.rating AND t1.cum_rating

Really, of course, not BETWEEN, but two inequalities, on the upper bound - strict.