Re: Showing a cumlative total by month

On Fri, Sep 11, 2015 at 10:01 AM, Stuart <sfbarbee(at)gmail(dot)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(at)gmail(dot)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
>>
>