You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@asterixdb.apache.org by James Fang <jf...@ucr.edu> on 2015/11/09 23:11:31 UTC
Postgres Aggregation Function - Discussion
The following functions that I am asking about are from
http://www.postgresql.org/docs/9.4/static/functions-aggregate.html
The following google doc contains a spreadsheet of all the current
aggregation functions that exist in AQL and SQL. The colored portions will
be the portions that I wish to discuss. I assumed that AQL returns null if
at least 1 of the results in the aggregation is null while SQL ignores the
tuple if it is null.
https://docs.google.com/document/d/1l9QtRAB7H3yzP3976livpKzJZHVJlPdvDrjlm2YCsM0/edit?usp=sharing
Questions:
1) Do we need array_agg, this function just groups up the results together
into one array as output. If so, do we want a scalar version or aggregate
version?
2) For bitwise-or and bitwise-and, I have implemented a sql aggregate
version. Do we want a AQL aggregate version as well? Do you think that we
should implement this as a scalar or as an aggregation?
3) For boolean-or and boolean and, we current have an existing scalar
version for AQL. Do we need a scalar version for SQL? Should we implement a
aggregate version of these boolean functions?
4) Do we need a json_agg function? I do not think so because all the data
is returned in json format. If you have a different answer, let me know.
5) For string_agg, we currently have a scalar version for AQL known as
string-join. Do we need a SQL version?
Re: Postgres Aggregation Function - Discussion
Posted by Yingyi Bu <bu...@gmail.com>.
>>Questions:
>>1) Do we need array_agg, this function just groups up the results together
>>into one array as output. If so, do we want a scalar version or aggregate
>>version?
AQL has an aggregation called "listify" which does the same thing as
"array_agg".
What's the scalar version of it? Do you mean list constructors? If so,
we have ordered list constructor, e.g., [1, 2, 3]
and unordered list constructor, e.g., {{1, 2, 3}}
>>2) For bitwise-or and bitwise-and, I have implemented a sql aggregate
>>version. Do we want a AQL aggregate version as well? Do you think that we
>>should implement this as a scalar or as an aggregation?
Yes, that would be nice!
>> " which is to say that if you have a null (a.k.a. unknown) input then
the output must logically be unknown"
@Mike, bitwsie-or and bitwise-and could be an exception to that:
0xfffffffff bitwise-or null = 0xffffffff
0 bitwise-and null = 0
>>3) For boolean-or and boolean and, we current have an existing scalar
>>version for AQL. Do we need a scalar version for SQL?
Is AQL different from SQL for scalar and/or?
>>Should we implement a
>>aggregate version of these boolean functions?
That will be fantastic!
>>4) Do we need a json_agg function? I do not think so because all the data
>>is returned in json format. If you have a different answer, let me know.
I agree with you.
>>5) For string_agg, we currently have a scalar version for AQL known as
>>string-join. Do we need a SQL version?
That would be fantastic!
Thanks!
Best,
Yingyi
On Wed, Nov 18, 2015 at 5:55 PM, Mike Carey <dt...@gmail.com> wrote:
> James,
>
> We all need to look at this!!! Sorry sorry sorry! I will have a look
> ASAP (by early next week, say Tuesday since that's traditionally a "UCR
> day" for me, at the latest).
>
> Question - what do you mean by "do we want a scalar or aggregate version"
> of the functions? It seems like we don't have a choice, do we? Aren't
> they inherently aggregate functions (so the answer would be the latter)? I
> think maybe I didn't get the gist of the question. On the SQL vs. AQL
> versions - it would be nice to have both versions of each (aggregate)
> function, actually - i.e., one version that does what SQL does in terms of
> turning a blind eye to nulls, and one version that does what AQL does,
> which is to say that if you have a null (a.k.a. unknown) input then the
> output must logically be unknown as well (e.g., if you add two numbers by
> either doing "+" or having them appear in a two-object dataset's numeric
> column and doing sum( ), you should get the same answer either way).
>
> More to follow when I look more closely! Sorry we've all been so slow.
> (@Others: your take(s)?)
>
> Cheers,
> Mike
>
>
>
> On 11/18/15 2:06 PM, James Fang wrote:
>
>> Just wondering if anyone has a response on this discussion?
>>
>> Thanks,
>> James
>>
>> On Mon, Nov 9, 2015 at 2:11 PM, James Fang <jf...@ucr.edu> wrote:
>>
>> The following functions that I am asking about are from
>>> http://www.postgresql.org/docs/9.4/static/functions-aggregate.html
>>>
>>> The following google doc contains a spreadsheet of all the current
>>> aggregation functions that exist in AQL and SQL. The colored portions
>>> will
>>> be the portions that I wish to discuss. I assumed that AQL returns null
>>> if
>>> at least 1 of the results in the aggregation is null while SQL ignores
>>> the
>>> tuple if it is null.
>>>
>>>
>>> https://docs.google.com/document/d/1l9QtRAB7H3yzP3976livpKzJZHVJlPdvDrjlm2YCsM0/edit?usp=sharing
>>>
>>> Questions:
>>> 1) Do we need array_agg, this function just groups up the results
>>> together
>>> into one array as output. If so, do we want a scalar version or aggregate
>>> version?
>>> 2) For bitwise-or and bitwise-and, I have implemented a sql aggregate
>>> version. Do we want a AQL aggregate version as well? Do you think that we
>>> should implement this as a scalar or as an aggregation?
>>> 3) For boolean-or and boolean and, we current have an existing scalar
>>> version for AQL. Do we need a scalar version for SQL? Should we
>>> implement a
>>> aggregate version of these boolean functions?
>>> 4) Do we need a json_agg function? I do not think so because all the data
>>> is returned in json format. If you have a different answer, let me know.
>>> 5) For string_agg, we currently have a scalar version for AQL known as
>>> string-join. Do we need a SQL version?
>>>
>>>
>
Re: Postgres Aggregation Function - Discussion
Posted by Mike Carey <dt...@gmail.com>.
James,
We all need to look at this!!! Sorry sorry sorry! I will have a look
ASAP (by early next week, say Tuesday since that's traditionally a "UCR
day" for me, at the latest).
Question - what do you mean by "do we want a scalar or aggregate
version" of the functions? It seems like we don't have a choice, do
we? Aren't they inherently aggregate functions (so the answer would be
the latter)? I think maybe I didn't get the gist of the question. On
the SQL vs. AQL versions - it would be nice to have both versions of
each (aggregate) function, actually - i.e., one version that does what
SQL does in terms of turning a blind eye to nulls, and one version that
does what AQL does, which is to say that if you have a null (a.k.a.
unknown) input then the output must logically be unknown as well (e.g.,
if you add two numbers by either doing "+" or having them appear in a
two-object dataset's numeric column and doing sum( ), you should get the
same answer either way).
More to follow when I look more closely! Sorry we've all been so slow.
(@Others: your take(s)?)
Cheers,
Mike
On 11/18/15 2:06 PM, James Fang wrote:
> Just wondering if anyone has a response on this discussion?
>
> Thanks,
> James
>
> On Mon, Nov 9, 2015 at 2:11 PM, James Fang <jf...@ucr.edu> wrote:
>
>> The following functions that I am asking about are from
>> http://www.postgresql.org/docs/9.4/static/functions-aggregate.html
>>
>> The following google doc contains a spreadsheet of all the current
>> aggregation functions that exist in AQL and SQL. The colored portions will
>> be the portions that I wish to discuss. I assumed that AQL returns null if
>> at least 1 of the results in the aggregation is null while SQL ignores the
>> tuple if it is null.
>>
>> https://docs.google.com/document/d/1l9QtRAB7H3yzP3976livpKzJZHVJlPdvDrjlm2YCsM0/edit?usp=sharing
>>
>> Questions:
>> 1) Do we need array_agg, this function just groups up the results together
>> into one array as output. If so, do we want a scalar version or aggregate
>> version?
>> 2) For bitwise-or and bitwise-and, I have implemented a sql aggregate
>> version. Do we want a AQL aggregate version as well? Do you think that we
>> should implement this as a scalar or as an aggregation?
>> 3) For boolean-or and boolean and, we current have an existing scalar
>> version for AQL. Do we need a scalar version for SQL? Should we implement a
>> aggregate version of these boolean functions?
>> 4) Do we need a json_agg function? I do not think so because all the data
>> is returned in json format. If you have a different answer, let me know.
>> 5) For string_agg, we currently have a scalar version for AQL known as
>> string-join. Do we need a SQL version?
>>
Re: Postgres Aggregation Function - Discussion
Posted by James Fang <jf...@ucr.edu>.
Just wondering if anyone has a response on this discussion?
Thanks,
James
On Mon, Nov 9, 2015 at 2:11 PM, James Fang <jf...@ucr.edu> wrote:
> The following functions that I am asking about are from
> http://www.postgresql.org/docs/9.4/static/functions-aggregate.html
>
> The following google doc contains a spreadsheet of all the current
> aggregation functions that exist in AQL and SQL. The colored portions will
> be the portions that I wish to discuss. I assumed that AQL returns null if
> at least 1 of the results in the aggregation is null while SQL ignores the
> tuple if it is null.
>
> https://docs.google.com/document/d/1l9QtRAB7H3yzP3976livpKzJZHVJlPdvDrjlm2YCsM0/edit?usp=sharing
>
> Questions:
> 1) Do we need array_agg, this function just groups up the results together
> into one array as output. If so, do we want a scalar version or aggregate
> version?
> 2) For bitwise-or and bitwise-and, I have implemented a sql aggregate
> version. Do we want a AQL aggregate version as well? Do you think that we
> should implement this as a scalar or as an aggregation?
> 3) For boolean-or and boolean and, we current have an existing scalar
> version for AQL. Do we need a scalar version for SQL? Should we implement a
> aggregate version of these boolean functions?
> 4) Do we need a json_agg function? I do not think so because all the data
> is returned in json format. If you have a different answer, let me know.
> 5) For string_agg, we currently have a scalar version for AQL known as
> string-join. Do we need a SQL version?
>