如何从非规范化表计算非虚增计数 [英] How to calculate a non-inflated count from a denormalized table
问题描述
假设我有一个非规范化表,该表包含一个ID和一个我需要计数的值。像这样的东西:
Suppose I have a denormalized table that includes an ID and a value that I need to count. Something like this:
Tree_ID | ...other columns... | Count_If_True
------------------------------------------------
1 | ...other values... | True
1 | ...other values... | True
2 | ...other values... | True
2 | ...other values... | True
3 | ...other values... | True
在这种情况下,从表组中选择Tree_ID,count(Count_If_True)由Tree_ID
显示:
Tree_ID | count(Count_If_True)
---------------------------------
1 | 2
2 | 2
3 | 1
但是如果我通过来自 Apples的联接进一步规范化我的表
表(其中每棵树都有多个苹果),它看起来像这样:
But If I denormalize my table further with a join from an Apples
table (where every tree has multiple apples), it would look something like this:
Apple_ID | Tree_ID | ...other columns... | Count_If_True
------------------------------------------------
1 | 1 | ...other values... | True
2 | 1 | ...other values... | True
3 | 1 | ...other values... | True
4 | 1 | ...other values... | True
5 | 1 | ...other values... | True
6 | 1 | ...other values... | True
7 | 2 | ...other values... | True
8 | 2 | ...other values... | True
9 | 2 | ...other values... | True
10 | 2 | ...other values... | True
11 | 2 | ...other values... | True
12 | 2 | ...other values... | True
13 | 2 | ...other values... | True
14 | 2 | ...other values... | True
15 | 3 | ...other values... | True
16 | 3 | ...other values... | True
17 | 3 | ...other values... | True
18 | 3 | ...other values... | True
19 | 3 | ...other values... | True
这会将我们的计数
膨胀为:
This would inflate our count
to:
Tree_ID | count(Count_If_True)
---------------------------------
1 | 6
2 | 8
3 | 5
是否有一种简单的方法(例如,没有CTE)编写单个查询以获取返回引入 Apple_ID
之前的原始计数结果?
Is there a simple way (without a CTE, for example) to write a single query to get back the original count result before Apple_ID
s were introduced?
推荐答案
您在第一个表中需要一个唯一的行标识符-也许在其他列中。它可以是一列或多列。然后,您可以使用 count(distinct)
:
You need a distinct row identifier in the first table -- perhaps that is among the other columns. It can be one or more columns. Then you can use count(distinct)
:
select tree_id,
count(distinct <unique row column>) filter (where count_if_true)
from t
group by tree_id;
这篇关于如何从非规范化表计算非虚增计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!