如何计算包含子类别的相关行？

inn6fuwd 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(369)

我在postgres12.3数据库中有一些表。
第一个被命名为 category :

id|template_id|name           |entry_count|is_base_template|can_rename|can_delete|section|userId|parentCategoryId|
--|-----------|---------------|-----------|----------------|----------|----------|-------|------|----------------|
 1|           |Notes          |          0|true            |true      |true      |A      |      |                |
 2|           |ToDo           |          0|true            |true      |true      |A      |      |                |
 3|          1|Notes          |          0|false           |true      |true      |A      |     1|                |
 4|          2|ToDo           |          0|false           |true      |true      |A      |     1|                |
 5|           |Must Do        |          0|false           |          |          |A      |      |               4|
 6|           |Important notes|          0|false           |          |          |A      |      |               3|

第二个表称为 entry -与眼前的问题无关。
还有一个链接表 category_entries_entry :

categoryId|entryId|
----------|-------|
         4|      1|
         5|      5|
         5|      6|
         4|      7|
         3|      8|
         6|      9|

一个类别可以拥有孩子，如果 parentCategoryId 如果不是空的，那么我们要处理的是一个孩子。例如，具有 id = 5 是的子类别 id = 4 . 孩子不能有自己的孩子，所以只能有一个层次的筑巢。
我需要计算每个类别的条目数，包括子类别。
这个请求基本上满足了我的需要。但不考虑用户：

SELECT COALESCE(c."parentCategoryId" , c.id) as cat_id , COUNT(*) as entries_in_cat
FROM category c JOIN
     category_entries_entry r
     ON c.id = r."categoryId" 
WHERE c.is_base_template = false
GROUP BY cat_id;

退货：

cat_id|entries_in_cat|
------|--------------|
     4|             4|
     3|             2|

这个 category 表也有 userId 并且只应对给定的用户执行计数。值得注意的是，只有根类别才有 userId .
我想列出子类别和它们的计数。所以期望的输出 userId = 1 对于给定的样本：

cat_id|entries_in_cat|
------|--------------|
     5|             2|
     4|             4|
     6|             1|
     3|             2|

下面是一个分解：
1）类别6是第三个类别的子类别，它有一个条目，因此结果是正确的。
2）类别3是一个类别（也就是说，它没有父类别），它包含1个条目，另一个条目应该来自第6个子类别，即总共2个。脚本返回错误的1。
3）类别编号5是第4个类别的子类别，它包含2个条目。您的脚本还返回2，这是正确的。
4）类别编号4是一个类别，它有两个自己的条目，另外两个来自第5个子类别，总共4个。脚本返回2，这是错误的。它应该返回4。
我怎样才能做到这一点？

sql postgresql Aggregate postgresql-12

来源：https://stackoverflow.com/questions/62350754/how-to-count-related-rows-including-subcategories

2条答案

按热度按时间

thtygnil1#

这就完成了单级嵌套的工作：
要仅列出根类别，计数包括子类别：

WITH root AS (
   SELECT id AS cat_id, id AS sub_id
   FROM   category
   WHERE  is_base_template = false
   AND    "userId" = 1
   )
SELECT c.cat_id, count(*)::int AS entries_in_cat
FROM  (
   TABLE root
   UNION ALL
   SELECT r.cat_id, c.id
   FROM   root     r
   JOIN   category c ON c."parentCategoryId" = r.cat_id
   ) c
JOIN   category_entries_entry e ON e."categoryId" = c.sub_id
GROUP  BY c.cat_id;

关键是加入 sub_id ，但分组依据 cat_id .
要列出如上所述的根类别以及其他子类别，请执行以下操作：

WITH root AS (
   SELECT id AS cat_id, id AS sub_id
   FROM   category
   WHERE  is_base_template = false
   AND    "userId" = 1
   )
, ct AS (
   SELECT c.cat_id, c.sub_id, count(*)::int AS ct
   FROM  (
      TABLE root
      UNION ALL
      SELECT r.cat_id, c.id AS sub_id
      FROM   root     r
      JOIN   category c ON c."parentCategoryId" = r.cat_id
      ) c
   JOIN   category_entries_entry e ON e."categoryId" = c.sub_id
   GROUP  BY c.cat_id, c.sub_id
   )
SELECT cat_id, sum(ct)::int AS entries_in_cat
FROM   ct
GROUP  BY 1

UNION ALL
SELECT sub_id, ct
FROM   ct
WHERE  cat_id <> sub_id;

db<>在这里摆弄
对于任意数量的嵌套级别，请使用递归cte。例子：
如何在多对多关系中链接行，其中类型有父母、祖父母等
关于可选短语法 TABLE parent :
是否有选择*的快捷方式？

赞(0）回复(0）举报 2021-08-09

ss2ws0br2#

因为过滤只应用于父类别，所以您需要首先将其应用于父类别：然后才能获得子类别。一旦有了父类别和子类别，就可以将它们加入到条目中并对它们进行计数。
总之，查询应该如下所示：

with
c as (
  SELECT id
  FROM category
  WHERE userId = 1 AND is_base_template = false
),
s as (
  SELECT d.id
  FROM c
  JOIN category d on d.parentCategoryId = c.id
)
SELECT u.id, count(*) as entries_in_cat
FROM (select id from c union select id from s) u
JOIN category_entries_entry r ON u.id = r."categoryId" 
GROUP BY u.id

赞(0）回复(0）举报 2021-08-09

我来回答

如何计算包含子类别的相关行？

2条答案

相关问题

热门标签

最新问答