Re: Need help with SQL query and finding NULL array_agg - Mailing list pgsql-general

On 2012-08-02 21:32, David Johnston wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Condor
>> Sent: Thursday, August 02, 2012 4:35 AM
>> To: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Need help with SQL query and finding NULL
>> array_agg
>>
>> On 2012-08-01 23:59, David Johnston wrote:
>> >> -----Original Message-----
>> >> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> >> owner@postgresql.org] On Behalf Of Condor
>> >> Sent: Wednesday, August 01, 2012 4:16 PM
>> >> To: pgsql-general@postgresql.org
>> >> Subject: [GENERAL] Need help with SQL query and finding NULL
>> >> array_agg
>> >>
>> >> Hello ppl,
>> >>
>> >> I have a problem with one sql query, can some one help me. My
>> query
>> >> is:
>> >>
>> >> SELECT array_agg(month) AS month, array_agg(status) AS status,
>> >> array_agg(service) AS service, case when
>> >> array_upper(array_agg(phone),
>> >> 1) is not null THEN array_agg(phone) else array_agg(mobile) END
>> FROM
>> >> bills WHERE status > 1 GROUP BY mobile
>> >>
>> >> I try with simple query to identify did array_agg(phone) is empty
>> or
>> >> null and if is it to return me mobile field, if not empty to
>> return
>> >> me phone.
>> >> Mobile field
>> >> exist always, but phone may exists for that mobile may not
>> exists.
>> >> One mobile can have few services like:
>> >>
>> >>
>> >>    mob         service    phone
>> >> 1321543434      64
>> >> 1321543434      66       13255555
>> >>
>> >>
>> >> I try few thing but sql only return me records that phone is not
>> >> empty, but I need them both.
>> >>
>> >> Any one has ideas what I can do ?
>> >
>> > ARRAY_AGG() is never an empty array since there is always at least
>> a
>> > single record that is going to be aggregated.  In your case your
>> array
>> > will have NULL "values" when phone numbers are missing but the
>> upper
>> > bound will still show a positive number.
>> >
>> > SELECT array_upper(ARRAY[NULL]::varchar[], 1) == 1
>> >
>> > You would need to write a custom aggregation that ignores NULL and
>> > thus could return an empty array if no valid phone numbers are
>> > present.
>> >
>> > The proper logic would be:
>> >
>> > CASE WHEN array_upper(phone_agg, 1) = 1 AND phone_agg[1] IS NULL
>> THEN
>> > ... END
>> >
>> > You also likely want to use:
>> >
>> > ARRAY_AGG(DISTINCT column) -- this makes sure duplicates are only
>> > present a single time and  ensure that an all-NULL situation
>> results
>> > in a single element instead of one NULL for each input record.
>> >
>> > Hopefully this help because I couldn't make heads nor tails as to
>> what
>> > exactly your issue is.  The lack of input data, the current
>> output,
>> > and the desired output limits my ability to understand and help.
>> >
>> > One last comment:  I would generally avoid naming the output of an
>> > ARRAY_AGG(column) the same name as the input column.  I generally,
>> at
>> > minimum, make the output column name plural to reflect the fact
>> that
>> > it contains multiple values of whatever is stored in the source
>> > column.
>> >
>> > David J.
>>
>>
>> Hello,
>> I understand what is the problem, but I can't combine your example
>> with my
>> case.
>>
>> I write my example in middle of the night and may be I miss to say
>> explain
>> much more about the structure:
>>
>>
>>      mob         service    phone     month
>>       132999      64                     1
>>   1321543434      66       13255555      1
>>   1321543434      67       13255555      2
>>
>> First record when phone is empty and mob is 132999 the number is
>> correct.
>> Second two records also is correct, but the phone is not empty so I
>> need
>> that filed phone, they have services and month when to start.
>> I'm unable to use phone_agg[1] IS NULL  because sql return me error
>> that can't
>> use the phone_agg[1]
>>
>> Im expect that result:
>>     month | status  | service |         array_agg
>> ---------+---------+---------+---------------------------
>>     {07}  |  {0}    |  {64}   | {132999}
>>   {08,07} | {0,0}   | {66,67} | {13255555,13255555}
>>
>>
>> In the end I will have arrays for every phone which service will
>> use.
>>
>>
>
> Hopefully this will help.
>
> The first thing I did was break up the query into parts
> 0) data
> 1) aggregation
> 2) conditional return
>
> Note I am using the ability for the CTE to provide column names so
> the contained queries are not cluttered with "AS alias" constructs.
>
> In order to make things simpler I avoid storing NULL in the "phones"
> array and instead store "N/A" if the phone is missing.  This lets me
> use " op ANY/ALL(array)" later on to check on the contents of the
> array.  The result of that condition is called "final_phones" and it
> either matches the "mobs" or the "phones" array depending on whether
> all of the "phone" numbers are missing.
>
> Another option is to use the "masterphones" array where the value
> stored into the array is the "phone" number if present otherwise it
> is
> the "mob" number.
>
> WITH data (mob, service, phone, mth) AS (
>      VALUES
>        ('132999','64',NULL,'1')
>      , ('132999','65','12345','1')
>      , ('1321543434','66','13255555','1')
>      , ('1321543434','67','13255555','2')
> )
> , maingroup (mob, mobiles, services, phones, months, masterphones) AS
> (
>   SELECT mob, array_agg(mob), array_agg(service),
> array_agg(COALESCE(phone,'N/A')), array_agg(mth),
> array_agg(COALESCE(phone, mob))
>   FROM data
>   GROUP BY mob
> )
> SELECT *, CASE WHEN 'N/A' = ALL(phones) THEN mobiles ELSE phones END
> AS final_phones
> FROM maingroup
>
> David J.

Very interesting,
it's very useful.

Thank you.