* Sums one dimension over the range. * * On the `machines` metric: `daily_dim_counts.machines` is per day, so summing * it over a range gives **machine-days**, not distinct machines — a machine * seen on ten days counts ten times. A range-wide distinct count per dimension * value is not recover
(env: Env, url: URL, range: Range)
| 574 | * `max` is over a single row and the question does not arise. |
| 575 | */ |
| 576 | async function breakdown(env: Env, url: URL, range: Range): Promise<ApiResult> { |
| 577 | const dim = url.searchParams.get('dim') ?? ''; |
| 578 | const spec = DIMS[dim]; |
| 579 | if (!spec) return fail(`unknown dim — one of: ${Object.keys(DIMS).join(', ')}`); |
| 580 | |
| 581 | const requestedMetric = url.searchParams.get('metric'); |
| 582 | if (requestedMetric !== null && requestedMetric !== 'count' && requestedMetric !== 'machines') { |
| 583 | return fail('metric must be count or machines'); |
| 584 | } |
| 585 | const metric = requestedMetric ?? spec.metric; |
| 586 | |
| 587 | const rawLimit = url.searchParams.get('limit'); |
| 588 | const limit = rawLimit === null ? DEFAULT_BREAKDOWN_LIMIT : Number(rawLimit); |
| 589 | if (!Number.isInteger(limit) || limit < 1 || limit > MAX_BREAKDOWN_LIMIT) { |
| 590 | return fail(`limit must be an integer between 1 and ${MAX_BREAKDOWN_LIMIT}`); |
| 591 | } |
| 592 | |
| 593 | const event = url.searchParams.get('event') ?? spec.event ?? null; |
| 594 | if (event !== null && !/^[a-z_]{1,32}$/.test(event)) return fail('event must be a bare event name'); |
| 595 | |
| 596 | const binds: (string | number)[] = [dim, range.from, range.to]; |
| 597 | if (event !== null) binds.push(event); |
| 598 | |
| 599 | const { results } = await env.DB.prepare( |
| 600 | `SELECT value, sum(day_count) AS count, sum(day_machines) AS machines |
| 601 | FROM (SELECT day, value, sum(count) AS day_count, max(machines) AS day_machines |
| 602 | FROM daily_dim_counts |
| 603 | WHERE dim = ? AND day BETWEEN ? AND ?${event !== null ? ' AND event = ?' : ''} |
| 604 | GROUP BY day, value) |
| 605 | GROUP BY value`, |
| 606 | ) |
| 607 | .bind(...binds) |
| 608 | .all<BreakdownRow>(); |
| 609 | |
| 610 | const rows = results.map((r) => ({ |
| 611 | value: r.value, |
| 612 | count: r.count ?? 0, |
| 613 | machines: r.machines ?? 0, |
| 614 | })); |
| 615 | |
| 616 | const pick = (r: BreakdownRow): number => (metric === 'machines' ? r.machines : r.count); |
| 617 | |
| 618 | let ordered: BreakdownRow[]; |
| 619 | let truncated = false; |
| 620 | if (spec.order === 'bucket' && spec.buckets) { |
| 621 | // An ordered scale: the buckets keep their own order and all of them show, |
| 622 | // including empty ones, so the ordinal colour ramp always means the same thing. |
| 623 | const found = new Map(rows.map((r) => [r.value, r])); |
| 624 | const extra = rows.filter((r) => !spec.buckets?.includes(r.value)).sort((x, y) => pick(y) - pick(x)); |
| 625 | ordered = [ |
| 626 | ...spec.buckets.map((b) => found.get(b) ?? { value: b, count: 0, machines: 0 }), |
| 627 | ...extra, |
| 628 | ]; |
| 629 | } else { |
| 630 | const sorted = [...rows].sort( |
| 631 | spec.order === 'version_desc' |
| 632 | ? (x, y) => compareVersionsDesc(x.value, y.value) |
| 633 | : (x, y) => pick(y) - pick(x) || x.value.localeCompare(y.value), |