I have a line:

sum(purchases) over(partition by category order by value_day range between interval '1' month preceding and current row) 

If value_day = Aug 21, it returns sum from and included July 21 till and included Aug 21, but I need from and included July 22 till and included Aug 21.

How can I do that?

CodePudding user response：

You can use an expression to define the starting point of the window. So you can

• Subtract a month from the current date
• Add a day to it

Giving something like:

sum ( purchases ) over (
  partition by category 
  order by value_day 
  range between ( value_day - ( add_months ( value_day, -1 )   1 ) ) preceding 
  and current row
) 

CodePudding user response：

You can either:

• Use two windowed functions, your one to add everything from the past month and then subtract a second one that just covers the range you do not want to include; or
• Use a correlated sub-query rather than windowed analytic functions.

SELECT t.*,
       sum(purchases) over(
         partition by category
         order by value_day
         range between interval '1' month preceding and current row
       ) -
       COALESCE(
         sum(purchases) over(
           partition by category
           order by value_day
           range between interval '1' month preceding and interval '1' month preceding
         ),
         0
       ) AS total1,
       ( SELECT SUM(s.purchases)
         FROM   table_name s
         WHERE  t.category = s.category
         AND    ADD_MONTHS(t.value_day, -1)   INTERVAL '1' DAY <= s.value_day
         AND    s.value_day <= t.value_day
       ) AS total2
FROM   table_name t;

Which, for the sample data:

CREATE TABLE table_name (category, value_day, purchases) AS
SELECT 1, DATE '2022-01-01'   LEVEL - 1, LEVEL
FROM   DUAL
CONNECT BY LEVEL <= 50;

Outputs:

CATEGORY	VALUE_DAY	PURCHASES	TOTAL1	TOTAL2
...	...	...	...	...
1	01-FEB-22	32	527	527
1	02-FEB-22	33	558	558
1	03-FEB-22	34	589	589
1	04-FEB-22	35	620	620
1	05-FEB-22	36	651	651
1	06-FEB-22	37	682	682
1	07-FEB-22	38	713	713
1	08-FEB-22	39	744	744
1	09-FEB-22	40	775	775
1	10-FEB-22	41	806	806
1	11-FEB-22	42	837	837
1	12-FEB-22	43	868	868
1	13-FEB-22	44	899	899
1	14-FEB-22	45	930	930
1	15-FEB-22	46	961	961
1	16-FEB-22	47	992	992
1	17-FEB-22	48	1023	1023
1	18-FEB-22	49	1054	1054
1	19-FEB-22	50	1085	1085

db<>fiddle here