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

Question

我正在尝试优化一个查询，该查询(不必要地)计算了表中近 900 000 行，这需要太长时间。

该表包含发生在 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

在此示例中，我当前的查询如下所示:
SELECT log_type, COUNT(*) AS unread FROM my_logs WHERE is_read = 0 GROUP BY log_type;
此查询计算每一行，并为每个日志类型提供正确的行数。问题是当表包含 900 000 行时，这是一个昂贵的查询，并且计算每种类型超过 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 s 在内部查询中并将其限制为 1001 行，这不是我想要的。我需要将行拆分为每个 log_type ，然后最多计算 1001 行。在这个例子中我想要的输出是:

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

This question和 this question讨论如何在找到 n 行时停止计数，但不要考虑我需要的分组。

有谁知道解决方案？

Answer 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 中的每个值执行一次。 ，带有 LIMIT 3 个(在这种情况下)。最后查询计算了多少 z遇到的值。

如您所见，上面的过程每种类型最多只能读取 3 行。