Обсуждение: help with aggregation query across a second text array column

Поиск
Список
Период
Сортировка

help with aggregation query across a second text array column

От
Scot Kreienkamp
Дата:

 

Hi everyone,

 

 

I had a dataset like so:

 

Servername|Primary class

----------------------------------

Server1| retail

Server2| dmz

Server3 | NA

 

 

And I used this query to summarize it for automated tools:

select environment|| ':' || string_agg(name,',') from servers group by environment order by environment;

 

 

Now my dataset has changed to:

Servername text|Primary class text|Secondary class text[1d array]

--------------------------------------------------------------------------------

Server1| retail |[‘dmz’]

 

 

 

There can be multiple classes for secondary but they will be the same classes as the primary classes.  Input is controlled via web pages so that should be guaranteed.  For instance, servers that have primary class of SQL but also belong to an application class.  Now I need to change my summary query to include all the servers for each class taking into account the secondary class column and I’m completely baffled on how to do so with the array.  I know the any trick and use it to match against the array when querying for specific primary and secondary classes but I can’t figure out how to generate the listing the same as the old summary query I was using.  

 

Scot Kreienkamp | Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162  | ( 734-384-6403 | | ) 7349151444 | *  Scot.Kreienkamp@la-z-boy.com
www.la-z-boy.com | facebook.com/lazboy | twitter.com/lazboy | youtube.com/lazboy

 

 

This message is intended only for the individual or entity to which it is addressed.  It may contain privileged, confidential information which is exempt from disclosure under applicable laws.  If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information.  If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.

Вложения

Re: help with aggregation query across a second text array column

От
Rob Nikander
Дата:

> On Nov 12, 2018, at 9:40 AM, Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com> wrote:
> …

I’m not too confident in my answer here (there could be a better way), but this might help. You could use the `unnest`
functionto transform the array into multiple rows. For example, given a table like  

    create table t1 (env text, cls text, cls2 text[]);

I can query it like:

    select env, string_agg(cls, ‘,’)
      from (select env, cls from t1
               union
               select env, unnest(cls2) from t1) t
    group by env;

Rob

RE: help with aggregation query across a second text array column

От
Scot Kreienkamp
Дата:
Thank you very much Rob, that concept worked out nicely.  I would never have thought of unioning the table to itself
withunnest.
 

Here's my final query:

   select environment ||':' || string_agg(name, ',')
      from (
select name,environment from servers union select name,unnest(auditenvironment) as environment from servers order by
name)t
 
    group by environment order by environment;

Cheers!


Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 |  Office: 734-384-6403 |  |  Mobile: 7349151444 | Email:
Scot.Kreienkamp@la-z-boy.com
> -----Original Message-----
> From: Rob Nikander [mailto:rob.nikander@gmail.com]
> Sent: Monday, November 12, 2018 10:30 AM
> To: Postgres General <pgsql-general@postgresql.org>
> Cc: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
> Subject: Re: help with aggregation query across a second text array column
>
>
>
> > On Nov 12, 2018, at 9:40 AM, Scot Kreienkamp <Scot.Kreienkamp@la-z-
> boy.com> wrote:
> > …
>
> I’m not too confident in my answer here (there could be a better way), but this
> might help. You could use the `unnest` function to transform the array into
> multiple rows. For example, given a table like
>
>     create table t1 (env text, cls text, cls2 text[]);
>
> I can query it like:
>
>     select env, string_agg(cls, ‘,’)
>       from (select env, cls from t1
>                union
>                select env, unnest(cls2) from t1) t
>     group by env;
>
> Rob

This message is intended only for the individual or entity to which it is addressed.  It may contain privileged,
confidentialinformation which is exempt from disclosure under applicable laws.  If you are not the intended recipient,
youare strictly prohibited from disseminating or distributing this information (other than to the intended recipient)
orcopying this information.  If you have received this communication in error, please notify us immediately by e-mail
orby telephone at the above number. Thank you.