Sample Examples | Vantage Analytics Library - Sample Examples - Vantage Analytics Library

In the following examples, the call statement requires preceding the td_analyze stored procedure name with the database where it is installed. For example:

call val_user.td_analyze(

Call:

Call td_analyze('sample','
	database = val_source;
	tablename = customer;
	columns = cust_id,income,age;
	samplesizes = 10;
');

SQL:

SELECT
	 "cust_id"
	,"income"
	,"age"
FROM "val_source"." customer"
SAMPLE 10
;

Call:

call td_analyze('sample','
	database = val_source;
	tablename = customer;
	columns = cust_id,income,age;
	outputstyle = table;
	outputdatabase=val_results;
	outputtablename=_val_sample_table;
	index=cust_id;
	where = cust_id > 0;
	samplesizes = 1,5,10;
');
sel * from val_results._val_sample_table;

SQL:

DROP TABLE "val_results"."_val_sample_table";
 CREATE SET TABLE "val_results"."_val_sample_table", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS (
SELECT
	 "cust_id"
	,"income"
	,"age"
	,SAMPLEID AS "xSampleId"
FROM "val_source"."customer"
SAMPLE 1, 5, 10
WHERE cust_id > 0
) WITH DATA PRIMARY INDEX ("cust_id");

Call:

call td_analyze('sample','
	database = val_source;
	tablename = customer;
	columns = cust_id,income,age,gender;
	conditions = {gender = ''F''},{gender = ''M''};
	conditionalsizes = {1,2,3},{4,5,6};
');

SQL:

SELECT
	 "cust_id"
	,"income"
	,"age"
	,"gender"
	,CASE 	WHEN _val_sampleid IN (1 , 4 ) THEN 1
		WHEN _val_sampleid IN (2 , 5 ) THEN 2
		WHEN _val_sampleid IN (3 , 6 ) THEN 3
	 END AS "xSampleId"
FROM
(SELECT
	 "cust_id"
	,"income"
	,"age"
	,"gender"
	,SAMPLEID AS "_val_sampleid"
FROM "val_source"."customer"
SAMPLE 
	WHEN gender = 'F' THEN 1, 2, 3
	WHEN gender = 'M' THEN 4, 5, 6
END
) AS "_valSample"
;

Call:

call td_analyze('sample','
	database = val_source;
	tablename = customer;
	columns = cust_id,income,age,gender;
	conditions = {gender = ''F''},{gender = ''M''};
	conditionalsizes = {1,2,3},{4,5,6};
	separatesamplepersize=true;
');

SQL:

SELECT
	 "cust_id"
	,"income"
	,"age"
	,"gender"
	,SAMPLEID AS "xSampleId"
FROM "val_source"."customer"
SAMPLE 
	WHEN gender = 'F' THEN 1, 2, 3
	WHEN gender = 'M' THEN 4, 5, 6
END
;

Call:

call td_analyze('sample','
	database = val_source;
	tablename = customer;
	columns = cust_id,income,age;
	outputdatabase = val_results;
	outputtablename = _val_sample;
	samplesizes = 1,2,3;
	sampleoutputstyle = multipletables;
	outputnames = s1,s2,s3;
	');
sel * from val_results.s1;
sel * from val_results.s2;
sel * from val_results.s3;
sel * from val_user._val_sample;

SQL:

DROP TABLE "val_user"."_val_sample";
 CREATE SET VOLATILE TABLE "val_user"."_val_sample", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS (
SELECT
	 "cust_id"
	,"income"
	,"age"
	,SAMPLEID AS "xSampleId"
FROM "val_source"."customer"
SAMPLE 1, 2, 3
) WITH DATA ON COMMIT PRESERVE ROWS;

DROP TABLE "val_result"."s1";
 CREATE SET TABLE "val_results"."s1", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS (
SELECT
	 "cust_id"
	,"income"
	,"age"

FROM "val_user"."_val_sample"
WHERE "xSampleId" = 1
) WITH DATA;

DROP TABLE "val_results"."s2";
 CREATE SET TABLE "val_results"."s2", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS (
SELECT
	 "cust_id"
	,"income"
	,"age"

FROM "val_user"."_val_sample"
WHERE "xSampleId" = 2
) WITH DATA;

DROP TABLE "val_results"."s3";
 CREATE SET TABLE "val_results"."s3", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS (
SELECT
	 "cust_id"
	,"income"
	,"age"

FROM "val_user"."_val_sample"
WHERE "xSampleId" = 3
) WITH DATA;

Call:

Call td_analyze('sample','
	database = val_source;
	tablename = customer;
	columns = cust_id,income,age;
	outputdatabase = val_results;
	outputtablename = _val_base_table;
	samplesizes = 1,2,3;
	sampleoutputstyle = multipleviews;
	outputnames = v1,v2,v3;
	where = cust_id > 0;
	index = cust_id;
	');

sel * from val_results.v1;
sel * from val_results.v2;
sel * from val_results.v3;
sel * from val_results._val_base_table;

SQL:

DROP TABLE "val_results"."_val_base_table";
 CREATE SET TABLE "val_results"."_val_base_table", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS (
SELECT
	 "cust_id"
	,"income"
	,"age"
	,SAMPLEID AS "xSampleId"
FROM "val_source"."customer"
SAMPLE 1, 2, 3
WHERE cust_id > 0
) WITH DATA PRIMARY INDEX ("cust_id");

DROP VIEW "val_results"."v1";
 CREATE VIEW "val_results"."v1" AS (
SELECT
	 "cust_id"
	,"income"
	,"age"

FROM "val_results"."_val_base_table"
WHERE "xSampleId" = 1
);

DROP VIEW "val_results"."v2";
 CREATE VIEW "val_results"."v2" AS (
SELECT
	 "cust_id"
	,"income"
	,"age"

FROM "val_results"."_val_base_table"
WHERE "xSampleId" = 2
);

DROP VIEW "val_results"."v3";
 CREATE VIEW "val_results"."v3" AS (
SELECT
	 "cust_id"
	,"income"
	,"age"

FROM "val_results"."_val_base_table"
WHERE "xSampleId" = 3
);