Skip site navigation (1) Skip section navigation (2)

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

From: Mark Roberts <mailing_lists(at)pandapocket(dot)com>
To: "Rodrigo E(dot) De" León Plicet <rdeleonp(at)gmail(dot)com>
Cc: Carol Cheung <cacheung(at)consumercontact(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: unsure of how to query for desired data/output
Date: 2008-12-01 23:00:07
Message-ID: 1228172407.32631.29.camel@localhost (view raw or flat)
Thread:
Lists: 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(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
> 
> 


In response to

pgsql-novice by date

Next:From: Andreas JuniusDate: 2008-12-02 07:12:15
Subject: Re: SQL Status:42883
Previous:From: Mark RobertsDate: 2008-12-01 22:37:41
Subject: Re: unsure of how to query for desired data/output

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group