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

提问者：小点点

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

我正在尝试优化一个查询，这个查询（不必要地）在一个表中计算将近90万行，这花费了太长的时间。

该表包含发生在web应用程序不同部分的事件的日志条目，我想知道当每种日志类型的行计数为1000或更少时，该类型存在多少未读日志条目，但如果计数为1001或更多，则最多计算1001行。

之后我就不需要再数了，我只会为那个日志类型输出“超过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

在本例中，我的当前查询如下所示:

选择log_type，从my_logs中将(*)计数为未读，其中is_read=0 GROUP BY log_type；

该查询对每一行进行计数，并给出每种日志类型的正确行数。问题是，当表包含90万行时，这是一个昂贵的查询，每种类型的行数超过1000行是完全没有必要的，因为用户不会在意1 000和20 000之间的差别，他们只会看到很多条目。

这是我得到的最接近解决方案（限制调整为适合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行时如何停止计数，但不要考虑到我需要的分组。

有人知道解决办法吗？

共1个答案

匿名用户

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

你要找的答案是基于一个“侧向表表达式”。这是在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行。