g_rank(G;S;O;X)
Returns the rank of every value within a given group.
Function type
Vector only
Syntax
g_rank(G;S;O;X)
t_rank(X)
Input
Argument | Type | Description |
---|---|---|
G |
any | A space- or comma-separated list of column names Rows are in the same group
if their values for all of the columns listed in If If any of the columns listed in |
S |
integer | The name of a column in which every row evaluates to a 1 or 0, which determines
whether or not that row is selected to be included in the calculation If
If any of the values in
|
O |
integer | A space- or comma-separated list of column names that
determine the row order within a particular group If
If any of the values in |
X |
any numeric type | A column name |
Return Value
For every row in each group defined by G
(and for those rows where
S
=1, if specified), g_rank
returns an integer value corresponding to the rank of X
in the list of all
valid (non-N/A) values of X
within the group.
The largest value has rank 1, and subsequent (decreasing) values are ranked in increasing increments of 1 (e.g., 2, 3, 4).
Repeated values are ranked according to the order of the group (i.e., by the values in
O
, if specified; otherwise by the current display order of the
table).
If X
is N/A, the result is N/A.
Sample Usage
<base table="pub.doc.samples.ref.func.g_func_time_series_sample_usage"/> <willbe name="g_rank_1" value="g_rank(state;include;order;value)"/> <willbe name="g_rank_2" value="g_rank(state city;include;order;value)"/>
Example
Let's say you want to rank the items for all the daily transactions from
highest to lowest in terms of sales price for each store in your
table. You can do this using g_rank(G;S;O;X)
.
Let's use the Sales Item Detail table (pub.demo.retail.item) to demonstrate the use of this function. When you open the table, it looks something like this:
Since you want to rank the transactions for every date, and you want to
do that for each store, you specify the store
and date columns for the G
parameter. You can omit the S
parameter so that all
of the rows will be considered, and you can omit the
O
parameter, so the ordering will be
determined by the current display order of the table. And you can
specify the sales column for
X
, since you want the ranking to be based
on the sales prices of the items.
<willbe name="rank" value="g_rank(store date;;;sales)"/>
This will give you a column that looks like the following:
To see the transactions in ranking order for every date for each store, you would sort first on the rank column, then on the date column, and then on the store column. You could do this by clicking on the arrow () that appears when you hover over the column name. Then, select Sort Up from the drop-down list, for rank, date, and store, in that order. (See Sorting rows in the 1010data User's Guide for more information about sorting multiple columns.)
<sort col="rank" dir="up"/>
<sort col="date" dir="up"/>
<sort col="store" dir="up"/>
The table would then show all the transactions for each store ordered by date, and all the transactions for each date ordered by rank:
You can see that on 05/18/12, there are five transactions that now appear in the table ranked from the one with the highest sales to the lowest. You'll also notice that the transactions with rank 3, 4, and 5 have the same value in the sales column (1.1), so they are ranked according to the same order they appeared in the original table; the transaction with SKU 96A appears before the one with SKU 969 and the SKU 3B7.
Additional Information
- The
t_
version of this function defaults theG
argument and omits theS
argument. The default forG
is set at table load time based on the organization of the table.