Predictive queries as SQL. TRAIN a model on the tables you already have, PREDICT with it, BACKTEST it.
Installing and Loading
INSTALL pql FROM community;
LOAD pql;
Example
CREATE TABLE customers AS
SELECT i AS id, (i % 7) + 1 AS tier, (i % 5) = 0 AS churned FROM range(300) t(i);
TRAIN MODEL churn PREDICT customers.churned FOR customers;
PREDICT customers.churned FOR customers WHERE tier >= 5 USING MODEL churn;
About pql
PQL adds TRAIN MODEL, PREDICT, BACKTEST MODEL, EXPLAIN MODEL and
DROP MODEL to DuckDB. A model is trained directly on the tables in the
catalog: PQL follows foreign keys to build features from related tables
(counts, averages, recency, spacing), holds out a slice, and reports the
metric. Forecast targets (COUNT(orders), SUM(orders.total),
EXISTS(orders) over a HORIZON) are labelled at anchor times so that
nothing after the anchor can leak into the features. Models live for the
session; pql_models() lists them with their defining statement.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| pql_exec | table | Run a PQL statement and return its result as a table. | NULL | [SELECT * FROM pql_exec('PREDICT customers.churned FOR customers USING MODEL churn')] |
| pql_models | table | List the PQL models trained in this session, with the statement that defined each one. | NULL | [SELECT * FROM pql_models()] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
This extension does not add any settings.