Home » Archived » BIRT » List of aggregate functions with all parameters and variants
|
Re: List of aggregate functions with all parameters and variants [message #360808 is a reply to message #360801] |
Mon, 25 February 2008 01:48 |
Tianli Zhang Messages: 2862 Registered: July 2009 |
Senior Member |
|
|
Hi Rick,
FYI ,
Functions
Comments
RANK( expr, [ascending], [group] )
Returns the rank of value in a series. Rank starts at 1. Duplicate
values will receive identical rank but will not affect the rank of
subsequent numbers
If ascending is FALSE or 0, rank assumes a descending order. Otherwise
an ascending order is assumed.
PERCENTRANK(expr, [group])
Returns the percentage rank of a value. Return value is in the range
[0.1].
PERCENTILE( expr, pct, [group])
Returns the percentile value. Pct is a number in the range [0.1]. If
pct is 0, the return value is the same as MIN(expr). If pct is 1, the return
value is the same as MAX(expr).
QUARTILE(expr, quart, [group])
Returns the quartile value. Quart must be an integer value between 0
and 4.
PERCENTSUM(expr, [group])
Percentage of total. Equals to
expr / SUM(expr, group)
ISTOPN(expr, n, [group] )
A running aggregate that returns a Boolean result. Returns true if the
value is one of the top n values in the group. Returns false otherwise.
ISTOPNPERCENT(expr, pct, [group] )
A running aggregate that returns a Boolean result. Returns true if the
value is one of the top (pct * 100) percent values in the group. Returns
false otherwise. For example, a pct value of 0.25 will cause the top 25% of
rows to evaluate to True for this function.
ISBOTTOMN(expr, n, [group] )
A running aggregate that returns a Boolean result. Returns true if the
value is one of the bottom n values in the group. Returns false otherwise.
AVERAGE(expr, [group])
Average value of the series
COUNT([group] )
Count of rows
COUNTDISTINCT([group])
Count of distinct values
FIRST(expr, [group])
First value in the series
LAST(expr, [group])
Last value in the series
MAX(expr, [group])
Max value in the series
MIN(expr, [group])
Min value in the series
MEDIAN(expr, [group])
Median value in the series
MODE( expr, [group])
Most frequently occurring value (the Mode value)
MOVINGAVERAGE(expr, window)
Moving average in a window of rows. Window must be a positive integer.
RUNNINGSUM(expr)
Running total
STDEV(expr)
Standard deviation
SUM(expr)
Total
VAR(expr)
Variance
Tianli
"R.Uhalde" <ricardo@faster-informatica.com> wrote in message
news:fpruqr$859$2@build.eclipse.org...
> Hello BIRTers,
>
> Where can I get a list of all aggregate functions described in all its
> glory?? I need to know parameters, variants of functions, etc.
>
> Thanks in advance,
>
> Rick.
|
|
|
Re: List of aggregate functions with all parameters and variants [message #360809 is a reply to message #360808] |
Mon, 25 February 2008 02:50 |
Ricardo Uhalde Messages: 30 Registered: July 2009 |
Member |
|
|
Tianli escreveu:
> Hi Rick,
> FYI ,
>
> Functions
> Comments
>
> RANK( expr, [ascending], [group] )
> Returns the rank of value in a series. Rank starts at 1. Duplicate
> values will receive identical rank but will not affect the rank of
> subsequent numbers
>
> If ascending is FALSE or 0, rank assumes a descending order. Otherwise
> an ascending order is assumed.
>
> PERCENTRANK(expr, [group])
> Returns the percentage rank of a value. Return value is in the range
> [0.1].
>
> PERCENTILE( expr, pct, [group])
> Returns the percentile value. Pct is a number in the range [0.1]. If
> pct is 0, the return value is the same as MIN(expr). If pct is 1, the return
> value is the same as MAX(expr).
>
> QUARTILE(expr, quart, [group])
> Returns the quartile value. Quart must be an integer value between 0
> and 4.
>
> PERCENTSUM(expr, [group])
> Percentage of total. Equals to
>
> expr / SUM(expr, group)
>
> ISTOPN(expr, n, [group] )
> A running aggregate that returns a Boolean result. Returns true if the
> value is one of the top n values in the group. Returns false otherwise.
>
> ISTOPNPERCENT(expr, pct, [group] )
> A running aggregate that returns a Boolean result. Returns true if the
> value is one of the top (pct * 100) percent values in the group. Returns
> false otherwise. For example, a pct value of 0.25 will cause the top 25% of
> rows to evaluate to True for this function.
>
> ISBOTTOMN(expr, n, [group] )
> A running aggregate that returns a Boolean result. Returns true if the
> value is one of the bottom n values in the group. Returns false otherwise.
>
> AVERAGE(expr, [group])
> Average value of the series
>
> COUNT([group] )
> Count of rows
>
> COUNTDISTINCT([group])
> Count of distinct values
>
> FIRST(expr, [group])
> First value in the series
>
> LAST(expr, [group])
> Last value in the series
>
> MAX(expr, [group])
> Max value in the series
>
> MIN(expr, [group])
> Min value in the series
>
> MEDIAN(expr, [group])
> Median value in the series
>
> MODE( expr, [group])
> Most frequently occurring value (the Mode value)
>
> MOVINGAVERAGE(expr, window)
> Moving average in a window of rows. Window must be a positive integer.
>
> RUNNINGSUM(expr)
> Running total
>
> STDEV(expr)
> Standard deviation
>
> SUM(expr)
> Total
>
> VAR(expr)
> Variance
>
>
>
> Tianli
>
> "R.Uhalde" <ricardo@faster-informatica.com> wrote in message
> news:fpruqr$859$2@build.eclipse.org...
>> Hello BIRTers,
>>
>> Where can I get a list of all aggregate functions described in all its
>> glory?? I need to know parameters, variants of functions, etc.
>>
>> Thanks in advance,
>>
>> Rick.
>
>
Many thanks Tianli!!
|
|
|
Goto Forum:
Current Time: Fri Apr 26 12:33:24 GMT 2024
Powered by FUDForum. Page generated in 0.03234 seconds
|