首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >SQL查询对按ID分组的行进行计数,但对每个组进行限制计数

问SQL查询对按ID分组的行进行计数,但对每个组进行限制计数
EN

Stack Overflow用户
提问于 2019-11-14 21:17:56
回答 2查看 1K关注 0票数 2

所以我有个不寻常的要求。我在处理一张有数十亿行的桌子。

该表有一个列'id‘,它不是唯一的,并且有一个列'data’。

我想要做的是对按“id”分组的行数进行计数,但将计数限制在仅150个条目。我只需要知道给定的id是否有150行。

这是为了优化查询和性能。

不一定要算。我只需要知道给定的id是否有150个条目,而不需要MySQL在查询期间继续计算条目。如果这有意义的话。

我知道如何计数,我知道如何分组,我知道如何两者都做,但计数将返回一个数以百万计的数字,这是浪费的处理时间,查询需要在成千上万的ids上运行。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2019-11-14 21:19:19

你不能为这个优化性能--我不认为。

代码语言:javascript
复制
select id, (count(*) >= 150)
from t
group by id;

如果您碰巧有一个单独的表,每个id有一行,t(id)上有一个索引,那么这可能会更快:

代码语言:javascript
复制
select ids.id,
       ((select count(*)
         from t
         where t.id = ids.id
        ) >= 150
       )
from ids;

不幸的是,MySQL不支持对相关子查询进行双重嵌套,因此这是不可能的:

代码语言:javascript
复制
select ids.id,
       ((select count(*)
         from (select 1
               from t
               where t.id = ids.id
               limit 150
              ) t
        ) >= 150
       )
from ids;

如果是这样,这可能会更快。

编辑:

如果您在id上有一个索引,并且只需要有150个或更多的ids,那么变量可能会更快:

代码语言:javascript
复制
select id,
       (@rn := if(@id = id, @rn + 1,
                  if(@id := id, 1, 1)
                 )
       ) as rn
from (select id
      from t
      order by id
     ) t cross join
     (select @id := 0, @rn := 0) params
having rn = 150;

这里的想法是,使用索引对表进行排序、物化和再次扫描可能比group by更快。我不认为row_number()具有相同的性能特征。

编辑二:

上面的一个细微的变化可以用来获得带标志的所有ids:

代码语言:javascript
复制
select id, (max(id) = 150)
from (select id,
             (@rn := if(@id = id, @rn + 1,
                        if(@id := id, 1, 1)
                       )
             ) as rn
      from (select id
            from t
            order by id
           ) t cross join
           (select @id := 0, @rn := 0) params
      having rn in (1, 150)
     ) t
group by id;

编辑三:

啊!如果您有一个单独的ids表,那么这可能是最好的方法:

代码语言:javascript
复制
select ids.id,
       (select id
        from t
        where t.id = ids.id
        limit 1 offset 149
       ) is not null
from ids;

这将从索引中获取第150行。如果它不在那里,则不会返回任何行。

票数 5
EN

Stack Overflow用户

发布于 2019-11-14 21:21:47

我觉得这不可能。您必须扫描整个表,才能知道哪个id至少有150个条目。

所以:

代码语言:javascript
复制
select id
from mytable
group by id
having count(*) >= 150

有了id上的索引,这应该是尽可能有效的。

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/58866652

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档