Data Analysis With PostgreSQL: Analytic Aggregate Functions

Due to the ever increasing reliance and growth of data there is even a greater demand for professionals who can help analyse these large volume of data that is used daily in applications that we use and rely on daily like Facebook, Instagram,bank apps etc. Aggregate functions perform a calculation on a set of values and return a single value. Analytic functions compute an aggregate value based on a set of values, and, unlike aggregate functions, can return multiple rows for each set of values.

If you want to run a query using only aggregate functions, the query will return one row with a column for each field. If you want to run a query using an aggregate function in conjunction with the GROUP BY function, the query will return one row for each value found in the grouped field. If you want to run a query using an analytic function, the query will return one row for each field in the defined range of fields used to perform the calculations.

If you use an aggregate function with any other function in a query, one field in the query must contain the GROUP BY function. Analytic functions are frequently used in fields such as finance and science to provide trend, outlier, and bucketed analysis for large data sets Unlike traditional aggregation functions, which return only a single value for each group defined in the query, Analytic functions return a single value for each input row. They increase the efficiency and reduce the complexity of queries that analyse partitions (windows) of a data set.

This course will use aggregate and analytic functions to query and analyse data from a PostgreSQL database.