CODEWITHSUNDEEP
sqlwindow-functionssliding-windowanalytic-functions
Nir Regev
Nir Regev

Reputation: 135

SQL sliding window aggregation (without using window function)

I am looking for a query that can aggregate data on a 21 days back basis in the following way: my table has the following columns: accountid, date, measure

I need, for each account, date to sum(measure) for the previous 21 days back. Any idea how to do it in pure SQL without window/analytic function ? (I'm writing SQL inside a BI product which does not have support in analytic functions)

Upvotes: 0

Views: 1431

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

A rather inefficient method uses correlated subqueries. If you want for each entry the previous 21 days, then:

select t.*,
       (select sum(t2.measure)
        from t t2
        where t2.accountid = t.accountid and
              t2.date > t.date - interval '21' day
       ) as sum21
from t;

Not that date functions differ by database, so your particular database might have another method of subtracting 21 days.

Upvotes: 2

Related Questions