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

A coworker points out that my status list can be more naturally
expressed as an unkeyed join table:

create table status_groupings (
    status_no,
    grouping_no
);

I also suggest starting with status_groupings and then left joining over
to statuses (so that you catch the case of a status not having any
values), or inner joining so that you cull out unnecessary statuses.

Shout outs to Kane.

-Mark

On Mon, 2008-12-01 at 14:37 -0800, Mark Roberts wrote:
> 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
>
>