Skip to content

Instantly share code, notes, and snippets.

@monfera

monfera/crossfilter_js.sql

Last active May 28, 2019
Embed
What would you like to do?
/* crossfilter.js interpretation for a single grouping on a specific dimension X */
/* Have some base table, eg. called `data` */
CREATE TABLE data (
dim0 INT, /* Of course in JS the type can be anything, but crossfilter requires */
dim1 CHAR(3), /* that values be orderable via `<`, `>`, `<=` and `>=` */
dim2 INT, /* such as all numbers, or all strings in a dimension. */
/* ... */
dimN VARCHAR /* constraint: N < 32 as it uses a filter bitmap of 8, 16 or 32 bit width */
);
/* The `data` table may have all records off the bat, but can also be initially empty, or partially filled */
INSERT INTO data
(dim0, /* ... */ dimN)
VALUES
(1, "EUR", 12, /* ... */, "Tacos"),
(2, "USD", 7, /* ... */, "Tomato Soup"),
(3, "USD", 23, /* ... */, "Grilled Cheese")
/* .
.
.
*/;
/* We can have zero or one range filter table per dimension */
CREATE TABLE rangeDim0 (dim0Lo INT, dim0Hi INT); INSERT INTO rangeDim0 VALUES ((2), (7));
CREATE TABLE rangeDim1 (dim1Lo CHAR(3), dim1Hi CHAR(3)); INSERT INTO rangeDim0 VALUES (("CHF"), ("EUR"));
CREATE TABLE rangeDim2 (dim2Lo INT, dim2Hi INT); /* the opportunity is there but we didn't add range values here */
CREATE TABLE rangeDim3 (dim3Lo ANY, dim3Hi ANY); /* no range here either; we'll filter `dim3` via a predicate function */
/* ... */
CREATE TABLE rangeDimX (dimXLo ANY, dimXHi ANY); INSERT INTO rangeDimX VALUES (("whatevFrom"), ("whatevTo"));
/* ... */
CREATE TABLE rangeDimN (dimNLo VARCHAR, dimNHi VARCHAR); INSERT INTO rangeDimN VALUES (("Aardvark"), ("Asparagus"));
/* Create group G for dimension X (each dimension can have zero to any number of groups) */
/* Note: `aggFun`s need to be commutative, associative, invertible */
/* see https://github.com/crossfilter/crossfilter/wiki/Crossfilter-Gotchas#reducer-functions and above */
CREATE VIEW groupGforDimensionX AS
SELECT grouperFunctionG(dimX) as groupKey, /* constraint: groupingFunctionG(dimX) must be monotonic with dimX itself! */
aggFun0(dim2) as groupValue0,
aggFun1(dim8) as groupValue1,
/* ... */
aggFunM(dimY) as groupValueY /* in JS, a single `aggFun` reduces all needed dims */
FROM data
JOIN rangeDim0, rangeDim1, /* ... */ rangeDimN
WHERE dim0Lo <= dim0 AND dim0 < dim0Hi /* filter ranges are left closed, right open intervals */
AND dim1Lo <= dim1 AND dim1 < dim1Hi /* each dimension can occur only once in the conjunction */
/* some dimensions may be left unconstrained, eg. here, `dim2` has no active filter */
AND SQRT(calculateSomething(dim3 * 4)) > 17 /* can use function `dim3 => whatever(dim3)`, slower than ranges */
/* AND dimXLo <= dimX AND dimX < dimXHi */
/* ^ note that the otherwise existing filter on `dimX` does NOT apply to any grouping for `dimX` */
AND dimNLo <= dimN AND dimN < dimNHi
GROUP BY groupKey
ORDER BY aggFun3 ASC /* this would be `DESC` for a `bottom` query; user specified `aggFun3` via `order` */
LIMIT k /* we want to get the top (or bottom) `k` number of groups */
CREATE BITMAP INDEX /* ... */ /* to indicate that crossfilter.js is using bitmap indices */
/* Then we can modify data in the base table and/or the range tables, or even change some structures,
the VIEW will be kept up to date */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment