<  *  | *** >

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

-- ###########################
-- RUNNING stop_on_error.sql
stop_on_error;
reponse
success

-- ###########################
-- RUNNING clear.sh

/*
sure mode, no confirmation prompt
clearing directory INTERNAL_FILES_DIR (../STORAGE/INT) ...
done
clearing directory MAX_PERF_FILES_DIR (../STORAGE/MAX_PERF) ...
done
clearing directory TRANSAC_FILES_DIR (../STORAGE/TRANSAC) ...
done
*/

-- ###########################
-- RUNNING doc_data_init.sql
create col_type t_site_id as text;
reponse
success

create col_type t_dept_id as text;
reponse
success

create col_type t_item_id as text;
reponse
success

create col_type t_customer_id as text;
reponse
success

create col_type t_date as text;
reponse
success

create col_type t_customer_info as text;
reponse
success

create col_type end_user as text;
reponse
success

create merge table items( item_id t_item_id, art_label text, dept t_dept_id, avg_week_sales number, sales_price number);
reponse
success

create merge table customers( customer_id t_customer_id, customer_name text);
reponse
success

create table item_tags( item_id t_item_id, tag text);
reponse
success

create table fidelity_cards( customer_id t_customer_id, card_label t_customer_info, valid_from t_date, valid_until t_date);
reponse
success

create table item_customer_infos( customer_id t_customer_id, item_id t_item_id, info t_customer_info, valid_from t_date, valid_until t_date);
reponse
success

create big table sales( item_id t_item_id, customer_id t_customer_id, sales_date t_date, sales_qty number, line_id text, packaging_id t_item_id);
reponse
success

create big table inventory( item_id t_item_id, inv_qty number);
reponse
success

create view v_items as select * from items;
reponse
success

create view v_item_tags as select * from item_tags;
reponse
success

create view v_fidelity_cards as select * from fidelity_cards;
reponse
success

create view v_item_customer_infos as select * from item_customer_infos;
reponse
success

create view v_sales as select * from sales, items, customers where items.item_id=sales.item_id and customers.customer_id=sales.customer_id;
reponse
success

create view v_inventory as select * from inventory, items where items.item_id=inventory.item_id;
reponse
success

insert into items values('artA','the article A','dept #1',10,1.5);
reponse
success

insert into items values('artB','the article B','dept #2',10,3.2);
reponse
success

insert into items values('box1','a box','packaging',10,0);
reponse
success

insert into customers values('C1','customer #1')('C2','customer #2');
reponse
success

insert into item_tags values('artA','tag #1');
reponse
success

insert into item_tags values('artA','tag #2');
reponse
success

insert into fidelity_cards values('C1','SILVER','20191201','20191231');
reponse
success

insert into fidelity_cards values('C1','GOLD','20201201','20201231');
reponse
success

insert into item_customer_infos values('C1','artA','FREQUENT BUYER of artA in 2019','20190101','20191231');
reponse
success

insert into item_customer_infos values('C1','artB','FREQUENT BUYER of artB in 2020','20200101','20201231');
reponse
success

insert into item_customer_infos values('C2','artB','FREQUENT BUYER of artB in 2020','20200101','20201231');
reponse
success

insert into sales values('artA','C1','20191231',5,'ID01','box1');
reponse
success

insert into sales values('artB','C2','20200102',6,'ID02','');
reponse
success

insert into sales values('artB','C1','20191231',4,'ID03','');
reponse
success

insert into sales values('artB','C2','20200102',7,'ID04','box1');
reponse
success

insert into sales values('artC','C1','20200102',8,'ID05','');
reponse
success

insert into sales values('artA','C1','20191231',5,'ID06','box1');
reponse
success

insert into sales values('artA','C2','20191231',5,'ID07','box1');
reponse
success

insert into inventory values('artA',32);
reponse
success

insert into inventory values('artC',12);
reponse
success

refresh dirty view;
reponse
success

-- ###########################
-- RUNNING doc_analytics.sql
--

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: = < > <= >=
select item_id, count(customer_id), countdistinct(customer_id), sum(sales_qty)
from v_sales
group by item_id
callback sort(4,'desc');
item_idcount(customer_id)countdistinct(customer_id)sum(sales_qty)
artB3217
artA3215
#118

select sum(sales_qty) from v_sales;
sum(sales_qty)
40

select item_id, p(customer_id) from v_sales function fn_having(count,=,3) group by item_id;
item_id=3
artAy
artBy

select item_id, p(customer_id) from v_sales function fn_having(countdistinct,>=,2) group by item_id;
item_id>=2
artAy
artBy

select item_id, p(sales_qty) from v_sales function fn_having(sum,<,17) group by item_id;
item_id<17.00
artAy
#y

