CODEWITHSUNDEEP
amazon-redshift
Charlie
Charlie

Reputation: 1746

Rolling count of rows over time?

I've found plenty of examples in the postgresql world but they all seem to use functions that redshift doesn't support.

Is there a simple query to get number rows per week, with a rolling sum?

The table has a "created" column that's a timestamp without time zone.

The closest I've gotten is:

SELECT DISTINCT
       date_trunc('week', created) AS week
      ,count(*) OVER (ORDER BY date_trunc('week', created) rows unbounded preceding) AS running_ct
FROM   device_unlock_report
order by 1

Which gives me:

2015-02-09 00:00:00 320
2015-02-09 00:00:00 321
2015-02-09 00:00:00 322
2015-02-09 00:00:00 323
2015-02-16 00:00:00 324
2015-02-16 00:00:00 325
2015-02-16 00:00:00 326
2015-02-16 00:00:00 327
2015-02-16 00:00:00 328

Seems to be giving me a row every time the sum increments.

Upvotes: 0

Views: 293

Answers (1)

Yusuf Hassan
Yusuf Hassan

Reputation: 2013

For Rolling Sum, try

select week,total, sum(total) over (order by week rows unbounded preceding) as rolling_sum from
        (SELECT
        date_trunc('week', created) AS week,
        count(*) total
        FROM   device_unlock_report
        group by 1)
group by 1,2

Upvotes: 2

Related Questions