GCP BigQuery

Reuse Column Aliases in BigQuery – Lateral Column alias

BigQuery lateral Column alias are columns that are derived from the previously computed columns in same SELECT statement. Derived columns or lateral Column alias are virtual columns that are not physically stored in the table. Their values are re-calculated every time they are referenced in a query. Many PostgreSQL relational databases such as Netezza supports reuse of the column aliases within the same SELECT statement but GCP BigQuery does not support reuse of calculated derived columns. In this article, we will identify the alternate methods reuse column aliases in Google…

Continue ReadingReuse Column Aliases in BigQuery – Lateral Column alias
Comments Off on Reuse Column Aliases in BigQuery – Lateral Column alias
April 18, 2023
GCP BigQuery

Google BigQuery GROUP BY CUBE Alternative and Example

In my other BigQuery related articles, we have seen BigQuery grouping sets alternative, BigQuery control flow statements, NVL and NVL2 alternatives in BigQuery, cursors in BigQuery, etc. In this article, we will check one of the important GROUP BY extensions GROUP BY CUBE alternative in Google BigQuery. Google BigQuery GROUP BY CUBE SQL GROUP BY CUBE in Google BigQuery GROUP BY CUBE is an extension of the GROUP BY clause similar to GROUP BY ROLLUP and GROUPING SETS which is used to analyzes data by grouping it into multiple dimensions. In addition to…

Continue ReadingGoogle BigQuery GROUP BY CUBE Alternative and Example
Comments Off on Google BigQuery GROUP BY CUBE Alternative and Example
September 15, 2022
GCP BigQuery

Google BigQuery Grouping Sets Alternative and Example

In my other BigQuery related articles, we have seen BigQuery control flow statements, NVL and NVL2 alternatives in BigQuery, cursors in BigQuery, etc. In this article, we will check one of the important GROUP BY extensions GROUPING SETS alternative in Google BigQuery. Google BigQuery Grouping Sets SQL GROUPING SETS in Google BigQuery Many modern day analytics databases support GROUPING SETS. The GROUPING SETS option in SQL gives you an ability to combine multiple GROUP BY clauses into one GROUP BY clause. By definition, a grouping set is a group of…

Continue ReadingGoogle BigQuery Grouping Sets Alternative and Example
Comments Off on Google BigQuery Grouping Sets Alternative and Example
September 15, 2022
General

Cloud Spanner FORMAT_DATE Function and Example

You can use the FORMAT_DATE function to format the string containing a date expression. It is one of the highly used functions to format date. This function is equivalent to to_char and to_date function present in other relational databases. In this article, we will check cloud Spanner FORMAT_DATE function, syntax and some example Cloud Spanner FORMAT_DATE Function The format_date function is used to date expression to required format. Cloud Spanner FORMAT_DATE function formats the date expression containing date according to the specified date format string. Cloud Spanner FORMAT_DATE Function Syntax Following is the FORMAT-DATE syntax. FORMAT_DATE(format_string,…

Continue ReadingCloud Spanner FORMAT_DATE Function and Example
Comments Off on Cloud Spanner FORMAT_DATE Function and Example
July 13, 2020
General

Google Cloud Spanner String Functions, Syntax, Examples

Google Cloud Spanner String Functions are used for various string manipulations in your SQL queries. Cloud Spanner supports most of the standard SQL string functions along with the many extensions to those functions. In my other article, Google Cloud Spanner Regular Expression Functions, we have seen regular expression string functions. In this article, we will check other string functions. Google Cloud Spanner String Functions Following are some of the commonly used Cloud Spanner string functions. String FunctionsDescriptionsBYTE_LENGTH(value)Returns the length of the value in bytes. Value can be string or bytes.CHAR_LENGTH(value)…

Continue ReadingGoogle Cloud Spanner String Functions, Syntax, Examples
Comments Off on Google Cloud Spanner String Functions, Syntax, Examples
July 13, 2020
General

Google Cloud Spanner Regular Expression Functions

The regular expressions are commonly used to identify the precise pattern of the characters in the string. Many popular programming languages such as Python, Java, R, etc. extensively use regular expression functions. You can also use regular expression to validate strings, for example, extracting numbers from the string values. In this article, we will check regular expression functions supported in cloud spanner. Cloud Spanner Regular Expression Functions The regular expression functions in cloud spanner are string functions that match a given regular expression. These functions are commonly called as a…

Continue ReadingGoogle Cloud Spanner Regular Expression Functions
Comments Off on Google Cloud Spanner Regular Expression Functions
July 7, 2020