Things

A Thing is a GROUP BY query whose result ASQL keeps current. When a row in a table the Thing reads changes, only the groups that row belongs to are computed again, before the write returns. Reading a Thing returns the stored result; the query does not run.

Use one where you would otherwise keep a summary table with triggers, run a batch job, or run the same aggregate on every page load.

Create, read, drop

CREATE THING order_totals KEY orderno READS lines
  AS SELECT orderno, COUNT(*) AS line_count, SUM(qty) AS qty, SUM(picked) AS picked
     FROM lines GROUP BY orderno;

SHOW THINGS;                 -- every Thing: rows, version, recomputes, what it waits for
SHOW THING order_totals;     -- the current result, with when each row last changed and why
DROP THING order_totals;

Rules

• The first selected column and the first GROUP BY column must be the KEY.
• GROUP BY is required. HAVING, ORDER BY, LIMIT, UNION and WITH ROLLUP are not allowed.
• READS t1, t2 is optional. Without it, the Thing reads every table its query names.
• Values can be any expression (CASE, MAX, arithmetic, joins).
• A query that uses CURDATE(), NOW() or another clock function is computed whole again once each new day, so date windows such as "this month" stay right.
• A table joined without the key column makes every change to it recompute the whole Thing. Join on the key where you can.

Partitioning by another column

PARTITION BY col defaults to the key. Partitioning by a different column lets one change touch fewer rows, but then every value must be a SUM or COUNT, optionally times a number:

CREATE THING sales_by_rep KEY salesman PARTITION BY debtorno READS debtortrans
  AS SELECT salesman, SUM(ovamount) AS sales, COUNT(*) AS invoices
     FROM debtortrans GROUP BY salesman;

A Thing as a table

INTO db.table also writes the result to a table that other queries, and other Things, can read:

CREATE THING order_totals KEY orderno INTO store.order_totals READS lines
  AS SELECT orderno, SUM(qty) AS qty FROM lines GROUP BY orderno;

A Thing cannot read, directly or through others, a table it writes.

Managing a Thing

Next: Acting on a Thing, History, previews and waiting.