Postgres Pro
Downloads
Home   >   mailing lists

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

From David Nelson
Subject Re: Showing a cumlative total by month
Date
Msg-id CANxyCUGeh_Xso0WZAYMoeEWRkNC7ONFWu53jP23ihiCYf-XNxA@mail.gmail.com
Whole thread Raw
In response to Showing a cumlative total by month  (David Nelson <dnelson77808@gmail.com>)
List pgsql-sql
Tree view
On Fri, Sep 11, 2015 at 10:01 AM, Stuart <sfbarbee@gmail.com> wrote:

David,

You can't just use the query you have to define a new view?  Seems straight forward.  Am I missing something?

Stuart

CCing the list.

Stuart, it turns out you are right. I assumed that the CTE solution wouldn't
work because a view definition needs to be a "simple" SELECT. But I didn't
test that and a quick test reveals it seems to work. I'll have to play around
with David Johnston's pointer to the windowing function as well to see which
is more likely to be future proof for production. But shame on me for not
trying it first and thanks for the prod.

David
 
On Sep 11, 2015 18:46, "David Nelson" <dnelson77808@gmail.com> wrote:
Good morning all,

We have a system that maintains information about files that have been uploaded to our system. I have a view that shows for each month the total size of all files submitted tht month. 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.

SELECT VERSION();
                            version                                                    
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit

DROP TABLE uploaded_files;

CREATE TABLE uploaded_files
(
   file_id              SERIAL                        NOT NULL,
   upload_date          TIMESTAMP WITH TIME ZONE      NOT NULL,
   upload_size          BIGINT                        NOT NULL,
   PRIMARY KEY (file_id)
);

This test case is populated with a handful of random records (659), and the following CTE seems to extract the information I am after (still not positive I have the date down exactly, but I can deal with that):

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;

 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



The basic view definition gives the first three columns (date fields concatenated into one):
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);

   mon   | monthly_total
---------+----------------
 2014-01 |     3179135699
 2014-02 |     1634499060
 2014-03 |     4278982588
 2014-04 |   142238544857
 2014-05 |   357240707209
 2014-06 |    64339859968
 2014-07 |    25513017728
 2014-08 |    11020669492
 2014-09 |     1018774598
 2014-10 |    76752535951
 2014-11 |     4611404964
 2014-12 |    69607199452
 2015-01 |       15700525
 2015-02 |    34234715981
 2015-03 |  1484150449194
 2015-04 | 13096208914706
 2015-05 |      814385166
 2015-06 |      563829413
 2015-07 |      656007272
 2015-08 |  1828956674258
 2015-09 |      822601322


I just can't figure out how to specify the summation of the file sizes through each month in a static query. Any suggestions?

Thanks,
David

pgsql-sql by date:

Previous
From: David Nelson
Date:
Subject: Re: Showing a cumlative total by month
Next
From: David Nelson
Date:
Subject: Re: Showing a cumlative total by month