Calculate the cumulative multiplication
Question
Calculating sum is somewhat easy(though we will surely this in another question).
We have 4 rows in a table T with column x
2
3
5
10
Output
2
6
30
300
solved
3
SQL
55 years
18 Answers
1577 views
Grand Master 0
Answers ( 18 )
SELECT EXP(SUM(LOG(1000 * (1 + COALESCE(X, 1)))) FROM TABLE T
Explain the logic please, like why use 100 and 1+ in expression? Btw the same expression is yielding me the same value present in the column and not the cumulative multiplication.
Derive to multiplication by sum because in sql we do not direct multiplication function:
MUL(x) = EXP(SUM(LN(x)))
So select the column apply above formula and orderby to get desired result.
To derive to multiplication using sum as there is no direct multiplication function in sql
We have MUL(x) = EXP(SUM(LN(x)))
So, select a column apply formula on the column and do order by to get the desired result.
SELECT *,
Lag(x, 1,2) OVER(
ORDER BY x ASC) AS x
FROM T;
language – PostgresSQL
Steps – convert numbers to log form , then apply cumulative sum , then exponentiate the result , also add ceiling function to get rounded off values
Logic – Multiplication operation become addition in log
Code:
create table t (n int);
insert into t values (2),(4),(3),(5),(10),(100);
—-
select n, ceiling(exp(sum(ln(n)) over (order by n))) as n2 from t
Hey Yash,
I tried running this query but I got 120001 as the last entry while it should be 120000. Please help me out.
PGSQL:
1) Firstly create a primary key to reference the columns. Let x_id be the primary key:
CREATE TABLE T
(x_id ,SERIAL, PRIMARY KEY,
x, integer, NOT NULL) ;
2) Then insert values to the table
3) create a cumulative multiplication (cumul_mul) function to be used as a window function:
CREATE AGGREGATE cumul_mul(int8) (SFUNC = int8mul, STYPE = int8);
————
SELECT x_id, x, cumul_mul (x) OVER (ORDER BY x_id)
FROM T;
PGSQL:
1) Firstly create a primary key to reference the columns. Let x_id be the primary key:
CREATE TABLE T
(x_id SERIAL PRIMARY KEY,
X integer NOT NULL) ;
2) Then insert values to the table
3) create a cumulative multiplication (cumul_mul) function to be used as a window function:
CREATE AGGREGATE cumul_mul(int8) (SFUNC = int8mul, STYPE = int8);
SELECT x_id, x, cumul_mul (x) OVER (ORDER BY x_id)
FROM T;
PGSQL:
1) Firstly create a primary key to reference the columns. Let x_id be the primary key:
CREATE TABLE T
(x_id SERIAL PRIMARY KEY,
X integer NOT NULL) ;
2) Then insert values to the table
3) create a cumulative multiplication (cumul_mul) function to be used as a window function:
CREATE AGGREGATE cumul_mul(int8) (SFUNC = int8mul, STYPE = int8);
SELECT x_id, x, cumul_mul (x) OVER (ORDER BY x_id)
FROM T;
SELECT A.ROW
, A.DATE
, A.RATE
, A.RATE * B.RATE AS [CUM RATE]
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY DATE) as ROW, DATE, RATE
FROM T
) A
LEFT JOIN (
SELECT ROW_NUMBER() OVER(ORDER BY DATE) as ROW, DATE, RATE
FROM T
) B
ON A.ROW + 1 = B.ROW
USING WINDOW FUNCTION-
First create unique, non null key= id
Then creating cummulative multiplication function-
CREATE AGGREGATE cum_mul(int8) (SFUNC = int8mul, STYPE = int8);
Finally, Run the code below to get the desired output.
SELECT id, x, cum_mul(x) OVER (ORDER BY id)
FROM T;
Declare @starter as INT;
Set @starter=1;
DECLARE @temp_variable AS INT;
Declare @counter as INT;
SET @counter=1;
While ((@counter)<=4)
BEGIN
SET @temp_variable=(Select TOP (@counter) * From temp
EXCEPT
Select TOP (@counter-1) * From temp)
PRINT @starter*@temp_variable
SET @starter=@temp_variable;
SET @counter=@counter+1;
END;
select a.x,round(exp(sum(log(b.x)))) as y
from table2 as a
join table2 as b
on a.x>=b.x
group by a.x
1) Firstly create a primary key to reference the columns. Let x_id be the primary key:
CREATE TABLE table
(x_id SERIAL PRIMARY KEY,
X integer NOT NULL) ;
2) Then insert values to the table
3) create a cumulative multiplication (cumul_mul) function to be used as a window function:
CREATE AGGREGATE cumul_mul(int8) (SFUNC = int8mul, STYPE = int8);
SELECT x_id, x, cumul_mul (x) OVER (ORDER BY x_id)
FROM table;
Select Exp(sum(ln(1000 * ( 1+ coalesce (x,1))))) as acc_multipliction from T
select a.x,ceiling(exp(sum(log(b.x)))) as y
from T as a
join T as b
on a.x>=b.x
group by a.x
Select Round(EXP(SUM(LOG([COLX]))),1)
FROM Table T
WHERE [COLX]!= 0.