单个查询，允许别名拥有自己的限制

Question

I would like to better optimize my code. I'd like to have a single query that allows an alias name to have it's own limit and also include a result with no limit.

Currently I'm using two queries like this:

// ALL TIME //
$mikep = mysqli_query($link, "SELECT tasks.EID, reports.how_did_gig_go FROM tasks INNER JOIN reports ON tasks.EID=reports.eid WHERE `priority` IS NOT NULL AND `partners_name` IS NOT NULL AND mike IS NOT NULL GROUP BY EID ORDER BY tasks.show_date DESC;"); 
$num_rows_mikep = mysqli_num_rows($mikep);
$rating_sum_mikep = 0;
    while ($row = mysqli_fetch_assoc($mikep)) {
          $rating_mikep = $row['how_did_gig_go'];
          $rating_sum_mikep += $rating_mikep;
    }
$average_mikep = $rating_sum_mikep/$num_rows_mikep;

// AND NOW WITH A LIMIT 10 //

$mikep_limit = mysqli_query($link, "SELECT tasks.EID, reports.how_did_gig_go FROM tasks INNER JOIN reports ON tasks.EID=reports.eid WHERE `priority` IS NOT NULL AND `partners_name` IS NOT NULL AND mike IS NOT NULL GROUP BY EID ORDER BY tasks.show_date DESC LIMIT 10;"); 
$num_rows_mikep_limit = mysqli_num_rows($mikep_limit);
$rating_sum_mikep_limit = 0;
    while ($row = mysqli_fetch_assoc($mikep_limit)) {
          $rating_mikep_limit = $row['how_did_gig_go'];
          $rating_sum_mikep_limit += $rating_mikep_limit;
    }
$average_mikep_limit = $rating_sum_mikep_limit/$num_rows_mikep_limit;

This allows me to show an all-time average and also an average over the last 10 reviews. Is it really necessary for me to set up two queries?

Also, I understand I could get the sum in the query, but not all the values are numbers, so I've actually converted them in PHP, but left out that code in order to try and simplify what is displayed in the code.

Answer 1

All-time average and average over the last 10 reviews

In the best case scenario, where your column how_did_gig_go was 100% numeric, a single query like this could work like so:

SELECT
      AVG(how_did_gig_go) AS avg_how_did_gig_go
    , SUM(CASE
            WHEN rn <= 10 THEN how_did_gig_go
            ELSE 0 
      END)  / 10              AS latest10_avg
FROM (
      SELECT
            @num + 1 AS rn
          , tasks.show_date
          , reports.how_did_gig_go
      FROM tasks
      INNER JOIN reports ON tasks.EID = reports.eid
      CROSS JOIN ( SELECT @num := 0 AS n ) AS v
      WHERE priority IS NOT NULL
      AND partners_name IS NOT NULL
      AND mike IS NOT NULL
      ORDER BY tasks.show_date DESC
) AS d

But; Unless all the "numbers" are in fact numeric you are doomed to sending every row back from the server for php to process unless you can clean-up the data in MySQL somehow.

You might avoid sending all that data twice if you establish a way for your php to use only the top 10 from the whole list. There are probably way of doing that in PHP.

If you wanted assistance in SQL to do that, then maybe having 2 columns would help, it would reduce the number of table scans.

SELECT
      EID
    , how_did_gig_go
    , CASE
            WHEN rn <= 10 THEN how_did_gig_go
            ELSE 0 
      END                AS latest10_how_did_gig_go
FROM (
      SELECT
            @num + 1 AS rn
          , tasks.EID
          , reports.how_did_gig_go
      FROM tasks
      INNER JOIN reports ON tasks.EID = reports.eid
      CROSS JOIN ( SELECT @num := 0 AS n ) AS v
      WHERE priority IS NOT NULL
      AND partners_name IS NOT NULL
      AND mike IS NOT NULL
      ORDER BY tasks.show_date DESC
) AS d

---

In future (MySQL 8.x) ROW_NUMBER() OVER(order by tasks.show_date DESC) would be a better method than the "roll your own" row numbering (using @num+1) shown before.