Product()聚合函数 [英] Product() aggregate function

问题描述

在通过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上更美丽的方式..这是纯粹的学习经验.

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

推荐答案

Oracle公开了这些参考:

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

这篇关于Product()聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！