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

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(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
>
>