此查询假设：
1.每只猴子至少有一个health_check和一组measurements
1.每只猴子只能有一个或零个发射器
让我们从获取最新的health_check开始。由于id和health_check_date不是按顺序排列的，我们需要获得每个monkey_id``health_check_date，然后连接回health_check表以获得id，然后连接到measurements。对于(monkey_id, health_check_date)上的索引，内部查询不需要从聚集索引读取：

SELECT
    hc.monkey_id,
    hc_max.last_check_date,
    AVG(length) AS newest_avg_length,
    AVG(width) AS newest_avg_width,
    AVG(weight) AS newest_avg_weight
FROM health_check hc
JOIN (
    SELECT monkey_id, MAX(health_check_date) AS last_check_date
    FROM health_check
    -- WHERE monkey_id = 2 /* Uncomment this line if for specific monkey */
    GROUP BY monkey_id
) hc_max
    ON hc.monkey_id = hc_max.monkey_id
    AND hc.health_check_date = hc_max.last_check_date
JOIN measurements ms
    ON hc.id = ms.health_check_id
GROUP BY hc.monkey_id;

您可以使用窗口函数实现相同的功能，但它会带来更大的开销。我将此作为一个选项，以便您可以根据您的数据对其进行测试：

SELECT
    monkey_id,
    health_check_date AS last_check_date,
    AVG(length) AS newest_avg_length,
    AVG(width)  AS newest_avg_width,
    AVG(weight) AS newest_avg_weight
FROM (    
    SELECT hc.*, ROW_NUMBER() OVER (PARTITION BY hc.monkey_id ORDER BY hc.health_check_date DESC) rn
    FROM health_check hc
) x
JOIN measurements m ON x.id = m.health_check_id
WHERE rn = 1
GROUP BY x.id;

使用上述查询之一作为派生表，然后可以通过health_check从LEFT JOIN到transmitter，以获取当前tx。

SELECT
    latest_health_check.monkey_id,
    latest_health_check.last_check_date,
    latest_health_check.newest_avg_length,
    latest_health_check.newest_avg_width,
    latest_health_check.newest_avg_weight,
    tx.channel,
    tx.offset,
    DATE(tx.attach_start) AS channel_attach_start
FROM (
    -- Derived table for the latest check.
    SELECT
        hc.monkey_id,
        hc_max.last_check_date,
        AVG(length) AS newest_avg_length,
        AVG(width) AS newest_avg_width,
        AVG(weight) AS newest_avg_weight
    FROM health_check hc
    JOIN (
        SELECT monkey_id, MAX(health_check_date) AS last_check_date
        FROM health_check
        -- WHERE monkey_id = 2 /* Uncomment this line if for specific monkey */
        GROUP BY monkey_id
    ) hc_max
        ON hc.monkey_id = hc_max.monkey_id
        AND hc.health_check_date = hc_max.last_check_date
    JOIN measurements ms
        ON hc.id = ms.health_check_id
    GROUP BY hc.monkey_id
) latest_health_check
LEFT JOIN transmitter tx
    ON EXISTS (
        SELECT 1 FROM health_check hc
        WHERE hc.id = tx.health_check_id
        AND hc.monkey_id = latest_health_check.monkey_id
    )
    AND tx.removed IS NULL;

这是你的小提琴的升级版
P.S.它只返回monkey_id 2，因为这是唯一一只在最近一次检查中有测量值的猴子。

SELECT
  m.id AS monkey_id,
  MAX(h.health_check_date) AS last_check_date,
  AVG(me.length) AS newest_average_length,
  AVG(me.width) AS newest_average_width,
  AVG(me.weight) AS newest_avg_weight,
  t.channel AS channel,
  t.offset AS tune,
  t.attach_start AS channel_attach_start
FROM
  monkeys m
 JOIN
  health_check h ON h.monkey_id = m.id
 JOIN
  measurements me ON me.health_check_id = h.id
 JOIN
  transmitter t ON t.health_check_id = h.id AND t.removed IS NULL

GROUP BY
  m.id, m.name
ORDER BY
  m.id;