Namrata,
I assume the aforementioned query with record defined in let clause was only
the example.
That query indeed has a bug, but is happen only because the type of the list is
not statically enforced.
Do you load your data into dataset? I so what is the type of that dataset?
If you enforce the type of your nested ordered lists upon data ingestion you
can calculate the average:
drop dataverse test if exists
create dataverse test
use dataverse test
create type testType as {
id: int32,
list: [[double]]
}
create dataset testDS(testType) primary key id;
insert into dataset testDS({"id": 1, "list": [[1.2, 2.3, 3.4],[6,3,7,2]]});
for $x in dataset testDS
for $y in $x.list
return {"avg": avg($y)}
> On Dec 7, 2015, at 09:57, Malarout, Namrata (398M-Affiliate)
> <[email protected]> wrote:
>
> Hi,
>
> Wail, thanks for looking into it and explaining the use of for. I will be
> following the issue. However, working with my sample data may be a little
> more tricky. I have a couple hundred of records which contain such nested
> ordered lists. I would like to perform an aggregation over all the values
> across all the records. Any suggestions on how to do it?
>
> Mike, thanks for understanding :) Appreciate all the help.
> -Namrata
>
> From: Michael Carey <[email protected] <mailto:[email protected]>>
> Reply-To: "[email protected]
> <mailto:[email protected]>"
> <[email protected]
> <mailto:[email protected]>>
> Date: Monday, December 7, 2015 at 7:28 AM
> To: "[email protected]
> <mailto:[email protected]>"
> <[email protected]
> <mailto:[email protected]>>,
> "[email protected]
> <mailto:[email protected]>"
> <[email protected]
> <mailto:[email protected]>>
> Subject: Re: Aggregate function on collection of ordered list
>
> + Looping in the dev list to try and get fast attention to the fix, if it's
> easy!
> (I know that Namarata's under time pressure in a NASA bakeoff exercise. :-))
>
> On 12/7/15 4:59 AM, Wail Alkowaileet wrote:
>> It's an easy fix...
>> Thanks for reporting that.
>>
>> I reported it in https://issues.apache.org/jira/browse/ASTERIXDB-1216
>> <https://issues.apache.org/jira/browse/ASTERIXDB-1216>
>>
>> On Mon, Dec 7, 2015 at 3:33 PM, Wail Alkowaileet <[email protected]
>> <mailto:[email protected]>> wrote:
>> Hi Namrata,
>>
>> The best way to think of for in lists is to think it works as foreach in
>> java.
>> So ..
>> in your first query, it should be like:
>>
>> let $l := [[1.2, 2.3, 3.4],[6,3,7,2]]
>> for $x in $l // for each list in the outer list
>> return {"avg”: avg($y)}
>>
>> However, I tried it and it seems that there is a bug for applying
>> aggregation on nested open field.
>>
>> I'll look into it to see if it's an easy fix
>>
>>
>>
>> On Mon, Dec 7, 2015 at 2:52 PM, Malarout, Namrata (398M-Affiliate)
>> <[email protected] <mailto:[email protected]>> wrote:
>> Hi,
>>
>> I am trying to perform avg, sum, min and max functions on a collection of
>> ordered lists. An example is:
>> let $l := [[1.2, 2.3, 3.4],[6,3,7,2]]
>> return {"avg”: avg($l)}
>>
>> I have tried both avg and sql-avg. But I get the following error:
>> Cannot compute AVG for values of type ORDEREDLIST [NotImplementedException].
>>
>> I’ve attached the sample data that I’m working with (sample.adm). My AQL
>> query to find the average of analysis_error looks like:
>>
>> use dataverse Test;
>> for $f in dataset sample
>> where not(is-null($f.analysis_error))
>> return avg($f.analysis_error);
>>
>> The error seen is as follows:
>> Type of argument in function-call: asterix:avg, Args:[function-call:
>> asterix:field-access-by-name, Args:[%0->$$0, AString: {analysis_error}]]
>> should be a collection type instead of ANY [AlgebricksException]
>>
>> I would like to know what is the correct syntax to find the average.
>> Appreciate the help.
>> Thanks,
>> Namrata
>>
>>
>>
>>
>>
>> --
>>
>> Regards,
>> Wail Alkowaileet
>>
>>
>>
>> --
>>
>> Regards,
>> Wail Alkowaileet
>
Best regards,
Ildar
