CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
DamienBAus
DamienBAus

Reputation: 268

Sum of shifting range in SQL Query

I am trying to write an efficient query to get the sum of the previous 7 days worth of values from a relational DB table, and record each total against the final date in the 7 day period (e.g. the 'WeeklyTotals Table' in the example below). For example, in my WeeklyTotals query, I would like the value for February 15th to be 333, since that is the total sum of users from Feb 9th - Feb 15th, and so on:

Example of desired outcome

I have a base query which gets me my previous weeks users for today's date (simplified for the sake of the example):

SELECT Date, Sum("Total Users")
FROM "UserRecords"
WHERE (dateadd(hour, -8, "UserRecords"."Date") BETWEEN 
    dateadd(hour, -8, sysdate) - INTERVAL '7 DAY' AND dateadd(hour, -8, sysdate);

The problem is, this only get's me the total for today's date. I need a query which will get me this information for the previous seven days.

I know I can make a view for each date (since I only need the previous seven entries) and join them all together, but that seems really inefficient (I'll have to create/update 7 views, and then do all the inner join operations). I am wondering if there's a more efficient way to achieve this.

Upvotes: 2

Views: 1391

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95029

Provided there are no gaps, you can use a running total with SUM OVER including the six previous rows. Use ROW_NUMBER to exclude the first six records, as their totals don't represent complete weeks.

select log_date, week_total
from
(
  select 
    log_date, 
    sum(total_users) over (order by log_date rows 6 preceding) as week_total,
    row_number() over (order by log_date) as rn
  from mytable
  where log_date > 0
)
where rn >= 7
order by log_date;

UPDATE: In case there are gaps, it should be 

sum(total_users) over (order by log_date range interval '6' day preceding)

but I don't know whether PostgreSQL supports this already. (Moreover the ROW_NUMBER exclusion wouldn't work then and would have to be replaced by something else.)

Upvotes: 2

Guy
Guy

Reputation: 12919

You can use the SUM over Window function, with the expression using Date Part, of week.

Self joins are much slower than Window functions.

Upvotes: 2

FuzzyTree
FuzzyTree

Reputation: 32402

Here's a a query that self joins to the previous 6 days and sums the value to get the weekly totals:

select u1.date, sum(u2.total_users) as weekly_users
from UserRecords u1
join UserRecords u2
    on u1.date - u2.date < 7
    and u1.date >= u2.date
group by u1.date
order by u1.date

Upvotes: 1

Related Questions