Array of Doubles functions — Apache DataSketches DuckDB Extension

DuckDB Extensions All extensions Apache DataSketches

Function category

Array of Doubles

23 functions

Array-of-doubles Tuple sketch — like the Tuple sketch, but every key carries a fixed-length array of DOUBLE values (1–255) summed element-wise, so one sketch tracks several metrics per distinct key.

datasketch_aod

Aggregate function Array of Doubles

Signature

4 overloaded forms · click to inspect

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Arguments (Positional)

Argument	Type	Mode	Description
Argument `lg_k`	Type `INTEGER`	Mode Positional	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Arguments (Positional)

Argument	Type	Mode	Description
Argument `key`	Type `UINTEGER \| INTEGER \| UTINYINT \| …` 11 concrete types `BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINTVARCHAR`	Mode Positional	Description
Argument `values`	Type `DOUBLE[]`	Mode Positional	Description
Argument `seed`	Type `BIGINT`	Mode Positional	Description
Argument `p`	Type `DOUBLE`	Mode Positional	Description

Arguments (Positional)

Argument	Type	Mode	Description
Argument `lg_k`	Type `INTEGER`	Mode Positional	Description
Argument `key`	Type `SMALLINT \| USMALLINT \| UTINYINT \| …` 11 concrete types `BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINTVARCHAR`	Mode Positional	Description
Argument `values`	Type `DOUBLE[]`	Mode Positional	Description
Argument `seed`	Type `BIGINT`	Mode Positional	Description
Argument `p`	Type `DOUBLE`	Mode Positional	Description

Description

Aggregate (key, values DOUBLE[]) rows into an array-of-doubles Tuple sketch (sketch_aod): a Theta sketch of the distinct keys where each retained key carries a fixed-length array of DOUBLE values (1–255 values, the same length on every row, inferred from the data and stored in the sketch), summed element-wise per key. The optional leading lg_k (4–26, default 12) and seed := / p := work as for datasketch_theta. Passing a sketch_aod merges sketches. Combining sketches with different numbers of values or different seeds is an error.

1 Distinct customers with per-customer revenue and item totals

SELECT datasketch_aod_estimate(s)     AS customers,
       datasketch_aod_column_sums(s)  AS totals,
       datasketch_aod_column_means(s) AS per_customer
FROM (SELECT datasketch_aod(customer_id, [amount, items]) AS s FROM orders);

Related functions

datasketch_aod_a_not_b

Scalar function Array of Doubles

Signature

datasketch_aod_a_not_b(sketch_a: sketch_aod, sketch_b: sketch_aod) → sketch_aod

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch_a`	Type `sketch_aod`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_aod`	Mode Positional	Description

Description

Returns a new array-of-doubles sketch with the keys (and their summaries) of sketch A that are not in sketch B (set difference)

1 Example 1

SELECT datasketch_aod_a_not_b(sketch_a, sketch_b) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_b FROM range(500, 1500) t(i)) b;

Output

datasketch_aod_a_not_b(sketch_a, sketch_b)
0101090314020000ffffffffffffff7f

Related functions

datasketch_aod_column_means

Scalar function Array of Doubles

Signature

datasketch_aod_column_means(sketch: sketch_aod) → DOUBLE[]

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns the mean of each value column over the retained keys (the mean per distinct key), or NULL if the sketch retains no entries

1 Example 1

