How to Compute Standard Deviation With PostgreSQL
Written by Anber Arif
If you’re working with data-driven decision-making (aren’t we all at least trying to?), you know that statistical analysis is absolutely pivotal, offering insights into the underlying patterns and trends within datasets. One key metric that aids in understanding data variability is standard deviation. This statistical measure quantifies the dispersion of data points and provides valuable context for interpreting the significance of observed values.
In this article, we’ll delve into the fundamental concepts of standard deviation (including examples to make things easier to grasp) and how to compute standard deviation in PostgreSQL, leveraging its built-in functions—hello, stddev()—that facilitate this process.
But if you want to make things really simple, skip to the section on Timescale hyperfunctions: this set of functions, procedures, and data types is optimized for data analysis, allowing you to query, aggregate, and analyze your data with fewer lines of code. Try them for free by creating a Timescale account. For more details on the building blocks of standard deviation, keep reading!
What Is Standard Deviation?
Standard deviation is a statistical measure that quantifies the amount of variation or dispersion in a set of values. In essence, it provides a way to express how much individual data points deviate from the mean of the dataset. A higher standard deviation indicates greater variability, while a lower standard deviation suggests the data points are closer to the mean.
To illustrate this concept, consider two datasets:
Dataset A: [5, 10, 15, 20, 25]
Dataset B: [9, 10, 11, 9, 11]
While both datasets have different means, the standard deviation reveals additional information about the spread of values within each dataset:
Dataset A:
The values in Dataset A are spread out over a broader range, ranging from 5 to 25.
The individual values deviate from the mean (15) to a greater extent, resulting in a higher standard deviation.
The higher standard deviation indicates higher variability or dispersion in the dataset.
Dataset B:
The values in Dataset B are closely clustered around the mean (10).
The individual values deviate minimally from the mean, contributing to a lower standard deviation.
The lower standard deviation suggests less variability, as the values are more tightly packed around the mean.
Why Does Standard Deviation Matter in Time-Series Data Analysis?
Standard deviation is significant in time-series data analysis, serving as a valuable metric for understanding and interpreting data patterns. Here’s why standard deviation matters in this context:
Volatility measurement: Standard deviation quantifies the degree of variability or volatility within a time-series dataset. Higher standard deviation values indicate significant fluctuations in the data, while low values suggest a more stable trend over time.
Risk assessment: In financial markets, for example, standard deviation is crucial for accessing the risk associated with investment portfolios or asset prices. A higher standard deviation implies greater risk and potential reward, making it an essential factor for investors and analysts.
Identifying trends and anomalies: Standard deviation helps identify deviations from the expected or average behavior in a time series. If you’re an analyst, you can use standard deviation to identify abnormal spikes or drops in data, aiding in detecting outliers.
Seasonal analysis: When analyzing time-series data that exhibits seasonality, standard deviation provides insights into the magnitude of seasonal variations. It allows for identifying periods of heightened or reduced activity within a given time frame.
Predictive analytics: Understanding standard deviation aids in predictive modeling, enabling analysts to make more accurate forecasts. Models incorporating standard deviation can better account for potential variability, resulting in more robust predictions.
Quality control in manufacturing: Time-series data in manufacturing often involves monitoring the consistency and quality of processes. Standard deviation is used to identify variations in product quality over time, facilitating timely adjustments and improvements.
How to Compute Standard Deviation?
Now that we have explained why standard deviation is helpful for anyone using statistical analysis, let’s explore the step-by-step process to calculate it.
Population standard deviation
1. Compute the mean (average)
Sum all the data points in the dataset.
Divide the sum by the total number of data points to obtain the mean.
2. Calculate the differences
Subtract the mean from each individual data point.
This step quantifies how much each data point deviates from the mean.
3. Square the differences
Square each of the differences obtained in the previous step.
Squaring ensures that all deviations are positive, emphasizing the magnitude of deviations.
4. Sum the squared differences
Add up all the squared differences obtained in the previous step.
The result is the sum of squared deviations.
5. Divide by the number of data points
Divide the sum of squared differences by the total number of total points (N).
6. Take the square root
The final step involves taking the square root of the value obtained in the previous step.
The result is the standard deviation, representing the average deviation of data points from the mean.
Here’s the mathematical form:
σ: population standard deviation
N: the size of the population
xi: each value from the population
μ: the population mean
Sample standard deviation
The sample standard deviation is a variation of standard deviation specifically designed for datasets that represent a sample rather than the entire population. The formula for calculating sample standard deviation involves dividing by (n-1) instead of n. This correction, known as Bessel’s correction, accounts for the fact that a sample is used rather than the entire population.
Here, xi represents each data point, x is the sample mean, and n is the number of data points in the sample.
Understanding and correctly applying sample standard deviation is crucial, especially when working with limited datasets. It ensures a more accurate representation of the underlying variability within a sample, facilitating robust statistical analyses and informed decision-making.
Calculating Standard Deviation in PostgreSQL
Calculating standard deviation in PostgreSQL involves leveraging the built-in stddev() function, a powerful SQL tool designed to streamline statistical analyses within the database. This function simplifies the computation process and is highly efficient.
What is the stddev() function in PostgreSQL?
The stddev() function in PostgreSQL is a statistical aggregate function that computes the sample standard deviation of all non-null input values. It is essentially an alias for stddev_samp(), and both functions can be used interchangeably in PostgreSQL.
Here’s the syntax of this function:
SELECT stddev(column_name) FROM table_name;
OR
SELECT stddev_samp(column_name) FROM table_name;
stddev()/stddev_samp(): the sample standard deviation aggregate function.
column_name: the specific column for which standard deviation is calculated.
table_name: the table containing the target column.
In contrast, the stddev_pop() function calculates the population standard deviation, considering the entire dataset. Here’s its syntax:
SELECT stddev_pop(column_name) FROM table_name;
stddev_pop(column_name): calculates the population standard deviation for the specified column.
Basic usage
SELECT stddev(sales) FROM transactions;
In this example, the standard deviation of the sales column in the transactions table is computed, providing insights into the variability of sales data.
Grouping data
SELECT city, stddev(temperature) FROM weather GROUP BY city;
This example showcases the ability to calculate standard deviation for each group (in this case, each city) independently, offering insights into temperature variations across different locations.
Filtering data
SELECT stddev(customer_age) FROM customer_data WHERE total_purchases > 1000;
Here, the stddev() function is applied to a subset of data, demonstrating its flexibility in analyzing specific segments of the dataset. Note that the stddev() function in PostgreSQL exclusively handles non-null values, i.e., null values are ignored by this function.
Use cases of stddev() function
The stddev() function in PostgreSQL finds applications in various scenarios, aiding in statistical analysis and decision-making. Here are some common use cases:
Temporal analysis with time-series data
The stddev() function proves invaluable in time-series data analysis by calculating the daily standard deviation of a numerical variable. This assists in understanding how data points vary over time, facilitating the identification of trends, patterns, and potential irregularities.
SELECT time_bucket('1 day', timestamp_column) AS day,
stddev(value_column) AS daily_stddev
FROM time_series_data
GROUP BY day;
Comparing variability across categories
Utilizing stddev() in conjunction with categorical data, such as product categories, allows for comparing variability in numerical values (e.g., sales amounts). This aids in assessing the relative stability or fluctuation within distinct categories.
SELECT category,
stddev(sales_amount) AS sales_variation
FROM sales_data
GROUP BY category;
Assessing data distribution in histograms
Incorporating the stddev() function in histogram analysis provides a granular view of data distribution within specified buckets. This aids in understanding the spread and concentration of data, supporting data quality assessment and normalization efforts.
SELECT width_bucket(data_column, min_value, max_value, num_buckets) AS bucket,
stddev(data_column) AS bucket_stddev
FROM dataset
GROUP BY bucket
ORDER BY bucket;
Calculating Standard Deviation Based on a Specific Time Frame in PostgreSQL
When working with time-series data in PostgreSQL, calculating the standard deviation within a specific time frame is a common requirement for extracting valuable insights. Let’s consider a dataset named sensor_data with columns timestamp and value. The objective is to compute the standard deviation of the value column for a subset of data points falling within a defined time range.
Here’s the SQL query that calculates the standard deviation within a specific time frame while incorporating dynamic date functions and additional filtering conditions:
WITH time_frame AS (
SELECT
NOW() AS end_timestamp,
NOW() - INTERVAL '30 days' AS start_timestamp
)
SELECT
stddev(value)
FROM
sensor_data
WHERE
timestamp BETWEEN (SELECT start_timestamp FROM time_frame) AND (SELECT end_timestamp FROM time_frame)
AND value > 50;
Common Table Expression (CTE): The query begins with a Common Table Expression named time_frame. This CTE defines the dynamic time frame by calculating the current timestamp NOW() as the end timestamp and subtracting a 30-day interval to obtain the start timestamp.
Main query: The main query utilizes the CTE to filter the sensor_data based on the defined time frame. The BETWEEN clause ensures that only data points within the specified time range are considered.
Additional condition: An extra condition is introduced AND value > 50, specifying that the standard deviation should only be calculated for data points with values above 50.
An Easier Way With Timescale: Hyperfunctions
Built on PostgreSQL—but faster—Timescale introduces a robust and streamlined approach for calculating standard deviation through hyperfunctions. Timescale’s hyperfunctions represent a set of advanced analytical functions tailored for time-series data.
These functions are specifically designed to operate seamlessly on temporal datasets, providing a simplified and efficient way to perform complex calculations. They enhance the capabilities of traditional SQL functions but also offer specialized statistical and regression analysis functions, including standard deviation. Take a closer look at Timescale hyperfunctions.
💡If you want to understand PostgreSQL aggregation and how it influenced our hyperfunctions’ design, check out this article.
stats_agg() Functions in Timescale
The stats_agg() functions in Timescale follow a two-step aggregation pattern, providing a more efficient and flexible approach to statistical analyses on time-series data. In this pattern, the calculation is split into two stages:
Intermediate aggregation: Initially, an intermediate aggregate is created using the aggregate function. This step involves aggregating data within specified time intervals.
Final result calculation: The final result is then calculated by applying one or more accessors on the intermediate aggregate. These accessors enable users to extract specific information from the aggregated data.
Advantages of the two-step aggregation pattern
The two-step aggregation pattern offers certain benefits:
Efficiency: multiple accessors can reuse the same intermediate aggregate, leading to enhanced efficiency in the calculation process.
Performance reasoning: separating aggregation from the final computation makes it easier to reason about performance, providing clarity in the analytical process.
Understanding: Especially useful in window functions and continuous aggregates, the two-step aggregation pattern makes it easier to understand calculations that can be rolled up into larger intervals. Explore more about continuous aggregates and window functions here.
Retrospective analysis: the intermediate aggregate stores additional information not available in the final result, enabling retrospective analysis even when the underlying data is dropped.
Standard Deviation in One-Dimensional Data
The stats_agg() hyperfunction for one-variable statistical aggregates in Timescale offers a powerful toolkit for common statistical analyses. These functions, akin to PostgreSQL statistical aggregates, provide additional features and enhanced ease of use within continuous aggregates and window functions. Specifically designed for one-dimensional data, they enable users to effortlessly perform analyses such as calculating averages and standard deviations.
To calculate the standard deviation of a sample containing integers from 0 to 100 using the stats_agg() function in Timescale, you can use the following SQL query:
SELECT stddev(stats_agg(data))
FROM generate_series(0, 100) data;
The generate_series function creates a series of integers from 0 to 100, representing your sample data. The alias data is assigned to this series. The stats_agg() function aggregates the sample data, preparing it for statistical analysis. In this case, it is calculating the standard deviation. The outer stddev() function then computes the standard deviation based on the aggregated sample data. Thus, the query effectively utilizes the stats_agg() function to perform a two-step aggregation, first aggregating the sample data and then calculating the standard deviation. The result provides the standard deviation for the specified sample containing integers from 0 to 100.
Extended example: Standard deviation in 1D data
The below example creates a statistical aggregate to summarize daily statistical data about the variable val1. It then uses the statistical aggregate to calculate standard deviation of the variable:
WITH t as (
SELECT
time_bucket('1 day'::interval, ts) as dt,
stats_agg(val1) AS stats1D
FROM foo
WHERE id = 'bar'
GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
stddev(stats1D),
FROM t;
Here, the Common Table Expression (CTE) named t leverages the time_bucket function to create daily intervals, ensuring that statistical data is aggregated within each interval. The aggregation is performed using stats_agg(val1), summarizing the data for the specified variable. Subsequently, the main query focuses on computing the aggregated statistical data's standard deviation stddev(stats1D). The result is a concise and efficient query that specifically targets the analysis of the variability of val1 over daily intervals, showcasing the flexibility and adaptability of the two-step aggregation pattern with stats_agg() for precise statistical computations. Explore the power of stats_agg() functions for one-dimensional data.
Standard Deviation in Two-Dimensional Data
The stats_agg() functions for two-variable statistical aggregates in Timescale provide a robust set of tools for conducting linear regression analysis on two-dimensional data. This functionality allows for the calculation of essential metrics like the correlation coefficient and covariance between two variables. Additionally, users can compute standard statistics such as average and standard deviation independently for each dimension.
Like PostgreSQL statistical aggregates, these functions offer extended features and enhanced usability, especially in continuous aggregates and window functions. The linear regressions performed by these functions are based on the standard least-squares fitting method, ensuring accuracy and reliability in the analysis.
To calculate the standard deviation for a two-dimensional sample using the stats_agg() function in Timescale, consider the following SQL query:
SELECT stddev_y(stats_agg(data, data))
FROM generate_series(0, 100) data;
The generate_series function creates a series of integers from 0 to 100, representing the sample data. The alias data is assigned to this series. Further, the stats_agg() function aggregates the two-dimensional sample data, considering both dimensions represented by data. The stddev_y() function then calculates the standard deviation along the y-axis (second dimension) for the aggregated two-dimensional sample data.
Extended example: Standard deviation in 2D data
The below example creates a statistical aggregate that summarizes daily statistical data about two variables, val2 and val1, where val2 is the dependent variable and val1 is the independent variable. We then use the statistical aggregate to calculate the standard deviation of the dependent variable.
WITH t as (
SELECT
time_bucket('1 day'::interval, ts) as dt,
stats_agg(val2, val1) AS stats2D,
FROM foo
WHERE id = 'bar'
GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
stddev_y(stats2D)
FROM t;
In this query, the Common Table Expression (CTE) named t utilizes the time_bucket function to create daily intervals and aggregates statistical data for both variables within each daily interval using stats_agg(). The subsequent main query focuses on calculating the standard deviation along the y-axis stddev_y(stats2D) for the dependent variable val2. This approach provides insights into the dispersion of val2 within each daily interval, highlighting the variability in its values.
Tumbling window statistical aggregates
In the following example, window functions are utilized to calculate tumbling window statistical aggregates, focusing on the standard deviation:
SELECT
bucket,
stddev(rolling(stats_agg) OVER fifteen_min, 'pop')
FROM (SELECT
time_bucket('1 min'::interval, ts) AS bucket,
stats_agg(val)
FROM measurements
GROUP BY 1) AS stats
WINDOW fifteen_min as (ORDER BY bucket ASC RANGE '15 minutes' PRECEDING);
The stats_agg(val) function is initially used to aggregate data over each minute, and then the rolling function is applied to re-aggregate the standard deviation over each 15-minute period. The pop parameter specifies the standard deviation for a population.
Importance of Hyperfunctions for Data Analysis
As mentioned earlier, Timescale’s hyperfunctions provide several advantages for data analysis, particularly in the context of time-series datasets:
Optimized for time-series data: Timescale’s hyperfunctions are specifically designed for time-series datasets, providing optimized performance when working with temporal data. They are tailored to handle the unique characteristics of time-stamped datasets more efficiently than generic statistical functions.
Efficient aggregation: Hyperfunctions streamline the process of aggregating statistical measures, such as standard deviation, over time. Their optimized algorithms enhance the efficiency of these calculations, even for large and complex datasets.
Ease of use: Timescale’s hyperfunctions simplify the syntax for complex statistical calculations, making it more accessible for users. The standardized approach to implementing functions like stddev() ensures consistency and reduces the complexity of query construction.
Two-dimensional aggregation: Timescale’s hyperfunctions include two-dimensional aggregation capabilities, allowing for simultaneous analysis of two variables (Y,X). This is useful for scenarios where you need to perform statistical analyses on multiple dimensions simultaneously.
Advanced analytical capabilities: Hyperfunctions extend beyond basic SQL functions, offering advanced analytical capabilities tailored for time-series scenarios. This includes not only standard deviation but also other statistical and regression analysis functions that provide a holistic view of data variability.
Elevate Your PostgreSQL Data Analysis: Try Timescale
From understanding standard deviation's significance in time-series data to learning the mathematical formulas for population and sample standard deviation, we hope this article has helped you learn how to conduct robust statistical analyses within PostgreSQL.
In addition, we also detailed a more advanced and efficient approach by introducing Timescale's hyperfunctions. These specialized functions are tailor-made for time-series data, providing a streamlined and powerful way to perform statistical calculations. Learn more about Timescale's hyperfunctions as they enhance ease of use, efficiency, and analytical capabilities, making them valuable for anyone dealing with temporal datasets.
To experiment with hyperfunctions, you can self-host TimescaleDB and install the Timescale Toolkit or simply create a free Timescale account.