SQL中的表分析（乘法） [英] Table analysis in SQL (multiplication)
问题描述
我有 TABLE_A ，需要从中创建 TABLE_A_FINAL 。
规则：
在 TABLE_A_FINAL 中，我们具有包含 ID_C 的所有可能组合的行，如果在 ID_C 的相同组合，我们将 WEIGHT 的值相乘。
这在SQL中可能吗？谢谢您的帮助和建议。
I have TABLE_A and i need to create TABLE_A_FINAL from it.
Rule:
In TABLE_A_FINAL we have rows with all possible combinations of ID_C and if in TABLE_A is same combination of ID_C we multiply the value of WEIGHT.
Is this possible in SQL? Thanks for help and advices.
TABLE_A
ID_N | WEIGHT | ID_C |
1 | 1.15 | 1A |
2 | 1.13 | 1A |
3 | 1.65 | 1B |
4 | 1.85 | 2A |
6 | 1.57 | 2A |
TABLE_A
ID_N |WEIGHT |ID_C |
1 |1.15 | 1A |
2 |1.13 | 1A |
3 |1.65 | 1B |
4 |1.85 | 2A |
6 |1.57 | 2A |
TABLE_A_FINAL
ID_C | FINAL_WEIGHT |
1A | 1.15×1.13 = 1.2995 |
1B | 1.65 |
2A | 1.85×1.57 = 2.9045 | |
TABLE_A_FINAL
ID_C |FINAL_WEIGHT |
1A |1.15×1.13 = 1.2995 |
1B |1.65 |
2A |1.85×1.57 = 2.9045 |
推荐答案
不幸的是，Postgres没有 product（）聚合函数。您可以使用 sum（）和日志/幂运算来模拟此类函数。因为您的值似乎都为正且非零：
Unfortunately, Postgres doesn't have a product() aggregation function. You can emulate such a function with sum() and logs/exponentiation. Because your values all seem to be positive and non-zero:
select id_c, exp(sum(ln(weight)) as final_weight
from table_a
group by id_c;
如果为零或负数字，那么逻辑就比较复杂了，但仍然很有可能。
If you have zeros or negative numbers, then the logic is rather more complicated, but still quite possible.
这篇关于SQL中的表分析（乘法）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