SELECT datasketch_aod_column_means(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_column_means(sketch)
[10.0, 2497.5]

Related functions

datasketch_aod_column_sums

Scalar function Array of Doubles

Signature

datasketch_aod_column_sums(sketch: sketch_aod) → DOUBLE[]

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns the estimated total of each value column over all distinct keys: the column sums of the retained entries divided by theta (exact when the sketch is not in estimation mode)

1 Example 1

SELECT datasketch_aod_column_sums(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_column_sums(sketch)
[1000.0, 249750.0]

Related functions

datasketch_aod_describe

Scalar function Array of Doubles

Signature

datasketch_aod_describe(sketch: sketch_aod) → VARCHAR

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns a human-readable description of the array-of-doubles sketch

1 Example 1

SELECT datasketch_aod_describe(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_describe(sketch)
### Tuple sketch summary: num retained entries : 100 seed hash : 37836 empty? : false ordered? : true estimation mode? : false theta (fraction) : 1 theta (raw 64-bit) : 9223372036854775807 estimate : 100 lower bound 95% conf : 100 upper bound 95% conf : 100 ### End sketch summary

datasketch_aod_describe(sketch)

### Tuple sketch summary:
   num retained entries : 100
   seed hash            : 37836
   empty?               : false
   ordered?             : true
   estimation mode?     : false
   theta (fraction)     : 1
   theta (raw 64-bit)   : 9223372036854775807
   estimate             : 100
   lower bound 95% conf : 100
   upper bound 95% conf : 100
### End sketch summary

Related functions

datasketch_aod() — Aggregate `(key, values DOUBLE[])` rows into an array-of-doubles Tuple sketch (`sketch_aod`): a Theta sketch of the distinct keys where each retained key carries a fixed-length array of DOUBLE values (1–255 values, the same length on every row, inferred from the data and stored in the sketch), summed element-wise per key

datasketch_aod_dissimilarity_test

Scalar function Array of Doubles

Signature

datasketch_aod_dissimilarity_test( actual: sketch_aod, expected: sketch_aod, threshold: DOUBLE ) → BOOLEAN

Arguments (Positional)

Argument	Type	Mode	Description
Argument `actual`	Type `sketch_aod`	Mode Positional	Description
Argument `expected`	Type `sketch_aod`	Mode Positional	Description
Argument `threshold`	Type `DOUBLE`	Mode Positional	Description

Description

Returns true if the Jaccard similarity of the key sets is at most the threshold, with high confidence (its upper bound is <= threshold)

1 Example 1

SELECT datasketch_aod_dissimilarity_test(actual, expected, 0.1) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS actual FROM range(0, 1000) t(i)) a, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS expected FROM range(100, 1100) t(i)) e;

Output

datasketch_aod_dissimilarity_test(actual, expected, 0.1)
false

Related functions

datasketch_aod_estimate

Scalar function Array of Doubles

Signature

datasketch_aod_estimate(sketch: sketch_aod) → DOUBLE

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns the estimated number of distinct keys in the array-of-doubles sketch

1 Example 1

SELECT datasketch_aod_estimate(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_estimate(sketch)
100.0

Related functions

datasketch_aod_exactly_equal

Scalar function Array of Doubles

Signature

datasketch_aod_exactly_equal(sketch_a: sketch_aod, sketch_b: sketch_aod) → BOOLEAN

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch_a`	Type `sketch_aod`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_aod`	Mode Positional	Description

Description

Returns true if the two sketches retain exactly the same keys with the same theta (summaries are not compared)

1 Example 1

SELECT datasketch_aod_exactly_equal(sketch_a, sketch_b) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_b FROM range(500, 1500) t(i)) b;

Output

datasketch_aod_exactly_equal(sketch_a, sketch_b)
true

Related functions

datasketch_aod_jaccard() — Returns the Jaccard similarity of the key sets of two sketches as a struct of its estimate and approximate lower and upper bounds (about 2 standard deviations)

datasketch_aod_get_theta

Scalar function Array of Doubles

Signature

datasketch_aod_get_theta(sketch: sketch_aod) → DOUBLE

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns the theta value of the array-of-doubles sketch (the fraction of the key hash space it retains)

1 Example 1

SELECT datasketch_aod_get_theta(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_get_theta(sketch)
1.0

Related functions

datasketch_aod() — Aggregate `(key, values DOUBLE[])` rows into an array-of-doubles Tuple sketch (`sketch_aod`): a Theta sketch of the distinct keys where each retained key carries a fixed-length array of DOUBLE values (1–255 values, the same length on every row, inferred from the data and stored in the sketch), summed element-wise per key

datasketch_aod_intersect

Scalar function Array of Doubles

Signature

datasketch_aod_intersect( sketch_a: sketch_aod, sketch_b: sketch_aod, varargs: sketch_aod ) → sketch_aod

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch_a`	Type `sketch_aod`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_aod`	Mode Positional	Description
Argument `varargs`	Type `sketch_aod`	Mode Positional	Description Varargs

Description

Returns the intersection of two or more array-of-doubles sketches: the keys present in every sketch, with their values summed element-wise

1 Example 1

SELECT datasketch_aod_intersect(sketch_a, sketch_b) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_b FROM range(500, 1500) t(i)) b;

2 Example 2

SELECT datasketch_aod_intersect(sketch_a, sketch_b, sketch_c) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_b FROM range(500, 1500) t(i)) b, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_c FROM range(250, 1250) t(i)) c;

