从每组中选择一列中具有相同值且在另一列中具有最大值的组中的平均值

Question

我一直在努力寻找答案，但似乎没有人遇到与我类似的问题，所以我决定将其发布在这里。我有一个包含 200 条记录的表，列中有重复值fix_id，在列时间戳中，每条记录的值范围为 1 到 5，在最后一列中我得到了年龄值。我喜欢为列中具有相同 id 的每个组选择fix_id同时在时间戳列中保持最大值的年龄列的平均值，并且有一个棘手的问题是年龄列有时可以具有值 0，在这种情况下，我喜欢跳过这个值。 fix_id   timestamp  age  10         2        0  10         2        2  10         4        0  10         4        1  10         4        3  5          4        2  5          4        4  5          3       10所以从这张表中，我想得到这个结果 fix_id    timestamp   age  10          4         2  5           4         3因此，如果年龄列中有 0，我不想在计算平均值时包括它。这可能吗？

Answer 1

一种方法是带过滤的聚合查询：

select fix_id, timestamp, avg(age)

from t

where age > 0 and

      timestamp = (select max(t2.timestamp) from t t2 where t2.fix_id = t.fix_id)

group by fix_id;

Answer 2

如果您在查询中设置条件，例如：

WHERE age > 0

0那么您将错过最大时间戳仅在列中包含 sage并且您不会fix_id在结果中得到它的情况。

所以使用条件聚合：

SELECT t.fix_id, t.timestamp, 

       AVG(CASE WHEN t.age > 0 THEN t.age END) average_age

FROM tablename t

WHERE t.timestamp = (SELECT MAX(timestamp) FROM tablename WHERE fix_id = t.fix_id)

GROUP BY t.fix_id, t.timestamp

请参阅演示。

结果：

| fix_id | timestamp | average_age |

| ------ | --------- | ----------- |

| 5      | 4         | 3           |

| 10     | 4         | 2           |