Analytics

functions

Functions allow to do advanced sql, the general syntax is this

select column_name, function_parameters
from view_name function function_name(function_arguments)
group by column_name

function_parameters: p(colum_name1), p(colum_name2), ...

function_arguments: depends on the function

function fn_having

Fn_having is a filter that applys on an operator results (for instance items that have been sold in more that 3 stores)

fn_having(<count|countdistinct|sum|sumpareto>, operator, value)

operator: = < > <= >=

function fn_store/fn_merge basics

Sql is often used to format/display results, fn_store/fn_merge do that

with
with_name1 as (
  select group_by_col_name, stored_values
  from view_name function fn_store ...
  group by group_by_col_name
),
...
select col_type.val, with_name1.alias1 ... from no_view, * function fn_merge

stored_values: operator(col_name) as alias1, operator(col_name) as alias2 etc..

Note1: all the with must have 0 or 1 group by column

Note2: all the group by columns must have the same col_type, you refer to this col_type with col_type.val in the fn_merge select

function fn_build_filter

Fn_build_filter builds a boolean matrix, matrix[x][y] = y/n (x: group by col value, y: text column value)

select group_by_col_name, p(text_col_name) from view_name function fn_build_filter(order,columns_kept)

group_by_col_name: optional, a one line matrix is created if not provided

order: <asc|desc> optional defaults to asc

columns_kept: 1 means keep first, 2 keep first two, etc... optional defaults to keep all

function fn_apply_filter

Fn_apply_filter applys the boolean matrix produced by fn_build_filter

select group_by_col_name, stored_values, p(col_name1), p(col_name2) from view_name
function fn_apply_filter(with_alias) ...
group by group_by_col_name

with_alias: with holding the fn_build_filter, or null meaning "take previous with"

group_by_col_name: optional

stored_values: operator(col_name) as alias1, operator(col_name) as alias2 etc..

col_name1: must have the same col_type as fn_build_filter_with group by column

col_name2: must have the same col_type as fn_build_filter_with first parameter column

function fn_pivot

Fn_pivot builds a number matrix, matrix[x][y] = num_value (x: group by col value, y: text column value)

select group_by_col_name, p(text_col_name), p(number_col_name)
from view_name function fn_pivot(operator, with_alias, custom_function, signature, before_fn, after_fn)
group by group_by_col_name

group_by_col_name: optional, a one line matrix is created if not provided

operator: <sum|sumprevious|sumafter|last>

with_alias: with holding the fn_build_filter, or null meaning "no filter, no sort"

custom_function: optional, can be dynamic code or function in stormbase.so

signature: <fn_num|fn_num_num|fn_key_num|fn_key3_num2>, the custom function signature (if and only if custom function is provided)

before_fn: optional, code or null, can be dynamic code or fn name in stormbase.so

after_fn: optional, code or null, can be dynamic code or fn name in stormbase.so

Note: custom function usage is explain in the fn_custom section

function fn_filter_pivot

Fn_filter_pivot removes columns from matrix created by fn_pivot

select * from no_view function fn_filter_pivot(with_alias1, with_alias2)

with_alias1: with holding the fn_pivot

with_alias2: with holding the fn_build_filter

Note: used in general with sumafter/sumprevious

function fn_unpivot

Fn_unpivot factorize the matrix created by fn_pivot

select * from no_view funtion fn_unpivot(<sum|avg>, with_holding_the_fn_pivot, custom_function)

custom_function: optional, can be dynamic code or code in stormbase.so

function fn_custom

Fn_custom: ...

select group_by_col_name, function_parameters from view_name
function fn_custom(custom_function, signature, before_fn, after_fn)
group by group_by_col_name

group_by_col_name: optional, a one line matrix is created if not provided

function_parameters: p(col_name1), p(col_name2), etc..

custom_function: optional, can be dynamic code or function in stormbase.so

signature: <fn_num|fn_num_num|fn_key_num|fn_key3_num2>, the custom function signature (if and only if custom function is provided)

before_fn: optional, code or null, can be dynamic code or fn name in stormbase.so

after_fn: optional, code or null, can be dynamic code or fn name in stormbase.so

< * | *** >

Analytics

Analytics

functions

function fn_having

function fn_store/fn_merge basics

function fn_build_filter

function fn_apply_filter

function fn_pivot

function fn_filter_pivot

function fn_unpivot

function fn_custom