sql_distinct_key

Usage

view: view_name {
  measure: field_name {
    type: sum_distinct
    sql_distinct_key: ${my_field_name} ;;
  }
}

Definition

The sql_distinct_key parameter is used with measure types that perform aggregations on non-repeated values, specifically measures of type average_distinct, median_distinct, percentile_distinct, and sum_distinct. sql_distinct_key tells Looker which field to use as a basis for determining unique values, thereby avoiding miscalculations in the case of a fanout.

For example, type: sum_distinct adds up the nonrepeated values in a given field, based on the unique values defined by the sql_distinct_key parameter.

Consider a table like this:

In this situation, there are multiple rows for each order. If you added a simple measure of type: sum for the order_shipping column, you get a total of 80.00, even though the total shipping collected is actually 30.00.

 # Will NOT calculate the correct shipping amount
measure: total_shipping {
  type: sum
  sql: ${order_shipping} ;;
}

To get an accurate result, you can explain to Looker how it should identify each unique entity (in this case, each unique order) by using the sql_distinct_key parameter. This will calculate the correct 30.00 amount:

 # Will calculate the correct shipping amount
measure: total_shipping {
  type: sum_distinct
  sql_distinct_key: ${order_id} ;;
  sql: ${order_shipping} ;;
}

Every unique value of sql_distinct_key must have just one corresponding value in sql. The above example works because every row with an order_id of 1 has the same order_shipping of 10.00, every row with an order_id of 2 has the same order_shipping of 20.00, and so on.