Blog/wwwroot/jsonql-js/aggregates.js 2.6 K · 81 lines · raw · history

1 import { SqlEvaluationError } from './errors.js';
2
3 /**
4 * Aggregate functions, keyed by uppercase name. Each one receives the argument
5 * value from every row of a group, in row order, and folds them into a single
6 * value. Following SQL, nulls are skipped rather than propagated, and an
7 * all-null (or empty) group aggregates to `null` - except COUNT, which counts.
8 *
9 * `COUNT(*)` passes a non-null placeholder for each row (see the `Star` argument
10 * handling in query.js), so it counts rows where `COUNT(expr)` counts non-nulls.
11 *
12 * These names are reserved by the query layer: unlike the builtins in
13 * functions.js they cannot be overridden via `{ functions }`, since shadowing
14 * one would silently turn a grouped query into a non-grouped one.
15 * @type {Record<string, (values: any[]) => any>}
16 */
17 export const aggregates = {
18 COUNT: (values) => values.reduce((n, v) => (v === null ? n : n + 1), 0),
19 SUM: (values) => {
20 const nums = numbersOf('SUM', values);
21 return nums.length === 0 ? null : nums.reduce((a, b) => a + b, 0);
22 },
23 AVG: (values) => {
24 const nums = numbersOf('AVG', values);
25 return nums.length === 0 ? null : nums.reduce((a, b) => a + b, 0) / nums.length;
26 },
27 MIN: (values) => extreme('MIN', values, -1),
28 MAX: (values) => extreme('MAX', values, 1),
29 ARRAY_AGG: (values) => values,
30 };
31
32 /** @param {string} name */
33 export function isAggregateName(name) {
34 return Object.prototype.hasOwnProperty.call(aggregates, name.toUpperCase());
35 }
36
37 /** @param {string} name @param {any[]} values */
38 function numbersOf(name, values) {
39 /** @type {number[]} */
40 const out = [];
41 for (const v of values) {
42 if (v === null) continue;
43 if (typeof v !== 'number') {
44 throw new SqlEvaluationError(`${name} requires numbers, got ${typeName(v)}`);
45 }
46 out.push(v);
47 }
48 return out;
49 }
50
51 /**
52 * @param {string} name
53 * @param {any[]} values
54 * @param {-1|1} sign - -1 keeps the smallest value, 1 the largest
55 */
56 function extreme(name, values, sign) {
57 /** @type {number|string|null} */
58 let best = null;
59 for (const v of values) {
60 if (v === null) continue;
61 if (typeof v !== 'number' && typeof v !== 'string') {
62 throw new SqlEvaluationError(`${name} requires numbers or strings, got ${typeName(v)}`);
63 }
64 if (best === null) {
65 best = v;
66 continue;
67 }
68 if (typeof v !== typeof best) {
69 throw new SqlEvaluationError(`${name} cannot mix numbers and strings`);
70 }
71 if (sign < 0 ? v < best : v > best) best = v;
72 }
73 return best;
74 }
75
76 /** @param {any} v */
77 function typeName(v) {
78 if (v === null) return 'null';
79 if (Array.isArray(v)) return 'array';
80 return typeof v;
81 }