Скачать или смотреть Calculating Percentage Change in PostgreSQL for Network Metrics

Learn how to calculate the `percentage change` of network metrics in PostgreSQL, using a structured approach with sample data and SQL queries.
---
This video is based on the question https://stackoverflow.com/q/62688273/ asked by the user 'Arda Savran' ( https://stackoverflow.com/u/9170497/ ) and on the answer https://stackoverflow.com/a/62691372/ provided by the user 'Mike Organek' ( https://stackoverflow.com/u/13808319/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Calculating percentage change of a metric for a group in PostgreSQL

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Percentage Change Calculation in PostgreSQL

When working with network metrics in PostgreSQL, a common requirement is to calculate the percentage change of specific values over a defined time interval, such as ten minutes. This could be particularly important for systems monitoring network usage, as it provides insights into traffic patterns, helps identify anomalies, and informs capacity planning.

In this guide, we will walk through a practical example of how to compute the percentage change of the totalbytes_sum metric for specific groups of data (grouped by agentid, input_interface, and sourceipv4address) over two consecutive ten-minute intervals.

Sample Table Structure

We will use a simplified version of the following sample table to illustrate the process:

idtimestampagentidinput_interfacesourceipv4addresstotalbytes_sum107331593648000203.121.214.129interface 110.10.10.103857107341593648000203.121.214.129interface 110.10.10.10145960107311593648600203.121.214.129interface 110.10.10.1020579107361593648600203.121.214.129interface 110.10.10.10121384107371593648600203.121.214.129interface 110.10.10.1072094Problem Statement

Our goal is to create a view that shows, for each combination of agentid, input_interface, and sourceipv4address, the following fields:

timestamp

agentid

input_interface

sourceipv4address

totalbytes_sum

percent change from the previous ten-minute calculation

The percentage should be calculated based on the current ten-minute total and the previous ten-minute total. If no record exists for the previous interval, the percentage change should default to + 100%.

SQL Query to Calculate Percentage Change

To accomplish this, we can use a SQL query that employs the window functions lag() and coalesce(). Here’s an example of how you could write the query:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query

Window Function: OVER w defines how the data will be partitioned. For example, it groups the data by agentid, input_interface, and sourceipv4address, and orders it by timestamp to apply calculations correctly.

Lag Function: lag(totalbytes_sum) OVER w returns the value of totalbytes_sum from the previous row in the defined window.

Elapsed Time and Last Total Calculation: The query also calculates the elapsed time between current and previous timestamps, as well as retrieves the last total bytes sum for display purposes.

Case Statement:

If there’s no previous record (coalesce(lag(timestamp) OVER w, 0) = 0), it assigns a percentage change of 100.0.

It also checks if the time difference exceeds 10 minutes (600 seconds) to assign 100.0%.

For other cases, it calculates the percentage change using the standard formula.

Conclusion

Using SQL functions effectively allows you to derive meaningful insights from your network data by calculating the percentage change of key metrics over time. The sample query provided here will enable you to create a robust view that supports network analysis and monitoring effectively.

We encourage you to try this query on your data and customize it as needed to fit your specific use case. If you run into any challenges, or if you have additional questions, feel free to leave your comments!