MySQL按两列分组并选择第三列的最大值 [英] Mysql group by two columns and pick the maximum value of third column

问题描述

我有一个表，其中包含user_id，item_id和interact_type作为列.interact_type可以是0、1、2、3、4或5.但是，对于某些user_id和item_id对，我们可能具有多个interact_type.例如，我们可能有:

I have a table that has user_id, item_id and interaction_type as columns. interaction_type could be 0, 1,2,3,4 or 5. However, for some user_id and item_id pairs, we might have multiple interaction_types. For example, we might have:

user_id   item_id    interaction_type
2            3            1
2            3            0
2            3            5
4            1            0
5            4            4
5            4            2  

我想要的是仅在存在多个倍数的情况下才保留最大的interact_type.所以我想要这个:

What I want is to only keep the maximum interaction_type if there are multiples. So I want this:

user_id   item_id    interaction_type
2            3            5
4            1            0
5            4            4

这是我为此编写的查询:

Here is the query I wrote for this purpose:

select user_id, item_id, max(interaction_type) as max_type
from mytable
group by user_id, item_id;

但是结果很奇怪.例如，在原始表中，我有100000行，其中interact_type = 5，但在结果表中，我只有2000行.这是怎么可能的，因为最大值将在每个包含5的比较之间选择5，因此我不应减少5在结果表中.

But the result is weird. For example, in the original table I have 100000 rows with interaction_type=5 but in the result table I have only 2000. How is this possible as the max will pick 5 between every comparison that contains 5 and therefore I shouldn't have fewer 5 in the result table.

推荐答案

您的查询很好.之所以要获得2000行，是因为每对唯一值 user_id ， item_id 都获得一行.

Your query is fine. The reason you are getting 2000 rows is because you are getting one row for every unique pair of values user_id, item_id.

如果要查看每一行的交互类型，请使用:

If you want to see the interaction types going into each row then use:

select user_id, item_id, max(interaction_type) as max_type,
       group_concat(distinct interaction_type) as interaction_types,
       count(*) as cnt
from mytable
group by user_id, item_id;

在我看来，您想要所有具有最大交互类型的行.如果是这样，请计算最大值，然后找到与该值匹配的所有行:

It occurs to me that you want all rows with the maximum interaction type. If so, calculate the maximum and then find all rows that match that value:

select t.*
from mytable t cross join
     (select max(interaction_type) as maxit from mytable) x
     on x.maxit = t.interaction_type;

此查询不需要分组依据.

这篇关于MySQL按两列分组并选择第三列的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！