Oracle SQL에 SUM 함수가 있는 것과 같은 PRODUCT 함수가 있습니까?

Oracle SQL에 SUM 함수가 있는 것과 같은 PRODUCT 함수가 있습니까?

저는 이것을 찾는 동료가 있는데, 저는 그런 것을 만난 적이 없었던 것으로 기억합니다.

그것을 시뮬레이션할 수 있는 합리적인 기술이 있습니까?

SELECT PRODUCT(X)
FROM
(
    SELECT 3 X FROM DUAL
    UNION ALL 
    SELECT 5 X FROM DUAL
    UNION ALL
    SELECT 2 X FROM DUAL
)

30을 산출할 것입니다.

select exp(sum(ln(col)))
  from table;

편집:

if col always > 0

DECLARE @a int
SET @a = 1
-- re-assign @a for each row in the result
-- as what @a was before * the value in the row
SELECT @a = @a * amount
FROM theTable

비슷한 문자열 콘캣을 수행하는 방법이 있습니다.

DECLARE @b varchar(max)
SET @b = ""

SELECT @b = @b + CustomerName
FROM Customers

여기 다른 방법이 있습니다.이것은 확실히 더 긴 방법이지만 재미있는 프로젝트의 일부였습니다.

이번 건은 학교로 다시 연락해야 해요, 하하.여기서 기억해야 할 핵심은 LOG가 지수의 역수라는 것입니다.

LOG10(X*Y) = LOG10(X) + LOG10(Y)

또는

ln(X*Y) = ln(X) + ln(Y) (표준 = 자연 로그 또는 간단히 로그 기저 10)

예
X=5 및 Y=6인 경우

X * Y = 30

ln(5) + ln(6) = 3.4

ln(30) = 3.4

e^3.4 = 30, 5 x 6도 마찬가지입니다.

EXP(3.4) = 30

그래서 위에서, 만약 5와 6이 각각 표에서 한 줄을 차지했다면, 우리는 각 값의 자연 로그를 가져와서, 그 행들을 합한 다음, 그 합의 지수를 30으로 구합니다.

아래는 SQL Server에 대한 SQL 문의 코드입니다.Oracle에서 실행하려면 약간의 편집이 필요할 수 있습니다.큰 차이가 없기를 바라지만 적어도 Oracle에 대한 CASE 진술이 같지는 않다고 생각합니다.줄의 부호가 음수인지 테스트하기 위해 추가적인 것들이 있을 것입니다.

CREATE TABLE DUAL (VAL INT NOT NULL)
INSERT DUAL VALUES (3)
INSERT DUAL VALUES (5)
INSERT DUAL VALUES (2)

    SELECT 
           CASE SUM(CASE WHEN SIGN(VAL) = -1 THEN 1 ELSE 0 END) % 2 
               WHEN 1 THEN -1 
               ELSE 1 
           END
         * CASE 
                WHEN SUM(VAL) = 0           THEN 0 
                WHEN SUM(VAL) IS NOT NULL   THEN EXP(SUM(LOG(ABS(CASE WHEN SIGN(VAL) <> 0 THEN VAL END)))) 
                ELSE NULL 
           END
         * CASE MIN(ABS(VAL)) WHEN 0 THEN 0 ELSE 1 END
        AS PRODUCT 
      FROM DUAL

튜턴스틸에 의해 받아들여진 답은 물론 정확합니다.

select exp(sum(ln(col)))
  from table;

하지만 만약에col유형의NUMBER를 사용하면 성능이 크게 향상됩니다.BINARY_DOUBLE대신.이상적으로, 당신은 그것을 가질 것입니다.BINARY_DOUBLE당신의 테이블에 있는 열, 하지만 그것이 불가능하다면, 당신은 여전히 캐스팅할 수 있습니다.col로.BINARY_DOUBLE여기에 기록한 간단한 테스트에서 100배 향상되었습니다. 이 캐스트의 경우:

select exp(sum(ln(cast(col as binary_double))))
  from table;

그것을 시뮬레이션할 수 있는 합리적인 기술이 있습니까?

한 가지 기술은 다음과 같습니다.LISTAGGproduct_product_retion 문자열을 생성합니다.XMLTABLE+GETXMLTYPE평가하기:

WITH cte AS (
  SELECT grp, LISTAGG(l, '*') AS product_expression
  FROM t
  GROUP BY grp
)
SELECT c.*, s.val AS product_value
FROM cte c
CROSS APPLY(
    SELECT *
    FROM XMLTABLE('/ROWSET/ROW/*' 
         PASSING dbms_xmlgen.getXMLType('SELECT ' || c.product_expression || ' FROM dual')
         COLUMNS val NUMBER PATH '.')
) s;

db<>디플 데모

출력:

+------+---------------------+---------------+
| GRP  | PRODUCT_EXPRESSION  | PRODUCT_VALUE |
+------+---------------------+---------------+
| b    | 2*6                 |            12 |
| a    | 3*5*7               |           105 |
+------+---------------------+---------------+

---

그룹에서 단일 NULL 값을 처리하는 강력한 버전:

WITH cte AS (
  SELECT grp, LISTAGG(l, '*') AS product_expression
  FROM t
  GROUP BY grp
)
SELECT c.*, s.val AS product_value
FROM cte c
OUTER APPLY(
   SELECT *
   FROM XMLTABLE('/ROWSET/ROW/*' 
        passing dbms_xmlgen.getXMLType('SELECT ' || c.product_expression || ' FROM dual')
        COLUMNS val NUMBER PATH '.')
   WHERE c.product_expression IS NOT NULL
) s;

db<>디플 데모

*CROSS/OUTER APPLY(Oracle 12c)는 편의를 위해 사용되며 중첩된 하위 쿼리로 대체할 수 있습니다.

---

이 접근 방식은 다양한 집계 함수를 생성하는 데 사용될 수 있습니다.

"SQL"에는 여러 가지 의미가 있습니다."Des SQL have"는 특정 ANSI 버전의 SQL 또는 벤더별 구현을 의미합니다.David B의 대답은 제가 테스트한 몇 가지 다른 환경에서 작동하는 것이지만, 당신의 환경에 따라 당신이 요청하는 것과 정확히 같은 기능을 작성하거나 찾을 수 있습니다.Microsoft SQL Server 2005를 사용하고 있었다고 가정하면, 가능한 해결책은 원래 쿼리가 작성한 대로 작동할 수 있도록 제품이라는 사용자 지정 애그리게이터를 .net 코드로 작성하는 것입니다.

c#에서 다음 작업을 수행해야 할 수 있습니다.

SELECT EXP(SUM(LOG([col]))) 
  FROM table;

언급URL : https://stackoverflow.com/questions/403924/is-there-a-product-function-like-there-is-a-sum-function-in-oracle-sql