sql – 获取n个分组的类别并将其他类别合并为一个

Contents (
  id
  name
  desc
  tdate
  categoryid
  ...
)

我需要对此表中的数据进行一些统计.例如,我希望通过该类别的分组和ID获得具有相同类别的行数.另外我想按降序将它们限制为n行,如果有更多类别可用,我想将它们标记为“其他”.到目前为止,我已经向数据库发出了2个查询：

按降序选择n行：

SELECT COALESCE(ca.NAME,'Unknown') AS label,ca.id AS catid,COUNT(c.id) AS data
FROM contents c
LEFT OUTER JOIN category ca ON ca.id = c.categoryid
GROUP BY label,catid
ORDER BY data DESC LIMIT 7

选择其他行为一：

SELECT 'Others' AS label,COUNT(c.id) AS data
FROM contents c
LEFT OUTER JOIN category ca ON ca.id = c.categoryid
WHERE c.categoryid NOT IN ($INCONDITION)

但是当我在db表中没有类别组时,我仍然得到一个“其他”记录.是否可以在一个查询中进行并使“其他”记录可选？

解决方法

您无法在WHERE子句中执行任何操作来禁止该行.您必须在事实之后,即在HAVING子句中或在外部查询中排除这样的行.

Per documentation:

If a query contains aggregate function calls,but no GROUP BY clause,
grouping still occurs: the result is a single group row (or perhaps no
rows at all,if the single row is then eliminated by HAVING). The same
is true if it contains a HAVING clause,even without any aggregate
function calls or GROUP BY clause.

应该注意的是,添加一个只包含常量表达式的GROUP BY子句(否则完全没有意义！)也可以.见下面的例子.但我宁愿不使用这个技巧,即使它很简单,便宜又简单,因为它的作用并不明显.

以下查询仅需要单个表扫描,并返回按计数排序的前7个类别.如果(且仅当)有更多类别,其余的归纳为“其他”：

WITH cte AS (
   SELECT categoryid,count(*) AS data,row_number() OVER (ORDER BY count(*) DESC,categoryid) AS rn
   FROM   contents
   GROUP  BY 1
   )
(  -- parentheses required again
SELECT categoryid,COALESCE(ca.name,data
FROM   cte
LEFT   JOIN category ca ON ca.id = cte.categoryid
WHERE  rn <= 7
ORDER  BY rn
)
UNION ALL
SELECT NULL,'Others',sum(data)
FROM   cte
WHERE  rn > 7         -- only take the rest
HAVING count(*) > 0;  -- only if there actually is a rest
-- or: HAVING  sum(data) > 0

>如果多个类别在第7/8级中具有相同的计数,则需要断开关系.在我的例子中,具有较小categoryid的类别赢得了这样的竞赛.
>括号必须包含对UNION查询的单个分支的LIMIT或ORDER BY子句.
>您只需加入前7个类别的表类别.首先聚合并在此场景中稍后加入通常会更便宜.所以不要加入CTE (common table expression)命名cte中的基本查询,只加入UNION查询的第一个SELECT,那就更便宜了.
>不确定为什么需要COALESCE.如果你有一个从contents.categoryid到category.id的外键,并且contents.categoryid和category.name都被定义为NOT NULL(就像它们可能应该的那样),那么你不需要它.

奇数GROUP BY是真的

这也可行：

...

UNION ALL
SELECT NULL,sum(data)
FROM   cte
WHERE  rn > 7
GROUP BY true;

而且我甚至可以获得稍快的查询计划.但这是一个相当奇怪的黑客……

SQL Fiddle展示全部.

相关答案以及对UNION ALL / LIMIT技术的更多解释：

> Sum results of a few queries and then find top 5 in SQL

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!