Tuple functions — Apache DataSketches DuckDB Extension

DuckDB Extensions All extensions Apache DataSketches

Function category

Tuple

22 functions

Tuple sketch — a Theta sketch whose retained keys each carry a DOUBLE summary (sum, min, max or presence). Count distinct keys and aggregate a metric per key in one pass — distinct customers with their revenue — with the same union / intersect / A-not-B algebra as Theta.

datasketch_tuple

Aggregate function Tuple

Signature

4 overloaded forms · click to inspect

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `mode`	Type `VARCHAR`	Mode Positional	Description

Arguments (Positional)

Argument	Type	Mode	Description
Argument `lg_k`	Type `INTEGER`	Mode Positional	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `mode`	Type `VARCHAR`	Mode Positional	Description

Arguments (Positional)

Argument	Type	Mode	Description
Argument `key`	Type `FLOAT \| DOUBLE \| SMALLINT \| …` 11 concrete types `BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINTVARCHAR`	Mode Positional	Description
Argument `value`	Type `DOUBLE`	Mode Positional	Description
Argument `mode`	Type `VARCHAR`	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 `DOUBLE \| INTEGER \| UINTEGER \| …` 11 concrete types `BIGINTDOUBLEFLOATINTEGERSMALLINTTINYINTUBIGINTUINTEGERUSMALLINTUTINYINTVARCHAR`	Mode Positional	Description
Argument `value`	Type `DOUBLE`	Mode Positional	Description
Argument `mode`	Type `VARCHAR`	Mode Positional	Description
Argument `seed`	Type `BIGINT`	Mode Positional	Description
Argument `p`	Type `DOUBLE`	Mode Positional	Description

Description

Aggregate (key, value) rows into a Tuple sketch (sketch_tuple_double): a Theta sketch of the distinct keys in which every retained key carries a DOUBLE summary of its values. mode := combines a key's values — 'sum' (default), 'min', 'max' or 'one' (presence only). The optional leading lg_k (4–26, default 12) sets the nominal size, and seed := / p := work as for datasketch_theta. NULL keys or values are skipped. Passing a sketch_tuple_double merges sketches (keeping the inputs' lg_k unless one is given); the mode is not stored in the sketch, so pass the same mode := when merging.

1 Distinct customers and their revenue from one pass

SELECT datasketch_tuple_estimate(s) AS customers,
       datasketch_tuple_sum(s)      AS revenue,
       datasketch_tuple_mean(s)     AS revenue_per_customer
FROM (SELECT datasketch_tuple(customer_id, amount) AS s FROM orders);

2 Keep each customer's largest order instead of the sum

SELECT datasketch_tuple_mean(datasketch_tuple(customer_id, amount, mode := 'max')) AS avg_largest_order
FROM orders;

3 Merge per-region sketches

SELECT datasketch_tuple_sum(datasketch_tuple(s)) AS total_revenue
FROM (SELECT region, datasketch_tuple(customer_id, amount) AS s FROM orders GROUP BY region);

Related functions

datasketch_tuple_a_not_b

Scalar function Tuple

Signature

datasketch_tuple_a_not_b(sketch_a: sketch_tuple_double, sketch_b: sketch_tuple_double) → sketch_tuple_double

Arguments (Positional)

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

Description

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

1 Example 1

SELECT datasketch_tuple_a_not_b(sketch_a, sketch_b) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_b FROM range(500, 1500) t(i)) b;

Output

datasketch_tuple_a_not_b(sketch_a, sketch_b)
01030901001e0000

Related functions

datasketch_tuple_describe

Scalar function Tuple

Signature

datasketch_tuple_describe(sketch: sketch_tuple_double) → VARCHAR

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

Returns a human-readable description of the Tuple sketch

1 Example 1

SELECT datasketch_tuple_describe(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_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_tuple_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_tuple() — Aggregate `(key, value)` rows into a Tuple sketch (`sketch_tuple_double`): a Theta sketch of the distinct keys in which every retained key carries a DOUBLE summary of its values

datasketch_tuple_dissimilarity_test

Scalar function Tuple

Signature

datasketch_tuple_dissimilarity_test( actual: sketch_tuple_double, expected: sketch_tuple_double, threshold: DOUBLE ) → BOOLEAN

Arguments (Positional)

Argument	Type	Mode	Description
Argument `actual`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `expected`	Type `sketch_tuple_double`	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_tuple_dissimilarity_test(actual, expected, 0.1) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS actual FROM range(0, 1000) t(i)) a, (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS expected FROM range(100, 1100) t(i)) e;

