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

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聚合查询确实非常强大。