Capture/lump a new "Others" group when applying limits

[mistercrunch]

In a recent discussion between @john-bodley @michael-s-molina @villebro @mistercrunch and @rusackas about handling/labeling the "Totals" fields for non-additive metrics in the "table" visualization, we all seemed to agree strongly that a new feature around "lumping" or "creating an extra aggregate for everything outside the limit" would be a very useful feature and help making "Total" or. "Summary" row align with the content of the table.

I'm opening the discussion here on GitHub to summarize what we spoke about, open thus topic to all, and discuss the intricacies around all this.

For the purpose of this conversation, let's call this family of features "lumping" as a way to get a full picture of the dataset while limiting the level of details.

Exposing this feature/option to users

It seems that wherever limits are set, an extra checkbox would be required. Labeling could be something like Include 'Other' bucket or Aggregate Remaining Data (good naming might be difficult here).

SQL / compute implications

There are multiple ways to do this, but one approach would using a LEFT JOIN to a limited subset, resulting in a single scan and proper grouping in the outer query.

WITH limit AS (
    // this query gets the dimension combination that fit within the limit, note there may be many dims here
    SELECT {dimensions} FROM {tbl} ORDER BY {sorted_metric} {DESC} LIMIT {limit}
)
SELECT
  COALESCE({dimensions}, 'Other') AS dim,
  {...}
  {metrics}
FROM {tbl}
LEFT JOIN limit ON {all_dimensions_predicates} 
{predicates}
GROUP BY {dimensions}

Note that some databases may not support CTEs or subqueries, in which case the feature could either be unsupported, or we could run something similar in multiple phases, though that may get messy. Picture a LIMIT 1000 on multiple dimensions (say country & region, that makes for a nasty looking second phase with 1000 OR predicates (WHERE (country='Canada' AND region='Quebec') OR (country='Canada' and region='Alberta', {+998 other OR clauses}). Maybe this feature is only supported with databases that support subqueries.

Other potential approaches:

• sequential execution (one query for NOT IN {limits} + one for IN limits)
• UNION ALL of both queries

Seems the LEFT JOIN + COALESCE is probably most efficient.

Labeling lumped groups

Maybe simply using "Others" here may work, or going more specific with "Other {dimension_label}". As far as the backend goes, we should be cautious around collision as the dimension may already contain an Other member and should be treated as a normal member (it shouldn't collide)

Relationship to "Series Limit"

Series limit is an interesting feature where we limit the number of time series as opposed to limiting the number of rows, the typical example is showing a line chart of population by country, but only for the top N countries. Clearly here showing an extra timeseries line for "other countries" would be useful, but is a bit of an edge case since we want to lump "other countries", but preserve the time component of it. In any case, I wanted to point out that while the user might check the same checkbox labeled Aggregate Remaining Data in the context of a time series, the handling behind the scene might be different as to how it's computed on the backend.

Note that handling lumping at the "row-level" (while grouping on dimension+time_grain) for time series doesn't really make sense. It does absolutely make sense at the series level though.

The handling of lumping for timeseries should be different than it is for non-time-series.

Other considerations and challenges:

• naming/labeling in controls and in-chart
• chart-specific implications - while something like Pie-Chart is super obvious here, does lumping make sense for all other chart types? what does it mean for the sankey diagram, a geospatial scatter plot, the heatmap, ...?
• when combining multiple dimensions, sometimes it could be interesting to lump some things but not others. Users may want to lump the long tail of countries (say top 10), but preserve the full detail of another dimension (say "market_category"). Allowing for lumping at the dimension level could be interesting but extremely complicated to handle. A challeng is that lupming by a combination of dimension does not insure completion for any member of a dimension. Meaning if I use top 100 country and market_category I'm getting the top 100 combination of those, and I can't get a total for any given country or market_category. Allowing for top 20 coutnries an top 15 market_category independently could be a desirable option in some cases
• ...

[an3uryzm]

@dosu any updates on that matter? When a user set series limit inside chart, all other data are not represented. Is it possible to see them grouped by "others" category?

[dosubot[bot]]

The concept of grouping data into an "Others" category when applying limits is indeed being discussed as a potential feature. This feature would allow users to aggregate data that falls outside the specified limits into a single "Others" group, providing a more comprehensive view of the dataset while maintaining the limit on the number of series or rows displayed.

Currently, the "Series Limit" feature in Apache Superset limits the number of series displayed by applying a joined subquery or an extra phase, which is useful for high cardinality columns but increases query complexity and cost ^[1][2]. The idea of adding an "Others" category would involve additional SQL logic, such as using a LEFT JOIN with COALESCE to group remaining data under a common label like "Others" or "Other {dimension_label}".

This feature is not yet implemented, but the discussion highlights its potential benefits and challenges, such as SQL complexity and database support for CTEs or subqueries. The implementation would need to consider different chart types and how "Others" would be represented in each, as well as the implications for time series data.

For now, this feature is still under discussion, and there are no updates on its implementation in the codebase.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}