mysql - "Other results"饼图数据

Question

例如，假设我有一个表，其中包含人名列表以及他们来自的国家/地区的 ID。如果我要制作一个饼图来显示每个国家有多少人，并且我展示了所有这些人，那么饼图上的部分太多了，以至于几乎无法区分线条。

假设查询看起来像这样:

SELECT COUNT(name) AS n, countryId
FROM persons
GROUP BY countryId
ORDER BY n DESC

如果我通过在末尾添加 LIMIT 20 将此查询中的结果数限制为前 20 个，那将使整个饼图更具可读性，因为它只会显示 20师。但是它的视觉表示是不正确的。整个饼图将不再代表所有人，它只代表前 20 个国家。

因此，如果我希望饼图的整个圆圈实际上代表数据库中的所有人，我想要的是饼图的第 21 部分相当于，比方说 14%，并且将包含属于不在前 20 名中的任何国家/地区的所有人员。

我的问题是，这可以在单个查询中完成吗？如果是这样，怎么办？

我知道我可以在这里做另一个查询，计算来自不在前 20 名中的国家/地区的结果数量，但如果可能的话，我试图避免对该解决方案进行两次查询。

SELECT COUNT(name) AS n
FROM persons 
WHERE countryId NOT IN ( {$concatenatedIdsFromPreviousQuery} )

Answer 1

我会使用您的第一个查询并在应用程序代码中决定要单独显示多少个国家，以及将多少个国家显示到“其他”组中。例如，如果结果集中的前三个国家/地区包含 95% 的用户群，则该图表将包含 17 个国家/地区，其饼图切片将无法区分，因为它们的百分比低于 1%。

您可以对单独显示的国家/地区数量或它们的百分比设置阈值，但无论哪种方式，您从数据库中获得的实际值在某些情况下都会使图表看起来很糟糕。

免责声明

这个答案是作为概念证明提供的。我不建议在生产代码中使用它，原因有两个:

• 由于内部查询，它很慢；在空闲服务器上处理一个 376 万行的表需要 1.06 秒；根据您的环境和应用程序，这个时间可能是可以接受的或很长；
• 不够灵活；见上面的解释。

代码

让我们分两步构建查询。首先，让我们编写一个查询，以绝对值和百分比的形式生成图表中显示的数字:

SELECT countryId, COUNT(*) AS cnt,
    COUNT(*)*100/(SELECT COUNT(*) FROM persons) AS percent
FROM persons
GROUP BY countryId
ORDER BY cnt DESC

此查询计算每个国家/地区的人数，并计算该值与表中总人数的比率，以百分比表示。

一些说明:

• 内部查询 SELECT COUNT(*) FROM persons 计算表中的人数。需要计算百分比。它可以在单独的查询中提取，并将其值存储在变量中:

  SELECT COUNT(*) INTO @total FROM persons;
  SELECT countryId, COUNT(*) AS cnt,
      COUNT(*)*100/@total AS percent
  FROM persons
  GROUP BY countryId
  ORDER BY cnt DESC
  

  但它不会缩短执行时间。 MySQL 足够聪明，只运行一次内部查询并将结果用于外部查询(它自己在内部执行变量技巧，因为内部查询不依赖于外部查询，它总是返回相同的值)。

• 因为它乘以 100，所以以 percent 计算的值是要在图表中显示的最终值。对于通常的处理，最好将比率计算为 COUNT(*)/@total，并且仅当该值显示在 UI 中时才将该值乘以 100。

第二步

我们现在可以处理上述查询生成的结果集，将列表底部的国家聚合到一个组中:

SELECT IF(percent < 5, -1, countryId) AS groupId,
    IF(percent < 5, 1, 0) AS isGroup,
    SUM(cnt) AS cnt, SUM(percent) AS percent
FROM (
    SELECT countryId, COUNT(*) AS cnt,
        COUNT(*)*100/(SELECT COUNT(*) FROM persons) AS percent
    FROM persons
    GROUP BY countryId
) t
GROUP BY groupId
ORDER BY isGroup ASC, percent DESC

说明

查询使用 5% 的阈值来决定国家是单独显示还是添加到“其他国家/地区”组中。

groupId 是 -1 对于“其他国家”组或图表中显示的国家的 countryId。

isGroup(1 或 0)用于获取列表末尾的组，在独立国家(ORDER BY isGroup ASC).然后，独立国家列表按 percent DESC 排序。 SELECT列表中不需要isGroup，可以直接将其定义替换到ORDER BY子句中。

额外

如果您在源代码中有国家列表，那么这就是您的查询。但是，如果您在数据库的表中有列表，那么您可能也需要获取他们的名字。加入countries表，查询变为:

SELECT IF(percent < 5, '-- other countries --', c.countryName) AS groupId,
    SUM(cnt) AS cnt, SUM(t.percent) AS percent
FROM (
    SELECT p.countryId, COUNT(*) AS cnt,
        COUNT(*)*100/(SELECT COUNT(*) FROM persons) AS percent
    FROM persons p
    GROUP BY countryId
) t
LEFT JOIN countries c ON t.countryId = c.countryId
GROUP BY groupId
ORDER BY IF(t.percent < 5, 1, 0) ASC, percent DESC

加入countries 表不会显着增加执行时间。世界上大约有 200 个国家，countries 表非常小。