I have a sql table with date, reference number and views as a column, I am trying to add up the views for each row based on the date grouped by the reference number.

I have data for 3 days, lets say the 27, 28, and 29 of March, I have multiple reference numbers but lets take 1 reference number: 123. I would like to add the number of views for that reference from the 27 to the 28 and produce a result in a new column in the same row for the 28. And then add that resulting number from the 28 to the 29 views and put that into the new column in the same row for the 29.

create or replace view acq.vw_cumulative(DATE, REF, VIEWS, URL, CUML_VIEWS)
as
SELECT DATE, REF, VIEWS, URL, sum(VIEWS) WHERE DATE > DATE - 1 OVER (PARTITION BY REF) FROM ACQ.CUMULATIVE;

This is what I have started out with but it just returns the total sum and appends to all 3 rows

CodePudding user response：

So with this CTE just for the simplicity (for me) of "fake data"

WITH data_table(date, ref, views) as (
    SELECT * FROM VALUES
        ('2022-03-27'::date, 123, 100),   
        ('2022-03-28'::date, 123, 20),   
        ('2022-03-29'::date, 123, 3),   
        ('2022-03-27'::date, 44, 10),   
        ('2022-03-28'::date, 44, 200),   
        ('2022-03-29'::date, 44, 32)   
)

the following SQL can be used, basically you need to do your SUM a little different.

SELECT d.date,
    d.ref,
    d.views,
    SUM(d.views) OVER (partition by d.ref order by d.date) as CUMULATIVE
FROM data_table AS d
order by 2,1;

we will get sum of all the prior view values.

and thus that could be poked into your VIEW (when you add URL and the other important to you bits)