-- ###########################
-- RUNNING stop_on_error.sql
stop_on_error;


reponse



success

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

reponse
success

create col_type t_dept_id as text;


reponse



success

reponse
success

create col_type t_item_id as text;


reponse



success

reponse
success

create col_type t_customer_id as text;


reponse



success

reponse
success

create col_type t_date as text;


reponse



success

reponse
success

create col_type t_customer_info as text;


reponse



success

reponse
success

create col_type end_user as text;


reponse



success

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

reponse
success

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


reponse



success

reponse
success

create table item_tags( item_id t_item_id, tag text);


reponse



success

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

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

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

reponse
success

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


reponse



success

reponse
success

create view v_items as select * from items;


reponse



success

reponse
success

create view v_item_tags as select * from item_tags;


reponse



success

reponse
success

create view v_fidelity_cards as select * from fidelity_cards;


reponse



success

reponse
success

create view v_item_customer_infos as select * from item_customer_infos;


reponse



success

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

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

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


reponse



success

reponse
success

refresh dirty view;


reponse



success

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_id count(customer_id) countdistinct(customer_id) sum(sales_qty)



artB 3 2 17
artA 3 2 15
# 1 1 8

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;


sum(sales_qty)



40

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



artA y
artB y

item_id	=3
artA	y
artB	y

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


item_id >=2



artA y
artB y

item_id	>=2
artA	y
artB	y

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


item_id <17.00



artA y
# y

item_id	<17.00
artA	y
#	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



artA y
artB y
# y

item_id	<=40.00
artA	y
artB	y
#	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

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..

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

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

--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_id 20191231 20200102



artA X _
artB X X

--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_id 20200102 20191231



artA _ X
artB X X

--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_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

--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_date sum(sales_qty) count(sales_qty)



artA 20191231 15 3

--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_date sum(sales_qty) count(sales_qty)



artB 20200102 13 2

--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.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

-- 1 dimension matrix per item_id
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

-- 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_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

-- 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_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

-- 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.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

-- 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.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,}

-- 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.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

-- 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.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 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;

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',}

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,}

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,}

< * | *** >

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

./_SO_CODE/Z_doc_fn_custom_step1.c