Re: unsure of how to query for desired data/output - Mailing list pgsql-novice

From Mark Roberts
Subject Re: unsure of how to query for desired data/output
Date
Msg-id 1228171061.32631.22.camel@localhost
Whole thread Raw
In response to Re: unsure of how to query for desired data/output  ("Rodrigo E. De León Plicet" <rdeleonp@gmail.com>)
Responses Re: unsure of how to query for desired data/output
List pgsql-novice
On Mon, 2008-12-01 at 16:08 -0500, Rodrigo E. De León Plicet wrote:
> On Mon, Dec 1, 2008 at 11:35 AM, Carol Cheung
> <cacheung@consumercontact.com> wrote:
> > Is it possible to get the following based on the above list of numbers:
> >
> > 25|3
> > 3,17|8
> > 40,28,6|3
> > 17|2
>
> create table t (
> status int
> );
>
> insert into t values
> (25),(25),(25),(3),(3),(3),(3),(3),(3),(17),(17),(6),(28),(28);
>
> select * from t;
>
>  status
> --------
>      25
>      25
>      25
>       3
>       3
>       3
>       3
>       3
>       3
>      17
>      17
>       6
>      28
>      28
> (14 rows)
>
> select replace(replace(x.val::text,'{',''),'}','') as status, count(t.*)
> from t, (values ('{25}'::int[]), ('{3,17}'), ('{40,28,6}'), ('{17}')) as x(val)
> where t.status=any(x.val)
> group by replace(replace(x.val::text,'{',''),'}','');
>
>  status  | count
> ---------+-------
>  25      |     3
>  3,17    |     8
>  40,28,6 |     3
>  17      |     2
> (4 rows)
>

Well, if you have a somewhat static list of interesting statuses (such
that you always want to monitor for the same set of statuses), you could
try something like this.  It works by using a table
(interesting_statuses) that groups the statuses into an interesting
group with an array.

The join makes sure that each status (and sum) is joined to the
appropriate array(s), and the final group simply sums the appropriate
status counts by list.

For more information about arrays:
http://www.postgresql.org/docs/8.3/interactive/arrays.html

--- SETUP ---
create temp table status (status integer);
create temp table interesting_statuses (status_list_no integer,
status_list integer[]);

insert into status values
(25),(25),(25),(3),(3),(3),(3),(3),(3),(17),(17),(6),(28),(28);
insert into interesting_statuses values (0, ARRAY[25]), (1, ARRAY[3,
17]), (2, ARRAY[40,28,6]), (3, ARRAY[17]);

-- Query --

select status_list, sum(status_count) as total_status_counts
from (
        select status, count(1) as status_count
        from status
        group by status
    ) x
    left outer join interesting_statuses int on (status =
ANY(status_list))
group by status_list
order by sum(status_count) desc
;

----
CREATE TABLE
Time: 3.839 ms
CREATE TABLE
Time: 62.156 ms
INSERT 0 14
Time: 1.737 ms
INSERT 0 4
Time: 0.579 ms
 status_list | total_status_counts
-------------+---------------------
 {3,17}      |                   8
 {25}        |                   3
 {40,28,6}   |                   3
 {17}        |                   2
(4 rows)


-Mark


pgsql-novice by date:

Previous
From: "Rodrigo E. De León Plicet"
Date:
Subject: Re: unsure of how to query for desired data/output
Next
From: Mark Roberts
Date:
Subject: Re: unsure of how to query for desired data/output