Output

datasketch_tuple_dissimilarity_test(actual, expected, 0.1)
false

Related functions

datasketch_tuple_estimate

Scalar function Tuple

Signature

datasketch_tuple_estimate(sketch: sketch_tuple_double) → DOUBLE

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

Returns the estimated number of distinct keys in the Tuple sketch

1 Example 1

SELECT datasketch_tuple_estimate(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_estimate(sketch)
100.0

Related functions

datasketch_tuple_exactly_equal

Scalar function Tuple

Signature

datasketch_tuple_exactly_equal(sketch_a: sketch_tuple_double, sketch_b: sketch_tuple_double) → BOOLEAN

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch_a`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_tuple_double`	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_tuple_exactly_equal(sketch_a, sketch_b) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_b FROM range(500, 1500) t(i)) b;

Output

datasketch_tuple_exactly_equal(sketch_a, sketch_b)
true

Related functions

datasketch_tuple_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_tuple_get_theta

Scalar function Tuple

Signature

datasketch_tuple_get_theta(sketch: sketch_tuple_double) → DOUBLE

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

Returns the theta value of the Tuple sketch (the fraction of the key hash space it retains)

1 Example 1

SELECT datasketch_tuple_get_theta(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_get_theta(sketch)
1.0

Related functions

datasketch_tuple() — Aggregate `(key, value)` rows into a Tuple sketch (`sketch_tuple_double`): a Theta sketch of the distinct keys in which every retained key carries a DOUBLE summary of its values

datasketch_tuple_intersect

Scalar function Tuple

Signature

datasketch_tuple_intersect( sketch_a: sketch_tuple_double, sketch_b: sketch_tuple_double, mode: VARCHAR, varargs: sketch_tuple_double ) → sketch_tuple_double

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch_a`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `mode`	Type `VARCHAR`	Mode Positional	Description
Argument `varargs`	Type `sketch_tuple_double`	Mode Positional	Description Varargs

Description

Returns the intersection of two or more Tuple sketches: the keys present in every sketch, with their summaries combined by mode := 'sum' (default), 'min', 'max' or 'one'

1 Customers in both segments, with their combined revenue

WITH a AS (SELECT datasketch_tuple(customer_id, amount) AS s FROM orders WHERE region = 'north' AND customer_id < 30),
     b AS (SELECT datasketch_tuple(customer_id, amount) AS s FROM orders WHERE region = 'north' AND customer_id >= 10)
SELECT datasketch_tuple_estimate(datasketch_tuple_intersect(a.s, b.s)) AS in_both,
       datasketch_tuple_sum(datasketch_tuple_intersect(a.s, b.s))      AS revenue
FROM a, b;

Related functions

datasketch_tuple_intersect_agg

Aggregate function Tuple

Signature

datasketch_tuple_intersect_agg(sketch: sketch_tuple_double, mode: VARCHAR) → sketch_tuple_double

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `mode`	Type `VARCHAR`	Mode Positional	Description

Description

Intersects Tuple sketches across rows: keeps the keys present in every sketch, combining their summaries by mode := 'sum' (default), 'min', 'max' or 'one'. NULL sketches are skipped; returns NULL if there are no sketches

1 Example 1

SELECT datasketch_tuple_intersect_agg(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i) UNION ALL SELECT datasketch_tuple(i % 100, i::DOUBLE) FROM range(500, 1500) t(i)) s;

2 Example 2

SELECT datasketch_tuple_intersect_agg(sketch, mode := 'min') FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i) UNION ALL SELECT datasketch_tuple(i % 100, i::DOUBLE) FROM range(500, 1500) t(i)) s;

Related functions

datasketch_tuple_intersect() — Returns the intersection of two or more Tuple sketches: the keys present in every sketch, with their summaries combined by mode := 'sum' (default), 'min', 'max' or 'one'

datasketch_tuple_is_empty

Scalar function Tuple

Signature

datasketch_tuple_is_empty(sketch: sketch_tuple_double) → BOOLEAN

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

Returns true if the Tuple sketch is empty

1 Example 1

SELECT datasketch_tuple_is_empty(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_is_empty(sketch)
false

Related functions

datasketch_tuple() — Aggregate `(key, value)` rows into a Tuple sketch (`sketch_tuple_double`): a Theta sketch of the distinct keys in which every retained key carries a DOUBLE summary of its values

datasketch_tuple_is_estimation_mode

Scalar function Tuple

Signature

datasketch_tuple_is_estimation_mode(sketch: sketch_tuple_double) → BOOLEAN

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

Returns true if the Tuple sketch is in estimation mode (it has sampled the keys)

1 Example 1

SELECT datasketch_tuple_is_estimation_mode(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_is_estimation_mode(sketch)
false

Related functions

datasketch_tuple() — Aggregate `(key, value)` rows into a Tuple sketch (`sketch_tuple_double`): a Theta sketch of the distinct keys in which every retained key carries a DOUBLE summary of its values

datasketch_tuple_jaccard

Scalar function Tuple

Signature

datasketch_tuple_jaccard(sketch_a: sketch_tuple_double, sketch_b: sketch_tuple_double) → STRUCT(lower DOUBLE, estimate DOUBLE, upper DOUBLE)

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch_a`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_tuple_double`	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_tuple_jaccard(sketch_a, sketch_b) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_b FROM range(500, 1500) t(i)) b;

Output

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

2 Example 2

SELECT datasketch_tuple_jaccard(sketch_a, sketch_b).estimate FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_b FROM range(500, 1500) t(i)) b;

Output

(datasketch_tuple_jaccard(sketch_a, sketch_b)).estimate
1.0

Related functions

datasketch_tuple_lower_bound

Scalar function Tuple

Signature

datasketch_tuple_lower_bound(sketch: sketch_tuple_double, num_std_devs: INTEGER) → DOUBLE

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	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_tuple_lower_bound(sketch, 2) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_lower_bound(sketch, 2)
100.0

Related functions

datasketch_tuple_mean

Scalar function Tuple

Signature

datasketch_tuple_mean(sketch: sketch_tuple_double) → DOUBLE

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

Returns the mean summary per distinct key (the mean of the retained summaries), or NULL if the sketch retains no entries

1 Example 1

SELECT datasketch_tuple_mean(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_mean(sketch)
4995.0

Related functions

datasketch_tuple_num_retained

Scalar function Tuple

Signature

datasketch_tuple_num_retained(sketch: sketch_tuple_double) → BIGINT

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

Returns the number of entries (keys with their summaries) retained in the Tuple sketch

1 Example 1

SELECT datasketch_tuple_num_retained(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_num_retained(sketch)
100

Related functions

datasketch_tuple() — Aggregate `(key, value)` rows into a Tuple sketch (`sketch_tuple_double`): a Theta sketch of the distinct keys in which every retained key carries a DOUBLE summary of its values

datasketch_tuple_seed_hash

Scalar function Tuple

Signature

datasketch_tuple_seed_hash(sketch: sketch_tuple_double) → BIGINT

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

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

1 Example 1

SELECT datasketch_tuple_seed_hash(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_seed_hash(sketch)
37836

Related functions

datasketch_tuple() — Aggregate `(key, value)` rows into a Tuple sketch (`sketch_tuple_double`): a Theta sketch of the distinct keys in which every retained key carries a DOUBLE summary of its values

datasketch_tuple_similarity_test

Scalar function Tuple

Signature

datasketch_tuple_similarity_test( actual: sketch_tuple_double, expected: sketch_tuple_double, threshold: DOUBLE ) → BOOLEAN

Arguments (Positional)

Argument	Type	Mode	Description
Argument `actual`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `expected`	Type `sketch_tuple_double`	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_tuple_similarity_test(actual, expected, 0.9) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS actual FROM range(0, 1000) t(i)) a, (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS expected FROM range(100, 1100) t(i)) e;

Output

datasketch_tuple_similarity_test(actual, expected, 0.9)
true

Related functions

datasketch_tuple_sum

Scalar function Tuple

Signature

datasketch_tuple_sum(sketch: sketch_tuple_double) → DOUBLE

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

Returns the estimated total of the summaries over all distinct keys: the sum of the retained summaries divided by theta (with mode 'sum', the estimated sum of the values; exact when the sketch is not in estimation mode)

1 Example 1

SELECT datasketch_tuple_sum(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_sum(sketch)
499500.0

Related functions

datasketch_tuple_summaries

Scalar function Tuple

Signature

datasketch_tuple_summaries(sketch: sketch_tuple_double) → STRUCT(hash UBIGINT, summary DOUBLE)[]

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

Returns the retained entries of the Tuple sketch as a list of {hash, summary} structs (the key hash and its summary)

1 Example 1

SELECT datasketch_tuple_summaries(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_summaries(sketch)
[{'hash': 98946274238865930, 'summary': 5230.0}, {'hash': 112348182036953339, 'summary': 4610.0}, {'hash': 266144333882809615, 'summary': 5430.0}, {'hash': 325961210337798120, 'summary': 5070.0}, {'hash': 341968790597130978, 'summary': 4710.0}, {'hash': 362181683233754093, 'summary': 5050.0}, {'hash': 405753591161026837, 'summary': 4510.0}, {'hash': 417004644657123063, 'summary': 5130.0}, {'hash': 427008693142989162, 'summary': 5030.0}, {'hash': 569192067085513059, 'summary': 4990.0}, {'hash': 593872385995628096, 'summary': 4540.0}, {'hash': 601064217205377897, 'summary': 4580.0}, {'hash': 812940711502225074, 'summary': 5100.0}, {'hash': 824017179179306593, 'summary': 5000.0}, {'hash': 879908469782966194, 'summary': 5420.0}, {'hash': 972597172966177155, 'summary': 5490.0}, {'hash': 1093502844168540522, 'summary': 5200.0}, {'hash': 1124757437863516995, 'summary': 5310.0}, {'hash': 1206007004353599230, 'summary': 4560.0}, {'hash': 1251274992440322927, 'summary': 5460.0}, {'hash': 1277456548225195409, 'summary': 4760.0}, {'hash': 1377882186530105587, 'summary': 5040.0}, {'hash': 1498732507761423037, 'summary': 4550.0}, {'hash': 1558021066182427594, 'summary': 4880.0}, {'hash': 1861786089180386393, 'summary': 5080.0}, {'hash': 1863747701890426060, 'summary': 5410.0}, {'hash': 2002374527476343707, 'summary': 4720.0}, {'hash': 2050455455346564949, 'summary': 5090.0}, {'hash': 2066603284366225999, 'summary': 4730.0}, {'hash': 2129030137640795257, 'summary': 5240.0}, {'hash': 2189143365047986541, 'summary': 4650.0}, {'hash': 2206043092153046979, 'summary': 4520.0}, {'hash': 2266714801530709283, 'summary': 4620.0}, {'hash': 2325124908111195109, 'summary': 4500.0}, {'hash': 2357193980256238142, 'summary': 4640.0}, {'hash': 2384647691295803423, 'summary': 4970.0}, {'hash': 2410093545811538614, 'summary': 4860.0}, {'hash': 2413646022939387078, 'summary': 5110.0}, {'hash': 2455581497211901808, 'summary': 5330.0}, {'hash': 2463342110140584218, 'summary': 4590.0}, {'hash': 2514330683478745141, 'summary': 5320.0}, {'hash': 2549264307540610026, 'summary': 5150.0}, {'hash': 2714062032037713655, 'summary': 4750.0}, {'hash': 2851584022655110423, 'summary': 5220.0}, {'hash': 2899408821718900171, 'summary': 4800.0}, {'hash': 2969606663411317205, 'summary': 4840.0}, {'hash': 3006543970770323890, 'summary': 5160.0}, {'hash': 3008285774336268528, 'summary': 5210.0}, {'hash': 3013536481635266031, 'summary': 5260.0}, {'hash': 3141028696583807384, 'summary': 4950.0}, {'hash': 3227693670343832694, 'summary': 5190.0}, {'hash': 3288091941603431578, 'summary': 4780.0}, {'hash': 3336050303298949223, 'summary': 4830.0}, {'hash': 3510295896655991449, 'summary': 4890.0}, {'hash': 3552011096661462239, 'summary': 5370.0}, {'hash': 3572065067015922122, 'summary': 5340.0}, {'hash': 3757905022948533469, 'summary': 5390.0}, {'hash': 3843018174740357702, 'summary': 4810.0}, {'hash': 4192119294258525341, 'summary': 5290.0}, {'hash': 4221915398188310103, 'summary': 4630.0}, {'hash': 4259706344156050626, 'summary': 4850.0}, {'hash': 4340457316873839382, 'summary': 4980.0}, {'hash': 4356676064183467115, 'summary': 4940.0}, {'hash': 4429638861388557148, 'summary': 4740.0}, {'hash': 4477382300635797467, 'summary': 4870.0}, {'hash': 4578992559693363303, 'summary': 4670.0}, {'hash': 4579472350053230007, 'summary': 5440.0}, {'hash': 4586427849190292816, 'summary': 4960.0}, {'hash': 4734825758749082701, 'summary': 5350.0}, {'hash': 4811922168148294097, 'summary': 5140.0}, {'hash': 4957856306060390113, 'summary': 4690.0}, {'hash': 5019462529220513108, 'summary': 5450.0}, {'hash': 5206189584322944525, 'summary': 4920.0}, {'hash': 5209267895868744375, 'summary': 4660.0}, {'hash': 5277048382241783140, 'summary': 4700.0}, {'hash': 5452684004115829364, 'summary': 5280.0}, {'hash': 5685878910210846062, 'summary': 5470.0}, {'hash': 5687495238192794851, 'summary': 4770.0}, {'hash': 5785933929594249671, 'summary': 4930.0}, {'hash': 5875894225988394840, 'summary': 5060.0}, {'hash': 6134377025443110356, 'summary': 5250.0}, {'hash': 6326905904218654186, 'summary': 5400.0}, {'hash': 6338256001173664528, 'summary': 5360.0}, {'hash': 6730918654704304314, 'summary': 4530.0}, {'hash': 6892035777578313306, 'summary': 5270.0}, {'hash': 7480992971775130706, 'summary': 5380.0}, {'hash': 7552228461607989126, 'summary': 5480.0}, {'hash': 7640953883025164414, 'summary': 5180.0}, {'hash': 7951043072459728883, 'summary': 5120.0}, {'hash': 8009730916123189212, 'summary': 4680.0}, {'hash': 8055291883392730446, 'summary': 5300.0}, {'hash': 8161144572732903681, 'summary': 4910.0}, {'hash': 8184602267473193545, 'summary': 4900.0}, {'hash': 8218596976656455375, 'summary': 4820.0}, {'hash': 8282051833711382510, 'summary': 5170.0}, {'hash': 8865927617827192748, 'summary': 4790.0}, {'hash': 8906405763079324994, 'summary': 5020.0}, {'hash': 8989629655658253488, 'summary': 5010.0}, {'hash': 8990173978249917664, 'summary': 4570.0}, {'hash': 9094378756317064664, 'summary': 4600.0}]

datasketch_tuple_summaries(sketch)

[{'hash': 98946274238865930, 'summary': 5230.0}, {'hash': 112348182036953339, 'summary': 4610.0}, {'hash': 266144333882809615, 'summary': 5430.0}, {'hash': 325961210337798120, 'summary': 5070.0}, {'hash': 341968790597130978, 'summary': 4710.0}, {'hash': 362181683233754093, 'summary': 5050.0}, {'hash': 405753591161026837, 'summary': 4510.0}, {'hash': 417004644657123063, 'summary': 5130.0}, {'hash': 427008693142989162, 'summary': 5030.0}, {'hash': 569192067085513059, 'summary': 4990.0}, {'hash': 593872385995628096, 'summary': 4540.0}, {'hash': 601064217205377897, 'summary': 4580.0}, {'hash': 812940711502225074, 'summary': 5100.0}, {'hash': 824017179179306593, 'summary': 5000.0}, {'hash': 879908469782966194, 'summary': 5420.0}, {'hash': 972597172966177155, 'summary': 5490.0}, {'hash': 1093502844168540522, 'summary': 5200.0}, {'hash': 1124757437863516995, 'summary': 5310.0}, {'hash': 1206007004353599230, 'summary': 4560.0}, {'hash': 1251274992440322927, 'summary': 5460.0}, {'hash': 1277456548225195409, 'summary': 4760.0}, {'hash': 1377882186530105587, 'summary': 5040.0}, {'hash': 1498732507761423037, 'summary': 4550.0}, {'hash': 1558021066182427594, 'summary': 4880.0}, {'hash': 1861786089180386393, 'summary': 5080.0}, {'hash': 1863747701890426060, 'summary': 5410.0}, {'hash': 2002374527476343707, 'summary': 4720.0}, {'hash': 2050455455346564949, 'summary': 5090.0}, {'hash': 2066603284366225999, 'summary': 4730.0}, {'hash': 2129030137640795257, 'summary': 5240.0}, {'hash': 2189143365047986541, 'summary': 4650.0}, {'hash': 2206043092153046979, 'summary': 4520.0}, {'hash': 2266714801530709283, 'summary': 4620.0}, {'hash': 2325124908111195109, 'summary': 4500.0}, {'hash': 2357193980256238142, 'summary': 4640.0}, {'hash': 2384647691295803423, 'summary': 4970.0}, {'hash': 2410093545811538614, 'summary': 4860.0}, {'hash': 2413646022939387078, 'summary': 5110.0}, {'hash': 2455581497211901808, 'summary': 5330.0}, {'hash': 2463342110140584218, 'summary': 4590.0}, {'hash': 2514330683478745141, 'summary': 5320.0}, {'hash': 2549264307540610026, 'summary': 5150.0}, {'hash': 2714062032037713655, 'summary': 4750.0}, {'hash': 2851584022655110423, 'summary': 5220.0}, {'hash': 2899408821718900171, 'summary': 4800.0}, {'hash': 2969606663411317205, 'summary': 4840.0}, {'hash': 3006543970770323890, 'summary': 5160.0}, {'hash': 3008285774336268528, 'summary': 5210.0}, {'hash': 3013536481635266031, 'summary': 5260.0}, {'hash': 3141028696583807384, 'summary': 4950.0}, {'hash': 3227693670343832694, 'summary': 5190.0}, {'hash': 3288091941603431578, 'summary': 4780.0}, {'hash': 3336050303298949223, 'summary': 4830.0}, {'hash': 3510295896655991449, 'summary': 4890.0}, {'hash': 3552011096661462239, 'summary': 5370.0}, {'hash': 3572065067015922122, 'summary': 5340.0}, {'hash': 3757905022948533469, 'summary': 5390.0}, {'hash': 3843018174740357702, 'summary': 4810.0}, {'hash': 4192119294258525341, 'summary': 5290.0}, {'hash': 4221915398188310103, 'summary': 4630.0}, {'hash': 4259706344156050626, 'summary': 4850.0}, {'hash': 4340457316873839382, 'summary': 4980.0}, {'hash': 4356676064183467115, 'summary': 4940.0}, {'hash': 4429638861388557148, 'summary': 4740.0}, {'hash': 4477382300635797467, 'summary': 4870.0}, {'hash': 4578992559693363303, 'summary': 4670.0}, {'hash': 4579472350053230007, 'summary': 5440.0}, {'hash': 4586427849190292816, 'summary': 4960.0}, {'hash': 4734825758749082701, 'summary': 5350.0}, {'hash': 4811922168148294097, 'summary': 5140.0}, {'hash': 4957856306060390113, 'summary': 4690.0}, {'hash': 5019462529220513108, 'summary': 5450.0}, {'hash': 5206189584322944525, 'summary': 4920.0}, {'hash': 5209267895868744375, 'summary': 4660.0}, {'hash': 5277048382241783140, 'summary': 4700.0}, {'hash': 5452684004115829364, 'summary': 5280.0}, {'hash': 5685878910210846062, 'summary': 5470.0}, {'hash': 5687495238192794851, 'summary': 4770.0}, {'hash': 5785933929594249671, 'summary': 4930.0}, {'hash': 5875894225988394840, 'summary': 5060.0}, {'hash': 6134377025443110356, 'summary': 5250.0}, {'hash': 6326905904218654186, 'summary': 5400.0}, {'hash': 6338256001173664528, 'summary': 5360.0}, {'hash': 6730918654704304314, 'summary': 4530.0}, {'hash': 6892035777578313306, 'summary': 5270.0}, {'hash': 7480992971775130706, 'summary': 5380.0}, {'hash': 7552228461607989126, 'summary': 5480.0}, {'hash': 7640953883025164414, 'summary': 5180.0}, {'hash': 7951043072459728883, 'summary': 5120.0}, {'hash': 8009730916123189212, 'summary': 4680.0}, {'hash': 8055291883392730446, 'summary': 5300.0}, {'hash': 8161144572732903681, 'summary': 4910.0}, {'hash': 8184602267473193545, 'summary': 4900.0}, {'hash': 8218596976656455375, 'summary': 4820.0}, {'hash': 8282051833711382510, 'summary': 5170.0}, {'hash': 8865927617827192748, 'summary': 4790.0}, {'hash': 8906405763079324994, 'summary': 5020.0}, {'hash': 8989629655658253488, 'summary': 5010.0}, {'hash': 8990173978249917664, 'summary': 4570.0}, {'hash': 9094378756317064664, 'summary': 4600.0}]

Related functions

datasketch_tuple() — Aggregate `(key, value)` rows into a Tuple sketch (`sketch_tuple_double`): a Theta sketch of the distinct keys in which every retained key carries a DOUBLE summary of its values

datasketch_tuple_to_theta

Scalar function Tuple

Signature

datasketch_tuple_to_theta(sketch: sketch_tuple_double) → sketch_theta

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	Mode Positional	Description

Description

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

1 Example 1

SELECT datasketch_tuple_to_theta(sketch) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Related functions

datasketch_tuple_union

Scalar function Tuple

Signature

2 overloaded forms · click to inspect

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch_a`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `mode`	Type `VARCHAR`	Mode Positional	Description
Argument `varargs`	Type `sketch_tuple_double`	Mode Positional	Description Varargs

Arguments (Positional)

Argument	Type	Mode	Description
Argument `lg_k`	Type `INTEGER`	Mode Positional	Description
Argument `sketch_a`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `sketch_b`	Type `sketch_tuple_double`	Mode Positional	Description
Argument `mode`	Type `VARCHAR`	Mode Positional	Description
Argument `varargs`	Type `sketch_tuple_double`	Mode Positional	Description Varargs

Description

Returns the union of two or more Tuple sketches, keeping as many entries as the largest input (at least lg_k 12); the summaries of a key are combined by mode := 'sum' (default), 'min', 'max' or 'one'

1 Example 1

SELECT datasketch_tuple_union(sketch_a, sketch_b) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_b FROM range(500, 1500) t(i)) b;

