从数百万行的mysql查询中选择第n条记录

mrfwxfqh  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(458)

我有一个mysql查询,如下所示;我想在一个有1.8m条记录的表中,为每个600条记录的范围选择最上面的记录。到目前为止,我必须循环3000次来完成这不是一个有效的解决方案。
数据库模式;

Table: bet_perm_13predict
 id     bet_id      perm_id     avg_odd     avg_odd2    avg_odd3
 1      23          1           43.29       28.82       28.82
 2      23          2           42.86       28.59       28.59
 3      23          3           43.13       28.73       28.73

 Table: bet_permute_13games
 perm_id    perm_code
 1      0000000000000
 2      0000000000001
 3      0000000000002
 4      0000000000010

php中的mysql查询示例

$totRange   = 0; //Used as starting point in rang
$range      = 600; //Used as range
$stop       = 0;//Used as endPoint of range

while($totRange < 1800000){
    $stop   = $totRange+$range;

    $sql = "SELECT (tb1.avg_odd2 + tb1.avg_odd3) AS totAvg_odd ,
    tb1.perm_id , tb1.avg_odd, tb1.avg_odd2, tb1.avg_odd3, tb2.perm_code 
    FROM bet_perm_13predict tb1 
    INNER JOIN bet_permute_13games tb2 ON tb2.perm_id = tb1.perm_id
    WHERE tb1.bet_id = '$bet_id' && tb1.perm_id 
    BETWEEN $startRange AND $stop ORDER BY totAvg_odd ASC LIMIT 1"

    $q1    = $this->db->query($sql);
    $totRange = $stop;
}

换句话说,我想选择一个数据样本,它将代表整个表,样本不是随机的,而是使用范围为600的top记录预定义的。到目前为止我还不知道该怎么办。关于这个问题没有明确的网上材料。

qv7cva1a

qv7cva1a1#

对于那些可能遇到同样问题的人,我就是这样解决的。我使用了@juancarlos建议,并添加了一种使用子查询来选择组顶部记录的方法。

SELECT * FROM 
         (SELECT * , perm_id DIV $limit as grp , (avg_odd2 + avg_odd3) AS totAvg_odd 
           FROM bet_perm_13predict WHERE bet_id = '$bet_id'  ORDER BY grp ASC ) tb1
INNER JOIN bet_permute_13games tb2 ON tb2.perm_id = tb1.perm_id
INNER JOIN bet_entry tb3 ON tb3.bet_id = tb1.bet_id
WHERE tb1.avg_odd2 < (SELECT AVG(avg_odd2) FROM bet_perm_13predict WHERE bet_id = '$bet_id' )
                     && tb1.avg_odd3 < (SELECT AVG(avg_odd3) FROM bet_perm_13predict WHERE bet_id = '$bet_id' )
GROUP BY grp ORDER BY totAvg_odd ASC
LIMIT 100
ui7jx7zq

ui7jx7zq2#

可以使用整数除法创建组。
演示

SELECT ID, ID DIV 600 as grp
FROM Table1

然后找出每组的最大值。这里有一些选择
获取每组分组sql结果的最大值记录

相关问题