Overview
-
Find out about the range of statistical functions and techniques that are available for analyzing data stored within a PostgreSQL database.
Syllabus
-
Introduction
- Gain additional insights from your PostgreSQL data
- What you should know
- Using the exercise files
- Using GROUP BY to aggregate data rows
- Obtain general-purpose aggregate statistics
- Evaluate columns with Boolean aggregates
- Find the standard deviation and variance of a dataset
- Include overall aggregates with ROLLUP
- Return all possible combinations of groups with CUBE
- Segmenting groups with aggregate filters
- Challenge: Group statistics
- Solution: Group statistics
- Create a window function with an OVER clause
- Partition rows within a window
- Streamline partition queries with a WINDOW clause
- Ordering data within a partition
- Calculate a moving average with a sliding window
- Return values at specific locations within a window
- Challenge: Leverage window functions
- Solution: Leverage window functions
- Calculate the median value of a dataset
- Calculate the first and third quartiles of a dataset
- Find the most frequent value within a dataset with MODE
- Determine the range of values within a dataset
- Challenge: Retrieve statistics of a dataset with groups
- Solution: Retrieve statistics of a dataset with groups
- Rank rows with a window function
- Find a hypothetical rank
- View top performers with percentile ranks
- Evaluate probability with cumulative distribution
- Challenge: Evaluate rankings within a dataset
- Solution: Evaluate rankings within a dataset
- Define values with CASE statements
- Merge columns with COALESCE
- Convert values to null with NULLIF
- Output row numbers with query results
- Cast values to a different data type
- Move rows within a result with LEAD and LAG
- Use an IN function with a subquery
- Define WHERE criteria with a series
- Challenge: Calculations across rows
- Solution: Calculations across rows
- Next steps