Related functions

datasketch_aod_intersect_agg

Aggregate function Array of Doubles

Signature

datasketch_aod_intersect_agg(sketch: sketch_aod) → sketch_aod

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Intersects array-of-doubles sketches across rows: keeps the keys present in every sketch, summing their values element-wise. NULL sketches are skipped; returns NULL if there are no sketches

1 Example 1

SELECT datasketch_aod_intersect_agg(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i) UNION ALL SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) FROM range(500, 1500) t(i)) s;

Related functions

datasketch_aod_intersect() — Returns the intersection of two or more array-of-doubles sketches: the keys present in every sketch, with their values summed element-wise

datasketch_aod_is_empty

Scalar function Array of Doubles

Signature

datasketch_aod_is_empty(sketch: sketch_aod) → BOOLEAN

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns true if the array-of-doubles sketch is empty

1 Example 1

SELECT datasketch_aod_is_empty(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_is_empty(sketch)
false

Related functions

datasketch_aod() — Aggregate `(key, values DOUBLE[])` rows into an array-of-doubles Tuple sketch (`sketch_aod`): a Theta sketch of the distinct keys where each retained key carries a fixed-length array of DOUBLE values (1–255 values, the same length on every row, inferred from the data and stored in the sketch), summed element-wise per key

datasketch_aod_is_estimation_mode

Scalar function Array of Doubles

Signature

datasketch_aod_is_estimation_mode(sketch: sketch_aod) → BOOLEAN

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns true if the array-of-doubles sketch is in estimation mode (it has sampled the keys)

1 Example 1

SELECT datasketch_aod_is_estimation_mode(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_is_estimation_mode(sketch)
false

Related functions

datasketch_aod() — Aggregate `(key, values DOUBLE[])` rows into an array-of-doubles Tuple sketch (`sketch_aod`): a Theta sketch of the distinct keys where each retained key carries a fixed-length array of DOUBLE values (1–255 values, the same length on every row, inferred from the data and stored in the sketch), summed element-wise per key

datasketch_aod_jaccard

Scalar function Array of Doubles

Signature

datasketch_aod_jaccard(sketch_a: sketch_aod, sketch_b: sketch_aod) → STRUCT(lower DOUBLE, estimate DOUBLE, upper DOUBLE)

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch_a`	Type `sketch_aod`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_aod`	Mode Positional	Description

Description

Returns the Jaccard similarity of the key sets of two sketches as a struct of its estimate and approximate lower and upper bounds (about 2 standard deviations). Two empty sketches are identical (1.0)

1 Example 1

SELECT datasketch_aod_jaccard(sketch_a, sketch_b) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_b FROM range(500, 1500) t(i)) b;

Output

datasketch_aod_jaccard(sketch_a, sketch_b)
{'lower': 1.0, 'estimate': 1.0, 'upper': 1.0}

2 Example 2

SELECT datasketch_aod_jaccard(sketch_a, sketch_b).estimate FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_b FROM range(500, 1500) t(i)) b;

Output

(datasketch_aod_jaccard(sketch_a, sketch_b)).estimate
1.0

Related functions

datasketch_aod_lower_bound

Scalar function Array of Doubles

Signature

datasketch_aod_lower_bound(sketch: sketch_aod, num_std_devs: INTEGER) → DOUBLE

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description
Argument `num_std_devs`	Type `INTEGER`	Mode Positional	Description

Description

Returns the lower bound of the estimated number of distinct keys at the given number of standard deviations (1, 2, or 3)

1 Example 1

SELECT datasketch_aod_lower_bound(sketch, 2) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_lower_bound(sketch, 2)
100.0

Related functions

datasketch_aod_num_retained

Scalar function Array of Doubles

Signature

datasketch_aod_num_retained(sketch: sketch_aod) → BIGINT

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns the number of entries (keys with their summaries) retained in the array-of-doubles sketch

1 Example 1

