python - sqlite - 如何通过整数值根据概率选择行？

Question

在我的数据库中，我有一个乐队列表和一个流行度列，当用户在网页上分别按下喜欢或不喜欢按钮时，该列会增加或减少。我想根据这个人气栏来选择乐队。选择波段的概率取决于这个流行度列，它是一个整数值，而不是像 0.3、0.1 这样的小数值，如果使用概率，这应该有意义，但就我而言，我不认为这是可能的。我的表格示例:

Bands        probability
Led Zeppelin    79
Megadeth         4
Queen           37
Aerosmith       20
Guns N Roses   103

据此，Guns N' Roses的入选几率应该是最高的，而Megadeth的入选几率是最低的，而其他乐队也各有各的入选几率。我将从 2000 个乐队中选出 10 个。

Answer 1

首先，计算 cumulative probability对于每个波段(排序顺序是任意的；您也可以使用一些 ID):

SELECT Band, 
       CAST((SELECT sum(probability)
             FROM Bands AS b2
             WHERE b2.Band <= Bands.Band
            ) AS FLOAT) /
       (SELECT sum(probability) FROM Bands)
       AS CumProb
FROM Bands
ORDER BY Band;

Band             CumProb       
---------------  ---------------
Aerosmith        0.0823045267489
Guns N Roses     0.5061728395061
Led Zeppelin     0.8312757201646
Megadeth         0.8477366255144
Queen            1.0            

(只要 SQLite 还没有窗口函数，用 Python 进行求和会更有效率。但是对于 2000 行，这并不重要。)

然后使用 0 到 1 之间的随机数查找其中一行(等于或大于的第一行):

WITH CPBands(Band, CumProb) AS (
  SELECT Band, 
         CAST((SELECT sum(probability)
               FROM Bands AS b2
               WHERE b2.Band <= Bands.Band
              ) AS FLOAT) /
         (SELECT sum(probability) FROM Bands)
  FROM Bands
)
SELECT Band
FROM CPBands
WHERE CumProb >= ?
ORDER BY CumProb ASC
LIMIT 1;

根据需要重复多次，忽略重复项。