Correlated Sub Queries

[lloydtabb]

Correlated Sub Queries

Assume the following model.

query: q_airports_rollup is table('malloytest.airports') -> {
  group_by: state
  aggregate: airport_count is count()
  aggregate: avg_elevation is elevation.avg()
  nest: by_facility_type is {
    group_by: fac_type
    aggregate: airport_count is count()
  }
  nest: by_county is {
    group_by: county
    aggregate: airport_count is count()
  }
}

explore: airport_rollup is from(->q_airports_rollup) {
}

We have a row for each state and two subtables, by_facility_type and by_county.

SQL had a notion of coorlated subquery that will return a scalar value (dimension:) and I think the same could work in Malloy.

explore: airport_rollup is from(->q_airports_rollup) {
   query: by_num_counties is {
     group_by: num_counties is by_county->{ aggregate: c is count() }
     aggregate: state_count is count()
   }
}

and the SQL

SELECT 
   (SELECT count() is c FROM UNNEST(by_county)) as num_counties
   , count(*) is state_count
FROM ..
GROUP BY 1
ORDER BY 2 DESC

You can only return a single row of data.
Not sure how we handle grouping by an inline record (we'll have to check how bigquery does it).

[christopherswenson]

I like this idea! I think it needs some syntax to highlight what's going on, otherwise it just looks like a type error (putting a query inside a group_by instead of a nest, I guess?). Could be as simple as group_by: num_counties is scalar(by_county->{ aggregate: c is count() }) or some other syntax we devise.

[mtoy-googly-moogly]

Not sure how I feel about the syntax. The -> operator up until this instant has explores on the left and queries on the right. Here a query is on the left, and we don't allow that anywhere else without a from