Implement range based grouping for numeric types.

[kgodey]

Problem

Users may want to group their records by ranges such as 0-1, 1-2, 2-3, or 0-10, 10-20, 20-30, or 0-100, 100-200, 200-300, etc. The range that they will want to group on will depend on the kind of data they have. For example, if the values in a column range from 0.3 to 5.2, then grouping by 0-10, 10-20 etc. is not very useful.

We need to provide both useful ranges for a given column and the ability to group by them.

Solution

• Columns of Number types should provide useful grouping ranges for that column in the Column API based on either:
  • the number of groups desired
  • the group size desired
• We should extend our record grouping mechanism to accept ranges for columns of Number types. We should be able to accept arbitrary ranges, not just the ranges suggested in the Column API.
  • Users should be able to set a min and max value for a range and the increment size

This involves:

• Implementing the grouping in the backend
• Updating the /api/v0/databases/<id>/types/ endpoint to store available grouping on this type This will be done in Grouping description endpoint #1310

Additional context

• Please review these design specs carefully before implementing this issue:
  • The Global Data Types spec has the latest version of the UX.
  • The Number Data Type spec has an older version of the UX but more information on user scenarios.
• Design issue: Design for Number data types #238
• Please see these PRs for code related to grouping:
  • Record grouping #315
  • Fix grouping counts #353
• Complete implementation of this issue is blocked by implementation of Number types, but we can start work with the NUMERIC type which is already implemented.
  • Handle NUMERIC data type in the backend. #377
  • Handle SMALLINT data type in the backend. #378
  • Handle INTEGER data type in the backend. #379
  • Handle BIGINT data type in the backend. #380
  • Handle DECIMAL data type in the backend. #381
  • Handle REAL data type in the backend. #382
  • Handle DOUBLE PRECISION data type in the backend. #383
  • Backend work for custom MATHESAR_TYPES.PERCENTAGE type. #434
• Update filtering issues to cover storing filter/group information in the backend. #557 provides some context on storing groups.

[mathemancer]

@kgodey @ghislaineguerin Should the ranges have the same size on the number line, or the same number of entries? For example, if a column has entries

mycol
-----
   1
   2
   3
   4
   5
   6
   7
  20

Should splitting into two groups result in

 1-10
-----
   1
   2
   3
   4
   5
   6
   7
-----
11-20
-----
  20

or

 1-4
-----
   1
   2
   3
   4
-----
 5-20
-----
   5
   6
   7
  20

[kgodey]

@mathemancer Good question. From the backend perspective, I think it makes sense to provide both options. The frontend (or other Mathesar clients) can use whichever would be best in the use case.

[kgodey]

@powellc FYI I updated the issue description with more details.

[mathemancer]

As I've gotten into this:

• Implementing "range-based" grouping turns out to be equivalent for any type that can be ordered (for example, text types could be a - c, d - f, ... x - z ).
• Grouping by distinct tuples is a special case of range-based grouping, where the ranges are very small (one element each).

I'm currently working on the assumption that the former bullet point makes sense, and we can have that functionality (even if we don't want it in the front end at the moment). For the second point, I think I'll start by just getting the range grouping working, then we can deprecate the other grouping form.

@kgodey Does that make sense to you?

[kgodey]

@mathemancer That sounds fine to me. I think we'll want different frontend experiences for grouping by distinct tuples vs. range-based grouping, but there's no reason why the API can't be the same for both.