Can I reference an explore in another derived table? (for calculating standard deviation)

done
low_priority
reply

#1

I have an explore joined with multiple views. Can I reference this explore as a subquery in another derived table? The goal is to calculate the standard deviation after the data is aggregated up to a certain level in that explore.

For example:

explore: order {
  join: customer {
    from: person
    sql_on: ${order.customer_id} = ${customer.id} ;;
  }
  join: representative {
    from: person
    sql_on: ${order.representative_id} = ${representative.id} ;;
  }
}

I want looker to return a query like:

select day_id, stddev(order_count)
from
(
  select day_id, customer_id, count(*) as order_count 
  from order 
  group by day_id, customer_id
) s
group by day_id

(Nicholas Wong) #2

Seems like you’re doing SELECT within SELECT! Your project sounds really awesome. I just read an article on: Aggregate based on a column value (SQL subselect) LookML Modeling. It is located at Aggregate based on a column value (SQL subselect). Could be of help.


(Izzy) #3

Have you played with Native Derived Tables @bliu? The syntax is a lil different, but if you build an explore, save that as a native derived table, and then reference that NDT in another explore (using ${derived_table_or_view_name.SQL_TABLE_NAME}), I think that might get you where you’re going…

Alternately, just build this

  select day_id, customer_id, count(*) as order_count 
  from order 
  group by day_id, customer_id

into a PDT and reference that, for a more hardcoded SQL approach. Does that get at what you’re asking?


#4

Yes pretty much. I’ve tried using ${derived_table_or_view_name.SQL_TABLE_NAME} but the sql in the derived table contained some parameters in that view. When I referenced this derived table in another view, Looker seems unable to look for those parameters. For example:

view: customer {
  derived_table: {
    sql: select * from customer where 
         {% if segment._parameter_value == "'Adults 19-34'" %}a19_34 = 1
         {% elsif segment._parameter_value == "'Adults 25-54'" %}a25_54 = 1
         {% else %}1 = 1{% endif %};;
  }

  parameter: segment {
    type: string
    suggestion: ["Adults 19-34", "Adults 25-54"]
  }
}

view: trying_to_reference_customer {
  derived_table: {
    sql: select day_id, customer_id, count(*) as order_count from order 
          join ${customer.SQL_TABLE_NAME} customer 
          on customer.id = order.customer_id 
          group by day_id, customer_id;;
  }

 measure: order_sd {
   type: number
   sql: stddev(${TABLE.order_count});;
 }
}

The segment parameter couldn’t be found.


(Izzy) #5

What’s your explore look like?
I just did this:

explore: trying_to_reference_customer {
  join: customer {
    sql_on: ${customer.join} = ${trying_to_reference_customer.join} ;;
  }
}

with your exact code posted above and it’s taking the parameter + updating the SQL all right.

You’ll always have to join them together in order to reference parameters defined in the other view file— Just referencing the ${customer.SQL_TABLE_NAME} pretty much only writes in the literal table reference into the generated SQL, it doesn’t go through and pull any of the LookML of the customer view in.