We are using cookies to give you the best experience on our site. Cookies are files stored in your browser and are used by most websites to help personalise your web experience.
By continuing to use our website without changing the settings, you are agreeing to our use of cookies.


COEL formulas

From The Concept-Oriented Portal
Revision as of 12:10, 22 August 2015 by Savinov (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

COEL formula represents a mathematical function which computes one output value for input values. One COEL expression determines how one output value is computed while it is the task of the engine to loop through all inputs and store the outputs.

Compare how COEL expressions are different from spreadsheet formulas. For example, the following formula defines a value in the cell C1 as a sum of values in A1 and B1:

   = A1 + B1 

Note that the value of only one cell is defined and will be computed. Each cell has two coordinates.

In COEL, we define columns rather than cells and an analogous formula computing column C from columns A and B could be written as follows:

   = A + B 

Note that now the arguments have only one coordinate: column name. This means that the system will evaluate this formula for all rows (inputs) by producing all outputs rather than a single value. This is why we call it column formula and not cell formula.

Contents

Calculated columns

Calculated columns return a primitive value which is computed from input values read from this same table. Assume that table LineItems has two columns price and quantity. We can compute a new column amount as follows:

   [amount] = [price] * [quantity] 

Each value of the amount column is equal to the product of values in the other two columns from same row of the table.

Link columns

Link columns are thought of as a substitute for joins.

A COEL expression can return an element of non-primitive table. Note however that it is not possible to compute a non-primitive output value using arithmetic operations. To return a non-primitive value, it is necessary to define a tuple from the output set. In other words, if an expression returns a tuple then this means that it returns a value representing the corresponding (matching) element from the output set.

Assume that we want to define a new column order to the table LineItems which returns a purchase order this item belongs to from the table Orders. This can be done as follows:

   [order] = TUPLE( supp=[supplierId], no=[orderNo] )

Here supp and no are column of the output table Orders while supplierId and orderNo are columns of the input table LineItems.

Importantly, once the link column order has been defined, it can be used as a normal column in other expressions which returns an order for each line item.

Accumulation columns

Accumulation columns are thought of as a substitute for group-by operation.

An accumulation column is able to aggregate multiple values during evaluation of its formula. These multiple values are produced while looping through some other table which is referred to as a fact table.

For example, we could add a new column total to the table Orders by summing up the prices of all line items belong to this order:

   [total] = ACCU( [LineItems], [order], [amount], SUM ) 

The accumulation function takes four parameters:

  • LineItems is a fact table
  • order is grouping column which specifies an order this fact record belongs to. It is a link column which can be an arbitrary COEL expression.
  • amount is a measure column the output values of which are being accumulated. It is an existing column or an arbitrary calculated column.
  • SUM is an aggregation function which specifies how the multiple values returned by the measure column have to be accumulated.

See also

Personal tools
Google AdSense