按每个组获取计数，但在每个组中的结果行数达到N之后停止计数

Question

按每个组获取计数，但在每个组中的结果行数达到N之后停止计数

mysqlmariadb

5

我正在尝试优化一条查询，该查询在一个表中计数近900,000行数据（不必要），这需要太长时间。

该表包含不同部分Web应用程序中发生的事件的日志条目，并且当该类型的行计数为1000或更少时，我想知道每个日志类型存在多少未读日志条目，但最多计算1001行，如果计数为1001或更多，则只需输出“超过1000”即可。

之后我不需要再计数了，我将只输出该日志类型的“超过1000”。

假设我们有以下名为my_logs的表及其数据：

id    log_type    log_text   is_read
1     'Type 1'    'Text 1'   1
2     'Type 1'    'Text 2'   1
3     'Type 1'    'Text 3'   0
4     'Type 1'    'Text 4'   0
5     'Type 1'    'Text 5'   0
6     'Type 1'    'Text 6'   0
7     'Type 2'    'Text 7'   0
8     'Type 2'    'Text 8'   0

在这个例子中，我的当前查询看起来像这样: SELECT log_type, COUNT(*) AS unread FROM my_logs WHERE is_read = 0 GROUP BY log_type; 这个查询计算每一行，并且当然为每个日志类型提供了正确的行数。问题是，当表格包含900,000行时，这是一个昂贵的查询，而且计算每种类型超过1000行是完全不必要的，因为用户不会关心1000和20000之间的区别，他们只会看到很多条目。

这是我接近解决方案的方式（限制调整以适应my_logs示例并演示用法）：

SELECT log_type, COUNT(*) AS unread
FROM (
    SELECT log_type
    FROM my_logs ml1
    WHERE is_read = 0
    LIMIT 3 /* To display "more than 2" in webapp */
) AS ml2
GROUP BY logtype_txt;

但是这个查询将内部查询中的所有log_type汇总在一起，并将其限制为1001行，这不是我想要的。我需要将行拆分为每个log_type，然后计算最多1001行。在这个例子中，我想要的输出是：

log_type    unread
'Type 1'    3
'Type 2'    2

这个问题和这个问题讨论了如何在找到n行时停止计数，但没有考虑到我需要的分组。

有人知道解决方案吗？

- Adrian Wiik

澄清一下问题：您是否只需要从每种日志类型中获取1000个未读日志条目？您有多少种日志类型？ - SaidbakR

我甚至不需要日志条目；我只需要计算每种日志类型有多少未读的日志条目，并且我希望在每种日志类型找到1001个条目后停止计数（一种日志类型可能有514个条目，而另一种则在1001个时停止计数）。日志类型可以是我们突然决定使用的任何字符串，因此日志类型的数量是动态的。 - Adrian Wiik

2个回答

0

请查看MariaDB-5.5.21的LIMIT ROWS EXAMINED：

https://mariadb.atlassian.net/browse/MDEV-28

这应该正是您所要求的。

（我认为它在MySQL中不可用。）

- Rick James

很遗憾，“LIMIT ROWS EXAMINED”子句是整个语句的全局限制，无法限制“GROUP BY”中每个分组中计数行的数量。不过还是感谢您的建议。 - Adrian Wiik

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- The Impaler · Accepted Answer

这个答案在MariaDB或MySQL中不起作用。

你要寻找的答案基于“lateral table expression”，这被实现在Oracle、DB2、PostgreSQL和SQL Server中。

以下是在PostgreSQL中最优的查询，以读取自表中的行：

select x.log_type, count(y.z)
from (
  select distinct log_type as log_type
  from my_log
) x
left join lateral (
  select 1 as z
  from my_log b 
  where b.log_type = x.log_type and is_read = 0
  limit 2 + 1
) y on true
group by x.log_type

请查看 DB Fiddle 中的运行示例。

横向查询根据放置在它们之前的表达式中可用的值执行一次。在本例中，表达式x将为log_type生成所有不同的值（使用索引以提高性能）。然后，对于来自x的每个值，都会执行一次横向查询，限制为3（在本例中）。最后，查询计算遇到了多少个z值。

正如您所见，上述过程仅针对每种类型读取最多3行。