looking for a little sql magic w/ arrays

#1

All,

I have a table analogous to this:

SALES

Column | Type | Modifiers
----------------------+-----------------------------+-----------
salespersonid bigint not null
dateofsale timestamp not null
salesandcommish bigint[] not null

For each salespersonid, I want an array of his/her sales (array
element one); e.g.

salespersonid monthlysales
100 | {123,45,8545,3446,3356,..... etc.}

that monthlysales would contain data ordered by dateofsale....

Any ideas would be most appreciated.

REgards,
Chas.

#2

Chas. wrote on 21.03.2011 21:00:

Quote:

All,

I have a table analogous to this:

SALES

Column | Type | Modifiers
----------------------+-----------------------------+-----------
salespersonid bigint not null
dateofsale timestamp not null
salesandcommish bigint[] not null

For each salespersonid, I want an array of his/her sales (array
element one); e.g.

salespersonid monthlysales
100 | {123,45,8545,3446,3356,..... etc.}

that monthlysales would contain data ordered by dateofsale....

Any ideas would be most appreciated.

select salespersonid,
array_agg(month_value)
from (
select salespersonid,
salesandcommish[1] as month_value
from sales
order by dateofsale asc
) t
group by salespersonid;

although I'm not 100% sure if the aggregate will really honor the order by

With 9.0 this is a lot easier:

SELECT salespersonid, array_agg(salesandcommish[1] order by dateofsale)
FROM sales
GROUP BY salespersonid;

#3

On Mar 21, 1:35*pm, Hans Castorp <REWYRLXHE... (AT) spammotel (DOT) com> wrote:

Quote:

Chas. wrote on 21.03.2011 21:00:

All,

I have a table analogous to this:

SALES

* * * * *Column * * * *| * * * * * *Type * * * * * * | Modifiers
----------------------+-----------------------------+-----------
salespersonid * * * * * bigint * * * * * * * * * not null
dateofsale * * * * * * * *timestamp * * * * *not null
salesandcommish * * bigint[] * * * * * * * *not null

For each salespersonid, I want an array of his/her sales (array
element one); e.g.

salespersonid *monthlysales
100 * * * * * * * | {123,45,8545,3446,3356,..... etc.}

that monthlysales would contain data ordered by dateofsale....

Any ideas would be most appreciated.

select salespersonid,
* * * * array_agg(month_value)
from (
* *select salespersonid,
* * * * * salesandcommish[1] as month_value
* *from sales
* *order by dateofsale asc
) t
group by salespersonid;

although I'm not 100% sure if the aggregate will really honor the order by

With 9.0 this is a lot easier:

SELECT salespersonid, array_agg(salesandcommish[1] order by dateofsale)
FROM sales
GROUP BY salespersonid;

Hans,

thank you.

unfortunately the db in question is greenplum, not true pg, so neither
works :-(

I shall keep hunting.

REgards,
Chas.

#4

On Mar 21, 3:12*pm, "Chas." <chas... (AT) gmail (DOT) com> wrote:

Quote:

On Mar 21, 1:35*pm, Hans Castorp <REWYRLXHE... (AT) spammotel (DOT) com> wrote:

Chas. wrote on 21.03.2011 21:00:

All,

I have a table analogous to this:

SALES

* * * * *Column * * * *| * * * * * *Type * * * * * * | Modifiers
----------------------+-----------------------------+-----------
salespersonid * * * * * bigint * * * * * * * * * not null
dateofsale * * * * * * * *timestamp * * * * * not null
salesandcommish * * bigint[] * * * * * * * *not null

For each salespersonid, I want an array of his/her sales (array
element one); e.g.

salespersonid *monthlysales
100 * * * * * * * | {123,45,8545,3446,3356,..... etc.}

that monthlysales would contain data ordered by dateofsale....

Any ideas would be most appreciated.

select salespersonid,
* * * * array_agg(month_value)
from (
* *select salespersonid,
* * * * * salesandcommish[1] as month_value
* *from sales
* *order by dateofsale asc
) t
group by salespersonid;

although I'm not 100% sure if the aggregate will really honor the orderby

With 9.0 this is a lot easier:

SELECT salespersonid, array_agg(salesandcommish[1] order by dateofsale)
FROM sales
GROUP BY salespersonid;

Hans,

thank you.

unfortunately the db in question is greenplum, not true pg, so neither
works :-(

I shall keep hunting.

REgards,
Chas.

All,

I found this little ditty which does the trick on greenplum:

CREATE AGGREGATE array_agg(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
INITCOND=’{}’
);

it's from http://abdulyadi.wordpress.com/2008/...ql-version-84/

REgards,
Chas.

#5

On 22.03.2011 01:02, Chas. wrote:

Quote:

On Mar 21, 3:12 pm, "Chas."<chas... (AT) gmail (DOT) com> wrote:
On Mar 21, 1:35 pm, Hans Castorp<REWYRLXHE... (AT) spammotel (DOT) com> wrote:

Chas. wrote on 21.03.2011 21:00:

All,

I have a table analogous to this:

SALES

Column | Type | Modifiers
----------------------+-----------------------------+-----------
salespersonid bigint not null
dateofsale timestamp not null
salesandcommish bigint[] not null

For each salespersonid, I want an array of his/her sales (array
element one); e.g.

salespersonid monthlysales
100 | {123,45,8545,3446,3356,..... etc.}

that monthlysales would contain data ordered by dateofsale....

Any ideas would be most appreciated.

select salespersonid,
array_agg(month_value)
from (
select salespersonid,
salesandcommish[1] as month_value
from sales
order by dateofsale asc
) t
group by salespersonid;

although I'm not 100% sure if the aggregate will really honor the order by

With 9.0 this is a lot easier:

SELECT salespersonid, array_agg(salesandcommish[1] order by dateofsale)
FROM sales
GROUP BY salespersonid;

Hans,

thank you.

unfortunately the db in question is greenplum, not true pg, so neither
works :-(

I shall keep hunting.

REgards,
Chas.

All,

I found this little ditty which does the trick on greenplum:

CREATE AGGREGATE array_agg(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
INITCOND=’{}’
);

it's from http://abdulyadi.wordpress.com/2008/...ql-version-84/

Why don't you want to have another table? Then you can easily ensure
proper ordering of sales.

table sale ( salespersonid, date_of_sale, sale, modifier )

The you can conveniently join that table to the other one (or even use
it directly) including ORDER BY date_of_sale etc.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

looking for a little sql magic w/ arrays

comp.databases.postgresql comp.databases.postgresql

looking for a little sql magic w/ arrays - 03-21-2011 , 02:00 PM

Re: looking for a little sql magic w/ arrays - 03-21-2011 , 02:35 PM

Re: looking for a little sql magic w/ arrays - 03-21-2011 , 04:12 PM

Re: looking for a little sql magic w/ arrays - 03-21-2011 , 06:02 PM

Re: looking for a little sql magic w/ arrays - 03-26-2011 , 11:34 AM