0

I am trying to aggregate the arrays present in column 'agg' grouping them by product and get the all the events from the last 13 weeks.

for example : the first row value of aggregation should be like : aggregate all the arrays grouping them by product and pick up only the last 13week data. that is from 2/8/2014 to last_13(last 13 week date taking 2/8/2014 as the reference which is 11-10-2013)

I tried the following way but does not work . 

select array_cat(a.agg2) over(partition by a.PName order by a.PName ,a.week_date desc rows between 0 preceding and 13 following)
    from (
    select PName,week_date,array_agg(raw_value)  agg2,
    from vin_temp_table group by PName,week_date order by PName,week_date desc
    )a

EDIT :

query : 

select PName,week_date,array_agg(raw_value)  agg,
        from vin_temp_table group by PName,week_date order by PName,week_date desc

results in the following data 

PName       week_date              agg
5773    2/8/2014    {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
5773    2/1/2014    {0,0,0,0,0,0,0,0,0,0,0,0,0}
5773    1/25/2014   {0,0,0,0,0,0,0,0,0,0,0,0,0}
5774    1/18/2014   {0,0,0,0,0,0,0,0,0,0,0,0,0}
5775    1/11/2014   {0,0,0,0,0,0,0,0,0,0,0,0,0}
5776    12/28/2013  {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
5776    12/21/2013  {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
5776    12/14/2013  {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
5776    12/7/2013   {0,0,0,0,0,0,0,0,0,0,0,0,0}
5777    11/30/2013  {0,0,0,0,0,0,0,0,0,0,0,0,0}
5778    11/16/2013      {0,0,0,0,0,0,0,0,0,0,0,0}
5778    11/9/2013   {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
5778    11/2/2013   {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

Raw data : select * from vin_temp_table

 Pname       week_date       raw_value
    5773    10/21/2006  0
    5773    10/28/2006  0
    5773    11/18/2006  0
    5773    11/18/2006  0
    5773    1/20/2007   0
    5773    5/12/2007   0
    5773    7/21/2007   0
    5773    7/21/2007   0
    5773    8/11/2007   0
    5773    8/25/2007   0
    5773    9/1/2007    0

I even tried to partitioning using array_agg on Pname and week_date. But was not successful with this one too as the Greenplum version 8.2 was not supporting it.

Could some one help me out ? Thanks.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
user2569524
  • 1,651
  • 7
  • 32
  • 57
  • Please show some sample data, preferably as `create table` and `insert` statements. Right now I see the query and the "wrong" output, but not the *expected* output or the input data. – Craig Ringer Feb 27 '14 at 02:16
  • Also, you say postgresql 8.2 in text, and use tags for 9.1 and 8.4. WTF? What *exactly* are you using? Show `SELECT version()`. And don't just add random tags. – Craig Ringer Feb 27 '14 at 02:17
  • its postgres 8.2.15 (Green plum). I dont have postgres 8.2 tag in stack overflow so I have tagged in latest versions. Sorry for the inconvenience caused – user2569524 Feb 27 '14 at 15:09
  • I have edited my post to include more details now. Thanks – user2569524 Feb 27 '14 at 15:22
  • Argh. Greenplum is not PostgreSQL. It's a heavily modified fork of an old PostgreSQL version. If you're using Greenplum, say so. (I don't have access to Greenplum, so I can't help you with this). – Craig Ringer Feb 27 '14 at 23:13

0 Answers0