Nir Regev Nir Regev - 4 months ago 31
SQL Question

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)

Answer

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.