Application areas
Application areas for formulas stored in database fields
Colbert allows to solve problems in SQL that otherwise would have to be solved outside the database for a multiple of cost and completion time.
We have listed a couple of examples below. See how easy problems can now be solved in SQL and realize how difficult, if not impossible, it would be to solve this in SQL without Colbert !
Criteria matching
Find a match between buyers and available cars
A formula can have a result of any type: for instance, a boolean formula Find can define criteria to indicate a person’s interest in a particular second hand 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.
The challenge:
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 Formula in the where-clause:
Buyer join Cars where Find
Bonus calculation
Calculate the individual annual employee bonus
With a field Bonus of type <Expr>double , the agreed annual employee bonus can easily be calculated.
The challenge:
Show the individual annual employee bonus.
Colbert SQL with a Formula in the from-clause:
Name, Year, Bonus from Employees natural join Sales
where Year between 2016 and 2018
Risk analysis
Evaluate risk scores in e.g. insurance or banking industry
To determine risk scores for a population, two formulas are
defined in the Risk table: a boolean risk criterion and an integer score calculation: If the criterion holds, the score contributes to a risk.
The challange:
Show the caclulated risk Scores for the matching Crit.
- The Risk table is defined as:
Create table Risk (Crit Expr<bool>, Score Expr<int>) - 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.
Colbert SQL with a Formula in the from-clause and a Formula in the where-clause:
Fam.*, AgeOf BirthDate as Age, Code Crit, Code Score, Score
from Fam join Risk on crit
Cohort definition
Divide a population into cohorts based on a formula
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.
The challange:
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.
Colbert SQL with the First operator and a Formula in the on-clause.
Name, CohortName from Fam
join first (FamCohort order by Rank) on Def
Contact
- Colbert Databases BV
- A.Groenlaan 83
- 9731 KK Groningen
- Netherlands
- info@colbert.nl
- +31 50 3603915
© Colbert Databases BV 2019