2 Example 2

SELECT datasketch_tuple_union(sketch_a, sketch_b, sketch_c, mode := 'max') FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_a FROM range(0, 1000) t(i)) a, (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_b FROM range(500, 1500) t(i)) b, (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch_c FROM range(250, 1250) t(i)) c;

Related functions

datasketch_tuple_upper_bound

Scalar function Tuple

Signature

datasketch_tuple_upper_bound(sketch: sketch_tuple_double, num_std_devs: INTEGER) → DOUBLE

Arguments (Positional)

Argument	Type	Mode	Description
Argument `sketch`	Type `sketch_tuple_double`	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_tuple_upper_bound(sketch, 2) FROM (SELECT datasketch_tuple(i % 100, i::DOUBLE) AS sketch FROM range(0, 1000) t(i)) s;

Output

datasketch_tuple_upper_bound(sketch, 2)
100.0

Tuple functions in the Apache DataSketches DuckDB extension

datasketch_tuple

Signature

Arguments (Positional)

Arguments (Positional)

Arguments (Positional)

Arguments (Positional)

Description

Related functions

datasketch_tuple_a_not_b

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_describe

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_dissimilarity_test

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_estimate

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_exactly_equal

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_get_theta

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_intersect

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_intersect_agg

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_is_empty

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_is_estimation_mode

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_jaccard

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_lower_bound

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_mean

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_num_retained

Signature

Arguments (Positional)

Description

Related functions

datasketch_tuple_seed_hash