mySQL 将数字数据与同一张表中的最后一张图像组合在一起

Question

我有一个包含数字数据和图像(剂量 react 曲线)的细胞活力数据表。该表可以包含同一化合物的多行(由批处理 ID 唯一)。每行都有一个唯一的 ID，以及一个日期字段。现在我想按批处理 ID 对数据进行分组并生成平均 EC50 值，但将其与为每个化合物 Batch_ID 生成的最后剂量 react 曲线一起显示。下面的代码将为特定化合物 Batch_ID 选择遇到的第一条曲线。我怎样才能选择最后一个，但仍将其与平均 EC50 一起显示？任何提示表示赞赏!

SELECT Batch_ID, avg(EC50), Curve FROM CELL_VIABILITY GROUP BY Batch_ID

示例数据:

ID  Batch_ID    EC50    Curve   Date
1   ABC123  6.72    blob_1  18-06-15
2   ABC123  4.74    blob_2  18-07-10
3   ABC123  8.72    blob_3  18-08-22
4   DEF456  1.95    blob_4  18-06-15
5   DEF456  1.66    blob_5  18-07-10
6   DEF456  3.06    blob_6  18-08-22

预期结果:

Batch_ID    EC50    Curve
ABC123     6.73    blob_3
DEF456     2.22    blob_6

Answer 1

请记住，数据是无序集。如果不定义特定的顺序，我们无法确定什么是最后的、第一个等等。

我们可以使用Date 列来定义订单。最新更新的条目(最大日期值)可以被视为“最后”。

然后我们可以使用 Correlated Subquery确定特定 Batch_ID 的 Last Curve。

SELECT cv1.Batch_ID, 
       AVG(cv1.EC50), 
       MAX((SELECT cv2.Curve 
           FROM CELL_VIABILITY AS cv2 
           WHERE cv2.Batch_ID = cv1.Batch_ID
           ORDER BY cv2.Date DESC LIMIT 1)) AS Last_Curve
FROM CELL_VIABILITY AS cv1
GROUP BY Batch_ID

---

另一种方法是使用派生表。我们可以获得每个 Batch_ID 的最后一个 Date 值。然后使用日期的最大值“自连接”到表以获得最后的 Curve:

SELECT 
  cv1.Batch_ID, 
  cv1.average, 
  cv2.Curve
FROM 
(
 SELECT Batch_ID, 
        AVG(EC50) AS average, 
        MAX(Date) AS last_date 
 FROM CELL_VIABILITY
 GROUP BY Batch_ID
) AS cv1 
JOIN CELL_VIABILITY AS cv2 
  ON cv2.Batch_ID = cv1.Batch_ID AND 
     cv2.Date = cv1.last_date