AVG

The AVG or average function computes the average based on a group of values. For the examples that follow, please reference the orders table within the order entry (oe) schema at livesql.oracle.com. The contents of this table can also be found below. This table contains one row for each order placed by a customer.

Compute the Average Order Total Across All Orders

SELECT
    AVG(oe.orders.order_total) AS average_order_total
FROM
    oe.orders;

In the example code above, note that a GROUP BY clause is not present because the code returns a single row representing the average order total value across all orders (34933.85).

COMPUTE THE AVERAGE ORDER TOTAL FOR EACH CUSTOMER

SELECT
    oe.orders.customer_id,
    AVG(oe.orders.order_total) AS average_order_total
FROM
    oe.orders
GROUP BY
    oe.orders.customer_id;

When an aggregate must be computed across and for each set of grouped rows, a GROUP BY clause is necessary. Here, the rows have been grouped by customer ID and for each of those sets of grouped rows, the average order total is computed.

COMPUTE THE AVERAGE TIME ELAPSED BETWEEN EACH ORDER

-- Using an analytic/window function to solve.
WITH time_elapsed AS (
    SELECT
    	(LEAD(CAST(oe.orders.order_date AS DATE), 1) OVER (
    		ORDER BY oe.orders.order_date
        ) - CAST(oe.orders.order_date AS DATE)
    	) AS amount
    FROM
    	oe.orders
)
SELECT
    AVG(time_elapsed.amount) AS average_time_elapsed
FROM
    time_elapsed;

-- Using an analytic/window function with join logic to solve.
WITH order_duration_setup AS (
    SELECT
    	CAST(oe.orders.order_date AS DATE) AS order_date,
    	ROW_NUMBER() OVER (
    		ORDER BY oe.orders.order_date
    	) AS order_sequence
    FROM
    	oe.orders
)
SELECT
    AVG(duration_end.order_date
    	- duration_start.order_date
    ) AS average_time_elapsed
FROM
    order_duration_setup duration_start
    LEFT JOIN
    order_duration_setup duration_end
    	ON duration_start.order_sequence = duration_end.order_sequence - 1;

Each solution above returns a one-by-one result set containing the average time elapsed between each order (15.33 days).

In the first solution, the analytic/window function, LEAD, is used to compute the difference between the order date value in the next row and the order date value in the current row when the order date values are sorted from earliest to most recent. The average of these individual differences is ultimately computed.

In the second solution, the analytic/window function, ROW_NUMBER, is first used to create a column containing a series of sequential integers. The integer value within each row corresponds to the order for which the orders were placed (i.e., based on the order date). These integer values are then used to ensure that each individual difference is computed using subsequent order date values. This is done by using a LEFT JOIN with the ON clause using the integer values to place the subsequent order date values within the same row. In short, the order date values for order 1 and order 2 will be placed in the same row. Order date values 2 and 3 in the same row and so forth. See the table below for clarification on how the result set would appear after the LEFT JOIN occurs and prior to computing the individual differences. My intent in displaying the table below is to show the result of the LEFT JOIN operation that allows the difference between subsequent order date values to be computed within each row. In the end, the average of each these individual differences is computed. It’s important to note that the additional complexity of ROW_NUMBER can be removed if the order date sequence can be inferred by the order ID of each order. That is, if the order ID values are ordered in ascending order, then the order date values would naturally be ordered as well. In this case, the ON clause would utilize the order ID value instead of our derived order date sequence values.

Compute the average TIME ELAPSED between each CUSTOMER’s ORDERS

-- Using an analytic/window function to solve.
WITH time_elapsed AS (
    SELECT
    	oe.orders.customer_id,
    	oe.orders.order_date,
    	(LEAD(CAST(oe.orders.order_date AS DATE), 1) OVER (
    		PARTITION BY oe.orders.customer_id
    		ORDER BY oe.orders.order_date
        ) - CAST(oe.orders.order_date AS DATE)
    	) AS amount
    FROM
    	oe.orders
)
SELECT
    time_elapsed.customer_id,
    AVG(time_elapsed.amount) AS average_time_elapsed
FROM
    time_elapsed
GROUP BY
    time_elapsed.customer_id;

-- Using an analytic/window function with join logic to solve.
WITH order_duration_setup AS (
    SELECT
    	oe.orders.customer_id,
    	CAST(oe.orders.order_date AS DATE) AS order_date,
    	DENSE_RANK() OVER (
    		PARTITION BY oe.orders.customer_id
    		ORDER BY oe.orders.order_date
    	) AS order_sequence
    FROM
    	oe.orders
)
SELECT
    duration_start.customer_id,
    AVG(duration_end.order_date
    	- duration_start.order_date
    ) AS average_time_elapsed
FROM
    order_duration_setup duration_start
    LEFT JOIN
    order_duration_setup duration_end
        ON duration_start.customer_id = duration_end.customer_id
    	AND duration_start.order_sequence = duration_end.order_sequence - 1
GROUP BY
    duration_start.customer_id;

Computing the average time elapsed between each order for each customer requires that we simply build onto our previous solution. In both solutions, a PARTITION BY clause is added to ensure each individual difference between subsequent order date values is calculated using the same customer (e.g., difference is computed using the first and second order date values for customer ID 101). I find that as the solution complexity increases, it becomes increasingly important to understand the intermediate result sets or steps returned or taken to fully understand the means to produce the final result set. See the intermediate result sets below (one for each solution), highlighting the appearance of the data prior to aggregation (i.e., before the average time elapsed between subsequent orders is computed for each customer). Additionally, consider taking a further step back by executing the SQL statements within the common table expression for each solution by itself and ensure the output of these statements are understood.

Knowledge Check
Need more practice or simply wish to test your understanding? Give a few of the practice problems I’ve provided in the SQL Practice Problems section a try.