Aggregate(SUM or AVG) over a window for max time period-CodePudding

I have an INPUT table as shown below. I want to calculate 2 metrics:

sellthru = sum(qty sold over 4 weeks) / initial stock at the beginning of the 4-week period x 100

coverage = stock at the end of the 4-week period / AVG(qty sold over 4 weeks) / x 100

The output table should display only the Maximum week for a given barcode.

Ive tried doing this using a window function but only manage to get the rolling value.

sum(quantity) over (partition by barcode order by SUM(week_number) ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING)/NULLIF(sum(stock),0)::decimal

--Code to create the table: 
CREATE TABLE sell ( week_number int, barcode int, quantity int, stock int );

-- Insert values    
insert into sell values (1,222,3,2); insert into sell values (2,222,1,0); insert into sell values (3,222,2,3); insert into sell values (4,222,2,1); insert into sell values (5,222,2,2); insert into sell values (6,222,2,2); insert into sell values (1,333,4,10); insert into sell values (2,333,5,2); insert into sell values (3,333,1,3); insert into sell values (4,333,0,4);

CodePudding user response：

Use subquery and different window functions / analytic function based on condition for getting expected output.

-- PostgreSQL(v11)
SELECT tt.week_number, tt.barcode
     , ((tt.total_qty :: decimal(10, 2) / tt.min_stock :: decimal(10, 2)) * 100) :: decimal(10, 2) sellthru
     , ((tt.max_stock :: decimal(10, 2) / tt.avg_qty :: decimal(10, 2)) * 100) :: decimal(10, 2) coverage
FROM (SELECT t.week_number, t.barcode, t.row_num
           , SUM(quantity) OVER (PARTITION BY t.barcode ORDER BY t.row_num rows between current row and unbounded following) total_qty
           , AVG(quantity) OVER (PARTITION BY t.barcode ORDER BY t.row_num rows between current row and unbounded following) avg_qty
           , FIRST_VALUE(stock) OVER (PARTITION BY t.barcode ORDER BY t.row_num rows between current row and unbounded following) max_stock
           , LAST_VALUE(stock) OVER (PARTITION BY t.barcode ORDER BY t.row_num rows between current row and unbounded following) min_stock
      FROM (SELECT *
                 , ROW_NUMBER() OVER (PARTITION BY barcode ORDER BY week_number DESC) row_num
            FROM sell) t
      WHERE t.row_num <= 4) tt
WHERE tt.row_num = 1

Another approach by using multiple CTE

-- PostgreSQL (v11)
WITH cte AS (
     SELECT *
          , ROW_NUMBER() OVER (PARTITION BY barcode ORDER BY week_number DESC) row_num
     FROM sell
), cte_1 AS (
      SELECT week_number, barcode, row_num
           , SUM(quantity) OVER (PARTITION BY barcode ORDER BY row_num rows between current row and unbounded following) total_qty
           , AVG(quantity) OVER (PARTITION BY barcode ORDER BY row_num rows between current row and unbounded following) avg_qty
           , FIRST_VALUE(stock) OVER (PARTITION BY barcode ORDER BY row_num rows between current row and unbounded following) max_stock
           , LAST_VALUE(stock) OVER (PARTITION BY barcode ORDER BY row_num rows between current row and unbounded following) min_stock
      FROM cte
      WHERE row_num <= 4
)
SELECT week_number, barcode
     , ((total_qty :: decimal(10, 2) / min_stock :: decimal(10, 2)) * 100) :: decimal(10, 2) sellthru
     , ((max_stock :: decimal(10, 2) / avg_qty :: decimal(10, 2)) * 100) :: decimal(10, 2) coverage
FROM cte_1
WHERE row_num = 1

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=5aa32e2e8f80be713774fc3ee761c2be

N.B.: If consider week_number = 5 or any number in future then use row_num <= 5 or that number instead of row_num <= 4