Table Programming

BigObject allows user-defined functions to be used within a query statement on table objects as well. Examples will be used to illustrate how it works.

IN GROUP BY

Assuming that a user would like to compare weekday and weekend sales, this question can be easily answered by the following SELECT statement:

 SELECT wkd(Date), SUM(total_price) FROM sales GROUP BY wkd(Date)

An example result table is as shown below:

As can be seen from the result, the weekday and weekend sales is about 3.9M and 1.55M respectively. The query is enabled by applying function wkd() to determine whether it is weekday (denoted by 0) or weekend (denoted by 1) for each transaction.

The function wkd() is quite simple:

function wkd(d)
  wd = d:weekday()
  if (wd >= 1 and wd <= 5) then
    return 0
  else 
    return 1
  end
end

The argument d is a DateTime type variable. We use built-in function weekday() to obtain its week day. It returns 0 if it is weekday (1 to 5) or 1 if it is weekend (0 or 6).

User-defined functions can be used with other attributes in the GROUP BY clause, such as:

SELECT Product.brand, wkd(Date), SUM(total_price) FROM sales GROUP BY Product.brand, wkd(Date)

The above SELECT statement returns the weekday and weekend sales for each product brand.

IN WHERE

User-defined functions can also be used in WHERE clause as a filter. For example, if the user only wants to obtain weekend sales, he can do:

SELECT SUM(total_price) FROM sales WHERE wkd(Date) = 1

IN SELECT

BigObject does not support expressions as selected values. However, it can be achieved by table programming. Assuming that the user would like to calculate the unit price for each transaction, he can define function unit_price() first:

function unit_price(qty, total_price)
   return qty / total_price;
end

Then the following SELECT can be issued together with function unit_price() to obtain the unit price for each transaction.

SELECT unit_price(qty, total_price) from sales