Manny Ezeagwula

Updated November 12, 2019 22:46

how do i aggregate a field using a window function with C9QL?

Answered

i want to return the max value for a given group without use the group by clause. the ideal out should look like this?

and the raw data looks like this:

1 comment

Official comment

Manny Ezeagwula November 12, 2019 22:54
Typically, a window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. Since window functions require an "OVER" clause, we can achieve the same results using a multi-step process with Cloud9QL.

To skip to final results, watch this tutorial, http://recordit.co/KYFi50L1XH

To follow along, upload this data to your Knowi account as a CSV paste or upload:
```
group_id,entry,value
1,xyz1,1
1,xyz2,2
2,zzz1,5
3,yyy1,1
3,yyy2,8
4,zzz3,5
4,zzz2,2
```
next, create a query from that dataset and paste this query
```
select group_id, NULL as entry, max(value) as max_value group by group_id
append

select *, NULL as max_value;

select * order by group_id, max_value desc;

select accumulate(max_value, group_id) as max_value, *;

select group_id, entry, value, max_value where entry is not null
```
This should return the ideal outcome. For other aggregate functions (i.e. average, sum, min) swap out max for the ideal function and adjust field . names to match yours. :)
Comment actions Permalink

Please sign in to leave a comment.