ai_top_drivers function

Applies to: Databricks SQL Databricks Runtime

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

ai_top_drivers() is a table-valued function for contribution analysis. It ranks dimension values, or pairs of values, that contribute the most to a change in a metric between a control group and a test group. See Arguments for available arguments.

Requirements

• Databricks Runtime 16.1 or above
• Pro or Serverless SQL warehouse with Photon enabled, or a Photon-enabled cluster on Databricks Runtime
• Enroll your workspace in the AI Top Drivers preview. See Manage Databricks previews.

Syntax

ai_top_drivers(input => input, metric => metric, is_test => is_test
  [, optional_param ] [...])

optional_param:
  { dimensions => dimensions |
    aggregation => aggregation |
    min_support => min_support }

Arguments

Pass all arguments by name.

• input is the table-valued input to analyze. Provide a table or subquery with the metric column, dimension columns, and the test label column.
• metric is a STRING naming the numeric column to analyze. The column must have a numeric type.
• is_test is a STRING naming a boolean column that labels each row. TRUE marks the test split and FALSE marks the control split.
• dimensions (optional) is an ARRAY<STRING> of column names to segment on. Low-cardinality numeric columns are segmented by exact value. High-cardinality numeric columns are automatically grouped into low, medium, and high ranges. If you omit this argument, the function auto-selects the top 20 correlated dimension columns from the input.
• aggregation (optional) is a STRING naming the aggregate to apply to metric within each segment. Supported values are sum, avg, count, min, and max. The default is sum.
• min_support (optional) is a DOUBLE between 0 and 1 representing the minimum fraction of analyzable rows a segment must cover to appear in the results. The default is 0.05.

Returns

The function returns a table with the following columns:

• contributor: An ARRAY<STRING> that describes the segment. Each element uses the form column=value, for example ["pickup_zip=high (>10044)"] or ["pickup_zip=high (>10044)", "payment_type=CRD"].
• metric_control: The aggregated metric for the segment in the control group (is_test = FALSE).
• metric_test: The aggregated metric for the segment in the test group (is_test = TRUE).
• change: The absolute difference, metric_test − metric_control.
• relative_change: The change as a fraction of metric_control.
• support: The fraction of total analyzable rows in the segment.

Results include single-dimension contributors, pairs of dimension values, and an ["all"] row that summarizes the full population.

Examples

The following example compares total fare revenue between January and February 2016 and finds the pickup ZIP code ranges that contribute most to the change:

SQL

WITH input AS (
  SELECT
    pickup_zip,
    fare_amount,
    CASE
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-01-31' THEN false
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-02-01' AND '2016-02-29' THEN true
      ELSE NULL
    END AS is_test
  FROM samples.nyctaxi.trips
  WHERE DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-02-29'
)
SELECT *
FROM ai_top_drivers(
  input => TABLE(input),
  metric => 'fare_amount',
  is_test => 'is_test',
  dimensions => ARRAY('pickup_zip'),
  aggregation => 'sum'
)
ORDER BY ABS(change) DESC

The following example counts trips across dropoff ZIP code ranges between the same periods:

SQL

WITH input AS (
  SELECT
    dropoff_zip,
    1 AS trip_count,
    CASE
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-01-31' THEN false
      WHEN DATE(tpep_pickup_datetime) BETWEEN '2016-02-01' AND '2016-02-29' THEN true
      ELSE NULL
    END AS is_test
  FROM samples.nyctaxi.trips
  WHERE DATE(tpep_pickup_datetime) BETWEEN '2016-01-01' AND '2016-02-29'
)
SELECT *
FROM ai_top_drivers(
  input => TABLE(input),
  metric => 'trip_count',
  is_test => 'is_test',
  dimensions => ARRAY('dropoff_zip'),
  aggregation => 'count'
)
ORDER BY ABS(change) DESC

注記

For count aggregation, provide a numeric column to count. A constant column such as 1 AS trip_count is a common pattern.

Limitations

The following limitations apply during Beta:

• Rows with a NULL value in the metric or is_test column are dropped before analysis.
• count distinct and median aggregates are not supported.
• MAP and STRUCT dimension columns are not supported. Numeric identifiers, such as ZIP codes and IDs, are treated as quantities and may be grouped into ranges. Cast numeric identifiers to STRING to segment by exact value.