monfera · May 28, 2019 12:25
diff --git a/crossfilter_js.sql b/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 */
	/* 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 */