methods/sketch/src/pg_gp/sql/cm.sql_in

---------------------------------------------------------------------------
-- Rules:
-- ------
-- 1) Any DB objects should be created w/o schema prefix,
--    since this file is executed in a separate schema context.
-- 2) There should be no DROP statements in this script, since
--    all objects created in the default schema will be cleaned-up outside.
---------------------------------------------------------------------------

---------------------------------------------------------------------------
-- Setup:
---------------------------------------------------------------------------
CREATE FUNCTION cm_install_test() RETURNS VOID AS $$
declare

	result INT[];
	result2 INT;

begin
	-- DROP TABLE IF EXISTS data;
	CREATE TABLE "CM_DATA"(class INT, a1 INT);
	INSERT INTO "CM_DATA" SELECT 1,1 FROM generate_series(1,10000);
	INSERT INTO "CM_DATA" SELECT 1,2 FROM generate_series(1,15000);
	INSERT INTO "CM_DATA" SELECT 1,3 FROM generate_series(1,10000);
	INSERT INTO "CM_DATA" SELECT 2,5 FROM generate_series(1,1000);
	INSERT INTO "CM_DATA" SELECT 2,6 FROM generate_series(1,1000);

	-- DROP TABLE IF EXISTS result_table;
	CREATE TABLE cm_result_table AS
	SELECT (MADLIB_SCHEMA.cmsketch_count(MADLIB_SCHEMA.cmsketch(a1),2)) as val FROM "CM_DATA" GROUP BY class ORDER BY class;

	SELECT array( SELECT val FROM cm_result_table) INTO result;
	IF ((result[1] + result[2]) != 15000) THEN
		RAISE EXCEPTION 'Incorrect cmsketch_count results, got %',result;
	END IF;
	TRUNCATE cm_result_table;

	INSERT INTO cm_result_table
	SELECT MADLIB_SCHEMA.cmsketch_rangecount(MADLIB_SCHEMA.cmsketch(a1),3,6) FROM "CM_DATA" GROUP BY class ORDER BY class;

	SELECT array( SELECT val FROM cm_result_table) INTO result;
	IF (result[1] + result[2] != 12000) THEN
		RAISE EXCEPTION 'Incorrect cmsketch_rangecount results, got %',result;
	END IF;
	TRUNCATE cm_result_table;

	SELECT MADLIB_SCHEMA.cmsketch_centile(MADLIB_SCHEMA.cmsketch(a1),90,count(*)) INTO result2 FROM "CM_DATA";
	IF result2 != 3 THEN
		RAISE EXCEPTION 'Incorrect cmsketch_centile results, got %',result2;
	END IF;

	PERFORM MADLIB_SCHEMA.cmsketch_width_histogram(MADLIB_SCHEMA.cmsketch(a1),0,10,2) FROM "CM_DATA";
	PERFORM MADLIB_SCHEMA.cmsketch_depth_histogram(MADLIB_SCHEMA.cmsketch(a1),2) FROM "CM_DATA";

-- 	SELECT class,fmsketch_dcount(a1) FROM cm_data GROUP BY class;
-- 	SELECT mfvsketch_top_histogram(a1,5) FROM cm_data;

	RAISE INFO 'CM-Sketches install checks passed';
	RETURN;

end
$$ language plpgsql;

---------------------------------------------------------------------------
-- Test:
---------------------------------------------------------------------------
SELECT cm_install_test();

-- Basic methods
select cmsketch_count(cmsketch(i),5) from generate_series(1,10000) as T(i);
select cmsketch_rangecount(cmsketch(i),1,1025) from generate_series(1,10000) as T(i);
select cmsketch_rangecount(cmsketch(i),1,200) from generate_series(1,10000) as R(i);
select cmsketch_width_histogram(cmsketch(i), min(i), max(i), 4) from generate_series(1,10000) as R(i);
select min(i),
       cmsketch_centile(cmsketch(i), 25, count(i)) AS quartile1,
       cmsketch_centile(cmsketch(i), 50, count(i)) AS quartile2,
       cmsketch_median(cmsketch(i), count(i)) AS median,
       cmsketch_centile(cmsketch(i), 75, count(i)) AS quartile3,
       max(i)
  from generate_series(1,10000) as R(i);
select cmsketch_depth_histogram(cmsketch(i), 4) from generate_series(1,10000) as R(i);

-- Test for all-NULL column
select cmsketch_count(cmsketch(NULL), 5) from generate_series(1,10000) as R(i) where i < 0;