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
determine whether it is weekday (denoted by 0) or weekend (denoted by 1) for
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.
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
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
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