Support aggregate filters

[faridnsh]

Feature description

Ideally I would there to be a way to have different filters for different aggregates in the same query, so something like the following example SQL can be generated:

SELECT
  conv_date AS date,
  COUNT(*) FILTER (WHERE visitor_status = 'new')        AS new_count,
  SUM(revenue) FILTER (WHERE visitor_status = 'new')    AS new_revenue,
  COUNT(*) FILTER (WHERE visitor_status = 'returning')  AS returning_count,
  SUM(revenue) FILTER (WHERE visitor_status = 'returning') AS returning_revenue
FROM picked
WHERE someother_condition = 1
GROUP BY conv_date
ORDER BY conv_date;

I'm not sure what's the best way for the GraphQL to look like to generate this kind of queries.

This doesn't have to be added out of the box, but rather could be provided as custom function or something similar to defining our own aggreations.
I asked about this and was suggested to post here.

Motivating example

We do many aggregations in our frontend and generate graphs for them. It's cumbersome to create a view with all possible combinations of filters so would be nice if this query could be natively expressed in GraphQL.

[benjie]

Seems like you want something like this?

{
  allPicked(condition: { someotherCondition: 1 }) {
    newAggs: groupedAggregates(groupBy: [CONV_DATE], condition: { visitorStatus: "new" }) {
      keys
      distinctCount
      sum {
        revenue
      }
    }
    returningAggs: groupedAggregates(groupBy: [CONV_DATE], condition: { visitorStatus: "returning" }) {
      keys
      distinctCount
      sum {
        revenue
      }
    }
  }
}

[faridnsh]

Thanks for the proposal. This is a good start however, it results in two different SQL queries. The source table is a view that's expensive to fetch from, if different aggregates could be applied on the same query, then work is not repeated.

[benjie]

Maybe you're after something like:

{
  allPicked(condition: { someotherCondition: 1 }) {
    groupedAggregates(groupBy: [CONV_DATE]) {
      keys
      newCount: distinctCount(condition: { visitorStatus: "new" })
      returningCount: distinctCount(condition: { visitorStatus: "returning" })
      sum {
        newRevenue: revenue(condition: { visitorStatus: "new" })
        returningRevenue: revenue(condition: { visitorStatus: "returning" })
      }
    }
  }
}

[faridnsh]

Yes, that seems like the idiomatic query that should reproduce the same SQL query.

[benjie]

Cool; seems feasible - I think you could probably add a couple fields to the grouped aggregate types via extendSchema() to see if it works or not. It might need patches in PgSelectStep, I'm not entirely sure - I don't think it will.

[hyusetiawan]

i guess the less sophisticated way is to send multiple independent queries and each with their own filter, and the server will then batch them together?

[benjie]

They won't be batched into the same query because PostGraphile hasn't been taught how to do that.