Usage
measure: field_name {
type: sum_distinct
sql_distinct_key: ${my_field_name} ;;
}
}
Hierarchysql_distinct_key |
Possible Field TypesMeasureAcceptsA 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.
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:
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.