如何计算包括子类别的相关行? [英] How to count related rows including subcategories?
问题描述
我在Postgres 12.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不为NULL，那么我们正在处理孩子.例如，带有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是第3个类别的子类别，它具有1个条目，因此结果是正确的.
2)类别编号3是一个类别(也就是说，它没有父类别)，它包含1个条目，另一个应来自第6个子类别，总计为2.您的脚本返回1，这是错误的.
3)类别编号5是第4个类别的子类别，它包含2个条目.您的脚本还返回2，这是正确的.
4)类别编号4是一个类别，它有2个单独的条目，另外两个来自第5个子类别，总共4个.您的脚本返回2，这是错误的.它应该返回4.
我该如何实现?
这会完成单个嵌套级别的工作:
仅列出根类别，计数包括子类别:
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:
I've got a few tables in a Postgres 12.3 database.
The first one is named 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|
The second table is called entry - which has no bearing on the question at hand.
And there is the link table category_entries_entry:
categoryId|entryId|
----------|-------|
4| 1|
5| 5|
5| 6|
4| 7|
3| 8|
6| 9|
A category can possess children, if parentCategoryId is not NULL then we're dealing with a child. For instance, the category with id = 5 is a subcategory of id = 4. Children can't have their own children, so only one level of nesting.
I need to count the number of entries for each category including subcategories.
This request does what I need, mostly. But it does not take into account the user:
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;
Returns:
cat_id|entries_in_cat|
------|--------------|
4| 4|
3| 2|
The category table has also userId and the count should only be executed for a given user. Notably, only root categories have entries for userId.
And I want to list subcategories with their counts additionally. So the desired output with userId = 1 for the given sample is:
cat_id|entries_in_cat|
------|--------------|
5| 2|
4| 4|
6| 1|
3| 2|
Here's a break down:
1) Category number 6 is a subcategory of the 3rd category it has 1 entry, so the result is correct.
2) Category number 3 is a category (that's to say, it does not have a parent), it contains 1 entry and another one should come from the 6th subcategory, that's 2 in total. Your script returns 1 which is wrong.
3) Category number 5 is a subcategory of the 4th category, it contains 2 entries. Your script returns also 2 which is right.
4) category number 4 is a category, it has 2 entries of its own and another two come from the 5th subcategory, that's 4 in total. Your script returns 2 which is wrong. It should return 4.
How can I achieve that?
This does the job for a single level of nesting:
To list only root categories, counts include subcategories:
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;
The point is to join on sub_id, but group by cat_id.
To list root categories like above, and subcategories additionally:
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<>fiddle here
For an arbitrary number of nesting levels, use a recursive CTE. Example:
About the optional short syntax TABLE parent:
这篇关于如何计算包括子类别的相关行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
