crossfiltering on grouped tables

[derekperkins]

I'm trying to make summary tables with various groupings, but I can't get filtering to work. Using the athletes example, I added a new nationality query + corresponding table, which initially loads great.

data:
  nationality:
    view: true
    query: SELECT
        nationality,
        COUNT(*) FILTER (sex = 'male') AS males,
        COUNT(*) FILTER (sex = 'female') AS females,
        COUNT(*) AS athletes,
      FROM athletes
      GROUP BY nationality

When crossfiltering, it tries to apply the filter on the nationality table post-grouping, when it needs to happen on the source athletes table, then rerun the nationality sql.

Candidate bindings: "nationality.athletes"
LINE 1: ... "athletes" FROM "nationality" WHERE (("weight" BETWEEN 51.82397003745318 AND ...

I tried setting it as a view, but that didn't change anything. Is there a way to properly cascade data dependencies?

Full modified yaml

meta:
  title: Olympic Athletes
  description: >
    An interactive dashboard of athlete statistics. The menus and searchbox
    filter the display and are automatically populated by backing data columns.
data:
  athletes: { file: data/athletes.parquet }

  nationality:
    view: true
    query: SELECT
        nationality,
        COUNT(*) FILTER (sex = 'male') AS males,
        COUNT(*) FILTER (sex = 'female') AS females,
        COUNT(*) AS athletes,
      FROM athletes
      GROUP BY nationality

params:
  category: { select: intersect }
  query: { select: intersect, include: $category }
  hover: { select: intersect, empty: true } # select nothing when empty
hconcat:
- vconcat:
  - hconcat:
    - input: menu
      label: Sport
      as: $category
      from: athletes
      column: sport
    - input: menu
      label: Sex
      as: $category
      from: athletes
      column: sex
    - input: search
      label: Name
      filterBy: $category
      as: $query
      from: athletes
      column: name
      type: contains
  - vspace: 10
  - plot:
    - mark: dot
      data: { from: athletes, filterBy: $query }
      x: weight
      y: height
      fill: sex
      r: 2
      opacity: 0.1
    - mark: regressionY
      data: { from: athletes, filterBy: $query }
      x: weight
      y: height
      stroke: sex
    - select: intervalXY
      as: $query
      brush: { fillOpacity: 0, stroke: black }
    - mark: dot
      data: { from: athletes, filterBy: $hover }
      x: weight
      y: height
      fill: sex
      stroke: currentColor
      strokeWidth: 1
      r: 3
    xyDomain: Fixed
    colorDomain: Fixed
    margins: { left: 35, top: 20, right: 1 }
    width: 570
    height: 350
  - vspace: 5
  - input: table
    from: athletes
    maxWidth: 570
    height: 250
    filterBy: $query
    as: $hover
    columns: [name, nationality, sex, height, weight, sport]
    width: { name: 180, nationality: 100, sex: 50, height: 50, weight: 50, sport: 100 }

  - input: table
    from: nationality
    maxWidth: 570
    height: 250
    filterBy: $query
    columns: [nationality, males, females, athletes]
    width: { nationality: 100, males: 75, females: 75, athletes: 75 }

[derekperkins]

Is the only way for the input component to support the aggregation? Using the Observable Latency example, the raw data from latency is selected, the summed for both the x-axis value, and post aggregation for sorting on the y-axis. Does routing it through the client and components essentially create a DAG allowing for incremental computation, where the data section of the spec doesn't currently allow for that?

- plot:
  - mark: barX
    data: { from: latency, filterBy: $filter }
    x: { sum: count }
    y: route
    fill: route
    sort: { y: -x, limit: 15 }
  - select: toggleY
    as: $filter
  - select: toggleY
    as: $highlight
  - select: highlight
    by: $highlight

[jheer]

As you note, selections need to be applied to a base table. In your original example you are creating an entirely new table, where the creation query gets executed in DuckDB; Mosaic currently knows nothing about the relationship between this new table and the base table.

The current vgplot components lack a mechanism to take a query as an input data source and then "rewrite" it by pushing down the filtering predicate to the base table. This is technically do-able but would require some non-trivial engineering for which I don't currently have the bandwidth -- especially if considered at the level of declarative specs and multiple components. Instead, components generate their own queries over the base table based on their parameters.

One incremental step that might help would be to revise the Table component to accept not only a base table name but also a data query (as a mosaic-sql Query object) and add support for predicate pushdown (e.g., clone the query and then add where clauses in the right places). We'd also need to expand the fieldInfo utility in mosaic-core which provides schema information. However, this would not apply at the level of declarative specs, as one would still need to parse a string-typed SQL query and translate it to a mosaic-sql AST to perform pushdown.