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: = < > <= >=
select
item_id, count(customer_id), countdistinct(customer_id), sum(sales_qty)
from
v_sales
group by
item_id
callback
sort(4,'desc'
);
item_id | count(customer_id) | countdistinct(customer_id) | sum(sales_qty) |
artB | 3 | 2 | 17 |
artA | 3 | 2 | 15 |
# | 1 | 1 | 8 |
select
sum(sales_qty) from
v_sales;
select
item_id, p(customer_id) from
v_sales function
fn_having(count,=,3) group by
item_id;
select
item_id, p(customer_id) from
v_sales function
fn_having(countdistinct,>=,2) group by
item_id;
select
item_id, p(sales_qty) from
v_sales function
fn_having(sum,<,17) group by
item_id;
select
item_id, p(sales_qty) from
v_sales function
fn_having(sumpareto,<=,40) group by
item_id;
item_id | <=40.00 |
artA | y |
artB | y |
# | y |
select
item_id, p(sales_qty) from
v_sales function
fn_having(sumpareto,>=,40) group by
item_id;
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..
operator: <sum|count,countdistinct|min|max|minstr|maxstr|uniques>
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
with
a as
(select
item_id, sum(avg_week_sales) as
v, unique(art_label) as
label from
v_items function
fn_store group by
item_id),
b as
(select
item_id, sum(sales_qty) as
v from
v_sales function
fn_store group by
item_id)
select
col_type
.val,
a.v, a.label,
b.v,
'avg is '
..decode(nvl(a.v,0)>b.v,true,'greater'
,'lower'
)..' than real sales'
as
label,
'previous value is: '
..line.label as
label2,
from
no_view, * function
fn_merge;
col_type.val | a.v | a.label | b.v | label | label2 |
| # | # | 8 | avg is lower than real sales | previous value is: avg is lower than real sales |
artA | 10 | the article A | 15 | avg is lower than real sales | previous value is: avg is lower than real sales |
artB | 10 | the article B | 17 | avg is lower than real sales | previous value is: avg is lower than real sales |
box1 | 10 | a box | # | # | previous value is: [string "return 'avg is '..decode(nvl(a.v,0)>b.v,tru..."]:1: attempt to compare nil with number |
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
select
item_id, p(sales_date) from
v_sales function
fn_build_filter() and
item_id nin (null
) group by
item_id;
item_id | 20191231 | 20200102 |
artA | X | _ |
artB | X | X |
select
item_id, p(sales_date) from
v_sales function
fn_build_filter(desc
) and
item_id nin (null
) group by
item_id;
item_id | 20200102 | 20191231 |
artA | _ | X |
artB | X | X |
select
item_id, p(sales_date) from
v_sales function
fn_build_filter(asc,1) and
item_id nin (null
) group by
item_id;
item_id | 20191231 | 20200102 |
artA | X | _ |
artB | X | _ |
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
select
unique(item_id), sales_date, sum(sales_qty), count(sales_qty) from
v_sales and
item_id='artA'
group by
sales_date callback
sort(2,'desc'
) limit(1);
unique(item_id) | sales_date | sum(sales_qty) | count(sales_qty) |
artA | 20191231 | 15 | 3 |
select
unique(item_id), sales_date, sum(sales_qty), count(sales_qty) from
v_sales and
item_id='artB'
group by
sales_date callback
sort(2,'desc'
) limit(1);
unique(item_id) | sales_date | sum(sales_qty) | count(sales_qty) |
artB | 20200102 | 13 | 2 |
with
a as
(select
item_id, p(sales_date) from
v_sales function
fn_build_filter(desc
,1) and
item_id nin (null
) group by
item_id),
b as
(
select
item_id,
maxstr(sales_date) as
sales_date, sum(sales_qty) as
sales_qty, count(sales_qty) as
count,
p(item_id), p(sales_date),
from
v_sales function
fn_apply_filter(a)
group by
item_id
)
select
col_type
.val, b.sales_date, b.sales_qty, b.count, stringify(b) from
no_view, * function
fn_merge
;
col_type.val | b.sales_date | b.sales_qty | b.count | stringify(b) |
artA | 20191231 | 15 | 3 | {#lines:3,count:3,sales_qty:15,sales_date:'20191231',} |
artB | 20200102 | 13 | 2 | {#lines:2,count:2,sales_qty:13,sales_date:'20200102',} |
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
select
p(item_id), p(sales_qty) from
v_sales function
fn_pivot(sum,null
);
| | | | null | | | | | | NO_DATA | artA | artB | box1 | artC |
0 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 15 | 17 | 0 | 0 |
select
customer_id, p(item_id), p(sales_qty) from
v_sales function
fn_pivot(sum,null
) group by
customer_id;
customer_id | | | | | null | | | | | | NO_DATA | artA | artB | box1 | artC |
C1 | 0 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 4 | 0 | 0 |
C2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 13 | 0 | 0 |
select
item_id, p(sales_date), p(sales_qty) from
v_sales function
fn_pivot(sum,null
) group by
item_id;
item_id | | | | | null | | | | | | 20191201 | 20191231 | 20201201 | 20201231 | 20190101 | 20200101 | 20200102 |
artA | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 15 | 0 | 0 | 0 | 0 | 0 |
artB | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 13 |
# | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 |
select
item_id, p(sales_date), p(sales_qty) from
v_sales function
fn_pivot(sumprevious,null
) group by
item_id;
item_id | | | | | null | | | | | | 20191201 | 20191231 | 20201201 | 20201231 | 20190101 | 20200101 | 20200102 |
artA | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 15 | 15 | 15 | 15 | 15 | 15 |
artB | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 17 |
# | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 |
select
item_id, p(sales_date), p(sales_qty) from
v_sales function
fn_pivot(sumafter,null
) group by
item_id;
item_id | | | | | null | | | | | | 20191201 | 20191231 | 20201201 | 20201231 | 20190101 | 20200101 | 20200102 |
artA | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 15 | 0 | 0 | 0 | 0 | 0 | 0 |
artB | 17 | 17 | 17 | 17 | 17 | 17 | 17 | 17 | 17 | 17 | 17 | 13 | 13 | 13 | 13 | 13 | 0 |
# | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 0 |
select
item_id, p(sales_date), p(sales_qty) from
v_sales function
fn_pivot(last,null
) group by
item_id;
item_id | | | | | null | | | | | | 20191201 | 20191231 | 20201201 | 20201231 | 20190101 | 20200101 | 20200102 |
artA | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | 0 | 0 |
artB | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 7 |
# | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 |
select
minstr(sales_date) from
v_sales and
customer_id='C2'
and
item_id='artA'
;
minstr(sales_date) |
20191231 |
with
a as
(select
p(sales_date) from
v_sales function
fn_build_filter(asc,1) and
customer_id='C2'
and
item_id='artA'
)
b as
(select
item_id, p(sales_date), p(sales_qty) from
v_sales function
fn_pivot(sum,a) group by
item_id)
select
col_type
.val, stringify(b) from
no_view, * function
fn_merge;
col_type.val | stringify(b) |
| {20191231:0,} |
artA | {20191231:15,} |
artB | {20191231:4,} |
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
with
a as
(select
p(sales_date) from
v_sales function
fn_build_filter(asc,1) and
customer_id='C2'
and
item_id='artA'
),
b as
(select
item_id, p(sales_date), p(sales_qty) from
v_sales function
fn_pivot(sum,null
) group by
item_id),
c as
(select
* from
no_view function
fn_filter_pivot(b,a)),
select
col_type
.val, stringify(a), stringify(b), stringify(c) from
no_view, * function
fn_merge;
col_type.val | stringify(a) | stringify(b) | stringify(c) |
| {20191231:'X',} | {:0,20191201:0,20201201:0,20191231:0,20200102:8,20190101:0,20201231:0,null:0,20200101:0,} | {20191231:0,} |
artA | {20191231:'X',} | {:0,20191201:0,20201201:0,20191231:15,20200102:0,20190101:0,20201231:0,null:0,20200101:0,} | {20191231:15,} |
artB | {20191231:'X',} | {:0,20191201:0,20201201:0,20191231:4,20200102:13,20190101:0,20201231:0,null:0,20200101:0,} | {20191231:4,} |
with
a as
(select
item_id, p(sales_date) from
v_sales function
fn_build_filter(asc,1) and
customer_id='C2'
group by
item_id),
b as
(select
item_id, p(sales_date), p(sales_qty) from
v_sales function
fn_pivot(sum,null
) group by
item_id),
c as
(select
* from
no_view function
fn_filter_pivot(b,a)),
select
col_type
.val, stringify(a), stringify(b), stringify(c) from
no_view, * function
fn_merge;
col_type.val | stringify(a) | stringify(b) | stringify(c) |
| {} | {:0,20191201:0,20201201:0,20191231:0,20200102:8,20190101:0,20201231:0,null:0,20200101:0,} | {} |
artA | {20191231:'X',20200102:'_',} | {:0,20191201:0,20201201:0,20191231:15,20200102:0,20190101:0,20201231:0,null:0,20200101:0,} | {20191231:15,20200102:0,} |
artB | {20191231:'_',20200102:'X',} | {:0,20191201:0,20201201:0,20191231:4,20200102:13,20190101:0,20201231:0,null:0,20200101:0,} | {20191231:0,20200102:13,} |
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
select
sum(sales_qty), count(sales_qty), avg(sales_qty) from
v_sales;
sum(sales_qty) | count(sales_qty) | avg(sales_qty) |
40 | 7 | 5.714 |
select
p(item_id), p(sales_qty) from
v_sales function
fn_pivot(sum,null
);
| | | | null | | | | | | NO_DATA | artA | artB | box1 | artC |
0 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 15 | 17 | 0 | 0 |
with
a as
(select
p(item_id), p(sales_qty) from
v_sales function
fn_pivot(sum,null
))
b as
(select
* from
no_view function
fn_unpivot(sum,a)),
c as
(select
* from
no_view function
fn_unpivot(avg,a))
select
b.val, c.val, b.val/c.val as
nb_col_in_fn_pivot from
no_view,* function
fn_merge;
b.val | c.val | nb_col_in_fn_pivot |
40 | 2.667 | 15 |
select
customer_id,item_id, sum(sales_qty) from
v_sales group by
customer_id,item_id;
customer_id | item_id | sum(sales_qty) |
C1 | artA | 10 |
C2 | artB | 13 |
C1 | artB | 4 |
C1 | # | 8 |
C2 | artA | 5 |
with
a as
(select
customer_id, p(item_id), p(sales_qty) from
v_sales function
fn_pivot(sum,null
) group by
customer_id)
b as
(select
* from
no_view function
fn_unpivot(sum,a)),
c as
(select
* from
no_view function
fn_unpivot(avg,a)),
select
col_type
.val, b.val, c.val from
no_view,* function
fn_merge;
col_type.val | b.val | c.val |
C1 | 22 | 1.467 |
C2 | 18 | 1.200 |
with
a as
(select
item_id, p(sales_date) from
v_sales function
fn_build_filter(asc,1) and
customer_id='C2'
group by
item_id),
b as
(select
item_id, p(sales_date), p(sales_qty) from
v_sales function
fn_pivot(sum,null
) group by
item_id),
c as
(select
* from
no_view function
fn_filter_pivot(b,a)),
d as
(select
* from
no_view function
fn_unpivot(sum,c)),
select
col_type
.val, d.val from
no_view, * function
fn_merge;
col_type.val | d.val |
artA | 15 |
artB | 13 |
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
./_SO_CODE/Z_doc_fn_custom_step1.c
#include
"./common.h"
static
char
*my_column = "my_column"
;
int
fn_one_column_one_line_before(WITH_INFOS* with_infos) {
 with_infos->out_col_count
= 1;
 with_infos->out_types
= malloc
(with_infos->out_col_count
* sizeof
(int
));
 with_infos->out_headers
= malloc
(with_infos->out_col_count
* sizeof
(char
*));
 char
* s = malloc
(strlen(my_column)+1);
 strcpy(s, my_column);
 with_infos->out_headers
[0] = s;
 with_infos->out_types
[0] = ANALYTIC_FLOAT;
 float
*f = malloc
(sizeof
(float
));
 *f = 0.;
 with_infos->out_dyn_fn_context
= f;
 with_infos->out_multi_thread_autorized
= 'n';
 return
OK;
}
int
fn_one_column_one_line_after(WITH_INFOS* with_infos) {
 WITH_RESULT *res = with_infos->current_with_result
;
 float
*f = res->dyn_fn_context
;
 res->lines_float
[0] = malloc
(res->col_count
* sizeof
(float
));
 res->lines_float
[0][0] = *f;
 return
OK;
}
set
SO_FILE_NAME='Z_doc_fn_custom_step1.so'
;
select
p(sales_qty) from
v_sales function
fn_custom(
'(void* c, U_INT thread_pos, U_INT iline, float num){float *f=c; *f+=num; return 0.;}'
,
'fn_num'
,
fn_one_column_one_line_before,fn_one_column_one_line_after
)
;
select
sum(sales_qty) from
v_sales
;
shutdown
;