Usage
view: view_name {
measure: field_name {
type: sum_distinct
sql_distinct_key: ${my_field_name} ;;
}
}
|
Hierarchy
sql_distinct_key |
Possible Field Types
Measure
Accepts
A SQL expression
|
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:
| Order Item ID | Order ID | Order Shipping |
|---|---|---|
| 1 | 1 | 10.00 |
| 2 | 1 | 10.00 |
| 3 | 2 | 20.00 |
| 4 | 2 | 20.00 |
| 5 | 2 | 20.00 |
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. This 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, etc.