Product() 聚合函数

2022-01-07 00:00:00 statistics sql oracle

在解释 CTE 的一些概念时，被问到一个可爱的问题..我们能找到行的乘法吗，而我们总是专注于新人的 SUM().这让我想到了！是否可以仅使用 SQL.我也在考虑我们甚至可以支持的最大数字精度，因为产品可能非常庞大.

While explaining some concepts over CTE, was asked with a cute question.. can we find the multiplication of rows , while we always concentrate on the SUM() from a fresher. That gave me a thought! Is it ever possible with just an SQL. I was also considering the maximum number precision we can even support, since the product may be so huge.

话虽如此，我们不能编写自己的聚合函数.(我们可以吗?)我在想仅使用 SQL 是否可行.

Having said, we cant write our own aggregate function.(can we?) I am thinking is it ever possible with SQL only.

我认为 2*3 就像将 2 、 3 次添加到自身.. 但是当集合很大.. 我不能让它成为可能，因为它可能很麻烦.

I thought of 2*3 is like adding 2 , 3 times to itself.. but when the set is huge.. I cant make it possible as it may be cumbersome.

另一种可能性是 log (a+b) = log a * log b; 和 Anti logarithm，给你结果.这是我设法回应的.对于 Oracle，我会有一个使用 EXECUTE IMMEDIATE 的函数.

Aonther possibility is log (a+b) = log a * log b; and Anti logarithm, gives you the result. This is what i managed to respond. For Oracle, I would have a function using EXECUTE IMMEDIATE.

我们真的可以乘以自己吗?SQL-ly哪个会更漂亮..这是纯学习经验.

Can we actually multiply itself. Which will be more beautiful SQL-ly.. This is for pure learning experience.

推荐答案

Oracle 已经公开了这些 ODCI(Oracle Data Cartridge Interface)方法来做聚合！参考:

Oracle has exposed these ODCI (Oracle Data Cartridge Interface) methods to do the Aggregate! Reference:

  • ODCIAggregateDelete() - 从当前组中删除一个输入值.
  • ODCIAggregateInitialize() - 初始化聚合上下文和实现对象类型的实例，并将其作为 OUT 参数返回.
  • ODCIAggregateIterate() - 通过处理输入值、更新然后返回聚合上下文来遍历输入行.
  • ODCIAggregateMerge() - 在用户定义的聚合的串行或并行评估期间，将两个聚合上下文合并到一个对象实例中.
  • ODCIAggregateTerminate() - 计算聚合计算的结果并执行所有必要的清理，例如释放内存.
  • ODCIAggregateWrapContext() 集成当前聚合上下文的所有外部部分，使上下文自包含.

PRODUCT() 聚合函数的代码:

CREATE OR REPLACE type PRODUCT_IMPL
AS
  object
  (
    result NUMBER,
    static FUNCTION ODCIAggregateInitialize(sctx IN OUT PRODUCT_IMPL)
    RETURN NUMBER,
    member FUNCTION ODCIAggregateIterate(self  IN OUT PRODUCT_IMPL,
                                         value IN NUMBER)
    RETURN NUMBER,
    member FUNCTION ODCIAggregateTerminate( self IN PRODUCT_IMPL,
                                            returnValue OUT NUMBER,
                                            flags IN NUMBER)
    RETURN NUMBER,
    member FUNCTION ODCIAggregateMerge(self IN OUT PRODUCT_IMPL,
                                       ctx2 IN PRODUCT_IMPL )
    RETURN NUMBER );
  /
  /* 1.Initializes the computation by initializing the aggregation context—the rows over which aggregation is performed: */
CREATE OR REPLACE type body PRODUCT_IMPL
IS
  static FUNCTION ODCIAggregateInitialize(sctx IN OUT PRODUCT_IMPL)
  RETURN NUMBER
IS
BEGIN
  sctx := PRODUCT_IMPL(1);
  RETURN ODCIConst.Success;
END;
/* 2.Iteratively processes each successive input value and updates the context: */
member FUNCTION ODCIAggregateIterate(self  IN OUT PRODUCT_IMPL,
                                     value IN NUMBER)
  RETURN NUMBER
IS
BEGIN
  self.result := value * self.result;
  RETURN ODCIConst.Success;
END;
member FUNCTION ODCIAggregateTerminate(
    self IN PRODUCT_IMPL,
    returnValue OUT NUMBER,
    flags IN NUMBER)
  RETURN NUMBER
IS
BEGIN
  returnValue := self.result;
  RETURN ODCIConst.Success;
END;
member FUNCTION ODCIAggregateMerge(self IN OUT PRODUCT_IMPL,
                                   ctx2 IN PRODUCT_IMPL)
  RETURN NUMBER
IS
BEGIN
  self.result := self.result;
  RETURN ODCIConst.Success;
END;
END;
/

/* Create A function using the PRODUCT_IMPL implementation we did above */
CREATE OR REPLACE FUNCTION product(input NUMBER)
RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING PRODUCT_IMPL;
/

结果:

SELECT group_name,product(num) FROM product_test GROUP BY group_name;

Mahesh  -60000
Mahesh_1    9

相关文章