时间戳

首页 > 解决方案 > 获取每组的计数，但在每组中的 N 个结果行后停止计数

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

问题描述

我正在尝试优化一个查询（不必要地）计算表中近 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

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

有谁知道解决方案？

标签： mysqlmariadb

解决方案

这个答案在 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，其中 aLIMIT为 3（在本例中）。最后，查询计算z遇到的值的数量。

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

推荐阅读