I have the following table with three columns:
Table:
CREATE TABLE Cal_3Month
(
ColID int,
ColDate timestamp,
ColValue bigint
);
INSERT INTO Cal_3Month values(1,'2017-01-31 00:00:00',100);
INSERT INTO Cal_3Month values(2,'2017-04-22 00:00:00',123);
INSERT INTO Cal_3Month values(3,'2017-04-02 00:00:00',990);
INSERT INTO Cal_3Month values(4,'2017-03-22 00:00:00',890);
INSERT INTO Cal_3Month values(5,'2017-02-11 00:00:00',1000);
INSERT INTO Cal_3Month values(6,'2017-01-02 00:00:00',2300);
INSERT INTO Cal_3Month values(1,'2017-03-27 00:00:00',238);
INSERT INTO Cal_3Month values(2,'2017-04-22 00:00:00',400);
INSERT INTO Cal_3Month values(1,'2017-02-12 00:00:00',500);
INSERT INTO Cal_3Month values(1,'2017-02-23 00:00:00',50);
INSERT INTO Cal_3Month values(7,'2017-02-25 00:00:00',780);
INSERT INTO Cal_3Month values(8,'2017-02-26 00:00:00',90);
INSERT INTO Cal_3Month values(2,'2017-03-22 00:00:00',78);
SELECT * FROM Cal_3Month order by colid;
Given records:
colid coldate colvalue
-----------------------------------------------
1 2017-01-31 00:00:00 100
1 2017-03-27 00:00:00 238
1 2017-02-12 00:00:00 500
1 2017-02-23 00:00:00 50
2 2017-03-22 00:00:00 78
2 2017-04-22 00:00:00 400
2 2017-04-22 00:00:00 123
3 2017-04-02 00:00:00 990
4 2017-03-22 00:00:00 890
5 2017-02-11 00:00:00 1000
6 2017-01-02 00:00:00 2300
7 2017-02-25 00:00:00 780
8 2017-02-26 00:00:00 90
Note: I want to calculate sum of colvalue of each colid of previous 3 months and want to store it
into HSTORE format.
Expected Result:
colid date and sum value(hstore)
-----------------------------------------------
1 "201702"=>"550","201703"=>"238"
2 "201703"=>"78","201704"=>"523"
3 "201704"=>"990"
4 "201703"=>"890"
5 "201702"=>"1000"
6
7 "201702"=>"780"
8 "201702"=>"90"
My try:
WITH a
as
(
select colid,coldate
from cal_3month
group by colid,coldate
),
b
as
(
select colid,SUM(colvalue) as sumvalue
from cal_3month td
WHERE extract('month' from td.coldate) between extract('month' from now() - interval '3 month') and extract('month' from now() - interval '1 month')
and extract('year' from td.coldate) = extract('year' from now()) --and td.st_exchng = 'NSE'
group by td.coldate,td.colid
order by td.coldate desc
)
,
c
as
(
SELECT a.colid,string_agg((extract('year' from a.coldate)::varchar||extract('month' from a.coldate)::varchar)::varchar,',') as dt,
string_agg(b.sumvalue::varchar,',') as vl
from a
inner join b
on a.colid = b.colid
group by a.colid
)
select c.colid,hstore(c.dt,c.vl)
from c;
Result:
colid hstore
-----------------------------------------------
4 "20173"=>"890"
5 "20172"=>"1000"
8 "20172"=>"90"
1 "20171,20171,20171,20173,20173,20173,20172,20172,20172,20172,20172,20172"=>"500,50,238,500,50,238,500,50,238,500,50,238"
2 "20173,20173,20174,20174"=>"78,523,78,523"
3 "20174"=>"990"
7 "20172"=>"780"
t=# with e as (
with c as (
SELECT distinct colid
, case when coldate < now() - '3 month'::interval then null else sum(colvalue) over (partition by date_trunc('month',coldate),colid) end summ
, case when coldate < now() - '3 month'::interval then null else replace(substr(date_trunc('month',coldate)::text,1,7),'-','') end m
FROM Cal_3Month
order by 1
)
select
colid, hstore(m,summ::text)
from c
)
select colid,string_agg(hstore::text,',') from e group by colid order by 1 ;
colid | string_agg
-------+---------------------------------
1 | "201702"=>"550","201703"=>"238"
2 | "201703"=>"78","201704"=>"523"
3 | "201704"=>"990"
4 | "201703"=>"890"
5 | "201702"=>"1000"
6 |
7 | "201702"=>"780"
8 | "201702"=>"90"
(8 rows)