Re: unsure of how to query for desired data/output

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(at)consumercontact(dot)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