Skip to content

Understanding a spec in SQL and dataframe terms#

This page describes the declarations and operators of the specification of an optimisation model (a spec) as tables and queries. It is for a reader who thinks in SQL, or in a dataframe library such as polars or pandas. The rules themselves are in the language reference.

Every query below is over this spec, in which generators meet the load at their bus in each snapshot:

dimensions:
  snapshot: { dtype: int, ordered: true }
  generator: { dtype: str }
  bus: { dtype: str }
maps:
  generator: { bus: bus }
parameters:
  load: { dims: [snapshot, bus] }
  capacity: { dims: [generator] }
  cost: { dims: [generator], missing: 0 }
  ramp_limit: { dims: [generator] }
variables:
  dispatch:
    dims: [snapshot, generator]
    where: "capacity > 0"
    bounds: { lower: 0, upper: capacity }
constraints:
  power_balance:
    dims: [snapshot, bus]
    expression: sum(dispatch, over=generator, by=generator[bus]) == load
  ramp_up:
    dims: [snapshot, generator]
    expression: dispatch - shift(dispatch, along=snapshot, offset=1) <= ramp_limit
objective:
  sense: minimize
  expression: sum(dispatch * cost)

dispatch is the output of each generator in each snapshot. power_balance requires the output at each bus to meet the load, and ramp_up limits how fast a generator raises its output. The spec loads.

Declarations#

A dimension is the domain of a key column. generator is the set of labels that a generator column may contain. One point of the dimensions of an entry, such as one generator in one snapshot, is a coordinate: one value of each key column.

A parameter is a table indexed by its dims. load is a table with the columns snapshot, bus and one value column, and its primary key is (snapshot, bus).

A gap in the data is a row that is not in the table. With data attached, a tool refuses a null value, so leave the row out instead of writing a null. The missing: key of the parameter sets what the gap means.

A variable and a constraint are tables of the rows that a tool builds. dispatch has one decision variable per coordinate of (snapshot, generator), and power_balance has one row per coordinate of (snapshot, bus).

A relation is a table whose columns are all in its primary key, so it lists which combinations of labels exist. A map is a column of the table of its subject. generator: { bus: bus } under maps: is a table generator with the columns generator and bus, and one row per generator. Under the default missing: refused, every generator needs a bus. Under missing: absent, the bus of a generator may be null.

Absence#

A where: is a SQL WHERE on the rows that a declaration builds. Where capacity is 0, dispatch has no decision variable, so the coordinate is absent. Absent is different from zero, because zero is a value. An expression that uses an absent coordinate has no row there either.

Arithmetic is an inner join on the shared key columns. dispatch * cost joins the table of dispatch and the table of cost on generator. A key column that only one side has is crossed, so dispatch * cost has the columns snapshot and generator. Where either side has no row, the result has no row. Under the default missing: refused, a parameter has every row, so the join drops only the rows that a where: or a missing: absent left out.

A missing: value turns the join into a left join that fills the gap. cost declares missing: 0, so a generator with no cost row has a cost of 0, as COALESCE(cost.value, 0) after a LEFT JOIN gives.

Operators#

The table covers sum, at, shift and sum_back, and each one is one query shape. In the table, x is the table of the operand and value is its value column. rest is every key column of x that the operator does not name, and S is the table of the subject in by= or within=.

Operator SQL polars
sum(x, over=d) SELECT rest, SUM(value) FROM x GROUP BY rest x.group_by(rest).agg(pl.col('value').sum())
sum(x) SELECT SUM(value) FROM x x.select(pl.col('value').sum())
sum(x, over=d, by=S[c]) SELECT rest, S.c, SUM(value) FROM x JOIN S ON x.d = S.d GROUP BY rest, S.c x.join(S, on=d).group_by([*rest, c]).agg(pl.col('value').sum())
at(x, by=S[c]) SELECT S.key, rest, value FROM S JOIN x ON S.c = x.c S.join(x, on=c)
shift(x, along=d, offset=n) LAG(value, n) OVER (PARTITION BY rest ORDER BY d), and the first n rows are dropped pl.col('value').shift(n).over(rest, order_by=d), then drop the nulls
shift(…, edge=v) LAG(value, n, v) OVER (PARTITION BY rest ORDER BY d) pl.col('value').shift(n, fill_value=v).over(rest, order_by=d)
sum_back(x, along=d, window=n) SUM(value) OVER (PARTITION BY rest ORDER BY d ROWS BETWEEN n - 1 PRECEDING AND CURRENT ROW) pl.col('value').rolling_sum(n, min_samples=1).over(rest, order_by=d)

The SQL column uses the window functions of the SQL:2011 standard, which PostgreSQL, DuckDB and SQLite run as written. The polars column needs polars 1.21 or later, the first release with min_samples=.

The queries state what an operator means, not how a tool computes it. They are exact except in four cases:

  • shift and sum_back count positions of the dimension, not rows of the table. LAG steps over a missing row to the row before it. Where the position before has no row, the result of shift is absent instead.
  • edge='wrap' has no window-function form. In shift, the first position takes the value of the last. In sum_back, a window that reaches past the start continues from the end of the dimension.
  • A window= given as a parameter, such as window=min_up, has no frame form. Each entity then has its own window length, and a SQL frame takes a constant. Write it as a self-join on the positions from d - min_up + 1 to d.
  • within=S[c] adds the group to the partition. After a join with S, shift(…, within=S[c]) is PARTITION BY rest, S.c.

Queries outside the language#

A spec combines two tables only through arithmetic, sum(by=) and at(by=). Each of sum(by=) and at(by=) joins with the table of a relation or a subject, and drops the key column of the join in the same call. A separate join would return a table with a key column that no dimension declares, so mathspec has no join operator.

You run any other query, such as a pivot, a string operation or a join on a computed column, before the data is attached. That work is data preparation.

You can now read each line of a spec as a query, and judge where a literal query differs from the spec.

See also