CODEWITHSUNDEEP
sqlsql-serverentity-frameworksql-server-cesqlcommand
Dynamo
Dynamo

Reputation: 51

SQL command for getting a specific pattern

I have a table sample with column 'observations':

enter image description here

Please help with the SQL command to get the following 'cumulative multiplication' output:

2
6
30
300

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

One method is a recursive CTE:

with tt as (
      select t.*, row_number() over (order by obs) as seqnum
      from t
     ),
     cte as (
      select obs as prod, seqnum
      from tt
      where seqnum = 1
      union all
      select cte.prod * tt.obs, tt.seqnum
      from cte join
           tt
           on tt.seqnum = cte.seqnum + 1
     )
select *
from cte;

Another uses arithmetic to implement a "product" window function:

select t.*,
       exp(sum(log(obs)) over (order by obs))
from t;

Here is a db<>fiddle.

Upvotes: 2

Related Questions