CODEWITHSUNDEEP
sqlsql-serversql-server-2012aggregate-functionssliding
vmanne
vmanne

Reputation: 1165

SQL Sliding Math

I have a requirement to achieve this in SQL

YYYYQ|  A | Desired Column
--------------------------
3Q12 | A1 | A1      
4Q12 | A2 | A1 * A2  
1Q13 | A3 | A1 * A2 * A3  
2Q13 | A4 | A1 * A2 * A3 * A4

I'm using SQL 2012 and tried to use partition and over clause, however, I'm not seeing the product as I don't have unique key to partition off.

What's the best way to achieve this?

Upvotes: 1

Views: 86

Answers (2)

vmanne
vmanne

Reputation: 1165

So, here's what I've done to solve the issue.

declare @test table(id INT IDENTITY(1, 1), value float, yyyyq varchar(5));

insert into @test
select 1.23, '20121' union all
select 2.23, '20122' union all
select 3.23, '20123' union all
select 4.23, '20124'

select
  id,
 yyyyq,
  value a,
exp(nullif(sign_min, 0) * sum_log) as cumuVal,
(exp(nullif(sign_min, 0) * sum_log)-1) * 100 as FinalVal
from @test a 
cross apply
( select
sum(coalesce(log(nullif(b.value,0)),0)) as sum_log,
max( sign(abs(b.value)) ) as sign_Min
from @test b
where  b.yyyyq <= a.yyyyq
) xa1
order by a.yyyyq

BTW, This is a great solution using CTE, however, this one fails for unique value (or unique id's in that example)

http://jingyangli.wordpress.com/2012/08/27/a-sliding-aggregate-multiply-code-sample-rows-between-1-preceding-and-current-row/

Upvotes: 0

mattmc3
mattmc3

Reputation: 18345

UPDATE:

If you need to do a PRODUCT() instead of a SUM(), there's obviously no built in aggregate for that. But, based on this answer, and a little row_number() magic, we can still achieve the desired result. I have a feeling there may be a smarter/faster way to do this, perhaps with Common Table Expressions, but I don't know what that would be.

select
    the_date,
    some_value,
    (
        select exp(sum(log(some_value)))
        from (
            select *, row_number() over (order by the_date, id) as row_num
            from #tmp
        ) y
        where y.row_num <= a.row_num
    ) as running_product
from
    (
        select *, row_number() over (order by the_date, id) as row_num
        from #tmp t
    ) a
order by
    the_date,
    id

Previous answer:

The simplest way to do it is with a sum() over like so:

if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (
    id int identity(1,1) not null,
    the_date datetime not null,
    some_value decimal(12,2) not null
)

insert into #tmp (
    the_date,
    some_value
)
select '1/1/2013', rand() * 100 union all
select '2/1/2013', rand() * 100 union all
select '3/1/2013', rand() * 100 union all
select '4/1/2013', rand() * 100 union all
select '5/1/2013', rand() * 100 union all
select '6/1/2013', rand() * 100

select id, the_date, some_value, 
sum(some_value) over (order by the_date, id) as total
from #tmp

And if you need to aggregate:

select
    the_date,
    some_value_sum,
    sum(some_value_sum) over (order by the_date) total
from (
    select the_date, sum(some_value) as some_value_sum
    from #tmp
    group by the_date
) x

Upvotes: 1

Related Questions