SQL中的表分析（乘法）

本文介绍了SQL中的表分析（乘法）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有 TABLE_A ，需要从中创建 TABLE_A_FINAL 。

规则：
在 TABLE_A_FINAL 中，我们具有包含 ID_C 的所有可能组合的行，如果在 TABLE_A 是 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中的表分析（乘法）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！