满足条件时调整SQL的AVG与COUNT函数——评论站点开发问题

Got it, let's tweak your SQL to account for banned reviews so your averages and counts reflect only valid feedback, while also tracking how many reviews are marked as Banned.

First, I'll assume your reviews table has a column (like review_status) that marks reviews as 'Banned'—adjust the column name if yours is different (e.g., is_banned as a boolean). Here are two clean approaches:

Option 1: Filter Within Aggregate Functions (Single Query) #

This method keeps all reviews in the main query but only includes non-Banned entries in your average and count calculations, while separately counting banned reviews:

$qa = $db->prepare("
    SELECT 
        -- Calculate average rating using only non-Banned reviews
        (
            AVG(CASE WHEN review_status != 'Banned' THEN review_service_rating END) + 
            AVG(CASE WHEN review_status != 'Banned' THEN review_price_rating END)
        ) AS average_rating,
        -- Count valid service ratings (non-Banned)
        COUNT(CASE WHEN review_status != 'Banned' THEN review_service_rating END) AS num_service_rating,
        -- Count valid price ratings (non-Banned)
        COUNT(CASE WHEN review_status != 'Banned' THEN review_price_rating END) AS num_price_rating,
        -- Total number of Banned reviews
        COUNT(CASE WHEN review_status = 'Banned' THEN 1 END) AS num_banned_reviews
    FROM reviews 
    WHERE assigned_id = :review_id
");

Option 2: Filter First with WHERE (More Efficient) #

If most reviews are valid, filtering non-Banned entries upfront reduces the data processed in aggregates. We use a subquery to get the banned count separately:

$qa = $db->prepare("
    SELECT 
        (AVG(review_service_rating) + AVG(review_price_rating)) AS average_rating,
        COUNT(review_service_rating) AS num_service_rating,
        COUNT(review_price_rating) AS num_price_rating,
        -- Subquery to count Banned reviews for the same assigned_id
        (SELECT COUNT(*) FROM reviews WHERE assigned_id = :review_id AND review_status = 'Banned') AS num_banned_reviews
    FROM reviews 
    WHERE assigned_id = :review_id
      AND review_status != 'Banned'
");

Key Notes: #

• Replace review_status with your actual column name (e.g., if using a boolean is_banned, change the condition to is_banned = 0 for non-Banned).
• Both options ensure your average_rating and count metrics only consider valid reviews, while giving you the total banned count you need.
• Option 2 is generally faster for large datasets since it reduces the number of rows processed in the main aggregate calculations.

内容的提问来源于stack exchange，提问作者user2293381