是否可以在所需的循环之外创建数据库调用？

Question

我正在尝试按照使用数据库中的数据创建的平均值来显示和排序数组。我从数据库中检索三个变量，并根据这些值创建平均值。然后将该值放入一个新数组中，以便与数据库数据的其余部分一起排序。

将SQL查询放在循环中不是一个好主意，这样的想法对吗？(性能问题？)

有没有可用的替代方案？我附加了下面的代码：

    ^ database connection/query string to retrieve all data...
$result = $stmt_business_list->fetchAll(PDO::FETCH_ASSOC);

$items = array();
    foreach($result as $row){

    $single_business_id = $row['id'];
    $name = $row['name'];
    //Query to get ALL the service, value and quality ratings for certain business
    $test_query = "SELECT * FROM rating WHERE business_id = $single_business_id";
    $test_query_stmt = $dbh->prepare($test_query);
    $test_query_stmt->execute();
    $test_row = $test_query_stmt->fetchAll(PDO::FETCH_ASSOC);
    $total_value = $total_quality = $total_service = 0;

        foreach($test_row as $review)
        {
            $total_value += $review['value'];
            $total_quality += $review['quality'];
            $total_service += $review['service'];
        }

    $bayesian_value = (($set_site_average_review_count * $set_site_average_review_score) + $total_value) / ($set_site_average_review_count + $business_review_count);
    $bayesian_quality = (($set_site_average_review_count * $set_site_average_review_score) + $total_quality) / ($set_site_average_review_count + $business_review_count);
    $bayesian_service = (($set_site_average_review_count * $set_site_average_review_score) + $total_service) / ($set_site_average_review_count + $business_review_count);
    $average_bayesian_rating = ($bayesian_value + $bayesian_quality + $bayesian_service) / 3;
    $average_bayesian_rating = $average_bayesian_rating;

        array_push($items, array(
        "id"=>"$single_business_id",
        "name"=>"$name",
        "value"=>"$total_value",
        "quality"=>"$total_quality",
        "service"=>"$total_service",
        "average"=>"$average_bayesian_rating"));

    echo 
    'Name: '.$name.'<br>
    Value: '.$total_value.'<br>
    Quality: '.$total_quality.'<br>
    Service: '.$total_service.'<br>
    Average: '.$average_bayesian_rating.'<br><br>';
    }
}

页面将被单独的分页脚本拆分，一次只能显示6个对象，但随着时间的推移，这种情况可能会改变，因此我会尽可能多地关注性能。

Answer 1

SQL聚合查询就是针对这类事情进行的。

使用此查询汇总结果

 SELECT b.name, b.id,
        SUM(value)   total_value,
        SUM(quality) total_quality,
        SUM(service) total_service,
        COUNT(*)     review_count,
        avg_reviews_per_biz
   FROM business b
   JOIN ratings r ON b.id = r.business_id
   JOIN (
          SELECT COUNT(DISTINCT business_id) / COUNT(*) avg_reviews_per_biz
            FROM ratings
        ) a ON 1=1                     
  GROUP BY b.name, b.id, avg_review_per_biz

这将为每个业务提供一行，显示总评分和评分数量。此结果集将包含以下列

name            business name
  id            business id
  total_value   sum of value ratings for that business
  total_quality sum of quality ditto
  total_service sum of service ditto
  review_count  number of reviews for business "id"
  avg_reviews_per_biz   avg number of reviews per business 

对于查询的所有行，最后一列具有相同的值。

然后，您可以遍历这些行，一次处理一项业务，进行统计计算。

我不能从你的问题中看出你是从哪里得到像$set_site_average_review_count这样的变量的，所以我无法帮助你进行这些计算。

您会发现SQL聚合查询确实非常强大。