You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datasketches.apache.org by al...@apache.org on 2024/11/19 02:48:23 UTC
(datasketches-bigquery) 01/01: split readme into sketch types
This is an automated email from the ASF dual-hosted git repository.
alsay pushed a commit to branch split_readme
in repository https://gitbox.apache.org/repos/asf/datasketches-bigquery.git
commit 27c5ff693b8a22f42c89e414ab303fc64ae04333
Author: AlexanderSaydakov <Al...@users.noreply.github.com>
AuthorDate: Mon Nov 18 18:47:55 2024 -0800
split readme into sketch types
---
README.md | 817 +-------------------------
README_template.md | 158 -----
cpc/README.md | 93 ++-
cpc/{README.md => README_template.md} | 12 +-
fi/README.md | 31 +
fi/{README.md => README_template.md} | 5 +
hll/README.md | 82 ++-
hll/{README.md => README_template.md} | 6 +-
kll/README.md | 89 ++-
kll/{README.md => README_template.md} | 6 +-
readme_generator.py | 119 ++--
tdigest/README.md | 88 +++
tuple/README.md => tdigest/README_template.md | 8 +-
theta/README.md | 177 +++++-
theta/{README.md => README_template.md} | 7 +-
tuple/README.md | 235 +++++++-
tuple/{README.md => README_template.md} | 8 +-
17 files changed, 883 insertions(+), 1058 deletions(-)
diff --git a/README.md b/README.md
index 0d96eaf..dc00ca0 100644
--- a/README.md
+++ b/README.md
@@ -23,6 +23,11 @@
[User-Defined Aggregate Functions (UDAFs)](https://cloud.google.com/bigquery/docs/user-defined-aggregates) and
[non-aggregate (scalar) functions (UDFs)](https://cloud.google.com/bigquery/docs/user-defined-functions) for BigQuery SQL engine.
+DataSketches are probabilistic data structures that can process massive
+amounts of data and return very accurate results with a small memory footprint.
+Because of this, DataSketches are particularly useful for "big data" use cases
+such as streaming analytics and data warehousing.
+
Please visit the main
[Apache DataSketches website](https://datasketches.apache.org)
for more information about DataSketches library.
@@ -56,815 +61,3 @@ make test # runs predefined tests in BQ
```
The above steps can be executed in the root directory to install everything, or can be run from an individual sketch directory to install only that particular sketch.
-
-## BigQuery Sketch Functions
-
-DataSketches are probabilistic data structures that can process massive
-amounts of data and return very accurate results with a small memory footprint.
-Because of this, DataSketches are particularly useful for "big data" use cases
-such as streaming analytics and data warehousing.
-
-This package includes BigQuery UD(A)Fs for the following Sketch types:
-
-| Sketch Type | Description |
-|---------------------------------------------------|---|
-| [**Frequent Items Sketch**](#fi-sketch-functions) | Estimates the frequency of items in a stream to find the most frequent ones (e.g., top-selling products, most active users). |
-| [**CPC Sketch**](#cpc-sketch-functions) | A very compact sketch for estimating the number of unique items, especially in distributed environments. |
-| [**HLL Sketch**](#hll-sketch-functions) | Memory-efficient sketch for estimating the number of unique items, optimized for high accuracy. |
-| [**KLL Sketch**](#kll-sketch-functions) | Estimates the distribution of values, allowing you to find quantiles (like median, percentiles) without storing all the data. |
-| [**Theta Sketch**](#theta-sketch-functions) | Estimates unique items and supports set operations (union, intersection, difference) on those items. |
-| [**Tuple Sketch**](#tuple-sketch-functions) | Similar to Theta Sketch but allows associating values with each unique item, enabling operations like sum, min, max on those values. |
-| [**TDigest**](#tdigest-functions) | Another algorithm and very compact data structure for estimating quantiles and ranks of numeric values. |
-
-## CPC Sketch Functions
-
-**Description:** CPC sketches are a compact and efficient way to estimate the
-cardinality of a dataset, especially in distributed environments. They provide
-accurate estimates with low memory usage and are particularly useful for
-applications like counting unique users, analyzing website traffic, or tracking
-distinct events.
-
-For more information: [CPC Sketches](https://datasketches.apache.org/docs/CPC/CpcSketches.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-| [cpc_sketch_agg_union](cpc/sqlx/cpc_sketch_agg_union.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Creates a sketch that represents the union of the given column of sketches.<br><br>Param sketch: the column of sketches. Each as BYTES.<br>Defaults: lg\_k = 12, seed = 9001.<br>Returns: a Compact, Compressed CPC Sketch, as BYTES. |
-| [cpc_sketch_agg_string](cpc/sqlx/cpc_sketch_agg_string.sqlx) | AGGREGATE | (str STRING) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.<br><br>Param str: the STRING column of identifiers.<br>Defaults: lg\_k = 12, seed = 9001.<br>Returns: a Compact, Compressed CPC Sketch, as BYTES |
-| [cpc_sketch_agg_int64](cpc/sqlx/cpc_sketch_agg_int64.sqlx) | AGGREGATE | (value INT64) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.<br><br>Param value: the INT64 column of identifiers.<br>Defaults: lg\_k = 12, seed = 9001.<br>Returns: a Compact, Compressed CPC Sketch, as BYTES |
-| [cpc_sketch_agg_string_lgk_seed](cpc/sqlx/cpc_sketch_agg_string_lgk_seed.sqlx) | AGGREGATE | (str STRING, params STRUCT<lg_k BYTEINT, seed INT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.<br><br>Param str: the STRING column of identifiers.<br>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 26\].<br>Param seed: the seed to be used by the underlying hash function.<br>Returns: a Compact, Compresse [...]
-| [cpc_sketch_agg_union_lgk_seed](cpc/sqlx/cpc_sketch_agg_union_lgk_seed.sqlx) | AGGREGATE | (sketch BYTES, params STRUCT<lg_k BYTEINT, seed INT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the union of the given column of sketches.<br><br>Param sketch: the column of sketches. Each as BYTES.<br>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 26\].<br>Param seed: This is used to confirm that the given sketches were configured with the corre [...]
-| [cpc_sketch_agg_int64_lgk_seed](cpc/sqlx/cpc_sketch_agg_int64_lgk_seed.sqlx) | AGGREGATE | (value INT64, params STRUCT<lg_k BYTEINT, seed INT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.<br><br>Param value: the INT64 column of identifiers.<br>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 26\].<br>Param seed: the seed to be used by the underlying hash function.<br>Returns: a Compact, Compressed [...]
-| [cpc_sketch_get_estimate](cpc/sqlx/cpc_sketch_get_estimate.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Gets cardinality estimate and bounds from given sketch.<br><br>Param sketch: The given sketch to query as BYTES.<br>Defaults: seed = 9001.<br>Returns: a FLOAT64 value as the cardinality estimate. |
-| [cpc_sketch_to_string](cpc/sqlx/cpc_sketch_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.<br><br>Param sketch the given sketch as BYTES.<br>Defaults: seed = 9001.<br>Returns: a STRING that represents the state of the given sketch. |
-| [cpc_sketch_get_estimate_seed](cpc/sqlx/cpc_sketch_get_estimate_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> FLOAT64 | Gets cardinality estimate and bounds from given sketch.<br><br>Param sketch: The given sketch to query as BYTES.<br>Param seed: This is used to confirm that the given sketch was configured with the correct seed.<br>Returns: a FLOAT64 value as the cardinality estimate. |
-| [cpc_sketch_to_string_seed](cpc/sqlx/cpc_sketch_to_string_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> STRING | Returns a summary string that represents the state of the given sketch.<br><br>Param sketch the given sketch as BYTES.<br>Param seed: This is used to confirm that the given sketch was configured with the correct seed.<br>Returns: a STRING that represents the state of the given sketch. |
-| [cpc_sketch_union](cpc/sqlx/cpc_sketch_union.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the scalar union of the two given sketches.<br><br>Param sketchA: the first sketch as BYTES.<br>Param sketchB: the second sketch as BYTES.<br>Defaults: lg\_k = 12, seed = 9001.<br>Returns: a CPC Sketch, as BYTES. |
-| [cpc_sketch_get_estimate_and_bounds](cpc/sqlx/cpc_sketch_get_estimate_and_bounds.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Gets cardinality estimate and bounds from given sketch.<br> <br>Param sketch: The given sketch to query as bytes.<br>Param num\_std\_devs: The returned bounds will be based on the statistical confidence interval determined by the given number of standard deviations<br> from the re [...]
-| [cpc_sketch_union_lgk_seed](cpc/sqlx/cpc_sketch_union_lgk_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, lg_k BYTEINT, seed INT64) -> BYTES | Computes a sketch that represents the scalar union of the two given sketches.<br><br>Param sketchA: the first sketch as BYTES.<br>Param sketchB: the second sketch as BYTES.<br>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 26\].<br>Param seed: This is used to confirm that the given sketches were configured w [...]
-| [cpc_sketch_get_estimate_and_bounds_seed](cpc/sqlx/cpc_sketch_get_estimate_and_bounds_seed.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT, seed INT64) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Gets cardinality estimate and bounds from given sketch.<br> <br>Param sketch: The given sketch to query as bytes.<br>Param num\_std\_devs: The returned bounds will be based on the statistical confidence interval determined by the given number of standard devia [...]
-
-**Examples:**
-
-```sql
-
-# using defaults
-# expected 5
-select `$BQ_DATASET`.cpc_sketch_get_estimate(
- `$BQ_DATASET`.cpc_sketch_union(
- (select `$BQ_DATASET`.cpc_sketch_agg_string(str) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.cpc_sketch_agg_string(str) from unnest(["c", "d", "e"]) as str)
- )
-);
-
-# using full signatures
-# expected 5
-select `$BQ_DATASET`.cpc_sketch_get_estimate_seed(
- `$BQ_DATASET`.cpc_sketch_union_lgk_seed(
- (select `$BQ_DATASET`.cpc_sketch_agg_string_lgk_seed(str, struct<byteint, int64>(10, 111)) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.cpc_sketch_agg_string_lgk_seed(str, struct<byteint, int64>(10, 111)) from unnest(["c", "d", "e"]) as str),
- 10,
- 111
- ),
- 111
-);
-
-# using defaults
-create or replace table `$BQ_DATASET`.cpc_sketch(sketch bytes);
-
-insert into `$BQ_DATASET`.cpc_sketch
-(select `$BQ_DATASET`.cpc_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
-insert into `$BQ_DATASET`.cpc_sketch
-(select `$BQ_DATASET`.cpc_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
-
-select `$BQ_DATASET`.cpc_sketch_to_string(sketch) from `$BQ_DATASET`.cpc_sketch;
-
-# expected about 20000
-select `$BQ_DATASET`.cpc_sketch_get_estimate(
- `$BQ_DATASET`.cpc_sketch_agg_union(sketch)
-) from `$BQ_DATASET`.cpc_sketch;
-
-select `$BQ_DATASET`.cpc_sketch_get_estimate_and_bounds(
- `$BQ_DATASET`.cpc_sketch_agg_union(sketch),
- 3
-) from `$BQ_DATASET`.cpc_sketch;
-
-drop table `$BQ_DATASET`.cpc_sketch;
-
-# using full signatures
-create or replace table `$BQ_DATASET`.cpc_sketch(sketch bytes);
-
-insert into `$BQ_DATASET`.cpc_sketch
-(select `$BQ_DATASET`.cpc_sketch_agg_int64_lgk_seed(value, struct<byteint, int64>(10, 111)) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
-insert into `$BQ_DATASET`.cpc_sketch
-(select `$BQ_DATASET`.cpc_sketch_agg_int64_lgk_seed(value, struct<byteint, int64>(10, 111)) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
-
-select `$BQ_DATASET`.cpc_sketch_to_string_seed(sketch, 111) from `$BQ_DATASET`.cpc_sketch;
-
-# expected about 20000
-select `$BQ_DATASET`.cpc_sketch_get_estimate_seed(
- `$BQ_DATASET`.cpc_sketch_agg_union_lgk_seed(sketch, struct<byteint, int64>(10, 111)),
- 111
-) from `$BQ_DATASET`.cpc_sketch;
-
-select `$BQ_DATASET`.cpc_sketch_get_estimate_and_bounds_seed(
- `$BQ_DATASET`.cpc_sketch_agg_union_lgk_seed(sketch, struct<byteint, int64>(10, 111)),
- 3,
- 111
-) from `$BQ_DATASET`.cpc_sketch;
-
-drop table `$BQ_DATASET`.cpc_sketch;
-```
-
-
-## FI Sketch Functions
-
-**Description:** Frequent Items (FI) sketches are used to estimate the
-frequencies of items in a dataset. They are effective for identifying the most
-frequent items, such as the top products purchased or the most popular search
-queries.
-
-For more information: [Frequency Sketches](https://datasketches.apache.org/docs/Frequency/FrequencySketches.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-| [frequent_strings_sketch_merge](fi/sqlx/frequent_strings_sketch_merge.sqlx) | AGGREGATE | (sketch BYTES, lg_max_map_size BYTEINT NOT AGGREGATE) -> BYTES | Merges sketches from the given column.<br><br>Param sketch: the column of values.<br>Param lg\_max\_map\_size: the sketch accuracy/size parameter as an integer not less than 3.<br>Returns: a serialized Frequent Strings sketch as BYTES. |
-| [frequent_strings_sketch_build](fi/sqlx/frequent_strings_sketch_build.sqlx) | AGGREGATE | (item STRING, weight INT64, lg_max_map_size BYTEINT NOT AGGREGATE) -> BYTES | Creates a sketch that represents frequencies of the given column.<br><br>Param item: the column of STRING values.<br>Param weight: the amount by which the weight of the item should be increased.<br>Param lg\_max\_map\_size: the sketch accuracy/size parameter as a BYTEINT not less than 3.<br>Returns: a Frequent Strings Sk [...]
-| [frequent_strings_sketch_to_string](fi/sqlx/frequent_strings_sketch_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.<br><br>Param sketch: the given sketch as sketch encoded bytes.<br>Returns: a string that represents the state of the given sketch. |
-| [frequent_strings_sketch_get_result](fi/sqlx/frequent_strings_sketch_get_result.sqlx) | SCALAR | (sketch BYTES, error_type STRING, threshold INT64) -> ARRAY<STRUCT<item STRING, estimate INT64, lower_bound INT64, upper_bound INT64>> | Returns an array of rows that include frequent items, estimates, lower and upper bounds<br>given an error\_type and a threshold.<br><br>Param sketch: the given sketch as sketch encoded bytes.<br>Param error\_type: determines whether no false positives or n [...]
-
-**Examples:**
-
-```sql
-
-select `$BQ_DATASET`.frequent_strings_sketch_to_string(`$BQ_DATASET`.frequent_strings_sketch_build(str, 1, 5)) from unnest(["a", "b", "c"]) as str;
-
-create or replace table `$BQ_DATASET`.fs_sketch(sketch bytes);
-
-insert into `$BQ_DATASET`.fs_sketch
-(select `$BQ_DATASET`.frequent_strings_sketch_build(str, 1, 5) from unnest(["a", "b", "c", "d"]) as str);
-
-insert into `$BQ_DATASET`.fs_sketch
-(select `$BQ_DATASET`.frequent_strings_sketch_build(str, 1, 5) from unnest(["a", "a", "c"]) as str);
-
-select `$BQ_DATASET`.frequent_strings_sketch_get_result(`$BQ_DATASET`.frequent_strings_sketch_merge(sketch, 5), "NO_FALSE_NEGATIVES", null) from `$BQ_DATASET`.fs_sketch;
-
-drop table `$BQ_DATASET`.fs_sketch;
-```
-
-
-## HLL Sketch Functions
-
-**Description:** HyperLogLog (HLL) sketches are another type of cardinality
-estimation sketch. They are known for their high accuracy and low memory
-consumption, making them suitable for large datasets and real-time analytics.
-
-For more information: [HLL Sketches](https://datasketches.apache.org/docs/HLL/HllSketches.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-| [hll_sketch_agg_string](hll/sqlx/hll_sketch_agg_string.sqlx) | AGGREGATE | (str STRING) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.<br><br>Param str: the STRING column of identifiers.<br>Defaults: lg\_k = 12, tgt\_type = HLL\_4.<br>Returns: an HLL Sketch, as BYTES. |
-| [hll_sketch_agg_union](hll/sqlx/hll_sketch_agg_union.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Creates a sketch that represents the union of the given column of sketches.<br><br>Param sketch: the column of sketches. Each as BYTES.<br>Defaults: lg\_k = 12, tgt\_type = HLL\_4.<br>Returns: an HLL Sketch, as BYTES. |
-| [hll_sketch_agg_int64](hll/sqlx/hll_sketch_agg_int64.sqlx) | AGGREGATE | (value INT64) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.<br><br>Param value: the INT64 column of identifiers.<br>Defaults: lg\_k = 12, tgt\_type = HLL\_4.<br>Returns: an HLL Sketch, as BYTES. |
-| [hll_sketch_agg_string_lgk_type](hll/sqlx/hll_sketch_agg_string_lgk_type.sqlx) | AGGREGATE | (str STRING, params STRUCT<lg_k BYTEINT, tgt_type STRING> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.<br><br>Param str: the STRING column of identifiers.<br>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 21\].<br>Param tgt\_type: The HLL type to use: one of {"HLL\_4", "HLL\_6", "HLL\_8"}.<br>Returns: an [...]
-| [hll_sketch_agg_union_lgk_type](hll/sqlx/hll_sketch_agg_union_lgk_type.sqlx) | AGGREGATE | (sketch BYTES, params STRUCT<lg_k BYTEINT, tgt_type STRING> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the union of the given column of sketches.<br><br>Param sketch: the column of sketches. Each as BYTES.<br>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 21\].<br>Param tgt\_type: The HLL type to use: one of {"HLL\_4", "HLL\_6", "HLL\_8"}.<br>Retur [...]
-| [hll_sketch_agg_int64_lgk_type](hll/sqlx/hll_sketch_agg_int64_lgk_type.sqlx) | AGGREGATE | (value INT64, params STRUCT<lg_k BYTEINT, tgt_type STRING> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.<br><br>Param value: the INT64 column of identifiers.<br>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 21\].<br>Param tgt\_type: The HLL type to use: one of {"HLL\_4", "HLL\_6", "HLL\_8"}.<br>Returns: an H [...]
-| [hll_sketch_get_estimate](hll/sqlx/hll_sketch_get_estimate.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns a summary string that represents the state of the given sketch.<br><br>Param sketch: the given sketch as BYTES.<br>Returns: the cardinality estimate as FLOAT64 value. |
-| [hll_sketch_to_string](hll/sqlx/hll_sketch_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.<br><br>Param sketch: the given sketch as BYTES.<br>Returns: a STRING that represents the state of the given sketch. |
-| [hll_sketch_union](hll/sqlx/hll_sketch_union.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the union of the two given sketches.<br><br>Param sketchA: the first sketch as bytes.<br>Param sketchB: the second sketch as bytes.<br>Defaults: lg\_k = 12, tgt\_type = HLL\_4.<br>Returns: an HLL Sketch, as BYTES. |
-| [hll_sketch_union_lgk_type](hll/sqlx/hll_sketch_union_lgk_type.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, lg_k BYTEINT, tgt_type STRING) -> BYTES | Computes a sketch that represents the union of the two given sketches.<br><br>Param sketchA: the first sketch as bytes.<br>Param sketchB: the second sketch as bytes.<br>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 21\].<br>Param tgt\_type: The HLL type to use: one of {"HLL\_4", "HLL\_6", "HLL\_8"}.<br [...]
-| [hll_sketch_get_estimate_and_bounds](hll/sqlx/hll_sketch_get_estimate_and_bounds.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Gets cardinality estimate and bounds from given sketch.<br><br>Param sketch: The given sketch to query as BYTES.<br>Param num\_std\_devs: The returned bounds will be based on the statistical confidence interval determined by the given number of standard deviations<br> from the retu [...]
-
-**Examples:**
-
-```sql
-
-# expected 3
-select `$BQ_DATASET`.hll_sketch_get_estimate(`$BQ_DATASET`.hll_sketch_agg_string(s)) from unnest(["a", "b", "c"]) as s;
-
-select `$BQ_DATASET`.hll_sketch_to_string(`$BQ_DATASET`.hll_sketch_agg_string(s)) from unnest(["a", "b", "c"]) as s;
-
-# expected 5
-select `$BQ_DATASET`.hll_sketch_get_estimate_and_bounds(
- `$BQ_DATASET`.hll_sketch_union_lgk_type(
- (select `$BQ_DATASET`.hll_sketch_agg_string_lgk_type(str, struct<byteint, string>(10, "HLL_8")) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.hll_sketch_agg_string_lgk_type(str, struct<byteint, string>(10, "HLL_8")) from unnest(["c", "d", "e"]) as str),
- 10,
- "HLL_8"
- ),
- 2
-);
-
-select `$BQ_DATASET`.hll_sketch_to_string(
- `$BQ_DATASET`.hll_sketch_union_lgk_type(
- (select `$BQ_DATASET`.hll_sketch_agg_string_lgk_type(str, struct<byteint, string>(10, "HLL_8")) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.hll_sketch_agg_string_lgk_type(str, struct<byteint, string>(10, "HLL_8")) from unnest(["c", "d", "e"]) as str),
- 10,
- "HLL_8"
- )
-);
-
-create or replace table `$BQ_DATASET`.hll_sketch(sketch bytes);
-
-insert into `$BQ_DATASET`.hll_sketch
-(select `$BQ_DATASET`.hll_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
-insert into `$BQ_DATASET`.hll_sketch
-(select `$BQ_DATASET`.hll_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
-
-# expected estimate about 20000
-select `$BQ_DATASET`.hll_sketch_to_string(
- `$BQ_DATASET`.hll_sketch_agg_union(sketch)
-) from `$BQ_DATASET`.hll_sketch;
-
-select `$BQ_DATASET`.hll_sketch_to_string(
- `$BQ_DATASET`.hll_sketch_agg_union_lgk_type(sketch, struct<byteint, string>(10, "HLL_6"))
-) from `$BQ_DATASET`.hll_sketch;
-
-drop table `$BQ_DATASET`.hll_sketch;
-
-create or replace table `$BQ_DATASET`.hll_sketch(sketch bytes);
-
-insert into `$BQ_DATASET`.hll_sketch
-(select `$BQ_DATASET`.hll_sketch_agg_int64_lgk_type(value, struct<int, string>(8, "HLL_6")) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
-insert into `$BQ_DATASET`.hll_sketch
-(select `$BQ_DATASET`.hll_sketch_agg_int64_lgk_type(value, struct<int, string>(8, "HLL_6")) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
-
-# expected estimate about 20000
-select `$BQ_DATASET`.hll_sketch_to_string(
- `$BQ_DATASET`.hll_sketch_agg_union_lgk_type(sketch, struct<byteint, string>(8, "HLL_6"))
-) from `$BQ_DATASET`.hll_sketch;
-
-drop table `$BQ_DATASET`.hll_sketch;
-```
-
-
-## KLL Sketch Functions
-
-**Description:** KLL sketches are quantile sketches that provide approximate
-quantiles for a dataset. They are useful for understanding the distribution of
-data and calculating percentiles, such as the median or 95th percentile.
-
-For more information: [KLL Sketches](https://datasketches.apache.org/docs/KLL/KLLSketch.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-| [kll_sketch_float_build](kll/sqlx/kll_sketch_float_build.sqlx) | AGGREGATE | (value FLOAT64) -> BYTES | Creates a sketch that represents the distribution of the given column.<br><br>Param value: the column of FLOAT64 values.<br>Defaults: k = 200.<br>Returns: a KLL Sketch, as bytes. |
-| [kll_sketch_float_merge](kll/sqlx/kll_sketch_float_merge.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Merges sketches from the given column.<br><br>Param sketch: the column of values.<br>Defaluts: k = 200.<br>Returns: a serialized KLL sketch as BYTES. |
-| [kll_sketch_float_merge_k](kll/sqlx/kll_sketch_float_merge_k.sqlx) | AGGREGATE | (sketch BYTES, k INT NOT AGGREGATE) -> BYTES | Merges sketches from the given column.<br><br>Param sketch: the column of values.<br>Param k: the sketch accuracy/size parameter as an integer in the range \[8, 65535\].<br>Returns: a serialized KLL sketch as BYTES. |
-| [kll_sketch_float_build_k](kll/sqlx/kll_sketch_float_build_k.sqlx) | AGGREGATE | (value FLOAT64, k INT NOT AGGREGATE) -> BYTES | Creates a sketch that represents the distribution of the given column.<br><br>Param value: the column of FLOAT64 values.<br>Param k: the sketch accuracy/size parameter as an INT in the range \[8, 65535\].<br>Returns: a KLL Sketch, as bytes. |
-| [kll_sketch_float_get_n](kll/sqlx/kll_sketch_float_get_n.sqlx) | SCALAR | (sketch BYTES) -> INT64 | Returns the length of the input stream.<br><br>Param sketch: the given sketch as BYTES.<br>Returns: stream length as INT64 |
-| [kll_sketch_float_get_min_value](kll/sqlx/kll_sketch_float_get_min_value.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns the minimum value of the input stream.<br><br>Param sketch: the given sketch as BYTES.<br>Returns: min value as FLOAT64 |
-| [kll_sketch_float_to_string](kll/sqlx/kll_sketch_float_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.<br><br>Param sketch: the given sketch as sketch encoded bytes.<br>Returns: a string that represents the state of the given sketch. |
-| [kll_sketch_float_get_num_retained](kll/sqlx/kll_sketch_float_get_num_retained.sqlx) | SCALAR | (sketch BYTES) -> INT64 | Returns the number of retained items \(samples\) in the sketch.<br><br>Param sketch: the given sketch as BYTES.<br>Returns: number of retained items as INT64 |
-| [kll_sketch_float_get_max_value](kll/sqlx/kll_sketch_float_get_max_value.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns the maximum value of the input stream.<br><br>Param sketch: the given sketch as BYTES.<br>Returns: max value as FLOAT64 |
-| [kll_sketch_float_get_normalized_rank_error](kll/sqlx/kll_sketch_float_get_normalized_rank_error.sqlx) | SCALAR | (sketch BYTES, pmf BOOL) -> FLOAT64 | Returns the approximate rank error of the given sketch normalized as a fraction between zero and one.<br>Param sketch: the given sketch as BYTES.<br>Param pmf: if true, returns the "double\-sided" normalized rank error for the get\_PMF\(\) function.<br>Otherwise, it is the "single\-sided" normalized rank error for all the other queries. [...]
-| [kll_sketch_float_get_rank](kll/sqlx/kll_sketch_float_get_rank.sqlx) | SCALAR | (sketch BYTES, value FLOAT64, inclusive BOOL) -> FLOAT64 | Returns an approximation to the normalized rank, on the interval \[0.0, 1.0\], of the given value.<br><br>Param sketch: the given sketch in serialized form.<br>Param value: value to be ranked.<br>Param inclusive: if true the weight of the given value is included into the rank.<br>Returns: an approximate rank of the given value. |
-| [kll_sketch_float_get_pmf](kll/sqlx/kll_sketch_float_get_pmf.sqlx) | SCALAR | (sketch BYTES, split_points ARRAY<FLOAT64>, inclusive BOOL) -> ARRAY<FLOAT64> | Returns an approximation to the Probability Mass Function \(PMF\)<br>of the input stream as an array of probability masses defined by the given split\_points.<br><br>Param sketch: the given sketch as BYTES.<br><br>Param split\_points: an array of M unique, monotonically increasing values <br> \(of the same type as the input value [...]
-| [kll_sketch_float_kolmogorov_smirnov](kll/sqlx/kll_sketch_float_kolmogorov_smirnov.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, pvalue FLOAT64) -> BOOL | Performs the Kolmogorov\-Smirnov Test between two KLL sketches of type FLOAT64.<br>If the given sketches have insufficient data or if the sketch sizes are too small, this will return false.<br><br>Param sketchA: sketch A in serialized form.<br>Param sketchB: sketch B in serialized form.<br>Param pvalue: Target p\-value. Typically 0 [...]
-| [kll_sketch_float_get_cdf](kll/sqlx/kll_sketch_float_get_cdf.sqlx) | SCALAR | (sketch BYTES, split_points ARRAY<FLOAT64>, inclusive BOOL) -> ARRAY<FLOAT64> | Returns an approximation to the Cumulative Distribution Function \(CDF\) <br>of the input stream as an array of cumulative probabilities defined by the given split\_points.<br><br>Param sketch: the given sketch as BYTES.<br><br>Param split\_points: an array of M unique, monotonically increasing values<br> \(of the same type as th [...]
-| [kll_sketch_float_get_quantile](kll/sqlx/kll_sketch_float_get_quantile.sqlx) | SCALAR | (sketch BYTES, rank FLOAT64, inclusive BOOL) -> FLOAT64 | Returns a value from the sketch that is the best approximation to a value from the original stream with the given rank.<br><br>Param sketch: the given sketch in serialized form.<br>Param rank: rank of a value in the hypothetical sorted stream.<br>Param inclusive: if true, the given rank is considered inclusive \(includes weight of a value\)<b [...]
-
-**Examples:**
-
-```sql
-
-create or replace table `$BQ_DATASET`.kll_sketch(sketch bytes);
-
-# using default
-insert into `$BQ_DATASET`.kll_sketch
-(select `$BQ_DATASET`.kll_sketch_float_build(value) from unnest([1,2,3,4,5,6,7,8,9,10]) as value);
-
-# using full signature
-insert into `$BQ_DATASET`.kll_sketch
-(select `$BQ_DATASET`.kll_sketch_float_build_k(value, 100) from unnest([11,12,13,14,15,16,17,18,19,20]) as value);
-
-select `$BQ_DATASET`.kll_sketch_float_to_string(sketch) from `$BQ_DATASET`.kll_sketch;
-
-# using default
-select `$BQ_DATASET`.kll_sketch_float_to_string(`$BQ_DATASET`.kll_sketch_float_merge(sketch)) from `$BQ_DATASET`.kll_sketch;
-
-# using full signature
-select `$BQ_DATASET`.kll_sketch_float_to_string(`$BQ_DATASET`.kll_sketch_float_merge_k(sketch, 100)) from `$BQ_DATASET`.kll_sketch;
-
-# expected 0.5
-select `$BQ_DATASET`.kll_sketch_float_get_rank(`$BQ_DATASET`.kll_sketch_float_merge(sketch), 10, true) from `$BQ_DATASET`.kll_sketch;
-
-# expected 10
-select `$BQ_DATASET`.kll_sketch_float_get_quantile(`$BQ_DATASET`.kll_sketch_float_merge(sketch), 0.5, true) from `$BQ_DATASET`.kll_sketch;
-
-# expected 20
-select `$BQ_DATASET`.kll_sketch_float_get_n(`$BQ_DATASET`.kll_sketch_float_merge(sketch)) from `$BQ_DATASET`.kll_sketch;
-
-# expected 0.5, 0.5
-select `$BQ_DATASET`.kll_sketch_float_get_pmf(`$BQ_DATASET`.kll_sketch_float_merge(sketch), [10.0], true) from `$BQ_DATASET`.kll_sketch;
-
-# expected 0.5, 1
-select `$BQ_DATASET`.kll_sketch_float_get_cdf(`$BQ_DATASET`.kll_sketch_float_merge(sketch), [10.0], true) from `$BQ_DATASET`.kll_sketch;
-
-# expected 1
-select `$BQ_DATASET`.kll_sketch_float_get_min_value(`$BQ_DATASET`.kll_sketch_float_merge(sketch)) from `$BQ_DATASET`.kll_sketch;
-
-# expected 20
-select `$BQ_DATASET`.kll_sketch_float_get_max_value(`$BQ_DATASET`.kll_sketch_float_merge(sketch)) from `$BQ_DATASET`.kll_sketch;
-
-drop table `$BQ_DATASET`.kll_sketch;
-
-# expected about 1.3%
-select `$BQ_DATASET`.kll_sketch_float_get_normalized_rank_error(`$BQ_DATASET`.kll_sketch_float_build(value), false) from unnest(generate_array(1, 10000)) as value;
-
-select `$BQ_DATASET`.kll_sketch_float_get_num_retained(`$BQ_DATASET`.kll_sketch_float_build(value)) from unnest(generate_array(1, 10000)) as value;
-
-# expected false
-select `$BQ_DATASET`.kll_sketch_float_kolmogorov_smirnov(
- (select `$BQ_DATASET`.kll_sketch_float_build(value) from unnest([1,2,3,4,5,6,7,8,9,10]) as value),
- (select `$BQ_DATASET`.kll_sketch_float_build(value) from unnest([1,2,3,4,5,6,7,8,9,10]) as value),
- 0.05
-);
-
-# expected true
-select `$BQ_DATASET`.kll_sketch_float_kolmogorov_smirnov(
- (select `$BQ_DATASET`.kll_sketch_float_build(value) from unnest([1,2,3,4,5,6,7,8,9,10]) as value),
- (select `$BQ_DATASET`.kll_sketch_float_build(value) from unnest([11,12,13,14,15,16,17,18,19,20]) as value),
- 0.05
-);
-```
-
-
-## Theta Sketch Functions
-
-**Description:** Theta sketches are used for set operations like union,
-intersection, and difference. They are efficient for estimating the size of
-these operations on large datasets, enabling applications like analyzing user
-overlap or comparing different groups.
-
-For more information: [Theta sketches](https://datasketches.apache.org/docs/Theta/ThetaSketches.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-| [theta_sketch_agg_int64](theta/sqlx/theta_sketch_agg_int64.sqlx) | AGGREGATE | (value INT64) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.<br> <br>Param value: the INT64 column of identifiers.<br>Defaults: lg\_k = 12, seed = 9001, p = 1.0.<br>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
-| [theta_sketch_agg_union](theta/sqlx/theta_sketch_agg_union.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Creates a sketch that represents the union of the given column of sketches.<br><br>Param sketch: the column of sketches. Each as BYTES.<br>Defaults: lg\_k = 12, seed = 9001.<br>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
-| [theta_sketch_agg_string](theta/sqlx/theta_sketch_agg_string.sqlx) | AGGREGATE | (str STRING) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.<br> <br>Param str: the STRING column of identifiers.<br>Defaults: lg\_k = 12, seed = 9001, p = 1.0.<br>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
-| [theta_sketch_agg_union_lgk_seed](theta/sqlx/theta_sketch_agg_union_lgk_seed.sqlx) | AGGREGATE | (sketch BYTES, params STRUCT<lg_k BYTEINT, seed INT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the union of the given column of sketches.<br><br>Param sketch: the column of sketches. Each as BYTES.<br>Param lg\_k: the sketch accuracy/size parameter as a BYTEINT in the range \[4, 26\].<br>Param seed: This is used to confirm that the given sketches were configured with the [...]
-| [theta_sketch_agg_int64_lgk_seed_p](theta/sqlx/theta_sketch_agg_int64_lgk_seed_p.sqlx) | AGGREGATE | (value INT64, params STRUCT<lg_k BYTEINT, seed INT64, p FLOAT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.<br><br>Param value: the INT64 column of identifiers.<br>Param lg\_k: the sketch accuracy/size parameter as a BYTEINT in the range \[4, 26\]. A NULL specifies the default of 12.<br>Param seed: the seed to be used by the und [...]
-| [theta_sketch_agg_string_lgk_seed_p](theta/sqlx/theta_sketch_agg_string_lgk_seed_p.sqlx) | AGGREGATE | (str STRING, params STRUCT<lg_k BYTEINT, seed INT64, p FLOAT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.<br><br>Param str: the STRING column of identifiers.<br>Param lg\_k: the sketch accuracy/size parameter as a BYTEINT in the range \[4, 26\]. A NULL specifies the default of 12.<br>Param seed: the seed to be used by the un [...]
-| [theta_sketch_get_estimate](theta/sqlx/theta_sketch_get_estimate.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Gets cardinality estimate and bounds from given sketch.<br> <br>Param sketch: The given sketch to query as BYTES.<br>Defaults: seed = 9001.<br>Returns: a FLOAT64 value as the cardinality estimate. |
-| [theta_sketch_to_string](theta/sqlx/theta_sketch_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.<br><br>Param sketch: the given sketch as BYTES.<br>Defaults: seed = 9001.<br>Returns: a STRING that represents the state of the given sketch. |
-| [theta_sketch_get_num_retained](theta/sqlx/theta_sketch_get_num_retained.sqlx) | SCALAR | (sketch BYTES) -> INT | Returns the number of retained entries in the given sketch.<br> <br>Param sketch: The given sketch to query as BYTES.<br>Defaults: seed = 9001.<br>Returns: number of retained entries as INT. |
-| [theta_sketch_get_theta](theta/sqlx/theta_sketch_get_theta.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns theta \(effective sampling rate\) as a fraction from 0 to 1.<br> <br>Param sketch: The given sketch to query as BYTES.<br>Defaults: seed = 9001.<br>Returns: theta as FLOAT64. |
-| [theta_sketch_get_num_retained_seed](theta/sqlx/theta_sketch_get_num_retained_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> INT | Returns the number of retained entries in the given sketch.<br> <br>Param sketch: The given sketch to query as BYTES.<br>Param seed: This is used to confirm that the given sketch was configured with the correct seed.<br>Returns: number of retained entries as INT. |
-| [theta_sketch_get_estimate_seed](theta/sqlx/theta_sketch_get_estimate_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> FLOAT64 | Gets cardinality estimate and bounds from given sketch.<br> <br>Param sketch: The given sketch to query as BYTES.<br>Param seed: This is used to confirm that the given sketch was configured with the correct seed.<br>Returns: a FLOAT64 value as the cardinality estimate. |
-| [theta_sketch_to_string_seed](theta/sqlx/theta_sketch_to_string_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> STRING | Returns a summary string that represents the state of the given sketch.<br><br>Param sketch: the given sketch as BYTES.<br>Param seed: This is used to confirm that the given sketch was configured with the correct seed.<br>Returns: a STRING that represents the state of the given sketch. |
-| [theta_sketch_get_theta_seed](theta/sqlx/theta_sketch_get_theta_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> FLOAT64 | Returns theta \(effective sampling rate\) as a fraction from 0 to 1.<br> <br>Param sketch: The given sketch to query as BYTES.<br>Param seed: This is used to confirm that the given sketch was configured with the correct seed.<br>Returns: theta as FLOAT64. |
-| [theta_sketch_intersection](theta/sqlx/theta_sketch_intersection.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the scalar intersection of the two given sketches.<br><br>Param sketchA: the first sketch as BYTES.<br>Param sketchB: the second sketch as BYTES.<br>Defaults: seed = 9001.<br>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
-| [theta_sketch_union](theta/sqlx/theta_sketch_union.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the scalar union of the two given sketches.<br><br>Param sketchA: the first sketch as BYTES.<br>Param sketchB: the second sketch as BYTES.<br>Defaults: lg\_k = 12, seed = 9001.<br>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
-| [theta_sketch_a_not_b](theta/sqlx/theta_sketch_a_not_b.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the scalar set difference: sketchA and not sketchB.<br><br>Param sketchA: the first sketch "A" as bytes.<br>Param sketchB: the second sketch "B" as bytes.<br>Defaults: seed = 9001.<br>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
-| [theta_sketch_intersection_seed](theta/sqlx/theta_sketch_intersection_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64) -> BYTES | Computes a sketch that represents the scalar intersection of the two given sketches.<br><br>Param sketchA: the first sketch as BYTES.<br>Param sketchB: the second sketch as BYTES.<br>Param seed: This is used to confirm that the given sketches were configured with the correct seed.<br>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
-| [theta_sketch_a_not_b_seed](theta/sqlx/theta_sketch_a_not_b_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64) -> BYTES | Computes a sketch that represents the scalar set difference: sketchA and not sketchB.<br><br>Param sketchA: the first sketch "A" as bytes.<br>Param sketchB: the second sketch "B" as bytes.<br>Param seed: This is used to confirm that the given sketches were configured with the correct seed.<br>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
-| [theta_sketch_union_lgk_seed](theta/sqlx/theta_sketch_union_lgk_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, lg_k BYTEINT, seed INT64) -> BYTES | Computes a sketch that represents the scalar union of the two given sketches.<br><br>Param sketchA: the first sketch as BYTES.<br>Param sketchB: the second sketch as BYTES.<br>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 26\].<br>Param seed: This is used to confirm that the given sketches were config [...]
-| [theta_sketch_get_estimate_and_bounds](theta/sqlx/theta_sketch_get_estimate_and_bounds.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Gets cardinality estimate and bounds from given sketch.<br><br>Param sketch: The given sketch to query as BYTES.<br>Param num\_std\_devs: The returned bounds will be based on the statistical confidence interval<br> determined by the given number of standard deviations from th [...]
-| [theta_sketch_jaccard_similarity](theta/sqlx/theta_sketch_jaccard_similarity.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> STRUCT<lower_bound FLOAT64, estimate FLOAT64, upper_bound FLOAT64> | Computes the Jaccard similarity index with upper and lower bounds.<br>The Jaccard similarity index J\(A,B\) = \(A ^ B\)/\(A U B\) is used to measure how similar the two sketches are to each other.<br>If J = 1.0, the sketches are considered equal. If J = 0, the two sketches are disjoint.<br>A [...]
-| [theta_sketch_get_estimate_and_bounds_seed](theta/sqlx/theta_sketch_get_estimate_and_bounds_seed.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT, seed INT64) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Gets cardinality estimate and bounds from given sketch.<br><br>Param sketch: The given sketch to query as BYTES.<br>Param num\_std\_devs: The returned bounds will be based on the statistical confidence interval<br> determined by the given number of stand [...]
-| [theta_sketch_jaccard_similarity_seed](theta/sqlx/theta_sketch_jaccard_similarity_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64) -> STRUCT<lower_bound FLOAT64, estimate FLOAT64, upper_bound FLOAT64> | Computes the Jaccard similarity index with upper and lower bounds.<br>The Jaccard similarity index J\(A,B\) = \(A ^ B\)/\(A U B\) is used to measure how similar the two sketches are to each other.<br>If J = 1.0, the sketches are considered equal. If J = 0, the two sketch [...]
-
-**Examples:**
-
-```sql
-
-# using defaults
-create or replace table `$BQ_DATASET`.theta_sketch(sketch bytes);
-
-insert into `$BQ_DATASET`.theta_sketch
-(select `$BQ_DATASET`.theta_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
-insert into `$BQ_DATASET`.theta_sketch
-(select `$BQ_DATASET`.theta_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
-
-# expected about 20000
-select `$BQ_DATASET`.theta_sketch_get_estimate_and_bounds(
- `$BQ_DATASET`.theta_sketch_agg_union(sketch),
- 2
-) from `$BQ_DATASET`.theta_sketch;
-
-# expected estimate about 20000
-select `$BQ_DATASET`.theta_sketch_to_string(
- `$BQ_DATASET`.theta_sketch_agg_union(sketch)
-) from `$BQ_DATASET`.theta_sketch;
-
-select `$BQ_DATASET`.theta_sketch_get_theta(
- `$BQ_DATASET`.theta_sketch_agg_union(sketch)
-) from `$BQ_DATASET`.theta_sketch;
-
-select `$BQ_DATASET`.theta_sketch_get_num_retained(
- `$BQ_DATASET`.theta_sketch_agg_union(sketch)
-) from `$BQ_DATASET`.theta_sketch;
-
-drop table `$BQ_DATASET`.theta_sketch;
-
-# using full signatures
-create or replace table `$BQ_DATASET`.theta_sketch(sketch bytes);
-
-insert into `$BQ_DATASET`.theta_sketch
-(select `$BQ_DATASET`.theta_sketch_agg_int64_lgk_seed_p(value, struct<int, int, float64>(14, 111, 0.9)) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
-insert into `$BQ_DATASET`.theta_sketch
-(select `$BQ_DATASET`.theta_sketch_agg_int64_lgk_seed_p(value, struct<int, int, float64>(14, 111, 0.9)) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
-
-# expected about 20000
-select `$BQ_DATASET`.theta_sketch_get_estimate_and_bounds_seed(
- `$BQ_DATASET`.theta_sketch_agg_union_lgk_seed(sketch, struct<int, int>(10, 111)),
- 2,
- 111
-) from `$BQ_DATASET`.theta_sketch;
-
-# expected estimate about 20000
-select `$BQ_DATASET`.theta_sketch_to_string_seed(
- `$BQ_DATASET`.theta_sketch_agg_union_lgk_seed(sketch, struct<int, int>(10, 111)),
- 111
-) from `$BQ_DATASET`.theta_sketch;
-
-select `$BQ_DATASET`.theta_sketch_get_theta_seed(
- `$BQ_DATASET`.theta_sketch_agg_union_lgk_seed(sketch, struct<int, int>(10, 111)),
- 111
-) from `$BQ_DATASET`.theta_sketch;
-
-select `$BQ_DATASET`.theta_sketch_get_num_retained_seed(
- `$BQ_DATASET`.theta_sketch_agg_union_lgk_seed(sketch, struct<int, int>(10, 111)),
- 111
-) from `$BQ_DATASET`.theta_sketch;
-
-drop table `$BQ_DATASET`.theta_sketch;
-
-# using defaults
-# expected 5
-select `$BQ_DATASET`.theta_sketch_get_estimate(
- `$BQ_DATASET`.theta_sketch_union(
- (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["c", "d", "e"]) as str)
- )
-);
-
-# full signatures
-# expected 5
-select `$BQ_DATASET`.theta_sketch_get_estimate_seed(
- `$BQ_DATASET`.theta_sketch_union_lgk_seed(
- (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["c", "d", "e"]) as str),
- 10,
- 111
- ),
- 111
-);
-
-# using defaults
-# expected 1
-select `$BQ_DATASET`.theta_sketch_get_estimate(
- `$BQ_DATASET`.theta_sketch_intersection(
- (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["c", "d", "e"]) as str)
- )
-);
-
-# full signatures
-# expected 1
-select `$BQ_DATASET`.theta_sketch_get_estimate_seed(
- `$BQ_DATASET`.theta_sketch_intersection_seed(
- (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["c", "d", "e"]) as str),
- 111
- ),
- 111
-);
-
-# using defaults
-# expected 2
-select `$BQ_DATASET`.theta_sketch_get_estimate(
- `$BQ_DATASET`.theta_sketch_a_not_b(
- (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["c", "d", "e"]) as str)
- )
-);
-
-# full signatures
-# expected 2
-select `$BQ_DATASET`.theta_sketch_get_estimate_seed(
- `$BQ_DATASET`.theta_sketch_a_not_b_seed(
- (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["c", "d", "e"]) as str),
- 111
- ),
- 111
-);
-
-# using defaults
-# expected 0.2
-select `$BQ_DATASET`.theta_sketch_jaccard_similarity(
- (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["c", "d", "e"]) as str)
-);
-
-# using full signatures
-# expected 0.2
-select `$BQ_DATASET`.theta_sketch_jaccard_similarity_seed(
- (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["c", "d", "e"]) as str),
- 111
-);
-```
-
-
-## Tuple Sketch Functions
-
-**Description:** Tuple sketches extend the functionality of Theta sketches by
-allowing you to associate a summary value with each item in the set. This
-enables calculations like the sum, minimum, or maximum of values associated with
-the distinct items.
-
-For more information: [Tuple sketches](https://datasketches.apache.org/docs/Tuple/TupleSketches.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-| [tuple_sketch_int64_agg_union](tuple/sqlx/tuple_sketch_int64_agg_union.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Builds a Tuple Sketch that represents the UNION of the given column of Tuple Sketches.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br><br>Param sketch: the given column of Tuple Sketches with an INT64 summary column. This may not be NU [...]
-| [tuple_sketch_int64_agg_string](tuple/sqlx/tuple_sketch_int64_agg_string.sqlx) | AGGREGATE | (key STRING, value INT64) -> BYTES | Builds a Tuple Sketch from an STRING Key column and an INT64 value column.<br>Multiple values for the same key are aggregated using the default mode.<br>Note that cardinality estimation accuracy, plots, error tables, and sampling probability p are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an STRING Key column and an I [...]
-| [tuple_sketch_int64_agg_int64](tuple/sqlx/tuple_sketch_int64_agg_int64.sqlx) | AGGREGATE | (key INT64, value INT64) -> BYTES | Builds a Tuple Sketch from an INT64 Key column and an INT64 value column.<br>Multiple values for the same key are aggregated using the default mode.<br>Note that cardinality estimation accuracy, plots, error tables, and sampling probability p are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 Key column and an INT64 [...]
-| [tuple_sketch_int64_agg_union_lgk_seed_mode](tuple/sqlx/tuple_sketch_int64_agg_union_lgk_seed_mode.sqlx) | AGGREGATE | (sketch BYTES, params STRUCT<lg_k BYTEINT, seed INT64, mode STRING> NOT AGGREGATE) -> BYTES | Builds a Tuple Sketch that represents the UNION of the given column of Tuple Sketches.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br><br>P [...]
-| [tuple_sketch_int64_agg_int64_lgk_seed_p_mode](tuple/sqlx/tuple_sketch_int64_agg_int64_lgk_seed_p_mode.sqlx) | AGGREGATE | (key INT64, value INT64, params STRUCT<lg_k BYTEINT, seed INT64, p FLOAT64, mode STRING> NOT AGGREGATE) -> BYTES | Builds a Tuple Sketch from an INT64 Key column and an INT64 value column.<br>Multiple values for the same key are aggregated using one of the selectable operations: { SUM, MIN, MAX, ONE \(constant 1\) }.<br>Note that cardinality estimation accuracy, pl [...]
-| [tuple_sketch_int64_agg_string_lgk_seed_p_mode](tuple/sqlx/tuple_sketch_int64_agg_string_lgk_seed_p_mode.sqlx) | AGGREGATE | (key STRING, value INT64, params STRUCT<lg_k BYTEINT, seed INT64, p FLOAT64, mode STRING> NOT AGGREGATE) -> BYTES | Builds a Tuple Sketch from an STRING Key column and an INT64 value column.<br>Multiple values for the same key are aggregated using one of the selectable operations: SUM, MIN, MAX, ONE.<br>Note that cardinality estimation accuracy, plots, error tabl [...]
-| [tuple_sketch_int64_to_string](tuple/sqlx/tuple_sketch_int64_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a human readable STRING that is a short summary of the state of this sketch.<br> Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br> This function only applies to Tuple Sketches with an INT64 summary column.<br><br>Param sketch: the sketch to be summarized. This may not be NULL.<br>Defaults: seed = 9001.<br>Ret [...]
-| [tuple_sketch_int64_get_estimate](tuple/sqlx/tuple_sketch_int64_get_estimate.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns the cardinality estimate of the given Tuple Sketch.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br> <br>Param sketch: the given Tuple Sketch. This may not be NULL.<br>Defaults: seed = 9001.<br>Returns: the cardinality est [...]
-| [tuple_sketch_int64_get_theta](tuple/sqlx/tuple_sketch_int64_get_theta.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns theta \(effective sampling rate\) as a fraction from 0 to 1.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br> <br>Param sketch: the given Tuple Sketch. This may not be NULL.<br>Defaults: seed = 9001.<br>Returns: theta as FLOAT64. |
-| [tuple_sketch_int64_get_num_retained](tuple/sqlx/tuple_sketch_int64_get_num_retained.sqlx) | SCALAR | (sketch BYTES) -> INT | Returns the number of retained entries in the given sketch.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br> <br>Param sketch: the given Tuple Sketch. This may not be NULL.<br>Defaults: seed = 9001.<br>Returns: number of retai [...]
-| [tuple_sketch_int64_get_theta_seed](tuple/sqlx/tuple_sketch_int64_get_theta_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> FLOAT64 | Returns theta \(effective sampling rate\) as a fraction from 0 to 1.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br> <br>Param sketch: the given Tuple Sketch. This may not be NULL.<br>Param seed: This is used to c [...]
-| [tuple_sketch_int64_get_num_retained_seed](tuple/sqlx/tuple_sketch_int64_get_num_retained_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> INT | Returns the number of retained entries in the given sketch.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br> <br>Param sketch: the given Tuple Sketch. This may not be NULL.<br>Param seed: This is used to [...]
-| [tuple_sketch_int64_to_string_seed](tuple/sqlx/tuple_sketch_int64_to_string_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> STRING | Returns a human readable STRING that is a short summary of the state of this sketch.<br> Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br> This function only applies to Tuple Sketches with an INT64 summary column.<br><br>Param sketch: the sketch to be summarized. This may not be NULL.<br>Param s [...]
-| [tuple_sketch_int64_a_not_b](tuple/sqlx/tuple_sketch_int64_a_not_b.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the set difference of sketchA and not sketchB.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column. <br> <br>Param sketchA: the first sketch "A" as BYTES. This may not be NULL.<br>Param sketchB: the s [...]
-| [tuple_sketch_int64_from_theta_sketch](tuple/sqlx/tuple_sketch_int64_from_theta_sketch.sqlx) | SCALAR | (sketch BYTES, value INT64) -> BYTES | Converts the given Theta Sketch into a Tuple Sketch with a INT64 summary column set to the given INT64 value.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br><br>Param sketch: the given Theta Sketch. This may not be NULL.<br>Param value: the given INT64 value. This may not be NULL.<br>De [...]
-| [tuple_sketch_int64_get_estimate_seed](tuple/sqlx/tuple_sketch_int64_get_estimate_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> FLOAT64 | Returns the cardinality estimate of the given Tuple Sketch.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br> <br>Param sketch: the given Tuple Sketch. This may not be NULL.<br>Param seed: This is used to conf [...]
-| [tuple_sketch_int64_intersection](tuple/sqlx/tuple_sketch_int64_intersection.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the scalar intersection of sketchA and sketchB.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br><br>Param sketchA: the first sketch "A" as BYTES.<br>Param sketchB: the second sketch " [...]
-| [tuple_sketch_int64_union](tuple/sqlx/tuple_sketch_int64_union.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a Tuple Sketch that represents the UNION of sketchA and sketchB.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br><br>Param sketchA: the first sketch "A" as BYTES. This may not be NULL.<br>Param sketchB: the second sketch " [...]
-| [tuple_sketch_int64_from_theta_sketch_seed](tuple/sqlx/tuple_sketch_int64_from_theta_sketch_seed.sqlx) | SCALAR | (sketch BYTES, value INT64, seed INT64) -> BYTES | Converts the given Theta Sketch into a Tuple Sketch with a INT64 summary column set to the given INT64 value.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br><br>Param sketch: the given Theta Sketch. This may not be NULL.<br>Param value: the given INT64 value. This [...]
-| [tuple_sketch_int64_a_not_b_seed](tuple/sqlx/tuple_sketch_int64_a_not_b_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64) -> BYTES | Computes a sketch that represents the scalar set difference of sketchA and not sketchB.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br><br>Param sketchA: the first sketch "A" as BYTES. This may not be NUL [...]
-| [tuple_sketch_int64_filter_low_high](tuple/sqlx/tuple_sketch_int64_filter_low_high.sqlx) | SCALAR | (sketch BYTES, low INT64, high INT64) -> BYTES | Returns a Tuple Sketch computed from the given sketch filtered by the given low and high values. <br>This returns a compact tuple sketch that contains the subset of rows of the give sketch where the<br>summary column is greater\-than or equal to the given low and less\-than or equal to the given high.<br>Note that cardinality estimation ac [...]
-| [tuple_sketch_int64_get_estimate_and_bounds](tuple/sqlx/tuple_sketch_int64_get_estimate_and_bounds.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Returns the cardinality estimate and bounds from the given Tuple Sketch.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br> <br>P [...]
-| [tuple_sketch_int64_filter_low_high_seed](tuple/sqlx/tuple_sketch_int64_filter_low_high_seed.sqlx) | SCALAR | (sketch BYTES, low INT64, high INT64, seed INT64) -> BYTES | Returns a Tuple Sketch computed from the given sketch filtered by the given low and high values. <br>This returns a compact tuple sketch that contains the subset of rows of the give sketch where the<br>summary column is greater\-than or equal to the given low and less\-than or equal to the given high.<br>Note that car [...]
-| [tuple_sketch_int64_jaccard_similarity](tuple/sqlx/tuple_sketch_int64_jaccard_similarity.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> STRUCT<lower_bound FLOAT64, estimate FLOAT64, upper_bound FLOAT64> | Computes the Jaccard similarity index with upper and lower bounds.<br>The Jaccard similarity index J\(A,B\) = \(A ^ B\)/\(A U B\) is used to measure how similar the two sketches are to each other.<br>If J = 1.0, the sketches are considered equal. If J = 0, the two sketches are dis [...]
-| [tuple_sketch_int64_get_sum_estimate_and_bounds](tuple/sqlx/tuple_sketch_int64_get_sum_estimate_and_bounds.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT) -> STRUCT<sum_estimate FLOAT64, sum_lower_bound FLOAT64, sum_upper_bound FLOAT64> | Returns the estimate and bounds for the sum of the INT64 summary column<br>scaled to the original population from the given Tuple Sketch.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br> [...]
-| [tuple_sketch_int64_intersection_seed_mode](tuple/sqlx/tuple_sketch_int64_intersection_seed_mode.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64, mode STRING) -> BYTES | Computes a sketch that represents the scalar intersection of sketchA and sketchB.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br><br>Param sketchA: the first sketch "A" as [...]
-| [tuple_sketch_int64_get_sum_estimate_and_bounds_seed](tuple/sqlx/tuple_sketch_int64_get_sum_estimate_and_bounds_seed.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT, seed INT64) -> STRUCT<sum_estimate FLOAT64, sum_lower_bound FLOAT64, sum_upper_bound FLOAT64> | Returns the estimate and bounds for the sum of the INT64 summary column<br>scaled to the original population from the given Tuple Sketch.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as [...]
-| [tuple_sketch_int64_union_lgk_seed_mode](tuple/sqlx/tuple_sketch_int64_union_lgk_seed_mode.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, lg_k BYTEINT, seed INT64, mode STRING) -> BYTES | Computes a Tuple Sketch that represents the UNION of sketchA and sketchB.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summary column.<br><br>Param sketchA: the first sketch "A" as [...]
-| [tuple_sketch_int64_get_estimate_and_bounds_seed](tuple/sqlx/tuple_sketch_int64_get_estimate_and_bounds_seed.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT, seed INT64) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Returns the cardinality estimate and bounds from the given Tuple Sketch.<br>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.<br>This function only applies to Tuple Sketches with an INT64 summ [...]
-| [tuple_sketch_int64_jaccard_similarity_seed](tuple/sqlx/tuple_sketch_int64_jaccard_similarity_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64) -> STRUCT<lower_bound FLOAT64, estimate FLOAT64, upper_bound FLOAT64> | Computes the Jaccard similarity index with upper and lower bounds.<br>The Jaccard similarity index J\(A,B\) = \(A ^ B\)/\(A U B\) is used to measure how similar the two sketches are to each other.<br>If J = 1.0, the sketches are considered equal. If J = 0, th [...]
-
-**Examples:**
-
-```sql
-
-# using defaults
-create or replace table `$BQ_DATASET`.tuple_sketch(sketch bytes);
-
-insert into `$BQ_DATASET`.tuple_sketch
-(select `$BQ_DATASET`.tuple_sketch_int64_from_theta_sketch(`$BQ_DATASET`.theta_sketch_agg_string(cast(value as string)), 1) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
-insert into `$BQ_DATASET`.tuple_sketch
-(select `$BQ_DATASET`.tuple_sketch_int64_from_theta_sketch(`$BQ_DATASET`.theta_sketch_agg_string(cast(value as string)), 1) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
-
-# expected about 20000
-select `$BQ_DATASET`.tuple_sketch_int64_get_estimate(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch)
-) from `$BQ_DATASET`.tuple_sketch;
-
-select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_and_bounds(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch),
- 2
-) from `$BQ_DATASET`.tuple_sketch;
-
-select `$BQ_DATASET`.tuple_sketch_int64_get_sum_estimate_and_bounds(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch),
- 2
-) from `$BQ_DATASET`.tuple_sketch;
-
-# expected estimate about 20000
-select `$BQ_DATASET`.tuple_sketch_int64_to_string(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch)
-) from `$BQ_DATASET`.tuple_sketch;
-
-select `$BQ_DATASET`.tuple_sketch_int64_get_theta(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch)
-) from `$BQ_DATASET`.tuple_sketch;
-
-select `$BQ_DATASET`.tuple_sketch_int64_get_num_retained(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch)
-) from `$BQ_DATASET`.tuple_sketch;
-
-drop table `$BQ_DATASET`.tuple_sketch;
-
-# using full signatures
-create or replace table `$BQ_DATASET`.tuple_sketch(sketch bytes);
-
-insert into `$BQ_DATASET`.tuple_sketch
-(select `$BQ_DATASET`.tuple_sketch_int64_from_theta_sketch_seed(
- `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(cast(value as string), STRUCT<BYTEINT, INT64, FLOAT64>(10, 111, 0.999)),
- 1,
- 111
-) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
-insert into `$BQ_DATASET`.tuple_sketch
-(select `$BQ_DATASET`.tuple_sketch_int64_from_theta_sketch_seed(
- `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(cast(value as string), STRUCT<BYTEINT, INT64, FLOAT64>(10, 111, 0.999)),
- 1,
- 111
-) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
-
-# expected about 20000
-select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_seed(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
- 111
-) from `$BQ_DATASET`.tuple_sketch;
-
-select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_and_bounds_seed(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
- 2,
- 111
-) from `$BQ_DATASET`.tuple_sketch;
-
-select `$BQ_DATASET`.tuple_sketch_int64_get_sum_estimate_and_bounds_seed(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
- 2,
- 111
-) from `$BQ_DATASET`.tuple_sketch;
-
-# expected estimate about 20000
-select `$BQ_DATASET`.tuple_sketch_int64_to_string_seed(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
- 111
-) from `$BQ_DATASET`.tuple_sketch;
-
-select `$BQ_DATASET`.tuple_sketch_int64_get_theta_seed(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
- 111
-) from `$BQ_DATASET`.tuple_sketch;
-
-select `$BQ_DATASET`.tuple_sketch_int64_get_num_retained_seed(
- `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
- 111
-) from `$BQ_DATASET`.tuple_sketch;
-
-drop table `$BQ_DATASET`.tuple_sketch;
-
-
-# using defaults
-# expected 5
-select `$BQ_DATASET`.tuple_sketch_int64_get_estimate(
- `$BQ_DATASET`.tuple_sketch_int64_union(
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_int64(key, 1) from unnest([1, 2, 3]) as key),
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_int64(key, 1) from unnest([3, 4, 5]) as key)
- )
-);
-
-# using full signatures
-# expected 5
-select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_seed(
- `$BQ_DATASET`.tuple_sketch_int64_union_lgk_seed_mode(
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_int64_lgk_seed_p_mode(key, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest([1, 2, 3]) as key),
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_int64_lgk_seed_p_mode(key, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest([3, 4, 5]) as key),
- 10,
- 111,
- "MIN"
- ),
- 111
-);
-
-# using defaults
-# expected 1
-select `$BQ_DATASET`.tuple_sketch_int64_get_estimate(
- `$BQ_DATASET`.tuple_sketch_int64_intersection(
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["c", "d", "e"]) as str)
- )
-);
-
-# using full signatures
-# expected 1
-select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_seed(
- `$BQ_DATASET`.tuple_sketch_int64_intersection_seed_mode(
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest(["c", "d", "e"]) as str),
- 111,
- "MIN"
- ),
- 111
-);
-
-# using defaults
-# expected 2
-select `$BQ_DATASET`.tuple_sketch_int64_get_estimate(
- `$BQ_DATASET`.tuple_sketch_int64_a_not_b(
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["c", "d", "e"]) as str)
- )
-);
-
-# using full signatures
-# expected 2
-select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_seed(
- `$BQ_DATASET`.tuple_sketch_int64_a_not_b_seed(
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest(["c", "d", "e"]) as str),
- 111
- ),
- 111
-);
-
-# using defaults
-# expected 0.2
-select `$BQ_DATASET`.tuple_sketch_int64_jaccard_similarity(
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["c", "d", "e"]) as str)
-);
-
-# using full signatures
-# expected 0.2
-select `$BQ_DATASET`.tuple_sketch_int64_jaccard_similarity_seed(
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "NOP")) from unnest(["a", "b", "c"]) as str),
- (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "NOP")) from unnest(["c", "d", "e"]) as str),
- 111
-);
-
-# using defaults
-# expected 1 entry
-select `$BQ_DATASET`.tuple_sketch_int64_to_string(
- `$BQ_DATASET`.tuple_sketch_int64_filter_low_high(
- `$BQ_DATASET`.tuple_sketch_int64_agg_string(key, 1),
- 2,
- 2
- )
-) from unnest(["a", "b", "c", "c"]) as key;
-
-# using full signatures
-# expected 1 entry
-select `$BQ_DATASET`.tuple_sketch_int64_to_string_seed(
- `$BQ_DATASET`.tuple_sketch_int64_filter_low_high_seed(
- `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(key, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "SUM")),
- 2,
- 2,
- 111
- ),
- 111
-) from unnest(["a", "b", "c", "c"]) as key;
-```
-
-
-## TDigest Functions
-
-**Description:** Similar to KLL sketch, estimates distributions of numeric values,
-provides approximate quantiles and ranks.
-
-For more information: [t-digest](https://datasketches.apache.org/docs/tdigest/tdigest.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-| [tdigest_double_build](tdigest/sqlx/tdigest_double_build.sqlx) | AGGREGATE | (value FLOAT64) -> BYTES | Creates a sketch that represents the distribution of the given column.<br><br>Param value: the column of FLOAT64 values.<br>Defaults: k = 200.<br>Returns: a t\-Digest, as bytes. |
-| [tdigest_double_merge](tdigest/sqlx/tdigest_double_merge.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Merges sketches from the given column.<br><br>Param sketch: the column of values.<br>Defaults: k = 200.<br>Returns: a serialized t\-Digest as BYTES. |
-| [tdigest_double_merge_k](tdigest/sqlx/tdigest_double_merge_k.sqlx) | AGGREGATE | (sketch BYTES, k INT NOT AGGREGATE) -> BYTES | Merges sketches from the given column.<br><br>Param sketch: the column of values.<br>Param k: the sketch accuracy/size parameter as an integer in the range \[10, 65535\].<br>Returns: a serialized t\-Digest as BYTES. |
-| [tdigest_double_build_k](tdigest/sqlx/tdigest_double_build_k.sqlx) | AGGREGATE | (value FLOAT64, k INT NOT AGGREGATE) -> BYTES | Creates a sketch that represents the distribution of the given column.<br><br>Param value: the column of FLOAT64 values.<br>Param k: the sketch accuracy/size parameter as an INT in the range \[10, 65535\].<br>Returns: a t\-Digest, as bytes. |
-| [tdigest_double_get_max_value](tdigest/sqlx/tdigest_double_get_max_value.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns the maximum value of the input stream.<br><br>Param sketch: the given sketch as BYTES.<br>Returns: max value as FLOAT64 |
-| [tdigest_double_to_string](tdigest/sqlx/tdigest_double_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.<br><br>Param sketch: the given sketch as sketch encoded bytes.<br>Returns: a string that represents the state of the given sketch. |
-| [tdigest_double_get_total_weight](tdigest/sqlx/tdigest_double_get_total_weight.sqlx) | SCALAR | (sketch BYTES) -> INT64 | Returns the total weight of the input stream.<br><br>Param sketch: the given sketch as BYTES.<br>Returns: total weight as INT64 |
-| [tdigest_double_get_min_value](tdigest/sqlx/tdigest_double_get_min_value.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns the minimum value of the input stream.<br><br>Param sketch: the given sketch as BYTES.<br>Returns: min value as FLOAT64 |
-| [tdigest_double_get_rank](tdigest/sqlx/tdigest_double_get_rank.sqlx) | SCALAR | (sketch BYTES, value FLOAT64) -> FLOAT64 | Returns an approximation to the normalized rank, on the interval \[0.0, 1.0\], of the given value.<br><br>Param sketch: the given sketch in serialized form.<br>Param value: value to be ranked.<br>Returns: an approximate rank of the given value. |
-| [tdigest_double_get_quantile](tdigest/sqlx/tdigest_double_get_quantile.sqlx) | SCALAR | (sketch BYTES, rank FLOAT64) -> FLOAT64 | Returns a value from the sketch that is the best approximation to a value from the original stream with the given rank.<br><br>Param sketch: the given sketch in serialized form.<br>Param rank: rank of a value in the hypothetical sorted stream.<br>Returns: an approximate quantile associated with the given rank. |
-
-**Examples:**
-
-```sql
-
-create or replace table `$BQ_DATASET`.tdigest_double(sketch bytes);
-
-# using default
-insert into `$BQ_DATASET`.tdigest_double
-(select `$BQ_DATASET`.tdigest_double_build(value) from unnest([1,2,3,4,5,6,7,8,9,10]) as value);
-
-# using full signature
-insert into `$BQ_DATASET`.tdigest_double
-(select `$BQ_DATASET`.tdigest_double_build_k(value, 100) from unnest([11,12,13,14,15,16,17,18,19,20]) as value);
-
-select `$BQ_DATASET`.tdigest_double_to_string(sketch) from `$BQ_DATASET`.tdigest_double;
-
-# using default
-select `$BQ_DATASET`.tdigest_double_to_string(`$BQ_DATASET`.tdigest_double_merge(sketch)) from `$BQ_DATASET`.tdigest_double;
-
-# using full signature
-select `$BQ_DATASET`.tdigest_double_to_string(`$BQ_DATASET`.tdigest_double_merge_k(sketch, 100)) from `$BQ_DATASET`.tdigest_double;
-
-# expected 0.5
-select `$BQ_DATASET`.tdigest_double_get_rank(`$BQ_DATASET`.tdigest_double_merge(sketch), 10) from `$BQ_DATASET`.tdigest_double;
-
-# expected 10
-select `$BQ_DATASET`.tdigest_double_get_quantile(`$BQ_DATASET`.tdigest_double_merge(sketch), 0.5) from `$BQ_DATASET`.tdigest_double;
-
-# expected 20
-select `$BQ_DATASET`.tdigest_double_get_total_weight(`$BQ_DATASET`.tdigest_double_merge(sketch)) from `$BQ_DATASET`.tdigest_double;
-
-# expected 1
-select `$BQ_DATASET`.tdigest_double_get_min_value(`$BQ_DATASET`.tdigest_double_merge(sketch)) from `$BQ_DATASET`.tdigest_double;
-
-# expected 20
-select `$BQ_DATASET`.tdigest_double_get_max_value(`$BQ_DATASET`.tdigest_double_merge(sketch)) from `$BQ_DATASET`.tdigest_double;
-
-drop table `$BQ_DATASET`.tdigest_double;
-```
-
diff --git a/README_template.md b/README_template.md
deleted file mode 100644
index e7fc2c4..0000000
--- a/README_template.md
+++ /dev/null
@@ -1,158 +0,0 @@
-
-<!--
- Licensed to the Apache Software Foundation (ASF) under one
- or more contributor license agreements. See the NOTICE file
- distributed with this work for additional information
- regarding copyright ownership. The ASF licenses this file
- to you under the Apache License, Version 2.0 (the
- "License"); you may not use this file except in compliance
- with the License. You may obtain a copy of the License at
-
- http://www.apache.org/licenses/LICENSE-2.0
-
- Unless required by applicable law or agreed to in writing,
- software distributed under the License is distributed on an
- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- KIND, either express or implied. See the License for the
- specific language governing permissions and limitations
- under the License.
--->
-
-# Apache DataSketches library functions for Google BigQuery
-
-[User-Defined Aggregate Functions (UDAFs)](https://cloud.google.com/bigquery/docs/user-defined-aggregates) and
-[non-aggregate (scalar) functions (UDFs)](https://cloud.google.com/bigquery/docs/user-defined-functions) for BigQuery SQL engine.
-
-Please visit the main
-[Apache DataSketches website](https://datasketches.apache.org)
-for more information about DataSketches library.
-
-If you are interested in making contributions to this project please see our
-[Community](https://datasketches.apache.org/docs/Community/)
-page for how to contact us.
-
-## Requirements
-
-- Requires [Emscripten (emcc compiler)](https://emscripten.org/)
-- Requires a link to **datasketches-cpp** in this repository
-- Requires make utility
-- Requires [Google Cloud CLI](https://cloud.google.com/sdk/docs/install)
-- Requires npm and @dataform/cli package `npm install -g @dataform/cli`
-
-
-## Building, Installing, and Testing
-
-- Requires setting environment variables
- - JS_BUCKET: to hold compiled artifacts (must include gs://)
- - BQ_PROJECT: location of stored SQL functions (routines)
- - BQ_DATASET: location of stored SQL functions (routines)
- - BQ_LOCATION: location of BQ_DATASET
-
-```
-gcloud auth application-default login # for authentication
-make # performs compilation
-make install # upload to $JS_BUCKET & create functions in $BQ_PROJECT.$BQ_DATASET
-make test # runs predefined tests in BQ
-```
-
-The above steps can be executed in the root directory to install everything, or can be run from an individual sketch directory to install only that particular sketch.
-
-## BigQuery Sketch Functions
-
-DataSketches are probabilistic data structures that can process massive
-amounts of data and return very accurate results with a small memory footprint.
-Because of this, DataSketches are particularly useful for "big data" use cases
-such as streaming analytics and data warehousing.
-
-This package includes BigQuery UD(A)Fs for the following Sketch types:
-
-| Sketch Type | Description |
-|---------------------------------------------------|---|
-| [**Frequent Items Sketch**](#fi-sketch-functions) | Estimates the frequency of items in a stream to find the most frequent ones (e.g., top-selling products, most active users). |
-| [**CPC Sketch**](#cpc-sketch-functions) | A very compact sketch for estimating the number of unique items, especially in distributed environments. |
-| [**HLL Sketch**](#hll-sketch-functions) | Memory-efficient sketch for estimating the number of unique items, optimized for high accuracy. |
-| [**KLL Sketch**](#kll-sketch-functions) | Estimates the distribution of values, allowing you to find quantiles (like median, percentiles) without storing all the data. |
-| [**Theta Sketch**](#theta-sketch-functions) | Estimates unique items and supports set operations (union, intersection, difference) on those items. |
-| [**Tuple Sketch**](#tuple-sketch-functions) | Similar to Theta Sketch but allows associating values with each unique item, enabling operations like sum, min, max on those values. |
-| [**TDigest**](#tdigest-functions) | Another algorithm and very compact data structure for estimating quantiles and ranks of numeric values. |
-
-## CPC Sketch Functions
-
-**Description:** CPC sketches are a compact and efficient way to estimate the
-cardinality of a dataset, especially in distributed environments. They provide
-accurate estimates with low memory usage and are particularly useful for
-applications like counting unique users, analyzing website traffic, or tracking
-distinct events.
-
-For more information: [CPC Sketches](https://datasketches.apache.org/docs/CPC/CpcSketches.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-
-## FI Sketch Functions
-
-**Description:** Frequent Items (FI) sketches are used to estimate the
-frequencies of items in a dataset. They are effective for identifying the most
-frequent items, such as the top products purchased or the most popular search
-queries.
-
-For more information: [Frequency Sketches](https://datasketches.apache.org/docs/Frequency/FrequencySketches.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-
-## HLL Sketch Functions
-
-**Description:** HyperLogLog (HLL) sketches are another type of cardinality
-estimation sketch. They are known for their high accuracy and low memory
-consumption, making them suitable for large datasets and real-time analytics.
-
-For more information: [HLL Sketches](https://datasketches.apache.org/docs/HLL/HllSketches.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-
-## KLL Sketch Functions
-
-**Description:** KLL sketches are quantile sketches that provide approximate
-quantiles for a dataset. They are useful for understanding the distribution of
-data and calculating percentiles, such as the median or 95th percentile.
-
-For more information: [KLL Sketches](https://datasketches.apache.org/docs/KLL/KLLSketch.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-
-## Theta Sketch Functions
-
-**Description:** Theta sketches are used for set operations like union,
-intersection, and difference. They are efficient for estimating the size of
-these operations on large datasets, enabling applications like analyzing user
-overlap or comparing different groups.
-
-For more information: [Theta sketches](https://datasketches.apache.org/docs/Theta/ThetaSketches.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-
-## Tuple Sketch Functions
-
-**Description:** Tuple sketches extend the functionality of Theta sketches by
-allowing you to associate a summary value with each item in the set. This
-enables calculations like the sum, minimum, or maximum of values associated with
-the distinct items.
-
-For more information: [Tuple sketches](https://datasketches.apache.org/docs/Tuple/TupleSketches.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
-
-## TDigest Functions
-
-**Description:** Similar to KLL sketch, estimates distributions of numeric values,
-provides approximate quantiles and ranks.
-
-For more information: [t-digest](https://datasketches.apache.org/docs/tdigest/tdigest.html)
-
-| Function Name | Function Type | Signature | Description |
-|---|---|---|---|
diff --git a/cpc/README.md b/cpc/README.md
index b740232..adb5a91 100644
--- a/cpc/README.md
+++ b/cpc/README.md
@@ -17,7 +17,13 @@
under the License.
-->
-# Apache DataSketches CPC Sketches for GCP/BigQuery
+# Apache DataSketches CPC Sketches for Google BigQuery
+
+CPC sketches are a compact and efficient way to estimate the
+cardinality of a dataset, especially in distributed environments. They provide
+accurate estimates with low memory usage and are particularly useful for
+applications like counting unique users, analyzing website traffic, or tracking
+distinct events.
Please visit
[CPC Sketches](https://datasketches.apache.org/docs/CPC/CpcSketches.html)
@@ -31,6 +37,91 @@ If you are interested in making contributions to this project please see our
[Community](https://datasketches.apache.org/docs/Community/)
page for how to contact us.
+| Function Name | Function Type | Signature | Description |
+|---|---|---|---|
+| [cpc_sketch_agg_union](cpc_sketch_agg_union.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Creates a sketch that represents the union of the given column of sketches.\<br\>\<br\>Param sketch: the column of sketches. Each as BYTES.\<br\>Defaults: lg\_k = 12, seed = 9001.\<br\>Returns: a Compact, Compressed CPC Sketch, as BYTES. |
+| [cpc_sketch_agg_string](cpc_sketch_agg_string.sqlx) | AGGREGATE | (str STRING) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.\<br\>\<br\>Param str: the STRING column of identifiers.\<br\>Defaults: lg\_k = 12, seed = 9001.\<br\>Returns: a Compact, Compressed CPC Sketch, as BYTES |
+| [cpc_sketch_agg_int64](cpc_sketch_agg_int64.sqlx) | AGGREGATE | (value INT64) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.\<br\>\<br\>Param value: the INT64 column of identifiers.\<br\>Defaults: lg\_k = 12, seed = 9001.\<br\>Returns: a Compact, Compressed CPC Sketch, as BYTES |
+| [cpc_sketch_agg_string_lgk_seed](cpc_sketch_agg_string_lgk_seed.sqlx) | AGGREGATE | (str STRING, params STRUCT<lg_k BYTEINT, seed INT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.\<br\>\<br\>Param str: the STRING column of identifiers.\<br\>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 26\].\<br\>Param seed: the seed to be used by the underlying hash function.\<br\>Returns: a Compact, Compress [...]
+| [cpc_sketch_agg_union_lgk_seed](cpc_sketch_agg_union_lgk_seed.sqlx) | AGGREGATE | (sketch BYTES, params STRUCT<lg_k BYTEINT, seed INT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the union of the given column of sketches.\<br\>\<br\>Param sketch: the column of sketches. Each as BYTES.\<br\>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 26\].\<br\>Param seed: This is used to confirm that the given sketches were configured with the correc [...]
+| [cpc_sketch_agg_int64_lgk_seed](cpc_sketch_agg_int64_lgk_seed.sqlx) | AGGREGATE | (value INT64, params STRUCT<lg_k BYTEINT, seed INT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.\<br\>\<br\>Param value: the INT64 column of identifiers.\<br\>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 26\].\<br\>Param seed: the seed to be used by the underlying hash function.\<br\>Returns: a Compact, Compresse [...]
+| [cpc_sketch_get_estimate](cpc_sketch_get_estimate.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Gets cardinality estimate and bounds from given sketch.\<br\>\<br\>Param sketch: The given sketch to query as BYTES.\<br\>Defaults: seed = 9001.\<br\>Returns: a FLOAT64 value as the cardinality estimate. |
+| [cpc_sketch_to_string](cpc_sketch_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.\<br\>\<br\>Param sketch the given sketch as BYTES.\<br\>Defaults: seed = 9001.\<br\>Returns: a STRING that represents the state of the given sketch. |
+| [cpc_sketch_get_estimate_seed](cpc_sketch_get_estimate_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> FLOAT64 | Gets cardinality estimate and bounds from given sketch.\<br\>\<br\>Param sketch: The given sketch to query as BYTES.\<br\>Param seed: This is used to confirm that the given sketch was configured with the correct seed.\<br\>Returns: a FLOAT64 value as the cardinality estimate. |
+| [cpc_sketch_to_string_seed](cpc_sketch_to_string_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> STRING | Returns a summary string that represents the state of the given sketch.\<br\>\<br\>Param sketch the given sketch as BYTES.\<br\>Param seed: This is used to confirm that the given sketch was configured with the correct seed.\<br\>Returns: a STRING that represents the state of the given sketch. |
+| [cpc_sketch_union](cpc_sketch_union.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the scalar union of the two given sketches.\<br\>\<br\>Param sketchA: the first sketch as BYTES.\<br\>Param sketchB: the second sketch as BYTES.\<br\>Defaults: lg\_k = 12, seed = 9001.\<br\>Returns: a CPC Sketch, as BYTES. |
+| [cpc_sketch_get_estimate_and_bounds](cpc_sketch_get_estimate_and_bounds.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Gets cardinality estimate and bounds from given sketch.\<br\> \<br\>Param sketch: The given sketch to query as bytes.\<br\>Param num\_std\_devs: The returned bounds will be based on the statistical confidence interval determined by the given number of standard deviations\<br\> from the ret [...]
+| [cpc_sketch_union_lgk_seed](cpc_sketch_union_lgk_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, lg_k BYTEINT, seed INT64) -> BYTES | Computes a sketch that represents the scalar union of the two given sketches.\<br\>\<br\>Param sketchA: the first sketch as BYTES.\<br\>Param sketchB: the second sketch as BYTES.\<br\>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 26\].\<br\>Param seed: This is used to confirm that the given sketches were configured [...]
+| [cpc_sketch_get_estimate_and_bounds_seed](cpc_sketch_get_estimate_and_bounds_seed.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT, seed INT64) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Gets cardinality estimate and bounds from given sketch.\<br\> \<br\>Param sketch: The given sketch to query as bytes.\<br\>Param num\_std\_devs: The returned bounds will be based on the statistical confidence interval determined by the given number of standard deviatio [...]
+
+**Examples:**
+
+```sql
+
+# using defaults
+# expected 5
+select `$BQ_DATASET`.cpc_sketch_get_estimate(
+ `$BQ_DATASET`.cpc_sketch_union(
+ (select `$BQ_DATASET`.cpc_sketch_agg_string(str) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.cpc_sketch_agg_string(str) from unnest(["c", "d", "e"]) as str)
+ )
+);
+
+# using full signatures
+# expected 5
+select `$BQ_DATASET`.cpc_sketch_get_estimate_seed(
+ `$BQ_DATASET`.cpc_sketch_union_lgk_seed(
+ (select `$BQ_DATASET`.cpc_sketch_agg_string_lgk_seed(str, struct<byteint, int64>(10, 111)) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.cpc_sketch_agg_string_lgk_seed(str, struct<byteint, int64>(10, 111)) from unnest(["c", "d", "e"]) as str),
+ 10,
+ 111
+ ),
+ 111
+);
+
+# using defaults
+create or replace table `$BQ_DATASET`.cpc_sketch(sketch bytes);
+
+insert into `$BQ_DATASET`.cpc_sketch
+(select `$BQ_DATASET`.cpc_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
+insert into `$BQ_DATASET`.cpc_sketch
+(select `$BQ_DATASET`.cpc_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
+
+select `$BQ_DATASET`.cpc_sketch_to_string(sketch) from `$BQ_DATASET`.cpc_sketch;
+
+# expected about 20000
+select `$BQ_DATASET`.cpc_sketch_get_estimate(
+ `$BQ_DATASET`.cpc_sketch_agg_union(sketch)
+) from `$BQ_DATASET`.cpc_sketch;
+
+select `$BQ_DATASET`.cpc_sketch_get_estimate_and_bounds(
+ `$BQ_DATASET`.cpc_sketch_agg_union(sketch),
+ 3
+) from `$BQ_DATASET`.cpc_sketch;
+
+drop table `$BQ_DATASET`.cpc_sketch;
+
+# using full signatures
+create or replace table `$BQ_DATASET`.cpc_sketch(sketch bytes);
+
+insert into `$BQ_DATASET`.cpc_sketch
+(select `$BQ_DATASET`.cpc_sketch_agg_int64_lgk_seed(value, struct<byteint, int64>(10, 111)) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
+insert into `$BQ_DATASET`.cpc_sketch
+(select `$BQ_DATASET`.cpc_sketch_agg_int64_lgk_seed(value, struct<byteint, int64>(10, 111)) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
+
+select `$BQ_DATASET`.cpc_sketch_to_string_seed(sketch, 111) from `$BQ_DATASET`.cpc_sketch;
+# expected about 20000
+select `$BQ_DATASET`.cpc_sketch_get_estimate_seed(
+ `$BQ_DATASET`.cpc_sketch_agg_union_lgk_seed(sketch, struct<byteint, int64>(10, 111)),
+ 111
+) from `$BQ_DATASET`.cpc_sketch;
+select `$BQ_DATASET`.cpc_sketch_get_estimate_and_bounds_seed(
+ `$BQ_DATASET`.cpc_sketch_agg_union_lgk_seed(sketch, struct<byteint, int64>(10, 111)),
+ 3,
+ 111
+) from `$BQ_DATASET`.cpc_sketch;
+drop table `$BQ_DATASET`.cpc_sketch;
+```
diff --git a/cpc/README.md b/cpc/README_template.md
similarity index 77%
copy from cpc/README.md
copy to cpc/README_template.md
index b740232..8c71cc4 100644
--- a/cpc/README.md
+++ b/cpc/README_template.md
@@ -17,7 +17,13 @@
under the License.
-->
-# Apache DataSketches CPC Sketches for GCP/BigQuery
+# Apache DataSketches CPC Sketches for Google BigQuery
+
+CPC sketches are a compact and efficient way to estimate the
+cardinality of a dataset, especially in distributed environments. They provide
+accurate estimates with low memory usage and are particularly useful for
+applications like counting unique users, analyzing website traffic, or tracking
+distinct events.
Please visit
[CPC Sketches](https://datasketches.apache.org/docs/CPC/CpcSketches.html)
@@ -30,7 +36,3 @@ for more information about DataSketches library.
If you are interested in making contributions to this project please see our
[Community](https://datasketches.apache.org/docs/Community/)
page for how to contact us.
-
-
-
-
diff --git a/fi/README.md b/fi/README.md
index 54780bb..dcbbabf 100644
--- a/fi/README.md
+++ b/fi/README.md
@@ -19,6 +19,11 @@
# Apache DataSketches Frequent Items Sketches for Google BigQuery
+Frequent Items (FI) sketches are used to estimate the
+frequencies of items in a dataset. They are effective for identifying the most
+frequent items, such as the top products purchased or the most popular search
+queries.
+
Please visit
[Frequent Items Sketches](https://datasketches.apache.org/docs/Frequency/FrequencySketches.html)
for more information about this sketch family.
@@ -30,3 +35,29 @@ for more information about DataSketches library.
If you are interested in making contributions to this project please see our
[Community](https://datasketches.apache.org/docs/Community/)
page for how to contact us.
+
+| Function Name | Function Type | Signature | Description |
+|---|---|---|---|
+| [frequent_strings_sketch_merge](frequent_strings_sketch_merge.sqlx) | AGGREGATE | (sketch BYTES, lg_max_map_size BYTEINT NOT AGGREGATE) -> BYTES | Merges sketches from the given column.\<br\>\<br\>Param sketch: the column of values.\<br\>Param lg\_max\_map\_size: the sketch accuracy/size parameter as an integer not less than 3.\<br\>Returns: a serialized Frequent Strings sketch as BYTES. |
+| [frequent_strings_sketch_build](frequent_strings_sketch_build.sqlx) | AGGREGATE | (item STRING, weight INT64, lg_max_map_size BYTEINT NOT AGGREGATE) -> BYTES | Creates a sketch that represents frequencies of the given column.\<br\>\<br\>Param item: the column of STRING values.\<br\>Param weight: the amount by which the weight of the item should be increased.\<br\>Param lg\_max\_map\_size: the sketch accuracy/size parameter as a BYTEINT not less than 3.\<br\>Returns: a Frequent Strings [...]
+| [frequent_strings_sketch_to_string](frequent_strings_sketch_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.\<br\>\<br\>Param sketch: the given sketch as sketch encoded bytes.\<br\>Returns: a string that represents the state of the given sketch. |
+| [frequent_strings_sketch_get_result](frequent_strings_sketch_get_result.sqlx) | SCALAR | (sketch BYTES, error_type STRING, threshold INT64) -> ARRAY<STRUCT<item STRING, estimate INT64, lower_bound INT64, upper_bound INT64>> | Returns an array of rows that include frequent items, estimates, lower and upper bounds\<br\>given an error\_type and a threshold.\<br\>\<br\>Param sketch: the given sketch as sketch encoded bytes.\<br\>Param error\_type: determines whether no false positives or n [...]
+
+**Examples:**
+
+```sql
+
+select `$BQ_DATASET`.frequent_strings_sketch_to_string(`$BQ_DATASET`.frequent_strings_sketch_build(str, 1, 5)) from unnest(["a", "b", "c"]) as str;
+
+create or replace table `$BQ_DATASET`.fs_sketch(sketch bytes);
+
+insert into `$BQ_DATASET`.fs_sketch
+(select `$BQ_DATASET`.frequent_strings_sketch_build(str, 1, 5) from unnest(["a", "b", "c", "d"]) as str);
+
+insert into `$BQ_DATASET`.fs_sketch
+(select `$BQ_DATASET`.frequent_strings_sketch_build(str, 1, 5) from unnest(["a", "a", "c"]) as str);
+
+select `$BQ_DATASET`.frequent_strings_sketch_get_result(`$BQ_DATASET`.frequent_strings_sketch_merge(sketch, 5), "NO_FALSE_NEGATIVES", null) from `$BQ_DATASET`.fs_sketch;
+
+drop table `$BQ_DATASET`.fs_sketch;
+```
diff --git a/fi/README.md b/fi/README_template.md
similarity index 85%
copy from fi/README.md
copy to fi/README_template.md
index 54780bb..11f7ae6 100644
--- a/fi/README.md
+++ b/fi/README_template.md
@@ -19,6 +19,11 @@
# Apache DataSketches Frequent Items Sketches for Google BigQuery
+Frequent Items (FI) sketches are used to estimate the
+frequencies of items in a dataset. They are effective for identifying the most
+frequent items, such as the top products purchased or the most popular search
+queries.
+
Please visit
[Frequent Items Sketches](https://datasketches.apache.org/docs/Frequency/FrequencySketches.html)
for more information about this sketch family.
diff --git a/hll/README.md b/hll/README.md
index 6cdb836..6451854 100644
--- a/hll/README.md
+++ b/hll/README.md
@@ -17,7 +17,11 @@
under the License.
-->
-# Apache DataSketches HLL Sketches for GCP/BigQuery
+# Apache DataSketches HLL Sketches for Google BigQuery
+
+HyperLogLog (HLL) sketches are another type of cardinality
+estimation sketch. They are known for their high accuracy and low memory
+consumption, making them suitable for large datasets and real-time analytics.
Please visit
[HLL Sketches](https://datasketches.apache.org/docs/HLL/HllSketches.html)
@@ -30,3 +34,79 @@ for more information about DataSketches library.
If you are interested in making contributions to this project please see our
[Community](https://datasketches.apache.org/docs/Community/)
page for how to contact us.
+
+| Function Name | Function Type | Signature | Description |
+|---|---|---|---|
+| [hll_sketch_agg_string](hll_sketch_agg_string.sqlx) | AGGREGATE | (str STRING) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.\<br\>\<br\>Param str: the STRING column of identifiers.\<br\>Defaults: lg\_k = 12, tgt\_type = HLL\_4.\<br\>Returns: an HLL Sketch, as BYTES. |
+| [hll_sketch_agg_union](hll_sketch_agg_union.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Creates a sketch that represents the union of the given column of sketches.\<br\>\<br\>Param sketch: the column of sketches. Each as BYTES.\<br\>Defaults: lg\_k = 12, tgt\_type = HLL\_4.\<br\>Returns: an HLL Sketch, as BYTES. |
+| [hll_sketch_agg_int64](hll_sketch_agg_int64.sqlx) | AGGREGATE | (value INT64) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.\<br\>\<br\>Param value: the INT64 column of identifiers.\<br\>Defaults: lg\_k = 12, tgt\_type = HLL\_4.\<br\>Returns: an HLL Sketch, as BYTES. |
+| [hll_sketch_agg_string_lgk_type](hll_sketch_agg_string_lgk_type.sqlx) | AGGREGATE | (str STRING, params STRUCT<lg_k BYTEINT, tgt_type STRING> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.\<br\>\<br\>Param str: the STRING column of identifiers.\<br\>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 21\].\<br\>Param tgt\_type: The HLL type to use: one of {"HLL\_4", "HLL\_6", "HLL\_8"}.\<br\>Returns: an [...]
+| [hll_sketch_agg_union_lgk_type](hll_sketch_agg_union_lgk_type.sqlx) | AGGREGATE | (sketch BYTES, params STRUCT<lg_k BYTEINT, tgt_type STRING> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the union of the given column of sketches.\<br\>\<br\>Param sketch: the column of sketches. Each as BYTES.\<br\>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 21\].\<br\>Param tgt\_type: The HLL type to use: one of {"HLL\_4", "HLL\_6", "HLL\_8"}.\<br\>Retu [...]
+| [hll_sketch_agg_int64_lgk_type](hll_sketch_agg_int64_lgk_type.sqlx) | AGGREGATE | (value INT64, params STRUCT<lg_k BYTEINT, tgt_type STRING> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.\<br\>\<br\>Param value: the INT64 column of identifiers.\<br\>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 21\].\<br\>Param tgt\_type: The HLL type to use: one of {"HLL\_4", "HLL\_6", "HLL\_8"}.\<br\>Returns: an [...]
+| [hll_sketch_get_estimate](hll_sketch_get_estimate.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns a summary string that represents the state of the given sketch.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Returns: the cardinality estimate as FLOAT64 value. |
+| [hll_sketch_to_string](hll_sketch_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Returns: a STRING that represents the state of the given sketch. |
+| [hll_sketch_union](hll_sketch_union.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the union of the two given sketches.\<br\>\<br\>Param sketchA: the first sketch as bytes.\<br\>Param sketchB: the second sketch as bytes.\<br\>Defaults: lg\_k = 12, tgt\_type = HLL\_4.\<br\>Returns: an HLL Sketch, as BYTES. |
+| [hll_sketch_union_lgk_type](hll_sketch_union_lgk_type.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, lg_k BYTEINT, tgt_type STRING) -> BYTES | Computes a sketch that represents the union of the two given sketches.\<br\>\<br\>Param sketchA: the first sketch as bytes.\<br\>Param sketchB: the second sketch as bytes.\<br\>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 21\].\<br\>Param tgt\_type: The HLL type to use: one of {"HLL\_4", "HLL\_6", "HLL\_8"}.\< [...]
+| [hll_sketch_get_estimate_and_bounds](hll_sketch_get_estimate_and_bounds.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Gets cardinality estimate and bounds from given sketch.\<br\>\<br\>Param sketch: The given sketch to query as BYTES.\<br\>Param num\_std\_devs: The returned bounds will be based on the statistical confidence interval determined by the given number of standard deviations\<br\> from the retur [...]
+
+**Examples:**
+
+```sql
+
+# expected 3
+select `$BQ_DATASET`.hll_sketch_get_estimate(`$BQ_DATASET`.hll_sketch_agg_string(s)) from unnest(["a", "b", "c"]) as s;
+
+select `$BQ_DATASET`.hll_sketch_to_string(`$BQ_DATASET`.hll_sketch_agg_string(s)) from unnest(["a", "b", "c"]) as s;
+
+# expected 5
+select `$BQ_DATASET`.hll_sketch_get_estimate_and_bounds(
+ `$BQ_DATASET`.hll_sketch_union_lgk_type(
+ (select `$BQ_DATASET`.hll_sketch_agg_string_lgk_type(str, struct<byteint, string>(10, "HLL_8")) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.hll_sketch_agg_string_lgk_type(str, struct<byteint, string>(10, "HLL_8")) from unnest(["c", "d", "e"]) as str),
+ 10,
+ "HLL_8"
+ ),
+ 2
+);
+
+select `$BQ_DATASET`.hll_sketch_to_string(
+ `$BQ_DATASET`.hll_sketch_union_lgk_type(
+ (select `$BQ_DATASET`.hll_sketch_agg_string_lgk_type(str, struct<byteint, string>(10, "HLL_8")) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.hll_sketch_agg_string_lgk_type(str, struct<byteint, string>(10, "HLL_8")) from unnest(["c", "d", "e"]) as str),
+ 10,
+ "HLL_8"
+ )
+);
+
+create or replace table `$BQ_DATASET`.hll_sketch(sketch bytes);
+
+insert into `$BQ_DATASET`.hll_sketch
+(select `$BQ_DATASET`.hll_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
+insert into `$BQ_DATASET`.hll_sketch
+(select `$BQ_DATASET`.hll_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
+
+# expected estimate about 20000
+select `$BQ_DATASET`.hll_sketch_to_string(
+ `$BQ_DATASET`.hll_sketch_agg_union(sketch)
+) from `$BQ_DATASET`.hll_sketch;
+
+select `$BQ_DATASET`.hll_sketch_to_string(
+ `$BQ_DATASET`.hll_sketch_agg_union_lgk_type(sketch, struct<byteint, string>(10, "HLL_6"))
+) from `$BQ_DATASET`.hll_sketch;
+
+drop table `$BQ_DATASET`.hll_sketch;
+
+create or replace table `$BQ_DATASET`.hll_sketch(sketch bytes);
+
+insert into `$BQ_DATASET`.hll_sketch
+(select `$BQ_DATASET`.hll_sketch_agg_int64_lgk_type(value, struct<int, string>(8, "HLL_6")) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
+insert into `$BQ_DATASET`.hll_sketch
+(select `$BQ_DATASET`.hll_sketch_agg_int64_lgk_type(value, struct<int, string>(8, "HLL_6")) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
+
+# expected estimate about 20000
+select `$BQ_DATASET`.hll_sketch_to_string(
+ `$BQ_DATASET`.hll_sketch_agg_union_lgk_type(sketch, struct<byteint, string>(8, "HLL_6"))
+) from `$BQ_DATASET`.hll_sketch;
+
+drop table `$BQ_DATASET`.hll_sketch;
+```
diff --git a/hll/README.md b/hll/README_template.md
similarity index 82%
copy from hll/README.md
copy to hll/README_template.md
index 6cdb836..15b6e69 100644
--- a/hll/README.md
+++ b/hll/README_template.md
@@ -17,7 +17,11 @@
under the License.
-->
-# Apache DataSketches HLL Sketches for GCP/BigQuery
+# Apache DataSketches HLL Sketches for Google BigQuery
+
+HyperLogLog (HLL) sketches are another type of cardinality
+estimation sketch. They are known for their high accuracy and low memory
+consumption, making them suitable for large datasets and real-time analytics.
Please visit
[HLL Sketches](https://datasketches.apache.org/docs/HLL/HllSketches.html)
diff --git a/kll/README.md b/kll/README.md
index 68816bc..f4df0f0 100644
--- a/kll/README.md
+++ b/kll/README.md
@@ -17,7 +17,11 @@
under the License.
-->
-# Apache DataSketches KLL Sketches for GCP/BigQuery
+# Apache DataSketches KLL Sketches for Google BigQuery
+
+KLL sketches are quantile sketches that provide approximate quantiles
+and ranks for a dataset. They are useful for understanding the distribution of
+data and calculating percentiles, such as the median or 95th percentile.
Please visit
[KLL Sketches](https://datasketches.apache.org/docs/KLL/KLLSketch.html)
@@ -30,3 +34,86 @@ for more information about DataSketches library.
If you are interested in making contributions to this project please see our
[Community](https://datasketches.apache.org/docs/Community/)
page for how to contact us.
+
+| Function Name | Function Type | Signature | Description |
+|---|---|---|---|
+| [kll_sketch_float_build](kll_sketch_float_build.sqlx) | AGGREGATE | (value FLOAT64) -> BYTES | Creates a sketch that represents the distribution of the given column.\<br\>\<br\>Param value: the column of FLOAT64 values.\<br\>Defaults: k = 200.\<br\>Returns: a KLL Sketch, as bytes. |
+| [kll_sketch_float_merge](kll_sketch_float_merge.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Merges sketches from the given column.\<br\>\<br\>Param sketch: the column of values.\<br\>Defaluts: k = 200.\<br\>Returns: a serialized KLL sketch as BYTES. |
+| [kll_sketch_float_merge_k](kll_sketch_float_merge_k.sqlx) | AGGREGATE | (sketch BYTES, k INT NOT AGGREGATE) -> BYTES | Merges sketches from the given column.\<br\>\<br\>Param sketch: the column of values.\<br\>Param k: the sketch accuracy/size parameter as an integer in the range \[8, 65535\].\<br\>Returns: a serialized KLL sketch as BYTES. |
+| [kll_sketch_float_build_k](kll_sketch_float_build_k.sqlx) | AGGREGATE | (value FLOAT64, k INT NOT AGGREGATE) -> BYTES | Creates a sketch that represents the distribution of the given column.\<br\>\<br\>Param value: the column of FLOAT64 values.\<br\>Param k: the sketch accuracy/size parameter as an INT in the range \[8, 65535\].\<br\>Returns: a KLL Sketch, as bytes. |
+| [kll_sketch_float_get_n](kll_sketch_float_get_n.sqlx) | SCALAR | (sketch BYTES) -> INT64 | Returns the length of the input stream.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Returns: stream length as INT64 |
+| [kll_sketch_float_get_min_value](kll_sketch_float_get_min_value.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns the minimum value of the input stream.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Returns: min value as FLOAT64 |
+| [kll_sketch_float_to_string](kll_sketch_float_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.\<br\>\<br\>Param sketch: the given sketch as sketch encoded bytes.\<br\>Returns: a string that represents the state of the given sketch. |
+| [kll_sketch_float_get_num_retained](kll_sketch_float_get_num_retained.sqlx) | SCALAR | (sketch BYTES) -> INT64 | Returns the number of retained items \(samples\) in the sketch.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Returns: number of retained items as INT64 |
+| [kll_sketch_float_get_max_value](kll_sketch_float_get_max_value.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns the maximum value of the input stream.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Returns: max value as FLOAT64 |
+| [kll_sketch_float_get_normalized_rank_error](kll_sketch_float_get_normalized_rank_error.sqlx) | SCALAR | (sketch BYTES, pmf BOOL) -> FLOAT64 | Returns the approximate rank error of the given sketch normalized as a fraction between zero and one.\<br\>Param sketch: the given sketch as BYTES.\<br\>Param pmf: if true, returns the "double\-sided" normalized rank error for the get\_PMF\(\) function.\<br\>Otherwise, it is the "single\-sided" normalized rank error for all the other queries.\<b [...]
+| [kll_sketch_float_get_rank](kll_sketch_float_get_rank.sqlx) | SCALAR | (sketch BYTES, value FLOAT64, inclusive BOOL) -> FLOAT64 | Returns an approximation to the normalized rank, on the interval \[0.0, 1.0\], of the given value.\<br\>\<br\>Param sketch: the given sketch in serialized form.\<br\>Param value: value to be ranked.\<br\>Param inclusive: if true the weight of the given value is included into the rank.\<br\>Returns: an approximate rank of the given value. |
+| [kll_sketch_float_get_pmf](kll_sketch_float_get_pmf.sqlx) | SCALAR | (sketch BYTES, split_points ARRAY<FLOAT64>, inclusive BOOL) -> ARRAY<FLOAT64> | Returns an approximation to the Probability Mass Function \(PMF\)\<br\>of the input stream as an array of probability masses defined by the given split\_points.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>\<br\>Param split\_points: an array of M unique, monotonically increasing values \<br\> \(of the same type as the input va [...]
+| [kll_sketch_float_kolmogorov_smirnov](kll_sketch_float_kolmogorov_smirnov.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, pvalue FLOAT64) -> BOOL | Performs the Kolmogorov\-Smirnov Test between two KLL sketches of type FLOAT64.\<br\>If the given sketches have insufficient data or if the sketch sizes are too small, this will return false.\<br\>\<br\>Param sketchA: sketch A in serialized form.\<br\>Param sketchB: sketch B in serialized form.\<br\>Param pvalue: Target p\-value. Typically [...]
+| [kll_sketch_float_get_cdf](kll_sketch_float_get_cdf.sqlx) | SCALAR | (sketch BYTES, split_points ARRAY<FLOAT64>, inclusive BOOL) -> ARRAY<FLOAT64> | Returns an approximation to the Cumulative Distribution Function \(CDF\) \<br\>of the input stream as an array of cumulative probabilities defined by the given split\_points.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>\<br\>Param split\_points: an array of M unique, monotonically increasing values\<br\> \(of the same type as [...]
+| [kll_sketch_float_get_quantile](kll_sketch_float_get_quantile.sqlx) | SCALAR | (sketch BYTES, rank FLOAT64, inclusive BOOL) -> FLOAT64 | Returns a value from the sketch that is the best approximation to a value from the original stream with the given rank.\<br\>\<br\>Param sketch: the given sketch in serialized form.\<br\>Param rank: rank of a value in the hypothetical sorted stream.\<br\>Param inclusive: if true, the given rank is considered inclusive \(includes weight of a value\)\<b [...]
+
+**Examples:**
+
+```sql
+
+create or replace table `$BQ_DATASET`.kll_sketch(sketch bytes);
+
+# using default
+insert into `$BQ_DATASET`.kll_sketch
+(select `$BQ_DATASET`.kll_sketch_float_build(value) from unnest([1,2,3,4,5,6,7,8,9,10]) as value);
+
+# using full signature
+insert into `$BQ_DATASET`.kll_sketch
+(select `$BQ_DATASET`.kll_sketch_float_build_k(value, 100) from unnest([11,12,13,14,15,16,17,18,19,20]) as value);
+
+select `$BQ_DATASET`.kll_sketch_float_to_string(sketch) from `$BQ_DATASET`.kll_sketch;
+
+# using default
+select `$BQ_DATASET`.kll_sketch_float_to_string(`$BQ_DATASET`.kll_sketch_float_merge(sketch)) from `$BQ_DATASET`.kll_sketch;
+
+# using full signature
+select `$BQ_DATASET`.kll_sketch_float_to_string(`$BQ_DATASET`.kll_sketch_float_merge_k(sketch, 100)) from `$BQ_DATASET`.kll_sketch;
+
+# expected 0.5
+select `$BQ_DATASET`.kll_sketch_float_get_rank(`$BQ_DATASET`.kll_sketch_float_merge(sketch), 10, true) from `$BQ_DATASET`.kll_sketch;
+
+# expected 10
+select `$BQ_DATASET`.kll_sketch_float_get_quantile(`$BQ_DATASET`.kll_sketch_float_merge(sketch), 0.5, true) from `$BQ_DATASET`.kll_sketch;
+
+# expected 20
+select `$BQ_DATASET`.kll_sketch_float_get_n(`$BQ_DATASET`.kll_sketch_float_merge(sketch)) from `$BQ_DATASET`.kll_sketch;
+
+# expected 0.5, 0.5
+select `$BQ_DATASET`.kll_sketch_float_get_pmf(`$BQ_DATASET`.kll_sketch_float_merge(sketch), [10.0], true) from `$BQ_DATASET`.kll_sketch;
+
+# expected 0.5, 1
+select `$BQ_DATASET`.kll_sketch_float_get_cdf(`$BQ_DATASET`.kll_sketch_float_merge(sketch), [10.0], true) from `$BQ_DATASET`.kll_sketch;
+
+# expected 1
+select `$BQ_DATASET`.kll_sketch_float_get_min_value(`$BQ_DATASET`.kll_sketch_float_merge(sketch)) from `$BQ_DATASET`.kll_sketch;
+
+# expected 20
+select `$BQ_DATASET`.kll_sketch_float_get_max_value(`$BQ_DATASET`.kll_sketch_float_merge(sketch)) from `$BQ_DATASET`.kll_sketch;
+
+drop table `$BQ_DATASET`.kll_sketch;
+
+# expected about 1.3%
+select `$BQ_DATASET`.kll_sketch_float_get_normalized_rank_error(`$BQ_DATASET`.kll_sketch_float_build(value), false) from unnest(generate_array(1, 10000)) as value;
+
+select `$BQ_DATASET`.kll_sketch_float_get_num_retained(`$BQ_DATASET`.kll_sketch_float_build(value)) from unnest(generate_array(1, 10000)) as value;
+
+# expected false
+select `$BQ_DATASET`.kll_sketch_float_kolmogorov_smirnov(
+ (select `$BQ_DATASET`.kll_sketch_float_build(value) from unnest([1,2,3,4,5,6,7,8,9,10]) as value),
+ (select `$BQ_DATASET`.kll_sketch_float_build(value) from unnest([1,2,3,4,5,6,7,8,9,10]) as value),
+ 0.05
+);
+
+# expected true
+select `$BQ_DATASET`.kll_sketch_float_kolmogorov_smirnov(
+ (select `$BQ_DATASET`.kll_sketch_float_build(value) from unnest([1,2,3,4,5,6,7,8,9,10]) as value),
+ (select `$BQ_DATASET`.kll_sketch_float_build(value) from unnest([11,12,13,14,15,16,17,18,19,20]) as value),
+ 0.05
+);
+```
diff --git a/kll/README.md b/kll/README_template.md
similarity index 81%
copy from kll/README.md
copy to kll/README_template.md
index 68816bc..d963754 100644
--- a/kll/README.md
+++ b/kll/README_template.md
@@ -17,7 +17,11 @@
under the License.
-->
-# Apache DataSketches KLL Sketches for GCP/BigQuery
+# Apache DataSketches KLL Sketches for Google BigQuery
+
+KLL sketches are quantile sketches that provide approximate quantiles
+and ranks for a dataset. They are useful for understanding the distribution of
+data and calculating percentiles, such as the median or 95th percentile.
Please visit
[KLL Sketches](https://datasketches.apache.org/docs/KLL/KLLSketch.html)
diff --git a/readme_generator.py b/readme_generator.py
index 795d26e..929c0c7 100644
--- a/readme_generator.py
+++ b/readme_generator.py
@@ -46,7 +46,6 @@ def escape_markdown(text):
text = text.replace(char, escaped)
return text
-
# Function to parse a single SQLX file and extract relevant details
def parse_sqlx(file_content: str, filename: str) -> dict:
logging.info(f"Parsing file: {filename}")
@@ -72,11 +71,9 @@ def parse_sqlx(file_content: str, filename: str) -> dict:
# Extract description
description_match = description_pattern.search(file_content)
description = description_match.group(1).strip() if description_match else "No description available"
-
- # Format multiline descriptions for Markdown and escape them
- description = escape_markdown(description)
description = re.compile(r'\n*For more info.*', re.M | re.S).sub('', description) # remove repetitive links
- description = description.replace('\n', '<br>') # Replace newlines with <br> tags
+ description = description.replace('\n', '<br>')
+ description = escape_markdown(description)
# Extract function arguments and their types
arg_list = []
@@ -97,14 +94,13 @@ def parse_sqlx(file_content: str, filename: str) -> dict:
}
# Function to walk through directories, parse SQLX files, and collect data for README
-def process_folder(input_folder: str) -> dict:
+def process_folder(input_folder: str, sketch_type: str) -> dict:
function_index = defaultdict(list)
for root, dirs, files in os.walk(input_folder):
for file in files:
if file.endswith(".sqlx"):
sqlx_path = os.path.join(root, file)
- sketch_type = root.split('/')[-2] # Get the second to last directory name
logging.info(f"Processing file: {sqlx_path}")
@@ -124,76 +120,59 @@ def process_folder(input_folder: str) -> dict:
'description': parsed_data['description'],
'path': relative_path # Store relative path for linking
})
-
return function_index
# Function to generate README content based on the template
-def generate_readme(template_path: str, function_index: dict, input_folder: str) -> str:
+def generate_readme(template_path: str, function_index: dict, examples_path: str) -> str:
# Read the template file
with open(template_path, 'r') as template_file:
- template_lines = template_file.readlines()
-
- # Populate the README content
- output_lines = []
- current_section = None
-
- for line in template_lines:
- if line.startswith("## "): # Start of a new section in README.md file
- current_section = line.strip().split(" ")[1].lower() # Extract sketch type from section name
- output_lines.append(line)
- elif line.startswith("| Function Name"): # Placeholder for the table
- if current_section in function_index:
- # Generate the table content
- table_content = "| Function Name | Function Type | Signature | Description |\n"
- table_content += "|---|---|---|---|\n" # Add the table header here
-
- # Sort functions by function type (AGGREGATE first, then SCALAR) and then by number of arguments
- sorted_functions = sorted(function_index[current_section], key=lambda x: (x['function_type'], len(x['signature'].split(','))), reverse=False)
- for function in sorted_functions:
- function_link = f"[{function['function_name']}]({function['path']})"
- table_content += f"| {function_link} | {function['function_type']} | {function['signature']} | {function['description']} |\n"
- output_lines.extend(table_content.splitlines(True)) # Add the table lines
-
- # Add examples section
- examples_path = os.path.join(input_folder, current_section, "test")
- example_files = [f for f in os.listdir(examples_path) if f.endswith("_test.sql")]
- if example_files:
- output_lines.append("\n**Examples:**\n\n")
- for example_file in example_files:
- # Read the example SQL file
- with open(os.path.join(examples_path, example_file), 'r') as f:
- sql_code = f.read()
-
- # Remove license header from examples
- sql_code_lines = sql_code.splitlines()
- start_index = 0
- for i, line in enumerate(sql_code_lines):
- if not line.startswith("/*") and not line.startswith(" *") and not line.startswith(" */"):
- start_index = i
- break
- sql_code_without_license = "\n".join(sql_code_lines[start_index:])
-
- # Add the SQL code in a code block
- output_lines.append(f"```sql\n{sql_code_without_license}\n```\n")
-
- else:
- output_lines.append(line) # Keep other lines from the template
+ output_lines = template_file.readlines()
+
+ # Generate the table content
+ output_lines += "\n"
+ output_lines += "| Function Name | Function Type | Signature | Description |\n"
+ output_lines += "|---|---|---|---|\n" # table header
+
+ # Sort functions by function type (AGGREGATE first, then SCALAR) and then by number of arguments
+ sorted_functions = sorted(function_index, key=lambda x: (x['function_type'], len(x['signature'].split(','))), reverse=False)
+ for function in sorted_functions:
+ function_link = f"[{function['function_name']}]({function['path']})"
+ output_lines += f"| {function_link} | {function['function_type']} | {function['signature']} | {function['description']} |\n"
+
+ # Add examples section
+ example_files = [f for f in os.listdir(examples_path) if f.endswith("_test.sql")]
+ if example_files:
+ output_lines.append("\n**Examples:**\n\n")
+ for example_file in example_files:
+ # Read the example SQL file
+ with open(os.path.join(examples_path, example_file), 'r') as f:
+ sql_code = f.read()
+
+ # Remove license header from examples
+ sql_code_lines = sql_code.splitlines()
+ start_index = 0
+ for i, line in enumerate(sql_code_lines):
+ if not line.startswith("/*") and not line.startswith(" *") and not line.startswith(" */"):
+ start_index = i
+ break
+ sql_code_without_license = "\n".join(sql_code_lines[start_index:])
+
+ # Add the SQL code in a code block
+ output_lines.append(f"```sql\n{sql_code_without_license}\n```\n")
output_content = "".join(output_lines)
- output_content = output_content.replace("```\n|---|---|---|---|","```\n") # Remove the extra table breaks
-
return output_content
if __name__ == "__main__":
- input_folder = "." # Set input folder path as current folder
- template_path = "README_template.md" # Path to the template
- function_index = process_folder(input_folder)
-
- # Generate README content from template
- readme_content = generate_readme(template_path, function_index, input_folder) # Pass input_folder
-
- # Write to README.md file
- with open("README.md", "w") as readme_file:
- readme_file.write(readme_content)
-
- logging.info("README.md generated successfully.")
\ No newline at end of file
+ sketch_types = ["cpc", "fi", "hll", "kll", "tdigest", "theta", "tuple"]
+ template_name = "README_template.md"
+ readme_name = "README.md"
+ for sketch_type in sketch_types:
+ logging.info("processing sketch type " + sketch_type)
+ function_index = process_folder(os.path.join("definitions", sketch_type), sketch_type)
+ sketch_type_readme_name = os.path.join(sketch_type, readme_name)
+ logging.info("generating " + sketch_type_readme_name)
+ readme_content = generate_readme(os.path.join(sketch_type, template_name), function_index[sketch_type], os.path.join(sketch_type, "test"))
+ with open(sketch_type_readme_name, "w") as readme_file:
+ readme_file.write(readme_content)
+ logging.info(sketch_type_readme_name + " generated successfully")
diff --git a/tdigest/README.md b/tdigest/README.md
new file mode 100644
index 0000000..3d700df
--- /dev/null
+++ b/tdigest/README.md
@@ -0,0 +1,88 @@
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+# Apache DataSketches t-digest for Google BigQuery
+
+Estimates distributions of numeric values, provides approximate quantiles and ranks
+prioritizing low and high rank accuracy.
+
+Please visit
+[t-digest overview](https://datasketches.apache.org/docs/tdigest/tdigest.html)
+for more information about this sketch family.
+
+Please visit the main
+[Apache DataSketches website](https://datasketches.apache.org)
+for more information about DataSketches library.
+
+If you are interested in making contributions to this project please see our
+[Community](https://datasketches.apache.org/docs/Community/)
+page for how to contact us.
+
+| Function Name | Function Type | Signature | Description |
+|---|---|---|---|
+| [tdigest_double_build](tdigest_double_build.sqlx) | AGGREGATE | (value FLOAT64) -> BYTES | Creates a sketch that represents the distribution of the given column.\<br\>\<br\>Param value: the column of FLOAT64 values.\<br\>Defaults: k = 200.\<br\>Returns: a t\-Digest, as bytes. |
+| [tdigest_double_merge](tdigest_double_merge.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Merges sketches from the given column.\<br\>\<br\>Param sketch: the column of values.\<br\>Defaults: k = 200.\<br\>Returns: a serialized t\-Digest as BYTES. |
+| [tdigest_double_merge_k](tdigest_double_merge_k.sqlx) | AGGREGATE | (sketch BYTES, k INT NOT AGGREGATE) -> BYTES | Merges sketches from the given column.\<br\>\<br\>Param sketch: the column of values.\<br\>Param k: the sketch accuracy/size parameter as an integer in the range \[10, 65535\].\<br\>Returns: a serialized t\-Digest as BYTES. |
+| [tdigest_double_build_k](tdigest_double_build_k.sqlx) | AGGREGATE | (value FLOAT64, k INT NOT AGGREGATE) -> BYTES | Creates a sketch that represents the distribution of the given column.\<br\>\<br\>Param value: the column of FLOAT64 values.\<br\>Param k: the sketch accuracy/size parameter as an INT in the range \[10, 65535\].\<br\>Returns: a t\-Digest, as bytes. |
+| [tdigest_double_get_max_value](tdigest_double_get_max_value.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns the maximum value of the input stream.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Returns: max value as FLOAT64 |
+| [tdigest_double_to_string](tdigest_double_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.\<br\>\<br\>Param sketch: the given sketch as sketch encoded bytes.\<br\>Returns: a string that represents the state of the given sketch. |
+| [tdigest_double_get_total_weight](tdigest_double_get_total_weight.sqlx) | SCALAR | (sketch BYTES) -> INT64 | Returns the total weight of the input stream.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Returns: total weight as INT64 |
+| [tdigest_double_get_min_value](tdigest_double_get_min_value.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns the minimum value of the input stream.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Returns: min value as FLOAT64 |
+| [tdigest_double_get_rank](tdigest_double_get_rank.sqlx) | SCALAR | (sketch BYTES, value FLOAT64) -> FLOAT64 | Returns an approximation to the normalized rank, on the interval \[0.0, 1.0\], of the given value.\<br\>\<br\>Param sketch: the given sketch in serialized form.\<br\>Param value: value to be ranked.\<br\>Returns: an approximate rank of the given value. |
+| [tdigest_double_get_quantile](tdigest_double_get_quantile.sqlx) | SCALAR | (sketch BYTES, rank FLOAT64) -> FLOAT64 | Returns a value from the sketch that is the best approximation to a value from the original stream with the given rank.\<br\>\<br\>Param sketch: the given sketch in serialized form.\<br\>Param rank: rank of a value in the hypothetical sorted stream.\<br\>Returns: an approximate quantile associated with the given rank. |
+
+**Examples:**
+
+```sql
+
+create or replace table `$BQ_DATASET`.tdigest_double(sketch bytes);
+
+# using default
+insert into `$BQ_DATASET`.tdigest_double
+(select `$BQ_DATASET`.tdigest_double_build(value) from unnest([1,2,3,4,5,6,7,8,9,10]) as value);
+
+# using full signature
+insert into `$BQ_DATASET`.tdigest_double
+(select `$BQ_DATASET`.tdigest_double_build_k(value, 100) from unnest([11,12,13,14,15,16,17,18,19,20]) as value);
+
+select `$BQ_DATASET`.tdigest_double_to_string(sketch) from `$BQ_DATASET`.tdigest_double;
+
+# using default
+select `$BQ_DATASET`.tdigest_double_to_string(`$BQ_DATASET`.tdigest_double_merge(sketch)) from `$BQ_DATASET`.tdigest_double;
+
+# using full signature
+select `$BQ_DATASET`.tdigest_double_to_string(`$BQ_DATASET`.tdigest_double_merge_k(sketch, 100)) from `$BQ_DATASET`.tdigest_double;
+
+# expected 0.5
+select `$BQ_DATASET`.tdigest_double_get_rank(`$BQ_DATASET`.tdigest_double_merge(sketch), 10) from `$BQ_DATASET`.tdigest_double;
+
+# expected 10
+select `$BQ_DATASET`.tdigest_double_get_quantile(`$BQ_DATASET`.tdigest_double_merge(sketch), 0.5) from `$BQ_DATASET`.tdigest_double;
+
+# expected 20
+select `$BQ_DATASET`.tdigest_double_get_total_weight(`$BQ_DATASET`.tdigest_double_merge(sketch)) from `$BQ_DATASET`.tdigest_double;
+
+# expected 1
+select `$BQ_DATASET`.tdigest_double_get_min_value(`$BQ_DATASET`.tdigest_double_merge(sketch)) from `$BQ_DATASET`.tdigest_double;
+
+# expected 20
+select `$BQ_DATASET`.tdigest_double_get_max_value(`$BQ_DATASET`.tdigest_double_merge(sketch)) from `$BQ_DATASET`.tdigest_double;
+
+drop table `$BQ_DATASET`.tdigest_double;
+```
diff --git a/tuple/README.md b/tdigest/README_template.md
similarity index 82%
copy from tuple/README.md
copy to tdigest/README_template.md
index 51af3fe..f91ca06 100644
--- a/tuple/README.md
+++ b/tdigest/README_template.md
@@ -17,10 +17,13 @@
under the License.
-->
-# Apache DataSketches Tuple Sketches for GCP/BigQuery
+# Apache DataSketches t-digest for Google BigQuery
+
+Estimates distributions of numeric values, provides approximate quantiles and ranks
+prioritizing low and high rank accuracy.
Please visit
-[Tuple Sketches](https://datasketches.apache.org/docs/Tuple/TupleSketches.html)
+[t-digest overview](https://datasketches.apache.org/docs/tdigest/tdigest.html)
for more information about this sketch family.
Please visit the main
@@ -30,4 +33,3 @@ for more information about DataSketches library.
If you are interested in making contributions to this project please see our
[Community](https://datasketches.apache.org/docs/Community/)
page for how to contact us.
-
diff --git a/theta/README.md b/theta/README.md
index 6044187..8c4eb24 100644
--- a/theta/README.md
+++ b/theta/README.md
@@ -17,7 +17,12 @@
under the License.
-->
-# Apache DataSketches Theta Sketches for GCP/BigQuery
+# Apache DataSketches Theta Sketches for Google BigQuery
+
+Theta sketches are used for distinct counting and set operations like union,
+intersection, and difference. They are efficient for estimating the size of
+these operations on large datasets, enabling applications like analyzing user
+overlap or comparing different groups.
Please visit
[Theta Sketches](https://datasketches.apache.org/docs/Theta/ThetaSketches.html)
@@ -30,3 +35,173 @@ for more information about DataSketches library.
If you are interested in making contributions to this project please see our
[Community](https://datasketches.apache.org/docs/Community/)
page for how to contact us.
+
+| Function Name | Function Type | Signature | Description |
+|---|---|---|---|
+| [theta_sketch_agg_int64](theta_sketch_agg_int64.sqlx) | AGGREGATE | (value INT64) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.\<br\> \<br\>Param value: the INT64 column of identifiers.\<br\>Defaults: lg\_k = 12, seed = 9001, p = 1.0.\<br\>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
+| [theta_sketch_agg_union](theta_sketch_agg_union.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Creates a sketch that represents the union of the given column of sketches.\<br\>\<br\>Param sketch: the column of sketches. Each as BYTES.\<br\>Defaults: lg\_k = 12, seed = 9001.\<br\>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
+| [theta_sketch_agg_string](theta_sketch_agg_string.sqlx) | AGGREGATE | (str STRING) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.\<br\> \<br\>Param str: the STRING column of identifiers.\<br\>Defaults: lg\_k = 12, seed = 9001, p = 1.0.\<br\>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
+| [theta_sketch_agg_union_lgk_seed](theta_sketch_agg_union_lgk_seed.sqlx) | AGGREGATE | (sketch BYTES, params STRUCT<lg_k BYTEINT, seed INT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the union of the given column of sketches.\<br\>\<br\>Param sketch: the column of sketches. Each as BYTES.\<br\>Param lg\_k: the sketch accuracy/size parameter as a BYTEINT in the range \[4, 26\].\<br\>Param seed: This is used to confirm that the given sketches were configured with the cor [...]
+| [theta_sketch_agg_int64_lgk_seed_p](theta_sketch_agg_int64_lgk_seed_p.sqlx) | AGGREGATE | (value INT64, params STRUCT<lg_k BYTEINT, seed INT64, p FLOAT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given INT64 column.\<br\>\<br\>Param value: the INT64 column of identifiers.\<br\>Param lg\_k: the sketch accuracy/size parameter as a BYTEINT in the range \[4, 26\]. A NULL specifies the default of 12.\<br\>Param seed: the seed to be used by the underl [...]
+| [theta_sketch_agg_string_lgk_seed_p](theta_sketch_agg_string_lgk_seed_p.sqlx) | AGGREGATE | (str STRING, params STRUCT<lg_k BYTEINT, seed INT64, p FLOAT64> NOT AGGREGATE) -> BYTES | Creates a sketch that represents the cardinality of the given STRING column.\<br\>\<br\>Param str: the STRING column of identifiers.\<br\>Param lg\_k: the sketch accuracy/size parameter as a BYTEINT in the range \[4, 26\]. A NULL specifies the default of 12.\<br\>Param seed: the seed to be used by the under [...]
+| [theta_sketch_get_estimate](theta_sketch_get_estimate.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Gets cardinality estimate and bounds from given sketch.\<br\> \<br\>Param sketch: The given sketch to query as BYTES.\<br\>Defaults: seed = 9001.\<br\>Returns: a FLOAT64 value as the cardinality estimate. |
+| [theta_sketch_to_string](theta_sketch_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a summary string that represents the state of the given sketch.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Defaults: seed = 9001.\<br\>Returns: a STRING that represents the state of the given sketch. |
+| [theta_sketch_get_num_retained](theta_sketch_get_num_retained.sqlx) | SCALAR | (sketch BYTES) -> INT | Returns the number of retained entries in the given sketch.\<br\> \<br\>Param sketch: The given sketch to query as BYTES.\<br\>Defaults: seed = 9001.\<br\>Returns: number of retained entries as INT. |
+| [theta_sketch_get_theta](theta_sketch_get_theta.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns theta \(effective sampling rate\) as a fraction from 0 to 1.\<br\> \<br\>Param sketch: The given sketch to query as BYTES.\<br\>Defaults: seed = 9001.\<br\>Returns: theta as FLOAT64. |
+| [theta_sketch_get_num_retained_seed](theta_sketch_get_num_retained_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> INT | Returns the number of retained entries in the given sketch.\<br\> \<br\>Param sketch: The given sketch to query as BYTES.\<br\>Param seed: This is used to confirm that the given sketch was configured with the correct seed.\<br\>Returns: number of retained entries as INT. |
+| [theta_sketch_get_estimate_seed](theta_sketch_get_estimate_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> FLOAT64 | Gets cardinality estimate and bounds from given sketch.\<br\> \<br\>Param sketch: The given sketch to query as BYTES.\<br\>Param seed: This is used to confirm that the given sketch was configured with the correct seed.\<br\>Returns: a FLOAT64 value as the cardinality estimate. |
+| [theta_sketch_to_string_seed](theta_sketch_to_string_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> STRING | Returns a summary string that represents the state of the given sketch.\<br\>\<br\>Param sketch: the given sketch as BYTES.\<br\>Param seed: This is used to confirm that the given sketch was configured with the correct seed.\<br\>Returns: a STRING that represents the state of the given sketch. |
+| [theta_sketch_get_theta_seed](theta_sketch_get_theta_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> FLOAT64 | Returns theta \(effective sampling rate\) as a fraction from 0 to 1.\<br\> \<br\>Param sketch: The given sketch to query as BYTES.\<br\>Param seed: This is used to confirm that the given sketch was configured with the correct seed.\<br\>Returns: theta as FLOAT64. |
+| [theta_sketch_intersection](theta_sketch_intersection.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the scalar intersection of the two given sketches.\<br\>\<br\>Param sketchA: the first sketch as BYTES.\<br\>Param sketchB: the second sketch as BYTES.\<br\>Defaults: seed = 9001.\<br\>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
+| [theta_sketch_union](theta_sketch_union.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the scalar union of the two given sketches.\<br\>\<br\>Param sketchA: the first sketch as BYTES.\<br\>Param sketchB: the second sketch as BYTES.\<br\>Defaults: lg\_k = 12, seed = 9001.\<br\>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
+| [theta_sketch_a_not_b](theta_sketch_a_not_b.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the scalar set difference: sketchA and not sketchB.\<br\>\<br\>Param sketchA: the first sketch "A" as bytes.\<br\>Param sketchB: the second sketch "B" as bytes.\<br\>Defaults: seed = 9001.\<br\>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
+| [theta_sketch_intersection_seed](theta_sketch_intersection_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64) -> BYTES | Computes a sketch that represents the scalar intersection of the two given sketches.\<br\>\<br\>Param sketchA: the first sketch as BYTES.\<br\>Param sketchB: the second sketch as BYTES.\<br\>Param seed: This is used to confirm that the given sketches were configured with the correct seed.\<br\>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
+| [theta_sketch_a_not_b_seed](theta_sketch_a_not_b_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64) -> BYTES | Computes a sketch that represents the scalar set difference: sketchA and not sketchB.\<br\>\<br\>Param sketchA: the first sketch "A" as bytes.\<br\>Param sketchB: the second sketch "B" as bytes.\<br\>Param seed: This is used to confirm that the given sketches were configured with the correct seed.\<br\>Returns: a Compact, Compressed Theta Sketch, as BYTES. |
+| [theta_sketch_union_lgk_seed](theta_sketch_union_lgk_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, lg_k BYTEINT, seed INT64) -> BYTES | Computes a sketch that represents the scalar union of the two given sketches.\<br\>\<br\>Param sketchA: the first sketch as BYTES.\<br\>Param sketchB: the second sketch as BYTES.\<br\>Param lg\_k: the sketch accuracy/size parameter as an integer in the range \[4, 26\].\<br\>Param seed: This is used to confirm that the given sketches were configu [...]
+| [theta_sketch_get_estimate_and_bounds](theta_sketch_get_estimate_and_bounds.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Gets cardinality estimate and bounds from given sketch.\<br\>\<br\>Param sketch: The given sketch to query as BYTES.\<br\>Param num\_std\_devs: The returned bounds will be based on the statistical confidence interval\<br\> determined by the given number of standard deviations from the r [...]
+| [theta_sketch_jaccard_similarity](theta_sketch_jaccard_similarity.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> STRUCT<lower_bound FLOAT64, estimate FLOAT64, upper_bound FLOAT64> | Computes the Jaccard similarity index with upper and lower bounds.\<br\>The Jaccard similarity index J\(A,B\) = \(A ^ B\)/\(A U B\) is used to measure how similar the two sketches are to each other.\<br\>If J = 1.0, the sketches are considered equal. If J = 0, the two sketches are disjoint.\<br\>A Jacca [...]
+| [theta_sketch_get_estimate_and_bounds_seed](theta_sketch_get_estimate_and_bounds_seed.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT, seed INT64) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Gets cardinality estimate and bounds from given sketch.\<br\>\<br\>Param sketch: The given sketch to query as BYTES.\<br\>Param num\_std\_devs: The returned bounds will be based on the statistical confidence interval\<br\> determined by the given number of standard [...]
+| [theta_sketch_jaccard_similarity_seed](theta_sketch_jaccard_similarity_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64) -> STRUCT<lower_bound FLOAT64, estimate FLOAT64, upper_bound FLOAT64> | Computes the Jaccard similarity index with upper and lower bounds.\<br\>The Jaccard similarity index J\(A,B\) = \(A ^ B\)/\(A U B\) is used to measure how similar the two sketches are to each other.\<br\>If J = 1.0, the sketches are considered equal. If J = 0, the two sketches are [...]
+
+**Examples:**
+
+```sql
+
+# using defaults
+create or replace table `$BQ_DATASET`.theta_sketch(sketch bytes);
+
+insert into `$BQ_DATASET`.theta_sketch
+(select `$BQ_DATASET`.theta_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
+insert into `$BQ_DATASET`.theta_sketch
+(select `$BQ_DATASET`.theta_sketch_agg_int64(value) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
+
+# expected about 20000
+select `$BQ_DATASET`.theta_sketch_get_estimate_and_bounds(
+ `$BQ_DATASET`.theta_sketch_agg_union(sketch),
+ 2
+) from `$BQ_DATASET`.theta_sketch;
+
+# expected estimate about 20000
+select `$BQ_DATASET`.theta_sketch_to_string(
+ `$BQ_DATASET`.theta_sketch_agg_union(sketch)
+) from `$BQ_DATASET`.theta_sketch;
+
+select `$BQ_DATASET`.theta_sketch_get_theta(
+ `$BQ_DATASET`.theta_sketch_agg_union(sketch)
+) from `$BQ_DATASET`.theta_sketch;
+
+select `$BQ_DATASET`.theta_sketch_get_num_retained(
+ `$BQ_DATASET`.theta_sketch_agg_union(sketch)
+) from `$BQ_DATASET`.theta_sketch;
+
+drop table `$BQ_DATASET`.theta_sketch;
+
+# using full signatures
+create or replace table `$BQ_DATASET`.theta_sketch(sketch bytes);
+
+insert into `$BQ_DATASET`.theta_sketch
+(select `$BQ_DATASET`.theta_sketch_agg_int64_lgk_seed_p(value, struct<int, int, float64>(14, 111, 0.9)) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
+insert into `$BQ_DATASET`.theta_sketch
+(select `$BQ_DATASET`.theta_sketch_agg_int64_lgk_seed_p(value, struct<int, int, float64>(14, 111, 0.9)) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
+
+# expected about 20000
+select `$BQ_DATASET`.theta_sketch_get_estimate_and_bounds_seed(
+ `$BQ_DATASET`.theta_sketch_agg_union_lgk_seed(sketch, struct<int, int>(10, 111)),
+ 2,
+ 111
+) from `$BQ_DATASET`.theta_sketch;
+
+# expected estimate about 20000
+select `$BQ_DATASET`.theta_sketch_to_string_seed(
+ `$BQ_DATASET`.theta_sketch_agg_union_lgk_seed(sketch, struct<int, int>(10, 111)),
+ 111
+) from `$BQ_DATASET`.theta_sketch;
+
+select `$BQ_DATASET`.theta_sketch_get_theta_seed(
+ `$BQ_DATASET`.theta_sketch_agg_union_lgk_seed(sketch, struct<int, int>(10, 111)),
+ 111
+) from `$BQ_DATASET`.theta_sketch;
+
+select `$BQ_DATASET`.theta_sketch_get_num_retained_seed(
+ `$BQ_DATASET`.theta_sketch_agg_union_lgk_seed(sketch, struct<int, int>(10, 111)),
+ 111
+) from `$BQ_DATASET`.theta_sketch;
+
+drop table `$BQ_DATASET`.theta_sketch;
+
+# using defaults
+# expected 5
+select `$BQ_DATASET`.theta_sketch_get_estimate(
+ `$BQ_DATASET`.theta_sketch_union(
+ (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["c", "d", "e"]) as str)
+ )
+);
+
+# full signatures
+# expected 5
+select `$BQ_DATASET`.theta_sketch_get_estimate_seed(
+ `$BQ_DATASET`.theta_sketch_union_lgk_seed(
+ (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["c", "d", "e"]) as str),
+ 10,
+ 111
+ ),
+ 111
+);
+
+# using defaults
+# expected 1
+select `$BQ_DATASET`.theta_sketch_get_estimate(
+ `$BQ_DATASET`.theta_sketch_intersection(
+ (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["c", "d", "e"]) as str)
+ )
+);
+
+# full signatures
+# expected 1
+select `$BQ_DATASET`.theta_sketch_get_estimate_seed(
+ `$BQ_DATASET`.theta_sketch_intersection_seed(
+ (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["c", "d", "e"]) as str),
+ 111
+ ),
+ 111
+);
+
+# using defaults
+# expected 2
+select `$BQ_DATASET`.theta_sketch_get_estimate(
+ `$BQ_DATASET`.theta_sketch_a_not_b(
+ (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["c", "d", "e"]) as str)
+ )
+);
+
+# full signatures
+# expected 2
+select `$BQ_DATASET`.theta_sketch_get_estimate_seed(
+ `$BQ_DATASET`.theta_sketch_a_not_b_seed(
+ (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["c", "d", "e"]) as str),
+ 111
+ ),
+ 111
+);
+
+# using defaults
+# expected 0.2
+select `$BQ_DATASET`.theta_sketch_jaccard_similarity(
+ (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.theta_sketch_agg_string(str) from unnest(["c", "d", "e"]) as str)
+);
+
+# using full signatures
+# expected 0.2
+select `$BQ_DATASET`.theta_sketch_jaccard_similarity_seed(
+ (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(str, struct<int, int, float64>(10, 111, 0.999)) from unnest(["c", "d", "e"]) as str),
+ 111
+);
+```
diff --git a/theta/README.md b/theta/README_template.md
similarity index 79%
copy from theta/README.md
copy to theta/README_template.md
index 6044187..3381a5d 100644
--- a/theta/README.md
+++ b/theta/README_template.md
@@ -17,7 +17,12 @@
under the License.
-->
-# Apache DataSketches Theta Sketches for GCP/BigQuery
+# Apache DataSketches Theta Sketches for Google BigQuery
+
+Theta sketches are used for distinct counting and set operations like union,
+intersection, and difference. They are efficient for estimating the size of
+these operations on large datasets, enabling applications like analyzing user
+overlap or comparing different groups.
Please visit
[Theta Sketches](https://datasketches.apache.org/docs/Theta/ThetaSketches.html)
diff --git a/tuple/README.md b/tuple/README.md
index 51af3fe..50215eb 100644
--- a/tuple/README.md
+++ b/tuple/README.md
@@ -17,7 +17,12 @@
under the License.
-->
-# Apache DataSketches Tuple Sketches for GCP/BigQuery
+# Apache DataSketches Tuple Sketches for Google BigQuery
+
+Tuple sketches extend the functionality of Theta sketches by
+allowing you to associate a summary value with each item in the set. This
+enables calculations like the sum, minimum, or maximum of values associated with
+the distinct items.
Please visit
[Tuple Sketches](https://datasketches.apache.org/docs/Tuple/TupleSketches.html)
@@ -31,3 +36,231 @@ If you are interested in making contributions to this project please see our
[Community](https://datasketches.apache.org/docs/Community/)
page for how to contact us.
+| Function Name | Function Type | Signature | Description |
+|---|---|---|---|
+| [tuple_sketch_int64_agg_union](tuple_sketch_int64_agg_union.sqlx) | AGGREGATE | (sketch BYTES) -> BYTES | Builds a Tuple Sketch that represents the UNION of the given column of Tuple Sketches.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\>\<br\>Param sketch: the given column of Tuple Sketches with an INT64 summary column. This may not be NULL. [...]
+| [tuple_sketch_int64_agg_string](tuple_sketch_int64_agg_string.sqlx) | AGGREGATE | (key STRING, value INT64) -> BYTES | Builds a Tuple Sketch from an STRING Key column and an INT64 value column.\<br\>Multiple values for the same key are aggregated using the default mode.\<br\>Note that cardinality estimation accuracy, plots, error tables, and sampling probability p are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an STRING Key column and an INT64 [...]
+| [tuple_sketch_int64_agg_int64](tuple_sketch_int64_agg_int64.sqlx) | AGGREGATE | (key INT64, value INT64) -> BYTES | Builds a Tuple Sketch from an INT64 Key column and an INT64 value column.\<br\>Multiple values for the same key are aggregated using the default mode.\<br\>Note that cardinality estimation accuracy, plots, error tables, and sampling probability p are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 Key column and an INT64 summa [...]
+| [tuple_sketch_int64_agg_union_lgk_seed_mode](tuple_sketch_int64_agg_union_lgk_seed_mode.sqlx) | AGGREGATE | (sketch BYTES, params STRUCT<lg_k BYTEINT, seed INT64, mode STRING> NOT AGGREGATE) -> BYTES | Builds a Tuple Sketch that represents the UNION of the given column of Tuple Sketches.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\>\<br\>Para [...]
+| [tuple_sketch_int64_agg_int64_lgk_seed_p_mode](tuple_sketch_int64_agg_int64_lgk_seed_p_mode.sqlx) | AGGREGATE | (key INT64, value INT64, params STRUCT<lg_k BYTEINT, seed INT64, p FLOAT64, mode STRING> NOT AGGREGATE) -> BYTES | Builds a Tuple Sketch from an INT64 Key column and an INT64 value column.\<br\>Multiple values for the same key are aggregated using one of the selectable operations: { SUM, MIN, MAX, ONE \(constant 1\) }.\<br\>Note that cardinality estimation accuracy, plots, er [...]
+| [tuple_sketch_int64_agg_string_lgk_seed_p_mode](tuple_sketch_int64_agg_string_lgk_seed_p_mode.sqlx) | AGGREGATE | (key STRING, value INT64, params STRUCT<lg_k BYTEINT, seed INT64, p FLOAT64, mode STRING> NOT AGGREGATE) -> BYTES | Builds a Tuple Sketch from an STRING Key column and an INT64 value column.\<br\>Multiple values for the same key are aggregated using one of the selectable operations: SUM, MIN, MAX, ONE.\<br\>Note that cardinality estimation accuracy, plots, error tables, and [...]
+| [tuple_sketch_int64_to_string](tuple_sketch_int64_to_string.sqlx) | SCALAR | (sketch BYTES) -> STRING | Returns a human readable STRING that is a short summary of the state of this sketch.\<br\> Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\> This function only applies to Tuple Sketches with an INT64 summary column.\<br\>\<br\>Param sketch: the sketch to be summarized. This may not be NULL.\<br\>Defaults: seed = 9001.\<br\>Re [...]
+| [tuple_sketch_int64_get_estimate](tuple_sketch_int64_get_estimate.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns the cardinality estimate of the given Tuple Sketch.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\> \<br\>Param sketch: the given Tuple Sketch. This may not be NULL.\<br\>Defaults: seed = 9001.\<br\>Returns: the cardinality es [...]
+| [tuple_sketch_int64_get_theta](tuple_sketch_int64_get_theta.sqlx) | SCALAR | (sketch BYTES) -> FLOAT64 | Returns theta \(effective sampling rate\) as a fraction from 0 to 1.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\> \<br\>Param sketch: the given Tuple Sketch. This may not be NULL.\<br\>Defaults: seed = 9001.\<br\>Returns: theta as FLOAT64. |
+| [tuple_sketch_int64_get_num_retained](tuple_sketch_int64_get_num_retained.sqlx) | SCALAR | (sketch BYTES) -> INT | Returns the number of retained entries in the given sketch.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\> \<br\>Param sketch: the given Tuple Sketch. This may not be NULL.\<br\>Defaults: seed = 9001.\<br\>Returns: number of reta [...]
+| [tuple_sketch_int64_get_theta_seed](tuple_sketch_int64_get_theta_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> FLOAT64 | Returns theta \(effective sampling rate\) as a fraction from 0 to 1.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\> \<br\>Param sketch: the given Tuple Sketch. This may not be NULL.\<br\>Param seed: This is used to co [...]
+| [tuple_sketch_int64_get_num_retained_seed](tuple_sketch_int64_get_num_retained_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> INT | Returns the number of retained entries in the given sketch.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\> \<br\>Param sketch: the given Tuple Sketch. This may not be NULL.\<br\>Param seed: This is used to c [...]
+| [tuple_sketch_int64_to_string_seed](tuple_sketch_int64_to_string_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> STRING | Returns a human readable STRING that is a short summary of the state of this sketch.\<br\> Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\> This function only applies to Tuple Sketches with an INT64 summary column.\<br\>\<br\>Param sketch: the sketch to be summarized. This may not be NULL.\<br\>Param se [...]
+| [tuple_sketch_int64_a_not_b](tuple_sketch_int64_a_not_b.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the set difference of sketchA and not sketchB.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column. \<br\> \<br\>Param sketchA: the first sketch "A" as BYTES. This may not be NULL.\<br\>Param sketchB: the se [...]
+| [tuple_sketch_int64_from_theta_sketch](tuple_sketch_int64_from_theta_sketch.sqlx) | SCALAR | (sketch BYTES, value INT64) -> BYTES | Converts the given Theta Sketch into a Tuple Sketch with a INT64 summary column set to the given INT64 value.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>\<br\>Param sketch: the given Theta Sketch. This may not be NULL.\<br\>Param value: the given INT64 value. This may not be NULL.\<br\>Def [...]
+| [tuple_sketch_int64_get_estimate_seed](tuple_sketch_int64_get_estimate_seed.sqlx) | SCALAR | (sketch BYTES, seed INT64) -> FLOAT64 | Returns the cardinality estimate of the given Tuple Sketch.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\> \<br\>Param sketch: the given Tuple Sketch. This may not be NULL.\<br\>Param seed: This is used to confi [...]
+| [tuple_sketch_int64_intersection](tuple_sketch_int64_intersection.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a sketch that represents the scalar intersection of sketchA and sketchB.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\>\<br\>Param sketchA: the first sketch "A" as BYTES.\<br\>Param sketchB: the second sketch "B [...]
+| [tuple_sketch_int64_union](tuple_sketch_int64_union.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> BYTES | Computes a Tuple Sketch that represents the UNION of sketchA and sketchB.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\>\<br\>Param sketchA: the first sketch "A" as BYTES. This may not be NULL.\<br\>Param sketchB: the second sketch "B [...]
+| [tuple_sketch_int64_from_theta_sketch_seed](tuple_sketch_int64_from_theta_sketch_seed.sqlx) | SCALAR | (sketch BYTES, value INT64, seed INT64) -> BYTES | Converts the given Theta Sketch into a Tuple Sketch with a INT64 summary column set to the given INT64 value.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>\<br\>Param sketch: the given Theta Sketch. This may not be NULL.\<br\>Param value: the given INT64 value. This may [...]
+| [tuple_sketch_int64_a_not_b_seed](tuple_sketch_int64_a_not_b_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64) -> BYTES | Computes a sketch that represents the scalar set difference of sketchA and not sketchB.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\>\<br\>Param sketchA: the first sketch "A" as BYTES. This may not be NULL.\ [...]
+| [tuple_sketch_int64_filter_low_high](tuple_sketch_int64_filter_low_high.sqlx) | SCALAR | (sketch BYTES, low INT64, high INT64) -> BYTES | Returns a Tuple Sketch computed from the given sketch filtered by the given low and high values. \<br\>This returns a compact tuple sketch that contains the subset of rows of the give sketch where the\<br\>summary column is greater\-than or equal to the given low and less\-than or equal to the given high.\<br\>Note that cardinality estimation accurac [...]
+| [tuple_sketch_int64_get_estimate_and_bounds](tuple_sketch_int64_get_estimate_and_bounds.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Returns the cardinality estimate and bounds from the given Tuple Sketch.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\> \<br\>Para [...]
+| [tuple_sketch_int64_filter_low_high_seed](tuple_sketch_int64_filter_low_high_seed.sqlx) | SCALAR | (sketch BYTES, low INT64, high INT64, seed INT64) -> BYTES | Returns a Tuple Sketch computed from the given sketch filtered by the given low and high values. \<br\>This returns a compact tuple sketch that contains the subset of rows of the give sketch where the\<br\>summary column is greater\-than or equal to the given low and less\-than or equal to the given high.\<br\>Note that cardinal [...]
+| [tuple_sketch_int64_jaccard_similarity](tuple_sketch_int64_jaccard_similarity.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES) -> STRUCT<lower_bound FLOAT64, estimate FLOAT64, upper_bound FLOAT64> | Computes the Jaccard similarity index with upper and lower bounds.\<br\>The Jaccard similarity index J\(A,B\) = \(A ^ B\)/\(A U B\) is used to measure how similar the two sketches are to each other.\<br\>If J = 1.0, the sketches are considered equal. If J = 0, the two sketches are disjoint.\ [...]
+| [tuple_sketch_int64_get_sum_estimate_and_bounds](tuple_sketch_int64_get_sum_estimate_and_bounds.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT) -> STRUCT<sum_estimate FLOAT64, sum_lower_bound FLOAT64, sum_upper_bound FLOAT64> | Returns the estimate and bounds for the sum of the INT64 summary column\<br\>scaled to the original population from the given Tuple Sketch.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This [...]
+| [tuple_sketch_int64_intersection_seed_mode](tuple_sketch_int64_intersection_seed_mode.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64, mode STRING) -> BYTES | Computes a sketch that represents the scalar intersection of sketchA and sketchB.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\>\<br\>Param sketchA: the first sketch "A" as BY [...]
+| [tuple_sketch_int64_get_sum_estimate_and_bounds_seed](tuple_sketch_int64_get_sum_estimate_and_bounds_seed.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT, seed INT64) -> STRUCT<sum_estimate FLOAT64, sum_lower_bound FLOAT64, sum_upper_bound FLOAT64> | Returns the estimate and bounds for the sum of the INT64 summary column\<br\>scaled to the original population from the given Tuple Sketch.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Th [...]
+| [tuple_sketch_int64_union_lgk_seed_mode](tuple_sketch_int64_union_lgk_seed_mode.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, lg_k BYTEINT, seed INT64, mode STRING) -> BYTES | Computes a Tuple Sketch that represents the UNION of sketchA and sketchB.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary column.\<br\>\<br\>Param sketchA: the first sketch "A" as BY [...]
+| [tuple_sketch_int64_get_estimate_and_bounds_seed](tuple_sketch_int64_get_estimate_and_bounds_seed.sqlx) | SCALAR | (sketch BYTES, num_std_devs BYTEINT, seed INT64) -> STRUCT<estimate FLOAT64, lower_bound FLOAT64, upper_bound FLOAT64> | Returns the cardinality estimate and bounds from the given Tuple Sketch.\<br\>Note that cardinality estimation accuracy, plots, and error tables are the same as the Theta Sketch.\<br\>This function only applies to Tuple Sketches with an INT64 summary col [...]
+| [tuple_sketch_int64_jaccard_similarity_seed](tuple_sketch_int64_jaccard_similarity_seed.sqlx) | SCALAR | (sketchA BYTES, sketchB BYTES, seed INT64) -> STRUCT<lower_bound FLOAT64, estimate FLOAT64, upper_bound FLOAT64> | Computes the Jaccard similarity index with upper and lower bounds.\<br\>The Jaccard similarity index J\(A,B\) = \(A ^ B\)/\(A U B\) is used to measure how similar the two sketches are to each other.\<br\>If J = 1.0, the sketches are considered equal. If J = 0, the two s [...]
+
+**Examples:**
+
+```sql
+
+# using defaults
+create or replace table `$BQ_DATASET`.tuple_sketch(sketch bytes);
+
+insert into `$BQ_DATASET`.tuple_sketch
+(select `$BQ_DATASET`.tuple_sketch_int64_from_theta_sketch(`$BQ_DATASET`.theta_sketch_agg_string(cast(value as string)), 1) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
+insert into `$BQ_DATASET`.tuple_sketch
+(select `$BQ_DATASET`.tuple_sketch_int64_from_theta_sketch(`$BQ_DATASET`.theta_sketch_agg_string(cast(value as string)), 1) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
+
+# expected about 20000
+select `$BQ_DATASET`.tuple_sketch_int64_get_estimate(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch)
+) from `$BQ_DATASET`.tuple_sketch;
+
+select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_and_bounds(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch),
+ 2
+) from `$BQ_DATASET`.tuple_sketch;
+
+select `$BQ_DATASET`.tuple_sketch_int64_get_sum_estimate_and_bounds(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch),
+ 2
+) from `$BQ_DATASET`.tuple_sketch;
+
+# expected estimate about 20000
+select `$BQ_DATASET`.tuple_sketch_int64_to_string(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch)
+) from `$BQ_DATASET`.tuple_sketch;
+
+select `$BQ_DATASET`.tuple_sketch_int64_get_theta(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch)
+) from `$BQ_DATASET`.tuple_sketch;
+
+select `$BQ_DATASET`.tuple_sketch_int64_get_num_retained(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union(sketch)
+) from `$BQ_DATASET`.tuple_sketch;
+
+drop table `$BQ_DATASET`.tuple_sketch;
+
+# using full signatures
+create or replace table `$BQ_DATASET`.tuple_sketch(sketch bytes);
+
+insert into `$BQ_DATASET`.tuple_sketch
+(select `$BQ_DATASET`.tuple_sketch_int64_from_theta_sketch_seed(
+ `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(cast(value as string), STRUCT<BYTEINT, INT64, FLOAT64>(10, 111, 0.999)),
+ 1,
+ 111
+) from unnest(GENERATE_ARRAY(1, 10000, 1)) as value);
+insert into `$BQ_DATASET`.tuple_sketch
+(select `$BQ_DATASET`.tuple_sketch_int64_from_theta_sketch_seed(
+ `$BQ_DATASET`.theta_sketch_agg_string_lgk_seed_p(cast(value as string), STRUCT<BYTEINT, INT64, FLOAT64>(10, 111, 0.999)),
+ 1,
+ 111
+) from unnest(GENERATE_ARRAY(100000, 110000, 1)) as value);
+
+# expected about 20000
+select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
+ 111
+) from `$BQ_DATASET`.tuple_sketch;
+
+select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_and_bounds_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
+ 2,
+ 111
+) from `$BQ_DATASET`.tuple_sketch;
+
+select `$BQ_DATASET`.tuple_sketch_int64_get_sum_estimate_and_bounds_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
+ 2,
+ 111
+) from `$BQ_DATASET`.tuple_sketch;
+
+# expected estimate about 20000
+select `$BQ_DATASET`.tuple_sketch_int64_to_string_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
+ 111
+) from `$BQ_DATASET`.tuple_sketch;
+
+select `$BQ_DATASET`.tuple_sketch_int64_get_theta_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
+ 111
+) from `$BQ_DATASET`.tuple_sketch;
+
+select `$BQ_DATASET`.tuple_sketch_int64_get_num_retained_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_union_lgk_seed_mode(sketch, STRUCT<BYTEINT, INT64, STRING>(10, 111, "NOP")),
+ 111
+) from `$BQ_DATASET`.tuple_sketch;
+
+drop table `$BQ_DATASET`.tuple_sketch;
+
+
+# using defaults
+# expected 5
+select `$BQ_DATASET`.tuple_sketch_int64_get_estimate(
+ `$BQ_DATASET`.tuple_sketch_int64_union(
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_int64(key, 1) from unnest([1, 2, 3]) as key),
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_int64(key, 1) from unnest([3, 4, 5]) as key)
+ )
+);
+
+# using full signatures
+# expected 5
+select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_union_lgk_seed_mode(
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_int64_lgk_seed_p_mode(key, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest([1, 2, 3]) as key),
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_int64_lgk_seed_p_mode(key, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest([3, 4, 5]) as key),
+ 10,
+ 111,
+ "MIN"
+ ),
+ 111
+);
+
+# using defaults
+# expected 1
+select `$BQ_DATASET`.tuple_sketch_int64_get_estimate(
+ `$BQ_DATASET`.tuple_sketch_int64_intersection(
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["c", "d", "e"]) as str)
+ )
+);
+
+# using full signatures
+# expected 1
+select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_intersection_seed_mode(
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest(["c", "d", "e"]) as str),
+ 111,
+ "MIN"
+ ),
+ 111
+);
+
+# using defaults
+# expected 2
+select `$BQ_DATASET`.tuple_sketch_int64_get_estimate(
+ `$BQ_DATASET`.tuple_sketch_int64_a_not_b(
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["c", "d", "e"]) as str)
+ )
+);
+
+# using full signatures
+# expected 2
+select `$BQ_DATASET`.tuple_sketch_int64_get_estimate_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_a_not_b_seed(
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "MIN")) from unnest(["c", "d", "e"]) as str),
+ 111
+ ),
+ 111
+);
+
+# using defaults
+# expected 0.2
+select `$BQ_DATASET`.tuple_sketch_int64_jaccard_similarity(
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string(str, 1) from unnest(["c", "d", "e"]) as str)
+);
+
+# using full signatures
+# expected 0.2
+select `$BQ_DATASET`.tuple_sketch_int64_jaccard_similarity_seed(
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "NOP")) from unnest(["a", "b", "c"]) as str),
+ (select `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(str, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "NOP")) from unnest(["c", "d", "e"]) as str),
+ 111
+);
+
+# using defaults
+# expected 1 entry
+select `$BQ_DATASET`.tuple_sketch_int64_to_string(
+ `$BQ_DATASET`.tuple_sketch_int64_filter_low_high(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_string(key, 1),
+ 2,
+ 2
+ )
+) from unnest(["a", "b", "c", "c"]) as key;
+
+# using full signatures
+# expected 1 entry
+select `$BQ_DATASET`.tuple_sketch_int64_to_string_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_filter_low_high_seed(
+ `$BQ_DATASET`.tuple_sketch_int64_agg_string_lgk_seed_p_mode(key, 1, STRUCT<BYTEINT, INT64, FLOAT64, STRING>(10, 111, 0.999, "SUM")),
+ 2,
+ 2,
+ 111
+ ),
+ 111
+) from unnest(["a", "b", "c", "c"]) as key;
+```
diff --git a/tuple/README.md b/tuple/README_template.md
similarity index 81%
copy from tuple/README.md
copy to tuple/README_template.md
index 51af3fe..5efa530 100644
--- a/tuple/README.md
+++ b/tuple/README_template.md
@@ -17,7 +17,12 @@
under the License.
-->
-# Apache DataSketches Tuple Sketches for GCP/BigQuery
+# Apache DataSketches Tuple Sketches for Google BigQuery
+
+Tuple sketches extend the functionality of Theta sketches by
+allowing you to associate a summary value with each item in the set. This
+enables calculations like the sum, minimum, or maximum of values associated with
+the distinct items.
Please visit
[Tuple Sketches](https://datasketches.apache.org/docs/Tuple/TupleSketches.html)
@@ -30,4 +35,3 @@ for more information about DataSketches library.
If you are interested in making contributions to this project please see our
[Community](https://datasketches.apache.org/docs/Community/)
page for how to contact us.
-
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datasketches.apache.org
For additional commands, e-mail: commits-help@datasketches.apache.org