[SQL] SUM Array values query
Hi all,
I've this query including arrays:
SELECT hour[1:5], statistics_date
FROM statistics_daily
WHERE statistics_date = to_date( '03/01/2008', 'DD/MM/' )
Result:
hour| statistics_date
+-
{1800,1800,1800,1800,1800} | 2008-01-03
I'm just wondering if there's some way to retrieve the hour column as the
sum of the array values... Just like this:
hour| statistics_date
+-
9000 | 2008-01-03
Hour is a bigint[] array column.
My version of postreSQL is 8.1.9
Thank you in advanced!
P.S.: Sorry if I had send it before, but I think I was not subscribed to the
mailist.
--
Dani Castaños Sánchez
dcasta...@androme.es
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Extracting data from arrays
Hi all,
I've this query including arrays:
SELECT hour[1:5], statistics_date
FROM statistics_daily
WHERE statistics_date = to_date( '03/01/2008', 'DD/MM/' )
Result:
hour| statistics_date
+-
{1800,1800,1800,1800,1800} | 2008-01-03
I'm just wondering if there's some way to retrieve the hour column as the
sum of the array values... Just like this:
hour| statistics_date
+-
9000 | 2008-01-03
Hour is a bigint[] array column.
My version of postreSQL is 8.1.9
Thank you in advanced!
--
Dani Castaños Sánchez
dcasta...@androme.es
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extracting data from arrays
Hello
postgres=# create or replace function sum_items(bigint[]) returns
bigint as $$ select sum($1[i])::bigint from
generate_series(array_lower($1,1), array_upper($1,1)) g(i)$$ language
sql immutable;
CREATE FUNCTION
Time: 2,510 ms
postgres=# select sum_items(array[1,2,3,4]); sum_items
---
10
(1 row)
regards
Pavel Stehule
2009/5/18 Dani Castaños :
> Hi all,
>
> I've this query including arrays:
>
> SELECT hour[1:5], statistics_date
> FROM statistics_daily
> WHERE statistics_date = to_date( '03/01/2008', 'DD/MM/' )
>
> Result:
>
> hour | statistics_date
> +-
> {1800,1800,1800,1800,1800} | 2008-01-03
>
>
> I'm just wondering if there's some way to retrieve the hour column as the
> sum of the array values... Just like this:
>
> hour | statistics_date
> +-
> 9000 | 2008-01-03
>
>
> Hour is a bigint[] array column.
> My version of postreSQL is 8.1.9
>
> Thank you in advanced!
>
> --
> Dani Castaños Sánchez
> dcasta...@androme.es
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] proposal for a CookBook in postgresql.org
I just saw a beatifull answer from Pavel, as an answer to this question: """ I'm just wondering if there's some way to retrieve the hour column as the > sum of the array values... Just like this: > >hour| statistics_date > +- > 9000 | 2008-01-03 """ With this function: """ postgres=# create or replace function sum_items(bigint[]) returns bigint as $$ select sum($1[i])::bigint from generate_series(array_lower($1,1), array_upper($1,1)) g(i)$$ language sql immutable; CREATE FUNCTION Time: 2,510 ms postgres=# select sum_items(array[1,2,3,4]); sum_items --- 10 (1 row) """ I think this is a good time to propose some kind of CookBook, to preserve this kind of answers. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] proposal for a CookBook in postgresql.org
On Mon, May 18, 2009 at 1:29 PM, Gerardo Herzig wrote: > I think this is a good time to propose some kind of CookBook, to > preserve this kind of answers. What, like this one? http://wiki.postgresql.org/wiki/Snippets :-) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extracting data from arrays
In article <162867790905180410n670062b0ud2d7fdd0e652...@mail.gmail.com>,
Pavel Stehule writes:
> Hello
> postgres=# create or replace function sum_items(bigint[]) returns
> bigint as $$ select sum($1[i])::bigint from
> generate_series(array_lower($1,1), array_upper($1,1)) g(i)$$ language
> sql immutable;
> CREATE FUNCTION
> Time: 2,510 ms
> postgres=# select sum_items(array[1,2,3,4]); sum_items
> ---
> 10
> (1 row)
> regards
> Pavel Stehule
> 2009/5/18 Dani Castaños :
>> Hi all,
>>
>> I've this query including arrays:
>>
>> SELECT hour[1:5], statistics_date
>> FROM statistics_daily
>> WHERE statistics_date = to_date( '03/01/2008', 'DD/MM/' )
>>
>> Result:
>>
>> hour | statistics_date
>> +-
>> {1800,1800,1800,1800,1800} | 2008-01-03
>>
>>
>> I'm just wondering if there's some way to retrieve the hour column as the
>> sum of the array values... Just like this:
>>
>> hour | statistics_date
>> +-
>> 9000 | 2008-01-03
>>
>>
>> Hour is a bigint[] array column.
FWIW, in PostgreSQL 8.4 you won't need your own sum_items function any
more:
SELECT sum(unnest), statistics_date
FROM (
SELECT statistics_date, unnest(hour)
FROM t1
) AS dummy
WHERE statistics_date = '2008-01-03'
GROUP BY statistics_date
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] proposal for a CookBook in postgresql.org
Dave Page wrote: > On Mon, May 18, 2009 at 1:29 PM, Gerardo Herzig wrote: > >> I think this is a good time to propose some kind of CookBook, to >> preserve this kind of answers. > > What, like this one? > > http://wiki.postgresql.org/wiki/Snippets > > :-) > Oops. Yeah, kind of :) Did not see any direct link from "Docs" main page. Sory about the noise. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] proposal for a CookBook in postgresql.org
2009/5/18 Gerardo Herzig : > Dave Page wrote: >> On Mon, May 18, 2009 at 1:29 PM, Gerardo Herzig wrote: >> >>> I think this is a good time to propose some kind of CookBook, to >>> preserve this kind of answers. >> >> What, like this one? >> >> http://wiki.postgresql.org/wiki/Snippets >> >> :-) >> > Oops. Yeah, kind of :) > Did not see any direct link from "Docs" main page. > > this is usual tricks for arrays - you ca use it for min, max, avg, sort for arrays. In 8.4 is possible to use generate_subscripts functions. regards Pavel Stehule others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks regards Pavel Stehule -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extracting data from arrays
Thank you very much!
-Mensaje original-
De: Pavel Stehule [mailto:pavel.steh...@gmail.com]
postgres=# create or replace function sum_items(bigint[]) returns
bigint as $$ select sum($1[i])::bigint from
generate_series(array_lower($1,1), array_upper($1,1)) g(i)$$ language
sql immutable;
CREATE FUNCTION
Time: 2,510 ms
postgres=# select sum_items(array[1,2,3,4]); sum_items
---
10
(1 row)
regards
Pavel Stehule
2009/5/18 Dani Castaños :
> Hi all,
>
> I've this query including arrays:
>
> SELECT hour[1:5], statistics_date
> FROM statistics_daily
> WHERE statistics_date = to_date( '03/01/2008', 'DD/MM/' )
>
> Result:
>
>hour| statistics_date
> +-
> {1800,1800,1800,1800,1800} | 2008-01-03
>
>
> I'm just wondering if there's some way to retrieve the hour column as the
> sum of the array values... Just like this:
>
>hour| statistics_date
> +-
> 9000 | 2008-01-03
>
>
> Hour is a bigint[] array column.
> My version of postreSQL is 8.1.9
>
> Thank you in advanced!
>
> --
> Dani Castaños Sánchez
> dcasta...@androme.es
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SUM Array values query
2009/5/18 Dani Castaños :
> Hi all,
>
> I've this query including arrays:
>
> SELECT hour[1:5], statistics_date
> FROM statistics_daily
> WHERE statistics_date = to_date( '03/01/2008', 'DD/MM/' )
>
> Result:
>
> hour | statistics_date
> +-
> {1800,1800,1800,1800,1800} | 2008-01-03
>
>
> I'm just wondering if there's some way to retrieve the hour column as the
> sum of the array values... Just like this:
>
> hour | statistics_date
> +-
> 9000 | 2008-01-03
>
>
> Hour is a bigint[] array column.
> My version of postreSQL is 8.1.9
>
> Thank you in advanced!
>
> P.S.: Sorry if I had send it before, but I think I was not subscribed to the
> mailist.
>
> --
> Dani Castaños Sánchez
> dcasta...@androme.es
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Create function in pure sql and ...
,-[15:27:18]mer...@[local]:5432=
`-merlin>create function array_sum (bigint[]) returns bigint as $$
SELECT sum($1[i])::bigint FROM
generate_series(array_lower($1,1),array_upper($1,1)
) index(i); $$ language sql;
CREATE FUNCTION
Time: 16,203 ms
,-[15:28:02]mer...@[local]:5432=
`-merlin>select array_sum(col_array) from t30;
array_sum
---
9000
(1 row)
:)
--
Pawel Socha
pawel.so...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Extract week from date
Hi again, I need to extract date grouped by week from an statistics table. I was trying something like this: SELECT total_duration, EXTRACT( week from date statistics_date ) FROM statistics_daily GROUP BY EXTRACT( week from date statistics_date ), total_duration; But it doesn't works... Neither: SELECT total_duration, statistics_date FROM statistics_daily GROUP BY EXTRACT( day from statistics_date ), total_duration, statistics_date; How can it be done? Note: statistics_date is a DATE data type column. Regards. -- Dani Castaños Sánchez dcasta...@androme.es -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extract week from date
2009/5/18 Dani Castaños > Hi again, > > I need to extract date grouped by week from an statistics table. > > I was trying something like this: > > SELECT total_duration, EXTRACT( week from date statistics_date ) > FROM statistics_daily > GROUP BY EXTRACT( week from date statistics_date ), total_duration; > > But it doesn't works... Neither: > > SELECT total_duration, statistics_date > FROM statistics_daily > GROUP BY EXTRACT( day from statistics_date ), total_duration, > statistics_date; > > hmmm.. it's not clear what do you want. why not just something like SELECT EXTRACT( week from statistics_date ) as week, SUM( total_duration) as total_duration_sum FROM statistics_daily GROUP BY 1; ? -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [SQL] Extract week from date
Dow!!! You’re ok! I forgot the aggregator funciont SUM! Thanks! De: Filip Rembiałkowski [mailto:plk.zu...@gmail.com] Enviado el: lunes, 18 de mayo de 2009 16:54 Para: Dani Castaños CC: pgsql-sql@postgresql.org Asunto: [?? Probable Spam] Re: [SQL] Extract week from date 2009/5/18 Dani Castaños Hi again, I need to extract date grouped by week from an statistics table. I was trying something like this: SELECT total_duration, EXTRACT( week from date statistics_date ) FROM statistics_daily GROUP BY EXTRACT( week from date statistics_date ), total_duration; But it doesn't works... Neither: SELECT total_duration, statistics_date FROM statistics_daily GROUP BY EXTRACT( day from statistics_date ), total_duration, statistics_date; hmmm.. it's not clear what do you want. why not just something like SELECT EXTRACT( week from statistics_date ) as week, SUM( total_duration) as total_duration_sum FROM statistics_daily GROUP BY 1; ? -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [SQL] Extrapolating performance expectation
On Mon, May 18, 2009 at 6:47 AM, Kenneth Marshall wrote: > On Sun, May 17, 2009 at 10:04:46PM -0600, Rob Sargent wrote: > > Can one extrapolate future performance expectations for ever-growing > tables > > from a given (non-trivial) data set, and if so with what curve? > Corollary: > > what would one expect a performance curve to approximate in terms of > query > > execution time v. number of data rows (hardware, load staying constant). > > > > I have user and group information on system usage. I would like to be > able > > to do year-to-date counts per user given a single group id but in the > data > > for one business quarter the query is taking in between 10 and 60+ > seconds > > depending on both on the size of the group and the group's total usage. > > Groups typically have 10-100 users and consume 20K - 80K records in a 9M > > record data set. Group id column is indexed, but it is not the primary > > index. (Sad note: two pseudo groups account for 50 percent of the total > > records IIRC (and will never be used for the usage-by-group query below) > > > > This is a single table query: > > > > select user_id, element_type, count(*) > > from dataset > > where group_id = N > > group by user_id, element_type > > order by user_id, element_type > > > > Is this the sort of situation which might benefit from increasing the > number > > of histogram bins (alter table alter column statistics (N>10))? > > > > Any and all pointers appreciated, > > Rob, > > What about partitioning the table based on the group. Then you could > put the two pseudo groups in their own separate child table. > > Cheers, > Ken > Ken, Interesting point. I'm hoping the psuedo groups are on the verge of extinction but will certainly investigate the possibility. R.
Re: [SQL] proposal for a CookBook in postgresql.org
Pavel Stehule escribió: > others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks There's a lot of good stuff in there ... would you care about copying/moving it to wiki.postgresql.org/wiki/Snippets ? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] proposal for a CookBook in postgresql.org
2009/5/18 Alvaro Herrera : > Pavel Stehule escribió: > >> others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks > > There's a lot of good stuff in there ... would you care about > copying/moving it to wiki.postgresql.org/wiki/Snippets ? > This contents is free - and if you would do it, please do it. My english isn't good, so it's work for someone with good english. I am maintainer and founder of this site, and I am granting rights for free content copy. Regards Pavel > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] proposal for a CookBook in postgresql.org
Pavel Stehule escribió: > 2009/5/18 Alvaro Herrera : > > Pavel Stehule escribió: > > > >> others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks > > > > There's a lot of good stuff in there ... would you care about > > copying/moving it to wiki.postgresql.org/wiki/Snippets ? > > This contents is free - and if you would do it, please do it. My > english isn't good, so it's work for someone with good english. I am > maintainer and founder of this site, and I am granting rights for free > content copy. I don't have time for that right now, but I have added a link to your page at the top of Snippets. Thanks. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