SELECT datasketch_aod_num_retained(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_num_retained(sketch)
100

Related functions

datasketch_aod() — Aggregate `(key, values DOUBLE[])` rows into an array-of-doubles Tuple sketch (`sketch_aod`): a Theta sketch of the distinct keys where each retained key carries a fixed-length array of DOUBLE values (1–255 values, the same length on every row, inferred from the data and stored in the sketch), summed element-wise per key

datasketch_aod_num_values

Scalar function Array of Doubles

Signature

datasketch_aod_num_values(sketch: sketch_aod) → BIGINT

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns the number of values per key of the array-of-doubles sketch

1 Example 1

SELECT datasketch_aod_num_values(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_num_values(sketch)
2

Related functions

datasketch_aod() — Aggregate `(key, values DOUBLE[])` rows into an array-of-doubles Tuple sketch (`sketch_aod`): a Theta sketch of the distinct keys where each retained key carries a fixed-length array of DOUBLE values (1–255 values, the same length on every row, inferred from the data and stored in the sketch), summed element-wise per key

datasketch_aod_seed_hash

Scalar function Array of Doubles

Signature

datasketch_aod_seed_hash(sketch: sketch_aod) → BIGINT

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns the 16-bit hash of the seed the array-of-doubles sketch was built with; sketches can only be combined if their seed hashes match

1 Example 1

SELECT datasketch_aod_seed_hash(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_seed_hash(sketch)
37836

Related functions

datasketch_aod() — Aggregate `(key, values DOUBLE[])` rows into an array-of-doubles Tuple sketch (`sketch_aod`): a Theta sketch of the distinct keys where each retained key carries a fixed-length array of DOUBLE values (1–255 values, the same length on every row, inferred from the data and stored in the sketch), summed element-wise per key

datasketch_aod_similarity_test

Scalar function Array of Doubles

Signature

datasketch_aod_similarity_test( actual: sketch_aod, expected: sketch_aod, threshold: DOUBLE ) → BOOLEAN

Arguments (Positional)

Argument	Type	Mode	Description
Argument `actual`	Type `sketch_aod`	Mode Positional	Description
Argument `expected`	Type `sketch_aod`	Mode Positional	Description
Argument `threshold`	Type `DOUBLE`	Mode Positional	Description

Description

Returns true if the Jaccard similarity of the key sets is at least the threshold, with high confidence (its lower bound is >= threshold)

1 Example 1

SELECT datasketch_aod_similarity_test(actual, expected, 0.9) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS actual FROM range(0, 1000) t(i)) a, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS expected FROM range(100, 1100) t(i)) e;

Output

datasketch_aod_similarity_test(actual, expected, 0.9)
true

Related functions

datasketch_aod_summaries

Scalar function Array of Doubles

Signature

datasketch_aod_summaries(sketch: sketch_aod) → STRUCT(hash UBIGINT, "values" DOUBLE[])[]

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Returns the retained entries of the array-of-doubles sketch as a list of {hash, values} structs (the key hash and its values)

1 Example 1

SELECT datasketch_aod_summaries(sketch) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_summaries(sketch)
[{'hash': 98946274238865930, 'values': [10.0, 2615.0]}, {'hash': 112348182036953339, 'values': [10.0, 2305.0]}, {'hash': 266144333882809615, 'values': [10.0, 2715.0]}, {'hash': 325961210337798120, 'values': [10.0, 2535.0]}, {'hash': 341968790597130978, 'values': [10.0, 2355.0]}, {'hash': 362181683233754093, 'values': [10.0, 2525.0]}, {'hash': 405753591161026837, 'values': [10.0, 2255.0]}, {'hash': 417004644657123063, 'values': [10.0, 2565.0]}, {'hash': 427008693142989162, 'values': [10.0, 2515.0]}, {'hash': 569192067085513059, 'values': [10.0, 2495.0]}, {'hash': 593872385995628096, 'values': [10.0, 2270.0]}, {'hash': 601064217205377897, 'values': [10.0, 2290.0]}, {'hash': 812940711502225074, 'values': [10.0, 2550.0]}, {'hash': 824017179179306593, 'values': [10.0, 2500.0]}, {'hash': 879908469782966194, 'values': [10.0, 2710.0]}, {'hash': 972597172966177155, 'values': [10.0, 2745.0]}, {'hash': 1093502844168540522, 'values': [10.0, 2600.0]}, {'hash': 1124757437863516995, 'values': [10.0, 2655.0]}, {'hash': 1206007004353599230, 'values': [10.0, 2280.0]}, {'hash': 1251274992440322927, 'values': [10.0, 2730.0]}, {'hash': 1277456548225195409, 'values': [10.0, 2380.0]}, {'hash': 1377882186530105587, 'values': [10.0, 2520.0]}, {'hash': 1498732507761423037, 'values': [10.0, 2275.0]}, {'hash': 1558021066182427594, 'values': [10.0, 2440.0]}, {'hash': 1861786089180386393, 'values': [10.0, 2540.0]}, {'hash': 1863747701890426060, 'values': [10.0, 2705.0]}, {'hash': 2002374527476343707, 'values': [10.0, 2360.0]}, {'hash': 2050455455346564949, 'values': [10.0, 2545.0]}, {'hash': 2066603284366225999, 'values': [10.0, 2365.0]}, {'hash': 2129030137640795257, 'values': [10.0, 2620.0]}, {'hash': 2189143365047986541, 'values': [10.0, 2325.0]}, {'hash': 2206043092153046979, 'values': [10.0, 2260.0]}, {'hash': 2266714801530709283, 'values': [10.0, 2310.0]}, {'hash': 2325124908111195109, 'values': [10.0, 2250.0]}, {'hash': 2357193980256238142, 'values': [10.0, 2320.0]}, {'hash': 2384647691295803423, 'values': [10.0, 2485.0]}, {'hash': 2410093545811538614, 'values': [10.0, 2430.0]}, {'hash': 2413646022939387078, 'values': [10.0, 2555.0]}, {'hash': 2455581497211901808, 'values': [10.0, 2665.0]}, {'hash': 2463342110140584218, 'values': [10.0, 2295.0]}, {'hash': 2514330683478745141, 'values': [10.0, 2660.0]}, {'hash': 2549264307540610026, 'values': [10.0, 2575.0]}, {'hash': 2714062032037713655, 'values': [10.0, 2375.0]}, {'hash': 2851584022655110423, 'values': [10.0, 2610.0]}, {'hash': 2899408821718900171, 'values': [10.0, 2400.0]}, {'hash': 2969606663411317205, 'values': [10.0, 2420.0]}, {'hash': 3006543970770323890, 'values': [10.0, 2580.0]}, {'hash': 3008285774336268528, 'values': [10.0, 2605.0]}, {'hash': 3013536481635266031, 'values': [10.0, 2630.0]}, {'hash': 3141028696583807384, 'values': [10.0, 2475.0]}, {'hash': 3227693670343832694, 'values': [10.0, 2595.0]}, {'hash': 3288091941603431578, 'values': [10.0, 2390.0]}, {'hash': 3336050303298949223, 'values': [10.0, 2415.0]}, {'hash': 3510295896655991449, 'values': [10.0, 2445.0]}, {'hash': 3552011096661462239, 'values': [10.0, 2685.0]}, {'hash': 3572065067015922122, 'values': [10.0, 2670.0]}, {'hash': 3757905022948533469, 'values': [10.0, 2695.0]}, {'hash': 3843018174740357702, 'values': [10.0, 2405.0]}, {'hash': 4192119294258525341, 'values': [10.0, 2645.0]}, {'hash': 4221915398188310103, 'values': [10.0, 2315.0]}, {'hash': 4259706344156050626, 'values': [10.0, 2425.0]}, {'hash': 4340457316873839382, 'values': [10.0, 2490.0]}, {'hash': 4356676064183467115, 'values': [10.0, 2470.0]}, {'hash': 4429638861388557148, 'values': [10.0, 2370.0]}, {'hash': 4477382300635797467, 'values': [10.0, 2435.0]}, {'hash': 4578992559693363303, 'values': [10.0, 2335.0]}, {'hash': 4579472350053230007, 'values': [10.0, 2720.0]}, {'hash': 4586427849190292816, 'values': [10.0, 2480.0]}, {'hash': 4734825758749082701, 'values': [10.0, 2675.0]}, {'hash': 4811922168148294097, 'values': [10.0, 2570.0]}, {'hash': 4957856306060390113, 'values': [10.0, 2345.0]}, {'hash': 5019462529220513108, 'values': [10.0, 2725.0]}, {'hash': 5206189584322944525, 'values': [10.0, 2460.0]}, {'hash': 5209267895868744375, 'values': [10.0, 2330.0]}, {'hash': 5277048382241783140, 'values': [10.0, 2350.0]}, {'hash': 5452684004115829364, 'values': [10.0, 2640.0]}, {'hash': 5685878910210846062, 'values': [10.0, 2735.0]}, {'hash': 5687495238192794851, 'values': [10.0, 2385.0]}, {'hash': 5785933929594249671, 'values': [10.0, 2465.0]}, {'hash': 5875894225988394840, 'values': [10.0, 2530.0]}, {'hash': 6134377025443110356, 'values': [10.0, 2625.0]}, {'hash': 6326905904218654186, 'values': [10.0, 2700.0]}, {'hash': 6338256001173664528, 'values': [10.0, 2680.0]}, {'hash': 6730918654704304314, 'values': [10.0, 2265.0]}, {'hash': 6892035777578313306, 'values': [10.0, 2635.0]}, {'hash': 7480992971775130706, 'values': [10.0, 2690.0]}, {'hash': 7552228461607989126, 'values': [10.0, 2740.0]}, {'hash': 7640953883025164414, 'values': [10.0, 2590.0]}, {'hash': 7951043072459728883, 'values': [10.0, 2560.0]}, {'hash': 8009730916123189212, 'values': [10.0, 2340.0]}, {'hash': 8055291883392730446, 'values': [10.0, 2650.0]}, {'hash': 8161144572732903681, 'values': [10.0, 2455.0]}, {'hash': 8184602267473193545, 'values': [10.0, 2450.0]}, {'hash': 8218596976656455375, 'values': [10.0, 2410.0]}, {'hash': 8282051833711382510, 'values': [10.0, 2585.0]}, {'hash': 8865927617827192748, 'values': [10.0, 2395.0]}, {'hash': 8906405763079324994, 'values': [10.0, 2510.0]}, {'hash': 8989629655658253488, 'values': [10.0, 2505.0]}, {'hash': 8990173978249917664, 'values': [10.0, 2285.0]}, {'hash': 9094378756317064664, 'values': [10.0, 2300.0]}]

datasketch_aod_summaries(sketch)

[{'hash': 98946274238865930, 'values': [10.0, 2615.0]}, {'hash': 112348182036953339, 'values': [10.0, 2305.0]}, {'hash': 266144333882809615, 'values': [10.0, 2715.0]}, {'hash': 325961210337798120, 'values': [10.0, 2535.0]}, {'hash': 341968790597130978, 'values': [10.0, 2355.0]}, {'hash': 362181683233754093, 'values': [10.0, 2525.0]}, {'hash': 405753591161026837, 'values': [10.0, 2255.0]}, {'hash': 417004644657123063, 'values': [10.0, 2565.0]}, {'hash': 427008693142989162, 'values': [10.0, 2515.0]}, {'hash': 569192067085513059, 'values': [10.0, 2495.0]}, {'hash': 593872385995628096, 'values': [10.0, 2270.0]}, {'hash': 601064217205377897, 'values': [10.0, 2290.0]}, {'hash': 812940711502225074, 'values': [10.0, 2550.0]}, {'hash': 824017179179306593, 'values': [10.0, 2500.0]}, {'hash': 879908469782966194, 'values': [10.0, 2710.0]}, {'hash': 972597172966177155, 'values': [10.0, 2745.0]}, {'hash': 1093502844168540522, 'values': [10.0, 2600.0]}, {'hash': 1124757437863516995, 'values': [10.0, 2655.0]}, {'hash': 1206007004353599230, 'values': [10.0, 2280.0]}, {'hash': 1251274992440322927, 'values': [10.0, 2730.0]}, {'hash': 1277456548225195409, 'values': [10.0, 2380.0]}, {'hash': 1377882186530105587, 'values': [10.0, 2520.0]}, {'hash': 1498732507761423037, 'values': [10.0, 2275.0]}, {'hash': 1558021066182427594, 'values': [10.0, 2440.0]}, {'hash': 1861786089180386393, 'values': [10.0, 2540.0]}, {'hash': 1863747701890426060, 'values': [10.0, 2705.0]}, {'hash': 2002374527476343707, 'values': [10.0, 2360.0]}, {'hash': 2050455455346564949, 'values': [10.0, 2545.0]}, {'hash': 2066603284366225999, 'values': [10.0, 2365.0]}, {'hash': 2129030137640795257, 'values': [10.0, 2620.0]}, {'hash': 2189143365047986541, 'values': [10.0, 2325.0]}, {'hash': 2206043092153046979, 'values': [10.0, 2260.0]}, {'hash': 2266714801530709283, 'values': [10.0, 2310.0]}, {'hash': 2325124908111195109, 'values': [10.0, 2250.0]}, {'hash': 2357193980256238142, 'values': [10.0, 2320.0]}, {'hash': 2384647691295803423, 'values': [10.0, 2485.0]}, {'hash': 2410093545811538614, 'values': [10.0, 2430.0]}, {'hash': 2413646022939387078, 'values': [10.0, 2555.0]}, {'hash': 2455581497211901808, 'values': [10.0, 2665.0]}, {'hash': 2463342110140584218, 'values': [10.0, 2295.0]}, {'hash': 2514330683478745141, 'values': [10.0, 2660.0]}, {'hash': 2549264307540610026, 'values': [10.0, 2575.0]}, {'hash': 2714062032037713655, 'values': [10.0, 2375.0]}, {'hash': 2851584022655110423, 'values': [10.0, 2610.0]}, {'hash': 2899408821718900171, 'values': [10.0, 2400.0]}, {'hash': 2969606663411317205, 'values': [10.0, 2420.0]}, {'hash': 3006543970770323890, 'values': [10.0, 2580.0]}, {'hash': 3008285774336268528, 'values': [10.0, 2605.0]}, {'hash': 3013536481635266031, 'values': [10.0, 2630.0]}, {'hash': 3141028696583807384, 'values': [10.0, 2475.0]}, {'hash': 3227693670343832694, 'values': [10.0, 2595.0]}, {'hash': 3288091941603431578, 'values': [10.0, 2390.0]}, {'hash': 3336050303298949223, 'values': [10.0, 2415.0]}, {'hash': 3510295896655991449, 'values': [10.0, 2445.0]}, {'hash': 3552011096661462239, 'values': [10.0, 2685.0]}, {'hash': 3572065067015922122, 'values': [10.0, 2670.0]}, {'hash': 3757905022948533469, 'values': [10.0, 2695.0]}, {'hash': 3843018174740357702, 'values': [10.0, 2405.0]}, {'hash': 4192119294258525341, 'values': [10.0, 2645.0]}, {'hash': 4221915398188310103, 'values': [10.0, 2315.0]}, {'hash': 4259706344156050626, 'values': [10.0, 2425.0]}, {'hash': 4340457316873839382, 'values': [10.0, 2490.0]}, {'hash': 4356676064183467115, 'values': [10.0, 2470.0]}, {'hash': 4429638861388557148, 'values': [10.0, 2370.0]}, {'hash': 4477382300635797467, 'values': [10.0, 2435.0]}, {'hash': 4578992559693363303, 'values': [10.0, 2335.0]}, {'hash': 4579472350053230007, 'values': [10.0, 2720.0]}, {'hash': 4586427849190292816, 'values': [10.0, 2480.0]}, {'hash': 4734825758749082701, 'values': [10.0, 2675.0]}, {'hash': 4811922168148294097, 'values': [10.0, 2570.0]}, {'hash': 4957856306060390113, 'values': [10.0, 2345.0]}, {'hash': 5019462529220513108, 'values': [10.0, 2725.0]}, {'hash': 5206189584322944525, 'values': [10.0, 2460.0]}, {'hash': 5209267895868744375, 'values': [10.0, 2330.0]}, {'hash': 5277048382241783140, 'values': [10.0, 2350.0]}, {'hash': 5452684004115829364, 'values': [10.0, 2640.0]}, {'hash': 5685878910210846062, 'values': [10.0, 2735.0]}, {'hash': 5687495238192794851, 'values': [10.0, 2385.0]}, {'hash': 5785933929594249671, 'values': [10.0, 2465.0]}, {'hash': 5875894225988394840, 'values': [10.0, 2530.0]}, {'hash': 6134377025443110356, 'values': [10.0, 2625.0]}, {'hash': 6326905904218654186, 'values': [10.0, 2700.0]}, {'hash': 6338256001173664528, 'values': [10.0, 2680.0]}, {'hash': 6730918654704304314, 'values': [10.0, 2265.0]}, {'hash': 6892035777578313306, 'values': [10.0, 2635.0]}, {'hash': 7480992971775130706, 'values': [10.0, 2690.0]}, {'hash': 7552228461607989126, 'values': [10.0, 2740.0]}, {'hash': 7640953883025164414, 'values': [10.0, 2590.0]}, {'hash': 7951043072459728883, 'values': [10.0, 2560.0]}, {'hash': 8009730916123189212, 'values': [10.0, 2340.0]}, {'hash': 8055291883392730446, 'values': [10.0, 2650.0]}, {'hash': 8161144572732903681, 'values': [10.0, 2455.0]}, {'hash': 8184602267473193545, 'values': [10.0, 2450.0]}, {'hash': 8218596976656455375, 'values': [10.0, 2410.0]}, {'hash': 8282051833711382510, 'values': [10.0, 2585.0]}, {'hash': 8865927617827192748, 'values': [10.0, 2395.0]}, {'hash': 8906405763079324994, 'values': [10.0, 2510.0]}, {'hash': 8989629655658253488, 'values': [10.0, 2505.0]}, {'hash': 8990173978249917664, 'values': [10.0, 2285.0]}, {'hash': 9094378756317064664, 'values': [10.0, 2300.0]}]

Related functions

datasketch_aod() — Aggregate `(key, values DOUBLE[])` rows into an array-of-doubles Tuple sketch (`sketch_aod`): a Theta sketch of the distinct keys where each retained key carries a fixed-length array of DOUBLE values (1–255 values, the same length on every row, inferred from the data and stored in the sketch), summed element-wise per key

datasketch_aod_to_theta

Scalar function Array of Doubles

Signature

datasketch_aod_to_theta(sketch: sketch_aod) → sketch_theta

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description

Description

Converts the array-of-doubles sketch to a Theta sketch of its keys (dropping the summaries), usable with the datasketch_theta functions

1 Use the keys with the Theta functions

SELECT datasketch_theta_estimate(datasketch_aod_to_theta(datasketch_aod(customer_id, [amount]))) AS customers
FROM orders;

Related functions

datasketch_aod_union

Scalar function Array of Doubles

Signature

2 overloaded forms · click to inspect

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch_a`	Type `sketch_aod`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_aod`	Mode Positional	Description
Argument `varargs`	Type `sketch_aod`	Mode Positional	Description Varargs

Arguments (Positional)

Argument	Type	Mode	Description
Argument `lg_k`	Type `INTEGER`	Mode Positional	Description
Argument `sketch_a`	Type `sketch_aod`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_aod`	Mode Positional	Description
Argument `varargs`	Type `sketch_aod`	Mode Positional	Description Varargs

Description

Returns the union of two or more array-of-doubles sketches (values of a key summed element-wise), keeping as many entries as the largest input (at least lg_k 12)

1 Example 1

SELECT datasketch_aod_union(sketch_a, sketch_b) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_b FROM range(500, 1500) t(i)) b;

2 Example 2

SELECT datasketch_aod_union(sketch_a, sketch_b, sketch_c) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_b FROM range(500, 1500) t(i)) b, (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch_c FROM range(250, 1250) t(i)) c;

Related functions

datasketch_aod_upper_bound

Scalar function Array of Doubles

Signature

datasketch_aod_upper_bound(sketch: sketch_aod, num_std_devs: INTEGER) → DOUBLE

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_aod`	Mode Positional	Description
Argument `num_std_devs`	Type `INTEGER`	Mode Positional	Description

Description

Returns the upper bound of the estimated number of distinct keys at the given number of standard deviations (1, 2, or 3)

1 Example 1

SELECT datasketch_aod_upper_bound(sketch, 2) FROM (SELECT datasketch_aod(i % 100, [1.0, i * 0.5]::DOUBLE[]) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_aod_upper_bound(sketch, 2)
100.0

Array of Doubles functions in the Apache DataSketches DuckDB extension

datasketch_aod

Signature

Arguments (Positional)

Arguments (Positional)

Arguments (Positional)

Arguments (Positional)

Description

Related functions

datasketch_aod_a_not_b

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_column_means

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_column_sums

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_describe

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_dissimilarity_test

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_estimate

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_exactly_equal

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_get_theta

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_intersect

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_intersect_agg

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_is_empty

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_is_estimation_mode

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_jaccard

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_lower_bound

Signature

Arguments (Positional)

Description

Related functions

datasketch_aod_num_retained