GoogleSQL Documentation

GoogleSQL supports numbering functions, which are a subset of window functions. These functions assign values to each row based on their position within a specified window using an OVER clause.

Supported numbering functions include:

• CUME_DIST: Cumulative distribution (relative position (0,1]).
• DENSE_RANK: Dense rank (1-based, no gaps).
• IS_FIRST: Returns true if the row is within the first k rows.
• IS_LAST: Returns true if the row is within the last k rows.
• NTILE: Quantile bucket number (1-based).
• PERCENT_RANK: Percentile rank (from 0 to 1).
• RANK: Rank (1-based).
• ROW_NUMBER: Sequential row number (1-based).

GoogleSQL supports GEOGRAPHY values and provides a suite of functions starting with ST_ to analyze, construct, and manipulate spatial data.

Key behaviors:

• Most functions operate on or generate GEOGRAPHY values.
• Null Handling: All geography functions return NULL if any input argument is NULL.

GoogleSQL supports data sketches, which are compact, serialized summaries of data aggregations. Sketches are used to reduce query time and storage requirements when computing metrics like cardinality (distinct counts) or quantiles.

Key properties of sketches:

• Compactness: They are typically a fixed size and asymptotically smaller than the input data.
• Re-aggregation: Sketches can be merged to summarize the union of underlying datasets (e.g., merging daily sketches to get a weekly total).
• Approximation: They trade precision for speed and efficiency, introducing a statistical error represented by a confidence interval (CI).
• Lossy: Because they use lossy compression, they cannot be used to recover the original raw data.

Collation defines the rules for sorting and comparing strings in GoogleSQL. By default, GoogleSQL uses case-sensitive sorting (e.g., Z comes before a). Collation allows you to implement case-insensitive sorting or language-specific rules.

To customize collation for a specific operation, you typically assign a collation specification to at least one string input in that operation.

A collation specification determines how strings are sorted and compared. GoogleSQL supports two main types of collation specifications:

1. Binary collation specification: Returns data in Unicode code point order. The only allowed language_tag is binary.
2. Unicode collation specification: Uses the Unicode Collation Algorithm. It follows the format 'language_tag[:collation_attribute]'.

If no specification is assigned, the default behavior is used. Note that a column with an empty collation does not inherit the table's default collation, whereas a column with an unassigned collation does.

Pipe query syntax is an extension to GoogleSQL designed to be more concise than standard query syntax. It uses the pipe symbol |> followed by an operator name and arguments.

Key characteristics:

• Pipe operators can be appended to any valid query.
• It can be used in queries, views, table-valued functions (TVFs), and other standard contexts.
• Pipe syntax can be mixed with standard syntax (e.g., using a standard subquery within a pipe query).
• A pipe operator can access any alias existing in the table preceding the pipe.
• Queries can start with a FROM clause, allowing pipe operators to be added immediately after.

Numbering functions are a subset of window functions that assign values to each row based on their position within a specified window. They require an OVER clause, which can include PARTITION BY and ORDER BY specifications.

Supported numbering functions include:

• CUME_DIST: Cumulative distribution (0, 1].
• DENSE_RANK: Dense rank (1-based, no gaps).
• IS_FIRST(k): Returns true if the row is within the first k rows.
• IS_LAST(k): Returns true if the row is within the last k rows.
• NTILE(n): Assigns rows to n quantile buckets.
• PERCENT_RANK: Percentile rank (0 to 1).
• RANK: Rank (1-based, with gaps for ties).
• ROW_NUMBER: Sequential row number (1-based).

GoogleSQL provides approximate aggregate functions that are scalable in terms of memory usage and time. These functions produce statistical estimates rather than exact results, making them suitable for large data streams where linear memory usage for exact aggregation (like COUNT(DISTINCT ...)) is impractical.

Note that these functions work directly on input data and do not allow specifying precision via sketches. For precision control using sketches, use HyperLogLog++ functions.