--top items, doing 80% (0.8*50=40) of the total sales
select item_id, p(sales_qty) from v_sales function fn_having(sumpareto,<=,40) group by item_id;
item_id<=40.00
artAy
artBy
#y

--bottom items, doing the remaning 20% of the total sales
select item_id, p(sales_qty) from v_sales function fn_having(sumpareto,>=,40) group by item_id;
item_id>=40.00
#y

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.vala.va.labelb.vlabellabel2
##8avg is lower than real salesprevious value is: avg is lower than real sales
artA10the article A15avg is lower than real salesprevious value is: avg is lower than real sales
artB10the article B17avg is lower than real salesprevious value is: avg is lower than real sales
box110a 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
--sort asc and keep everything by default
select item_id, p(sales_date) from v_sales function fn_build_filter() and item_id nin (null) group by item_id;
item_id2019123120200102
artAX_
artBXX

--sort desc
select item_id, p(sales_date) from v_sales function fn_build_filter(desc) and item_id nin (null) group by item_id;
item_id2020010220191231
artA_X
artBXX

--get the first sales_date by item
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_id2019123120200102
artAX_
artBX_

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
--last sales date of item artA
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_datesum(sales_qty)count(sales_qty)
artA20191231153

--last sales date of item artB
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_datesum(sales_qty)count(sales_qty)
artB20200102132

--same in one select using fn_build_filter/fn_apply_filter
--note the utility function stringify
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.valb.sales_dateb.sales_qtyb.countstringify(b)
artA20191231153{#lines:3,count:3,sales_qty:15,sales_date:'20191231',}
artB20200102132{#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
-- 1 dimension matrix per item_id
select p(item_id), p(sales_qty) from v_sales function fn_pivot(sum,null);
nullNO_DATAartAartBbox1artC
00008000000151700

-- 2 dimensions matrix per customer_id/item_id
select customer_id, p(item_id), p(sales_qty) from v_sales function fn_pivot(sum,null) group by customer_id;
customer_idnullNO_DATAartAartBbox1artC
C10000800000010400
C20000000000051300

-- 2 dimensions matrix per item_id/sales_date
select item_id, p(sales_date), p(sales_qty) from v_sales function fn_pivot(sum,null) group by item_id;
item_idnull20191201201912312020120120201231201901012020010120200102
artA000000000001500000
artB000000000004000013
#00000000000000008

select item_id, p(sales_date), p(sales_qty) from v_sales function fn_pivot(sumprevious,null) group by item_id;
item_idnull20191201201912312020120120201231201901012020010120200102
artA00000000000151515151515
artB000000000004444417
#00000000000000008

select item_id, p(sales_date), p(sales_qty) from v_sales function fn_pivot(sumafter,null) group by item_id;
item_idnull20191201201912312020120120201231201901012020010120200102
artA1515151515151515151515000000
artB171717171717171717171713131313130
#88888888888888880

select item_id, p(sales_date), p(sales_qty) from v_sales function fn_pivot(last,null) group by item_id;
item_idnull20191201201912312020120120201231201901012020010120200102
artA00000000000500000
artB00000000000400007
#00000000000000008

-- fn_build_filter+fn_pivot
-- 2 dimensions matrix per item_id/sales_date, the columns are limited to the first sales date of C2/artA
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.valstringify(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
-- fn_build_filter+fn_pivot+fn_filter_pivot
-- build 2 dimensions matrix per item_id/sales_date build only for the first sales date of C2/artA
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.valstringify(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,}

-- fn_build_filter+fn_pivot+fn_filter_pivot
-- build a 2 dimensions matrix per item_id/sales_date, then keep only the first sales date of the item for customer C2
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.valstringify(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)
4075.714

select p(item_id), p(sales_qty) from v_sales function fn_pivot(sum,null);
nullNO_DATAartAartBbox1artC
00008000000151700

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.valc.valnb_col_in_fn_pivot
402.66715

select customer_id,item_id, sum(sales_qty) from v_sales group by customer_id,item_id;
customer_iditem_idsum(sales_qty)
C1artA10
C2artB13
C1artB4
C1#8
C2artA5

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.valb.valc.val
C1221.467
C2181.200

-- fn_build_filter+fn_pivot+fn_filter_pivot+fn_unpivot
-- show total sales per item on the date where customer C2 bought it for the first time
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.vald.val
artA15
artB13

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 the_group_by_column
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';
reponse
success

--#SB log_verbose
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
)
;
my_column
40

select sum(sales_qty) from v_sales
;
sum(sales_qty)
40

-- ###########################
-- RUNNING shutdown.sql
shutdown;