How can I find the amount for an interval of 100 days?

Question

How can I find the amount for an interval of 100 days? I got this option:

SELECT SUM(AMN) 
OVER (PARTITION BY ID 
     ORDER BY DATE RANGE BETWEEN INTERVAL '100' DAY(3) PRECEDING 
     AND CURRENT ROW) S1
FROM ddd

Answer 1

You did it correctly. The other way is correlated subquery, like below. But window functions are designed exactly for such purposes and you should use them instead of subqueries. They are much faster.

^{SQLFiddle demo}

select id, date_, amn, 
       sum(amn) over(partition by id order by date_ 
                     range between interval '100' day(3) preceding
                       and current row) s1, 
       (select sum(amn) 
          from ddd d2 
          where ddd.id = d2.id 
            and date_ between ddd.date_ - interval '100' day(3) 
                          and ddd.date_) s2
  from ddd