blob: 8187b2c288ab895fff0e2d008c81f7d1326cd80b (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
--
-- RANDOM
-- Test the random function
--
-- count the number of tuples originally, should be 1000
SELECT count(*) FROM onek;
-- pick three random rows, they shouldn't match
(SELECT unique1 AS random
FROM onek ORDER BY random() LIMIT 1)
INTERSECT
(SELECT unique1 AS random
FROM onek ORDER BY random() LIMIT 1)
INTERSECT
(SELECT unique1 AS random
FROM onek ORDER BY random() LIMIT 1);
-- count roughly 1/10 of the tuples
CREATE TABLE RANDOM_TBL AS
SELECT count(*) AS random
FROM onek WHERE random() < 1.0/10;
-- select again, the count should be different
INSERT INTO RANDOM_TBL (random)
SELECT count(*)
FROM onek WHERE random() < 1.0/10;
-- select again, the count should be different
INSERT INTO RANDOM_TBL (random)
SELECT count(*)
FROM onek WHERE random() < 1.0/10;
-- select again, the count should be different
INSERT INTO RANDOM_TBL (random)
SELECT count(*)
FROM onek WHERE random() < 1.0/10;
-- now test that they are different counts
SELECT random, count(random) FROM RANDOM_TBL
GROUP BY random HAVING count(random) > 3;
SELECT AVG(random) FROM RANDOM_TBL
HAVING AVG(random) NOT BETWEEN 80 AND 120;
|
