Data Analysis With PostgreSQL: Analytic |Aggregate Functions
- Install PostgreSQL
- Create and populate a table
- Load sample database
- Connect to database
- Analyse data with AVG aggregate function
- Analyse data with SUM aggregate function
- Analyse data with COUNT aggregate function
- Analyse data with MAX aggregate function
- Analyse data with MIN aggregate function
- Analyse data with FIRST VALUE analytic function
- Analyse data with RANK analytic function
- Analyse data with DENSE RANK analytic function
- Analyse data with LAST analytic function
- Analyse data with LAG analytic function
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.