SQL sliding window aggregation (without using window function)

Question

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)

Can you provide sample data and desired results? 21 days back from what? — Gordon Linoff, Jul 23 '16 at 18:46

score 2 · Accepted Answer · answered Jul 23 '16 at 18:47

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.

SQL sliding window aggregation (without using window function)

1 Answers1