查询:每个项目计数多个聚合 [英] Query: count multiple aggregates per item

问题描述

通常，您需要显示数据库项目的列表以及有关每个项目的某些汇总编号.例如，当您在堆栈溢出"中键入标题文本时，将出现相关问题"列表.该列表显示了相关条目的标题以及每个标题的响应数量的单个汇总数量.

Often you need to show a list of database items and certain aggregate numbers about each item. For instance, when you type the title text on Stack Overflow, the Related Questions list appears. The list shows the titles of related entries and the single aggregated number of quantity of responses for each title.

我有一个类似的问题，但是需要多个聚合.我想根据用户选项以3种格式中的任何一种显示项目列表:

I have a similar problem but needing multiple aggregates. I'd like to display a list of items in any of 3 formats depending on user options:

• 我的物品的名称(总共15个，我拥有13个)
• 我的物品的名称(共15个)
• 我的物品的名称(我拥有13个)

我的数据库是:

• 项目:itemId，itemName，ownerId
• 类别:catId，catName
• 地图:mapId，itemId，catId

• items: itemId, itemName, ownerId
• categories: catId, catName
• map: mapId, itemId, catId

下面的查询获取:类别名称，每个类别的项目ID的计数

The query below gets: category name, count of item ids per category

SELECT
  categories.catName,
  COUNT(map.itemId) AS item_count
FROM categories
LEFT JOIN map
  ON categories.catId = map.catId
GROUP BY categories.catName

此获取:类别名称，仅此owner_id的每个类别的项目ID的计数

This one gets: category name, count of item ids per category for this owner_id only

SELECT categories.catName, 
COUNT(map.itemId) AS owner_item_count
FROM categories
LEFT JOIN map
  ON categories.catId = map.catId
LEFT JOIN items
  ON items.itemId = map.itemId
WHERE owner = @ownerId
GROUP BY categories.catId

但是如何在单个查询中同时获取它们?即:类别名称，仅此owner_id的每个类别的项目ID的计数，每个类别的项目ID的计数

But how do i get them at the same time in a single query? I.e.: category name, count of item ids per category, count of item ids per category for this owner_id only

奖金.我怎样才能有选择地只检索其中任何一个的catId count！= 0?在尝试"WHERE item_count<> 0"时，我得到:

Bonus. How can I optionally only retrieve where catId count != 0 for any of these? In trying "WHERE item_count <> 0" I get:

MySQL said: Documentation
#1054 - Unknown column 'rid_count' in 'where clause' 

推荐答案

这是一个技巧:计算已知值为1或0的值的SUM()等效于该值的行的COUNT()是1.您知道布尔比较返回1或0(或NULL).

Here's a trick: calculating a SUM() of values that are known to be either 1 or 0 is equivalent to a COUNT() of the rows where the value is 1. And you know that a boolean comparison returns 1 or 0 (or NULL).

SELECT c.catname, COUNT(m.catid) AS item_count,
  SUM(i.ownerid = @ownerid) AS owner_item_count
FROM categories c
  LEFT JOIN map m USING (catid)
  LEFT JOIN items i USING (itemid)
GROUP BY c.catid;

关于奖励问题，您可以简单地执行内部联接而不是外部联接，这意味着将仅返回在map中具有至少一行的类别.

As for the bonus question, you could simply do an inner join instead of an outer join, which would mean only categories with at least one row in map would be returned.

SELECT c.catname, COUNT(m.catid) AS item_count,
  SUM(i.ownerid = @ownerid) AS owner_item_count
FROM categories c
  INNER JOIN map m USING (catid)
  INNER JOIN items i USING (itemid)
GROUP BY c.catid;

这是另一种解决方案，虽然效率不高，但我将向您展示它来解释为什么会出现错误:

Here's another solution, which is not as efficient but I'll show it to explain why you got the error:

SELECT c.catname, COUNT(m.catid) AS item_count,
  SUM(i.ownerid = @ownerid) AS owner_item_count
FROM categories c
  LEFT JOIN map m USING (catid)
  LEFT JOIN items i USING (itemid)
GROUP BY c.catid
HAVING item_count > 0;

您不能在WHERE子句中使用列别名，因为WHERE子句中的表达式先于选择列表中的表达式求值.换句话说，与选择列表表达式关联的值尚不可用.

You can't use column aliases in the WHERE clause, because expressions in the WHERE clause are evaluated before the expressions in the select-list. In other words, the values associated with select-list expressions aren't available yet.

您可以在GROUP BY，HAVING和ORDER BY子句中使用列别名.这些子句在对select-list中的所有表达式求值之后运行.

You can use column aliases in the GROUP BY, HAVING, and ORDER BY clauses. These clauses are run after all the expressions in the select-list have been evaluated.

这篇关于查询:每个项目计数多个聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！