I want to add an average cost column which calculates the average across different time periods.

So in the example below, there are 6 months of cost, the first column finds the average across all 6 i.e. average(1,5,8,12,15,20)

The next "Half Period" column determines how many total periods there are and calculates the average across the most recent 3 periods i.e. average(12,15,20)

The first average is straightforward e.g.

AVG(COST)

What I've tried for the half period is:

AVG(COST) OVER (ORDER BY PERIOD ROWS BETWEEN x PRECEDING AND CURRENT ROW)

The x is of course an integer value, how would I write the statement to automatically enter the integer required? i.e. in this example 6 periods requires 3 rows averaged, therefore x=2.

x can be found by some sub-query e.g.

SELECT ( CEILING(COUNT(PERIOD) / 2) - 1) FROM TABLE

Example table:

Desired Output:

CodePudding user response：

The main problem here is that you cannot use a variable or an expression for the number of rows Preceeding in the window expression, we must use a literal value for x in the following:

BETWEEN x PRECEDING

If there is a finite number of periods, then we can use a CASE statement to switch between the possible expressions:

CASE
    WHEN CEILING(COUNT(PERIOD) / 2) - 1 <= 1 
        THEN AVG(COST) OVER (ORDER BY PERIOD ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    WHEN CEILING(COUNT(PERIOD) / 2) - 1 <= 2 
        THEN AVG(COST) OVER (ORDER BY PERIOD ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    WHEN CEILING(COUNT(PERIOD) / 2) - 1 <= 3 
        THEN AVG(COST) OVER (ORDER BY PERIOD ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
    WHEN CEILING(COUNT(PERIOD) / 2) - 1 <= 4 
        THEN AVG(COST) OVER (ORDER BY PERIOD ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)
    WHEN CEILING(COUNT(PERIOD) / 2) - 1 <= 5 
        THEN AVG(COST) OVER (ORDER BY PERIOD ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
    WHEN CEILING(COUNT(PERIOD) / 2) - 1 <= 6 
        THEN AVG(COST) OVER (ORDER BY PERIOD ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
END as [Half Period Average Cost]

CodePudding user response：

I added this step in SQL. But my window function denied taking the variable half_period_rounded. So we're not quite there yet. :-)

SQL query