BottomPercent (DMX)

Returns, in order of increasing rank, the bottom-most rows of a table whose cumulative total is at least a specified percentage.

Syntax

BottomPercent(<table expression>, <rank expression>, <percent>)

Arguments

• <Table expression>
  The name of a nested table column or table-valued expression.

• <rank expression>
  A column in the nested table, or expression that evaluates to a column.

• <percent>
  A double that indicates the total target percentage.

Result Type

A table.

Remarks

The BottomPercent function returns the bottom-most rows in increasing order of rank. The rank is based on the evaluated value of the <rank expression> argument for each row, such that the sum of the <rank expression> values is at least the given percentage that is specified by the <percent> argument. BottomPercent returns the smallest number of elements possible while still meeting the specified percent value.

Examples

The following example creates a prediction query against the Association model that you built in the Basic Data Mining Tutorial.

To understand how BottomPercent works, it may be helpful to first execute a prediction query that returns only the nested table.

SELECT Predict ([Association].[v Assoc Seq Line Items], INCLUDE_STATISTICS, 10)
FROM 
     [Association]
NATURAL PREDICTION JOIN
SELECT (SELECT 'Women''s Mountain Shorts' as [Model]) AS [v Assoc Seq Line Items]) AS t

Example results:

The BottomPercent function takes the results of this query and returns the smallest-valued rows that sum to the specified percentage.

SELECT 
BottomPercent
    (
    Predict ([Association].[v Assoc Seq Line Items],INCLUDE_STATISTICS,10),
    $SUPPORT,
    50)
FROM 
     [Association]
NATURAL PREDICTION JOIN
(SELECT (SELECT 'Women''s Mountain Shorts' as [Model]) AS [v Assoc Seq Line Items]) AS t

The first argument to the BottomPercent function is the name of a table column. In this example, the nested table is returned by calling the Predict function and using the INCLUDE_STATISTICS argument.

The second argument to the BottomPercent function is the column in the nested table that you use to order the results. In this example, the INCLUDE_STATISTICS option returns the columns $SUPPORT, $PROBABILTY, and $ADJUSTED PROBABILITY. This example uses $SUPPORT because support values are not fractional and therefore are easier to verify.

The third argument to the BottomPercent function specifies the percentage, as a double. To get the rows that represent the bottom 50 percent of the support, you type 50.

Example results:

Note   This example is provided only to illustrate the usage of BottomPercent. Depending on the size of your data set, this query might take a long time to run.

See Also

Reference

Data Mining Extensions (DMX) Function Reference

Functions (DMX)