sql - SQL：每组乘以行值

Question

我有一个像这样的表“MYTABLE”：

Id    name           gr      possibility
1     artv432        1           10
2     brtu52         1           5
3     gep2           1           2
4     cert9          2           12

我需要将每组列中的值相乘possibility。这是所需的输出：

gr            possibility     
1                100             -> 10*5*2
2                 12             ->   12

更新1：

此代码不起作用。

select exp(sum(ln(Possibility)))

因为Possibility是十进制值，例如可以是0.56. 为了便于理解，我在这里使用了整数值。

标签： sqloracle

Answer 1

您可以创建用户定义的聚合函数：

CREATE OR REPLACE TYPE ProductAggregation AS OBJECT(
  value    NUMBER,

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT ProductAggregation
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT ProductAggregation,
    value       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT ProductAggregation,
    returnValue    OUT NUMBER,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT ProductAggregation,
    ctx         IN OUT ProductAggregation
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY ProductAggregation
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT ProductAggregation
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := ProductAggregation(1);
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT ProductAggregation,
    value       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    self.value := self.value * value;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT ProductAggregation,
    returnValue    OUT NUMBER,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := self.value;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT ProductAggregation,
    ctx         IN OUT ProductAggregation
  ) RETURN NUMBER
  IS
  BEGIN
    self.value := self.value * ctx.value;
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

CREATE FUNCTION product( value NUMBER )
RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING ProductAggregation;
/

然后，对于样本数据：

CREATE TABLE table_name (Id, name, gr, possibility) AS
SELECT 1, 'artv432', 1, 10.0 FROM DUAL UNION ALL
SELECT 2, 'brtu52',  1,  5.0 FROM DUAL UNION ALL
SELECT 3, 'gep2',    1,  2.0 FROM DUAL UNION ALL
SELECT 4, 'cert9',   2, 12.0 FROM DUAL UNION ALL
SELECT 5, 'abc',     3,  0.1 FROM DUAL UNION ALL
SELECT 6, 'def',     3,  0.2 FROM DUAL UNION ALL
SELECT 7, 'ghi',     4, NULL FROM DUAL UNION ALL
SELECT 8, 'jki',     4,  5.0 FROM DUAL;

查询：

SELECT gr,
       product(possibility)
FROM   table_name
GROUP BY gr;

输出：

GR	产品（可能性）
1	100
2	12
3	.02
4	5

db<>在这里摆弄