TopPercent (DMX)

The TopPercent function returns, in order of decreasing rank, the top-most rows of a table whose cumulative total is at least a specified percentage.

Syntax

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

Applies To

An expression that returns a table, such as a <table column reference>, or a function that returns a table.

Return Type

<table expression>

Remarks

The TopPercent function returns the top-most rows in decreasing order of rank 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. TopPercent 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 build by using the Basic Data Mining Tutorial.

To understand how TopPercent works, it might 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 TopPercent function takes the results of this query and returns the rows with the greatest values that sum to the specified percentage.

SELECT 
TopPercent
    (
    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 TopPercent 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 TopPercent 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 TopPercent function specifies the percentage, as a double. To get the rows for the top products that sum to 50 percent of the total support, you type 50.

Example results:

Note   This example is provided only to illustrate the usage of TopPercent. 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)

General Prediction Functions (DMX)