Formulas in database fields

Application areas for formulas stored in database fields

With Colbert it is possible to solve problems in SQL that would otherwise have to be solved outside the database for a multiple of cost and completion time.
We have listed some examples below. See how easy problems can be solved now in SQL and realize how difficult, if not impossible, it would be to solve this in SQL without Colbert !

Criteria matching

A formula can have a result of any type: for instance, a boolean formula might define criteria to indicate a person’s interest in a particular car.

When a formula is called in a SQL query, it is compiled once and parameters are bound to expressions found in the context of that query.
Buyer search car by formula
Second hand cars
Show which car matches the search criteria for every buyer.
  • The Buyer table is defined as: create table Buyer(Name string, Find Expr<bool>)
  • No further SQL extensions are needed.
  • The calculated value of Find is used in the where-clause to find a match.

Colbert SQL with a Formule in the where-clause

Buyer join Cars where Find

Bonus calculation

With a field Bonus of type <Expr>double , the agreed bonus per year can easily be calculated.

Employees table with bonus formulas
Sales figures
Show the Bonus each employee earns per year.
  • In Colbert the formula is registered while the calculated value is available at the same time.
Bonus calculation in SQL

Colbert SQL with a formule in the from-clause

Name, Year, Bonus from Employees  natural join Sales where Year between 2016 and 2018

Risk analysis

To determine risk scores for a population, two formulas are
defined in the Risk table: a boolean risk criterion and an integer score calculation.

Risk criteria and ris scores in SQL
Fam table

Show the caclulated risk Scores for the matching Crit.

  • The Risk table is defined as:
    Create table Risk (Crit Expr, Score Expr)
  • As table Fam has no Age field, it is defined in the from-clause as AgeOf BirthDay
  • The operator Code shows the formula itself instead of the calculated value.
Risk calculation in SQL

Colbert SQL with a formula in the from-clause and in the where-clause.

Fam.*, AgeOf BirthDate as Age, Code Crit, Code Score, Score from Fam join Risk on crit

Show total risk scores per person.
(Just an aggregation added to the former example).

Colbert SQL with sum over formulas.

Name, Count as Hits, Sum Score from (Name, AgeOf BirthDate as Age, Score from Fam join Risk on crit)

Cohort definition

Sometimes it is not precise enough to divide a population into cohorts based on field values. Sometimes you want to divide a population into several cohorts by assigning characteristics based on formulas.

With Colbert you can apply these formulas with a SQL join to a population to get the right cohort division.

Fam table
Cohort definition

Show cohorts

  • In this example a person is assigned to exactly one cohort based on the matching formula with the lowest Rank.
    In Colbert this can be done by the First operator.
First found cohort in SQL

Colbert SQL with First and a Formula in the on-clause.

Name, CohortName from Fam join first (FamCohort order by Rank) on Def