Apache Spark

Spark SQL Count Distinct Window Function

Windows are commonly used analytical functions in a Spark SQL query. The COUNT is one of such a windows functions that will allow you to count over certain window. Many relational databases such as Oracle support COUNT window function with distinct keyword. However, Spark SQL does not support count distinct window function. Spark SQL Count Distinct Window Function Spark SQL supports count window functions. However, the COUNT window function with distinct keyword is not supported as of now. Following is the example of an Oracle COUNT window function with distinct…

Continue ReadingSpark SQL Count Distinct Window Function
Comments Off on Spark SQL Count Distinct Window Function
November 29, 2022
Redshift

Redshift Nested Window Functions and Examples

Amazon Redshift cloud data warehouse supports many useful windows or analytical functions. Many Redshift reporting queries use the analytic functions such as cumulative sum and average. But, because of Redshift limitation, you cannot call an analytics function within another. Whenever you try to call an analytics function within another analytics function, you will end up with an error such as "Invalid operation: aggregate function calls may not have nested aggregate or window function". In this article, we will check how to use the nested window functions in Amazon Redshift with…

Continue ReadingRedshift Nested Window Functions and Examples
Comments Off on Redshift Nested Window Functions and Examples
July 21, 2021
Snowflake

Snowflake Nested Window Functions and Examples

Snowflake supports many useful windows or analytical functions. Many reporting queries use the analytic functions such as cumulative sum and average. But, whenever you try to call an analytics function within another analytics function, you will end up with an error such as "may not be nested inside another window function.". In this article, we will check how to use the nested window functions in Snowflake with an alternate example. Snowflake does not allow you to define the nested window function. You will have to use alternative methods such as…

Continue ReadingSnowflake Nested Window Functions and Examples
Comments Off on Snowflake Nested Window Functions and Examples
July 8, 2021
General

SQL and Hive GROUP BY Alternative-Example

It is common to write the queries using GROUP BY and HAVING clause to group records or rows. Group by clause use columns in Hive or relational database tables for grouping particular column values mentioned with the group by. But, GROUP BY and DISTINCT operations are costly. It is applicable to both Hive and relational databases. But, in some cases, you can rewrite the queries to remove GROUP BY clause. In this article, we will check what are GROUP BY alternative methods available in Hive and SQL. SQL and Hive…

Continue ReadingSQL and Hive GROUP BY Alternative-Example
Comments Off on SQL and Hive GROUP BY Alternative-Example
January 24, 2020
General

Teradata ROWNUM Pseudocolumn Alternative and Examples

The relational databases like Oracle supports the ROWNUM pseudo column. If you are from Oracle background, you may have used ROWNUM extensively to limit the output result set. But, There is no ROWNUM in Teradata. In this article, we will check Teradata ROWNUM pseudocolumn alternative with some examples. Teradata ROWNUM Pseudocolumn Alternative A ROWNUM is a pseudo column, which indicates the row number in a result set retrieved by a SQL query. It starts by assigning 1 to the first row and increments the ROWNUM value with each subsequent row returned. In the databases…

Continue ReadingTeradata ROWNUM Pseudocolumn Alternative and Examples
Comments Off on Teradata ROWNUM Pseudocolumn Alternative and Examples
December 17, 2019