Inline filters

[max-sixty]

Something that's come up a couple of times (discussion of tertiary operator in https://github.com/max-sixty/prql/issues/11, Malloy impl in https://github.com/max-sixty/prql/issues/71) is the concept of inline filters:

Taken from Malloy's "filtering measures", note the COUNT( CASE....

SELECT 
   base.state as state,
   COUNT( CASE WHEN base.fac_type='AIRPORT' THEN 1 END) as airports,
   COUNT( CASE WHEN base.fac_type='HELIPORT' THEN 1 END) as heliports,
   COUNT( 1) as total
FROM `malloy-data.faa.airports` as base
GROUP BY 1
ORDER BY 2 desc

This allows for aggregating over a subset of rows for only that field.

Here's a truncated example from our readme:

func if_valid x = is_valid_price ? x : null

from prices
derive [
  return_total:      prices_adj   | ret | if_valid
  return_usd:        prices_usd   | ret | if_valid
]

With an inline filter, this could be:

from prices
derive [
  return_total:      prices_adj   | ret | filter is_valid_price
  return_usd:        prices_usd   | ret | filter is_valid_price
]

Or with an expression rather than a column:

from prices
derive [
  return_total:      prices_adj   | ret | filter price > 1
  return_usd:        prices_usd   | ret | filter price > 1
]

We'd need to think through what restrictions we'd need on the filter — is any expression allowed?

[jcuenod]

Fwiw, I keep returning to #4928, hoping to see development. Since I realize that the discussion did not ping this issue, I'm adding a comment.

[vanillajonathan]

Looks nice and easy to use.