--Test
out some numbers to see the Central Limit Theorem in action.
--There
is some rounding and a random function is not always really
--random
but... it should be close enough to get the idea. Compare
--this
script with the CentralValues scripts. Change things up and
--run
different set sizes to see how things work.
--By
C. Eric Cashon
--Create
the tables first.
CREATE
TABLE Gaussian1( RowID INTEGER, Number1 INTEGER);
CREATE
TABLE Gaussian2( RowID INTEGER, Number1 INTEGER);
CREATE
TABLE PossibleValues(List INTEGER);
--
generate 50 records, Used later in a join to spread out the values
--and
to hopefully see some central tendency.
INSERT
INTO PossibleValues
WITH Generate(List) AS (VALUES(1) UNION ALL
SELECT List+1 FROM Generate WHERE List < 50 )
SELECT
List FROM Generate;
SELECT
* FROM PossibleValues;
--Create
a function to randomly generate values from 1 to 50.
--Create
the following function and procedure in Development
--Center.
Then the rest of the script can be tested in the
--Command
Editor with different combinations.
CREATE
FUNCTION RandomSamples()
RETURNS REAL
LANGUAGE SQL
BEGIN ATOMIC
DECLARE Test1 REAL;
SET Test1 = RAND() * 1000;
IF Test1 <= 20 THEN
SET Test1 = 1.0;
ELSEIF Test1 > 20 AND Test1 <= 40
THEN
SET Test1 = 2.0;
ELSEIF Test1 > 40 AND Test1 <= 60
THEN
SET Test1 = 3.0;
ELSEIF Test1 > 60 AND Test1 <= 80
THEN
SET Test1 = 4.0;
ELSEIF Test1 > 80 AND Test1 <= 100
THEN
SET Test1 = 5.0;
ELSEIF Test1 > 100 AND Test1 <=
120 THEN
SET Test1 = 6.0;
ELSEIF Test1 > 120 AND Test1 <=
140 THEN
SET Test1 = 7.0;
ELSEIF Test1 > 140 AND Test1 <=
160 THEN
SET Test1 = 8.0;
ELSEIF Test1 > 160 AND Test1 <=
180 THEN
SET Test1 = 9.0;
ELSEIF Test1 > 180 AND Test1 <=
200 THEN
SET Test1 = 10.0;
ELSEIF Test1 > 200 AND Test1 <=
220 THEN
SET Test1 = 11.0;
ELSEIF Test1 > 220 AND Test1 <=
240 THEN
SET Test1 = 12.0;
ELSEIF Test1 > 240 AND Test1 <=
260 THEN
SET Test1 = 13.0;
ELSEIF Test1 > 260 AND Test1 <=
280 THEN
SET Test1 = 14.0;
ELSEIF Test1 > 280 AND Test1 <=
300 THEN
SET Test1 = 15.0;
ELSEIF Test1 > 300 AND Test1 <=
320 THEN
SET Test1 = 16.0;
ELSEIF Test1 > 320 AND Test1 <=
340 THEN
SET Test1 = 17.0;
ELSEIF Test1 > 340 AND Test1 <=
360 THEN
SET Test1 = 18.0;
ELSEIF Test1 > 360 AND Test1 <=
380 THEN
SET Test1 = 19.0;
ELSEIF Test1 > 380 AND Test1 <=
400 THEN
SET Test1 = 20.0;
ELSEIF Test1 > 400 AND Test1 <=
420 THEN
SET Test1 = 21.0;
ELSEIF Test1 > 420 AND Test1 <= 440 THEN
SET Test1 = 22.0;
ELSEIF Test1 > 440 AND Test1 <=
460 THEN
SET Test1 = 23.0;
ELSEIF Test1 > 460 AND Test1 <=
480 THEN
SET Test1 = 24.0;
ELSEIF Test1 > 480 AND Test1 <=
500 THEN
SET Test1 = 25.0;
ELSEIF Test1 > 500 AND Test1 <=
520 THEN
SET Test1 = 26.0;
ELSEIF Test1 > 520 AND Test1 <=
540 THEN
SET Test1 = 27.0;
ELSEIF Test1 > 540 AND Test1 <=
560 THEN
SET Test1 = 28.0;
ELSEIF Test1 > 560 AND Test1 <=
580 THEN
SET Test1 = 29.0;
ELSEIF Test1 > 580 AND Test1 <=
600 THEN
SET Test1 = 30.0;
ELSEIF Test1 > 600 AND Test1 <=
620 THEN
SET Test1 = 31.0;
ELSEIF Test1 > 620 AND Test1 <=
640 THEN
SET Test1 = 32.0;
ELSEIF Test1 > 640 AND Test1 <=
660 THEN
SET Test1 = 33.0;
ELSEIF Test1 > 660 AND Test1 <=
680 THEN
SET Test1 = 34.0;
ELSEIF Test1 > 680 AND Test1 <=
700 THEN
SET Test1 = 35.0;
ELSEIF Test1 > 700 AND Test1 <=
720 THEN
SET Test1 = 36.0;
ELSEIF Test1 > 720 AND Test1 <=
740 THEN
SET Test1 = 37.0;
ELSEIF Test1 > 740 AND Test1 <=
760 THEN
SET Test1 = 38.0;
ELSEIF Test1 > 760 AND Test1 <=
780 THEN
SET Test1 = 39.0;
ELSEIF Test1 > 780 AND Test1 <=
800 THEN
SET Test1 = 40.0;
ELSEIF Test1 > 800 AND Test1 <=
820 THEN
SET Test1 = 41.0;
ELSEIF Test1 > 820 AND Test1 <=
840 THEN
SET Test1 = 42.0;
ELSEIF Test1 > 840 AND Test1 <=
860 THEN
SET Test1 = 43.0;
ELSEIF Test1 > 860 AND Test1 <=
880 THEN
SET Test1 = 44.0;
ELSEIF Test1 > 880 AND Test1 <=
900 THEN
SET Test1 = 45.0;
ELSEIF Test1 > 900 AND Test1 <=
920 THEN
SET Test1 = 46.0;
ELSEIF Test1 > 920 AND Test1 <=
940 THEN
SET Test1 = 47.0;
ELSEIF Test1 > 940 AND Test1 <=
960 THEN
SET Test1 = 48.0;
ELSEIF Test1 > 960 AND Test1 <= 980 THEN
SET Test1 = 49.0;
ELSEIF Test1 > 980 AND Test1 <=
1000 THEN
SET Test1 = 50.0;
ELSE
SET Test1 = 0.0;
END IF;
RETURN Test1;
END
--Create
a procedure to fill your tables with random values.
CREATE
PROCEDURE PopulateTable(IN p_SetSize INTEGER,
IN
p_NumberOfSets INTEGER,
IN p_TableName
VARCHAR(20))
LANGUAGE SQL
BEGIN
DECLARE v_Counter1 INTEGER;
DECLARE v_Counter2 INTEGER;
DECLARE v_Counter3 INTEGER;
SET v_Counter1 = 1;
SET v_Counter2 = 1;
SET v_Counter3 = 1;
WHILE v_Counter1 <= p_NumberOfSets DO
WHILE v_Counter2 <= p_SetSize DO
IF p_TableName = 'Gaussian1' THEN
INSERT INTO Gaussian1(RowID,
Number1) Values (v_Counter3, RandomSamples());
ELSE
INSERT INTO Gaussian2(RowID,
Number1) Values (v_Counter3, RandomSamples());
END IF;
SET v_Counter2 = v_Counter2 + 1;
END WHILE;
SET v_Counter1 = v_Counter1 + 1;
SET v_Counter2 = 1;
SET v_Counter3 = v_Counter3 + 1;
END WHILE;
END
--Load
the tables with values.
CALL
PopulateTable(10, 60, 'Gaussian1');
CALL
PopulateTable(2, 60, 'Gaussian2');
--Create
a Histogram for each set. The groups
--of
10 should have greater central tendency
--than
the groups of 2.
CREATE
VIEW AverageSets AS
SELECT
SUM(Number1/10) AS Average
FROM Gaussian1
GROUP BY RowID;
CREATE
VIEW Histo1 AS
SELECT
AVG(Average) AS Link, REPEAT('*', COUNT(Average)) AS Histogram
FROM AverageSets
GROUP BY Average;
SELECT
List, Link, Histogram
FROM PossibleValues LEFT OUTER JOIN Histo1
ON List = Link
ORDER BY List;
--Create
a histogram for the second set
CREATE
VIEW AverageSets2 AS
SELECT
SUM(Number1/2) AS Average
FROM Gaussian2
GROUP BY RowID;
CREATE
VIEW Histo2 AS
SELECT
AVG(Average) AS Link, REPEAT('*', COUNT(Average)) AS Histogram
FROM AverageSets2
GROUP BY Average;
SELECT
List, Link, Histogram
FROM PossibleValues LEFT OUTER JOIN Histo2
ON List = Link
ORDER BY List;
--Clean
Up
DROP TABLE Gaussian1;
DROP TABLE Gaussian2;
DROP TABLE PossibleValues;
DROP VIEW Histo1;
DROP VIEW Histo2;
DROP VIEW AverageSets1;
DROP VIEW AverageSets2;
--DROP PROCEDURE PopulateTable;
--DROP FUNCTION RandomSamples;