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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
-- Setup the table
--
set extra_float_digits=0;
SELECT hll_set_output_version(1);
hll_set_output_version
------------------------
1
(1 row)
DROP TABLE IF EXISTS test_mpuahgwy;
NOTICE: table "test_mpuahgwy" does not exist, skipping
CREATE TABLE test_mpuahgwy (
recno SERIAL,
cardinality double precision,
compressed_multiset hll,
union_cardinality double precision,
union_compressed_multiset hll
);
-- Copy the CSV data into the table
--
\copy test_mpuahgwy (cardinality,compressed_multiset,union_cardinality,union_compressed_multiset) from sql/data/cumulative_union_probabilistic_probabilistic.csv with csv header
SELECT COUNT(*) FROM test_mpuahgwy;
count
-------
1001
(1 row)
-- Cardinality of incremental multisets
--
SELECT recno,
cardinality,
hll_cardinality(compressed_multiset)
FROM test_mpuahgwy
WHERE cardinality != hll_cardinality(compressed_multiset)
ORDER BY recno;
recno | cardinality | hll_cardinality
-------+------------------+------------------
303 | 4250.71186178904 | 4250.71186178904
352 | 4250.71186178904 | 4250.71186178904
945 | 4250.71186178904 | 4250.71186178904
(3 rows)
-- Cardinality of unioned multisets
--
SELECT recno,
union_cardinality,
hll_cardinality(union_compressed_multiset)
FROM test_mpuahgwy
WHERE union_cardinality != hll_cardinality(union_compressed_multiset)
ORDER BY recno;
recno | union_cardinality | hll_cardinality
-------+-------------------+-----------------
(0 rows)
-- Test union of incremental multiset.
--
SELECT curr.recno,
curr.union_compressed_multiset,
hll_union(curr.compressed_multiset, prev.union_compressed_multiset)
FROM test_mpuahgwy prev, test_mpuahgwy curr
WHERE curr.recno > 1
AND curr.recno = prev.recno + 1
AND curr.union_compressed_multiset !=
hll_union(curr.compressed_multiset, prev.union_compressed_multiset)
ORDER BY curr.recno;
recno | union_compressed_multiset | hll_union
-------+---------------------------+-----------
(0 rows)
-- Test cardinality of union of incremental multiset.
--
SELECT curr.recno,
curr.union_cardinality,
hll_cardinality(hll_union(curr.compressed_multiset,
prev.union_compressed_multiset))
FROM test_mpuahgwy prev, test_mpuahgwy curr
WHERE curr.recno > 1
AND curr.recno = prev.recno + 1
AND curr.union_cardinality !=
hll_cardinality(hll_union(curr.compressed_multiset,
prev.union_compressed_multiset))
ORDER BY curr.recno;
recno | union_cardinality | hll_cardinality
-------+-------------------+-----------------
(0 rows)
-- Test aggregate accumulation
--
SELECT v1.recno,
v1.union_compressed_multiset,
(select hll_union_agg(compressed_multiset)
from test_mpuahgwy
where recno <= v1.recno) as hll_union_agg
FROM test_mpuahgwy v1
WHERE v1.union_compressed_multiset !=
(select hll_union_agg(compressed_multiset)
from test_mpuahgwy
where recno <= v1.recno)
ORDER BY v1.recno;
recno | union_compressed_multiset | hll_union_agg
-------+---------------------------+---------------
(0 rows)
-- Test aggregate accumulation with cardinality
--
SELECT v1.recno,
ceil(v1.union_cardinality),
(select ceiling(hll_cardinality(hll_union_agg(compressed_multiset)))
from test_mpuahgwy
where recno <= v1.recno) as ceiling
FROM test_mpuahgwy v1
WHERE ceil(v1.union_cardinality) !=
(select ceiling(hll_cardinality(hll_union_agg(compressed_multiset)))
from test_mpuahgwy
where recno <= v1.recno)
ORDER BY v1.recno;
recno | ceil | ceiling
-------+------+---------
(0 rows)
DROP TABLE test_mpuahgwy;
|
