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 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
GCP BigQuery

What is SQL Cursor Alternative in BigQuery?

SQL Cursor is a database object to retrieve data from a result set one row at a time. The cursor are useful in many scenarios such as updating records using loops, inserting only required records, etc. You can think cursor as a temporary workspace provisioned in database system memory when a SQL query is executed. SQL Cursor always returns one row at a time, you can perform your calculation on returned values. In this article, we will check what is SQL cursor alternative in Google BigQuery with an example. Cursors…

Continue ReadingWhat is SQL Cursor Alternative in BigQuery?
Comments Off on What is SQL Cursor Alternative in BigQuery?
October 28, 2020
GCP BigQuery

BigQuery Recursive Query Alternative – Example

You can use recursive query to identify the hierarchies of data, such as an organizational structure, employee-manager, bill-of-materials, and document hierarchy. Many relational databases such as Teradata, Oracle, etc. supports recursive queries, but Google BigQuery does not support all features. One of such features is Recursive CTE or VIEWS. Google BigQuery does not support either WITH RECURSIVE Clause or using the RECURSIVE Clause in a CREATE VIEW Statement. In this article, we will check Google BigQuery Recursive Query Alternative with an working example. BigQuery Recursive Query Alternative As mentioned earlier,…

Continue ReadingBigQuery Recursive Query Alternative – Example
Comments Off on BigQuery Recursive Query Alternative – Example
October 16, 2020