Re: Showing a cumlative total by month - Mailing list pgsql-sql

On Fri, Sep 11, 2015 at 10:15 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Fri, Sep 11, 2015 at 10:46 AM, David Nelson <dnelson77808@gmail.com> wrote:
>>
>> I would like to add a running total column to the view, but can't quite get there. I figured out how to show the information I want using a CTE, but I can not figure out how to translate that to a single query to define a view with. So I'm hoping someone can point me in the right direction.
>>
>
> -->  http://www.postgresql.org/docs/9.3/interactive/functions-window.html
>
> David J.
>  

Thanks David and Stuart!

these two solutions seem to be what I'm after. I will apply them to the
production view (which is more complex), and see how they play out. But you
two pointed me in the directions that I needed to go.

DROP VIEW upload_info;

CREATE VIEW upload_info AS
SELECT mon,
       monthly_total,
       SUM(monthly_total) OVER (ORDER BY mon) AS cumulative
FROM (SELECT CONCAT(EXTRACT(YEAR FROM upload_date),
                    '-',
                    LPAD(CAST(EXTRACT(MONTH FROM upload_date) AS TEXT), 2, '0')) AS mon,
             SUM(upload_size) AS monthly_total
      FROM uploaded_files u
      GROUP BY EXTRACT(MONTH FROM upload_date), EXTRACT(YEAR FROM upload_date)
      ORDER BY EXTRACT(YEAR FROM upload_date), EXTRACT(MONTH FROM upload_date)) AS u;

SELECT *
FROM upload_info;

   mon   | monthly_total  |   cumulative  
---------+----------------+----------------
 2014-01 |     3179135699 |     3179135699
 2014-02 |     1634499060 |     4813634759
 2014-03 |     4278982588 |     9092617347
 2014-04 |   142238544857 |   151331162204
 2014-05 |   357240707209 |   508571869413
 2014-06 |    64339859968 |   572911729381
 2014-07 |    25513017728 |   598424747109
 2014-08 |    11020669492 |   609445416601
 2014-09 |     1018774598 |   610464191199
 2014-10 |    76752535951 |   687216727150
 2014-11 |     4611404964 |   691828132114
 2014-12 |    69607199452 |   761435331566
 2015-01 |       15700525 |   761451032091
 2015-02 |    34234715981 |   795685748072
 2015-03 |  1484150449194 |  2279836197266
 2015-04 | 13096208914706 | 15376045111972
 2015-05 |      814385166 | 15376859497138
 2015-06 |      563829413 | 15377423326551
 2015-07 |      656007272 | 15378079333823
 2015-08 |  1828956674258 | 17207036008081
 2015-09 |      822601322 | 17207858609403
(21 rows)



DROP VIEW upload_info;

CREATE VIEW upload_info AS
WITH upload_summary AS
(
   SELECT EXTRACT(YEAR FROM upload_date) AS up_year,
          LPAD(CAST(EXTRACT(MONTH FROM upload_date) AS TEXT), 2, '0') AS up_month,
          SUM(upload_size) AS monthly_total
   FROM uploaded_files
   GROUP BY up_month, up_year
)
SELECT u.up_year,
       u.up_month,
       u.monthly_total,
       (SELECT SUM(monthly_total)
        FROM upload_summary
        WHERE CAST(CONCAT(up_year,
                          '-',
                          up_month,
                          '-01 00:00:00-05')
                   AS TIMESTAMP) <=
              CAST(CONCAT(u.up_year,
                          '-',
                          u.up_month,
                          '-01 00:00:00-05')
                   AS TIMESTAMP)
       ) AS cumulative
FROM upload_summary u
ORDER BY up_year, up_month;

SELECT *
FROM upload_info;

 up_year | up_month | monthly_total  |   cumulative  
---------+----------+----------------+----------------
    2014 | 01       |     3179135699 |     3179135699
    2014 | 02       |     1634499060 |     4813634759
    2014 | 03       |     4278982588 |     9092617347
    2014 | 04       |   142238544857 |   151331162204
    2014 | 05       |   357240707209 |   508571869413
    2014 | 06       |    64339859968 |   572911729381
    2014 | 07       |    25513017728 |   598424747109
    2014 | 08       |    11020669492 |   609445416601
    2014 | 09       |     1018774598 |   610464191199
    2014 | 10       |    76752535951 |   687216727150
    2014 | 11       |     4611404964 |   691828132114
    2014 | 12       |    69607199452 |   761435331566
    2015 | 01       |       15700525 |   761451032091
    2015 | 02       |    34234715981 |   795685748072
    2015 | 03       |  1484150449194 |  2279836197266
    2015 | 04       | 13096208914706 | 15376045111972
    2015 | 05       |      814385166 | 15376859497138
    2015 | 06       |      563829413 | 15377423326551
    2015 | 07       |      656007272 | 15378079333823
    2015 | 08       |  1828956674258 | 17207036008081
    2015 | 09       |      822601322 | 17207858609403
(21 rows)