crossfilter_js.sql

/* 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 */