Thread: Showing a cumlative total by month
<div dir="ltr">Good morning all,<br /><br />We have a system that maintains information about files that have been uploadedto our system. I have a view that shows for each month the total size of all files submitted tht month. I would liketo add a running total column to the view, but can't quite get there. I figured out how to show the information I wantusing 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 someonecan point me in the right direction.<br /><br />SELECT VERSION();<br /> version <br />----------------------------------------------------------------------------------------------------------------<br /> PostgreSQL9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit<br /><br/>DROP TABLE uploaded_files;<br /><br />CREATE TABLE uploaded_files<br />(<br /> file_id SERIAL NOT NULL,<br /> upload_date TIMESTAMP WITH TIME ZONE NOT NULL,<br /> upload_size BIGINT NOT NULL,<br /> PRIMARY KEY (file_id)<br />);<br /><br />This test case is populatedwith a handful of random records (659), and the following CTE seems to extract the information I am after (stillnot positive I have the date down exactly, but I can deal with that):<br /><br />WITH upload_summary AS<br />(<br /> SELECT EXTRACT(YEAR FROM upload_date) AS up_year,<br /> LPAD(CAST(EXTRACT(MONTH FROM upload_date) AS TEXT),2, '0') AS up_month,<br /> SUM(upload_size) AS monthly_total<br /> FROM uploaded_files<br /> GROUP BYup_month, up_year<br />)<br />SELECT u.up_year,<br /> u.up_month,<br /> u.monthly_total,<br /> (SELECTSUM(monthly_total)<br /> FROM upload_summary<br /> WHERE CAST(CONCAT(up_year,<br /> '-',<br /> up_month,<br /> '-01 00:00:00-05')<br /> AS TIMESTAMP) <=<br /> CAST(CONCAT(u.up_year,<br /> '-',<br /> u.up_month,<br /> '-01 00:00:00-05')<br /> AS TIMESTAMP)<br/> ) AS cumulative<br />FROM upload_summary u<br />ORDER BY up_year, up_month;<br /><br /> up_year | up_month| monthly_total | cumulative <br />---------+----------+----------------+----------------<br /> 2014 | 01 | 3179135699 | 3179135699<br /> 2014 | 02 | 1634499060 | 4813634759<br /> 2014 | 03 | 4278982588 | 9092617347<br /> 2014 | 04 | 142238544857 | 151331162204<br /> 2014 | 05 | 357240707209 | 508571869413<br /> 2014 | 06 | 64339859968 | 572911729381<br /> 2014 | 07 | 25513017728 | 598424747109<br /> 2014 | 08 | 11020669492 | 609445416601<br /> 2014 | 09 | 1018774598 | 610464191199<br /> 2014 | 10 | 76752535951 | 687216727150<br /> 2014 | 11 | 4611404964 | 691828132114<br /> 2014 | 12 | 69607199452 | 761435331566<br /> 2015 | 01 | 15700525 | 761451032091<br /> 2015 | 02 | 34234715981 | 795685748072<br /> 2015 | 03 | 1484150449194| 2279836197266<br /> 2015 | 04 | 13096208914706 | 15376045111972<br /> 2015 | 05 | 814385166 | 15376859497138<br /> 2015 | 06 | 563829413 | 15377423326551<br /> 2015 | 07 | 656007272| 15378079333823<br /> 2015 | 08 | 1828956674258 | 17207036008081<br /> 2015 | 09 | 822601322| 17207858609403<br /><br /><br /><br />The basic view definition gives the first three columns (date fields concatenatedinto one):<br />SELECT CONCAT(EXTRACT(YEAR FROM upload_date),<br /> '-',<br /> LPAD(CAST(EXTRACT(MONTHFROM upload_date) AS TEXT), 2, '0')) AS mon,<br /> SUM(upload_size) AS monthly_total<br />FROMuploaded_files u<br />GROUP BY EXTRACT(MONTH FROM upload_date), EXTRACT(YEAR FROM upload_date)<br />ORDER BY EXTRACT(YEARFROM upload_date), EXTRACT(MONTH FROM upload_date);<br /><br /> mon | monthly_total<br />---------+----------------<br/> 2014-01 | 3179135699<br /> 2014-02 | 1634499060<br /> 2014-03 | 4278982588<br/> 2014-04 | 142238544857<br /> 2014-05 | 357240707209<br /> 2014-06 | 64339859968<br /> 2014-07 | 25513017728<br /> 2014-08 | 11020669492<br /> 2014-09 | 1018774598<br /> 2014-10 | 76752535951<br /> 2014-11| 4611404964<br /> 2014-12 | 69607199452<br /> 2015-01 | 15700525<br /> 2015-02 | 34234715981<br/> 2015-03 | 1484150449194<br /> 2015-04 | 13096208914706<br /> 2015-05 | 814385166<br /> 2015-06 | 563829413<br /> 2015-07 | 656007272<br /> 2015-08 | 1828956674258<br /> 2015-09 | 822601322<br /><br /><br/>I just can't figure out how to specify the summation of the file sizes through each month in a static query. Any suggestions?<br/><br />Thanks,<br />David</div>
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.
David J.
<div dir="ltr">On Fri, Sep 11, 2015 at 10:15 AM, David G. Johnston <<a href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>>wrote:<br />><br />> On Fri, Sep 11, 2015at 10:46 AM, David Nelson <<a href="mailto:dnelson77808@gmail.com">dnelson77808@gmail.com</a>> wrote:<br />>><br/>>> I would like to add a running total column to the view, but can't quite get there. I figured outhow to show the information I want using a CTE, but I can not figure out how to translate that to a single query to definea view with. So I'm hoping someone can point me in the right direction.<br />>><br />><br />> --> <a href="http://www.postgresql.org/docs/9.3/interactive/functions-window.html">http://www.postgresql.org/docs/9.3/interactive/functions-window.html</a><br />><br/>> David J.<br />> <br />Ha! How ironic. I made myself a note to try to understand windowing functions<br/>a couple of weeks ago due to a conversation on one of the PostgreSQL lists<br />regarding finding first andlast values. Looks like that just got moved up<br />in priority.<br /><br />Thanks!<br /><br />I'll post that solutionwhen I find it for posterity.<br /></div>
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
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
<div dir="ltr">On Fri, Sep 11, 2015 at 10:15 AM, David G. Johnston <<a href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>>wrote:<br />><br />> On Fri, Sep 11, 2015at 10:46 AM, David Nelson <<a href="mailto:dnelson77808@gmail.com">dnelson77808@gmail.com</a>> wrote:<br />>><br/>>> I would like to add a running total column to the view, but can't quite get there. I figured outhow to show the information I want using a CTE, but I can not figure out how to translate that to a single query to definea view with. So I'm hoping someone can point me in the right direction.<br />>><br />><br />> --> <a href="http://www.postgresql.org/docs/9.3/interactive/functions-window.html">http://www.postgresql.org/docs/9.3/interactive/functions-window.html</a><br />><br/>> David J.<br />> <br /><br />Thanks David and Stuart!<br /><br />these two solutions seem to be what I'mafter. I will apply them to the<br />production view (which is more complex), and see how they play out. But you<br />twopointed me in the directions that I needed to go.<br /><br />DROP VIEW upload_info;<br /><br />CREATE VIEW upload_infoAS<br />SELECT mon,<br /> monthly_total,<br /> SUM(monthly_total) OVER (ORDER BY mon) AS cumulative<br/>FROM (SELECT CONCAT(EXTRACT(YEAR FROM upload_date),<br /> '-',<br /> LPAD(CAST(EXTRACT(MONTH FROM upload_date) AS TEXT), 2, '0')) AS mon,<br /> SUM(upload_size)AS monthly_total<br /> FROM uploaded_files u<br /> GROUP BY EXTRACT(MONTH FROM upload_date), EXTRACT(YEARFROM upload_date)<br /> ORDER BY EXTRACT(YEAR FROM upload_date), EXTRACT(MONTH FROM upload_date)) AS u;<br/><br />SELECT *<br />FROM upload_info;<br /><br /> mon | monthly_total | cumulative <br />---------+----------------+----------------<br/> 2014-01 | 3179135699 | 3179135699<br /> 2014-02 | 1634499060| 4813634759<br /> 2014-03 | 4278982588 | 9092617347<br /> 2014-04 | 142238544857 | 151331162204<br/> 2014-05 | 357240707209 | 508571869413<br /> 2014-06 | 64339859968 | 572911729381<br /> 2014-07| 25513017728 | 598424747109<br /> 2014-08 | 11020669492 | 609445416601<br /> 2014-09 | 1018774598| 610464191199<br /> 2014-10 | 76752535951 | 687216727150<br /> 2014-11 | 4611404964 | 691828132114<br/> 2014-12 | 69607199452 | 761435331566<br /> 2015-01 | 15700525 | 761451032091<br /> 2015-02| 34234715981 | 795685748072<br /> 2015-03 | 1484150449194 | 2279836197266<br /> 2015-04 | 13096208914706| 15376045111972<br /> 2015-05 | 814385166 | 15376859497138<br /> 2015-06 | 563829413 | 15377423326551<br/> 2015-07 | 656007272 | 15378079333823<br /> 2015-08 | 1828956674258 | 17207036008081<br /> 2015-09| 822601322 | 17207858609403<br />(21 rows)<br /><br /><br /><br />DROP VIEW upload_info;<br /><br />CREATEVIEW upload_info AS<br />WITH upload_summary AS<br />(<br /> SELECT EXTRACT(YEAR FROM upload_date) AS up_year,<br/> LPAD(CAST(EXTRACT(MONTH FROM upload_date) AS TEXT), 2, '0') AS up_month,<br /> SUM(upload_size)AS monthly_total<br /> FROM uploaded_files<br /> GROUP BY up_month, up_year<br />)<br />SELECT u.up_year,<br/> u.up_month,<br /> u.monthly_total,<br /> (SELECT SUM(monthly_total)<br /> FROM upload_summary<br/> WHERE CAST(CONCAT(up_year,<br /> '-',<br /> up_month,<br /> '-01 00:00:00-05')<br /> AS TIMESTAMP)<=<br /> CAST(CONCAT(u.up_year,<br /> '-',<br /> u.up_month,<br /> '-01 00:00:00-05')<br /> AS TIMESTAMP)<br/> ) AS cumulative<br />FROM upload_summary u<br />ORDER BY up_year, up_month;<br /><br />SELECT *<br/>FROM upload_info;<br /><br /> up_year | up_month | monthly_total | cumulative <br />---------+----------+----------------+----------------<br/> 2014 | 01 | 3179135699 | 3179135699<br /> 2014 | 02 | 1634499060 | 4813634759<br /> 2014 | 03 | 4278982588 | 9092617347<br /> 2014 | 04 | 142238544857 | 151331162204<br /> 2014 | 05 | 357240707209 | 508571869413<br /> 2014 | 06 | 64339859968 | 572911729381<br /> 2014 | 07 | 25513017728 | 598424747109<br /> 2014 | 08 | 11020669492 | 609445416601<br /> 2014 | 09 | 1018774598 | 610464191199<br /> 2014 | 10 | 76752535951 | 687216727150<br /> 2014 | 11 | 4611404964 | 691828132114<br /> 2014 | 12 | 69607199452 | 761435331566<br /> 2015 | 01 | 15700525 | 761451032091<br /> 2015 | 02 | 34234715981 | 795685748072<br /> 2015 | 03 | 1484150449194 | 2279836197266<br /> 2015 | 04 | 13096208914706 | 15376045111972<br /> 2015 | 05 | 814385166 | 15376859497138<br /> 2015 | 06 | 563829413 | 15377423326551<br /> 2015 | 07 | 656007272 | 15378079333823<br /> 2015 | 08 | 1828956674258 | 17207036008081<br /> 2015 | 09 | 822601322 | 17207858609403<br />(21rows)<br /></div>