sql_distinct_key
is usually used when there is no primary key on the table you are aggregating over. In most cases, you will not need sql_distinct_key
if you have a primary key.
Measures of typesum_distinct
and typeaverage_distinct
can optionally take a new parameter:sql_distinct_key
.
Normally, sum_distinct
and average_distinct
measures use the declared primary key of the view they are contained in as unique value associated with the number on which they are performing the operation. This parameter lets sum_distinct
and average_distinct
work in situations where there is no primary key or the view is denormalized (for example, a very wide table with subtables embedded).
Example
Suppose you have a table that looks like the following:
| order_time | order_id | order_shipping_amount | customer_id | item_id | item_name | item_amount |
In this particular table, there is no primary key and each row is repeated for every item in an order.
A single order may consist of multiple rows in this table.
Summing order_shipping_amount
would be incorrect, because it would be repeated for each item.
You could use LookML's sum_distinct
to perform this instead:
measure: total_shipping_amount { type: sum_distinct sql: ${TABLE}.order_shipping_amount ;; sql_distinct_key: ${TABLE}.order_id ;; }