Cumulative Average
The window frame determines the range of rows that contribute to the window function's calculation. In this case, we'll use the
ROWS BETWEEN
clause to specify the frame.Suppose you have a table named
sales
with columnssale_date
andrevenue
. To calculate a cumulative average considering a specific window frame, you can modify theROWS BETWEEN
clause within theOVER()
clause. Here's an example:
SELECT
sale_date,
revenue,
AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_avg
FROM
sales;
In this query, the
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
specifies that the window frame includes all rows from the beginning of the partition (unbounded preceding) up to the current row. This effectively calculates the cumulative average for all previous rows.If you want to calculate a cumulative average within a specific window size, you can adjust the window frame accordingly. For example, to calculate the cumulative average for the last 3 rows, you can use the following query:
SELECT
sale_date,
revenue,
AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS cumulative_avg
FROM
sales;
In this case, the window frame includes the current row and the two preceding rows, calculating the cumulative average over a window of size 3.
Remember that the availability of window frame specifications may depend on the specific SQL database system you are using.
Running Average
To calculate a running or cumulative average using a window function in SQL, you can use the
SUM()
function along with theROWS BETWEEN
clause to define the window frame for the calculation. Here's how you can do it:Suppose you have a table named
sales
with columnssale_date
andrevenue
. To calculate a running average of revenue using a window function, you can use the following SQL query:
SELECT
sale_date,
revenue,
AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg
FROM
sales;
In this query:
ORDER BY sale_date
ensures that the rows are ordered by the sale date.ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
specifies the window frame. It includes all rows from the beginning of the partition (unbounded preceding) up to the current row.
This calculation gives you a running average of the revenue, considering all previous rows up to the current row.
If you want to calculate a running average over a specific number of preceding rows (a moving window), you can adjust the window frame accordingly. For example, to calculate a running average over the last 3 rows, you can use the following query:
SELECT
sale_date,
revenue,
AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_avg
FROM
sales;
In this case, the window frame includes the current row and the two preceding rows, calculating the running average over a window of size 3.
Percent Of Total
It looks like you're asking about calculating the percentage of a value relative to a total using SQL window functions. To calculate the percentage of a value compared to a total, you can use the SUM()
function to compute the total and then divide the value by the total, multiplying by 100 to get the percentage.
Here's an example using SQL to calculate the percentage of each sale's revenue compared to the total revenue:
SELECT
sale_date,
revenue,
(revenue / SUM(revenue) OVER ()) * 100 AS percentage_of_total
FROM
sales;
In this query:
SUM(revenue) OVER ()
calculates the total revenue over the entire result set.(revenue / SUM(revenue) OVER ()) * 100
calculates the percentage of each sale's revenue relative to the total revenue, multiplied by 100 to get the percentage value.
This calculation gives you the percentage of each sale's revenue compared to the total revenue.
If you want to calculate the percentage within a specific window or partition, you can adjust the OVER()
clause accordingly. For instance, to calculate the percentage of each sale's revenue within a specific month compared to the total revenue for that month:
SELECT
sale_date,
revenue,
(revenue / SUM(revenue) OVER (PARTITION BY EXTRACT(MONTH FROM sale_date))) * 100 AS percentage_of_monthly_total
FROM
sales;
In this example, the PARTITION BY EXTRACT(MONTH FROM sale_date)
clause partitions the data by month, and the calculation is performed within each partition.