Aggregate functions based on current Row value

user_1261124 0
Viewed 317

I am working with data similar to below,

week | product | sale    
1    | ABC     | 2
1    | ABC     | 1
2    | ABC     | 1
3    | ABC     | 5
4    | ABC     | 1
2    | DEF     | 5

Let us say that is my Orders table named tblOrders. Now, in each row, I want to aggregate the total sales from last week for that product - for instance, if I am on week 2 of product "ABC", I need to show the aggregated sales amount of week 1 for product ABC. so, the output should look something like below,

week | product | sale    | ProductPreviousWeekSales
1    | ABC     | 2       | 0
1    | ABC     | 1       | 0
2    | ABC     | 1       | 3
3    | ABC     | 5       | 1
4    | ABC     | 1       | 5
2    | DEF     | 5       | 0

I was originally thinking I could solve this using Aggregates and Window Function, but doesn't look to be so. Another thought I was having is to use Conditional Aggregate - something like sum(case when x=currentRow.x then sale else 0 end), but that wouldn't work too.

Here is the SQLFiddle for above sample - http://sqlfiddle.com/#!18/890b7/2

Note: I need to calculate similar value for Last 4 weeks, so trying to avoid doing this as a sub-query or multiple joins (if possible), as the data set I am working with is very large, and don't want to add to much performance overhead trying to incorporate this change.

sql-server-2019 sql-server sql
4 Answers

Here is one approach which first aggregates your table in a separate CTE and uses LAG to find the previous week's amount, for each week and product:

WITH cte AS (
    SELECT week, product,
           LAG(SUM(sale)) OVER (PARTITION BY product ORDER BY week) AS lag_total_sales
    FROM yourTable
    GROUP BY week, product
)

SELECT t1.week, t1.product, t1.sale,
       COALESCE(t2.lag_total_sales, 0) AS ProductPreviousWeekSales
FROM yourTable t1
INNER JOIN cte t2
    ON t2.week = t1.week AND
       t2.product = t1.product
ORDER BY
    t1.product,
    t1.week;

screen capture from demo link below

Demo

user_18632290

DISCLAIMER

The query I am showing below doesn't work in SQL Server, unfortunately. Up to SQL Server version 2019 the DBMS lacks full support of the RANGE clause that is essential for the query to work. Running the query in SQL Server results in

Msg 4194 Level 16 State 1 Line 1 RANGE is only supported with UNBOUNDED and CURRENT ROW window frame delimiters.

I am not deleting this answer, because this is standard SQL and the approach may help future readers. It runs fine in a lot of DBMS, and maybe a future version of SQL Server will be able to deal with this, too. I've added demos to show that it runs in PostgreSQL, MySQL and Oracle, but fails in SQL Server 2019.

ORIGINAL ANSWER

Your query shown in the fiddle (select a.*, sum(sale) over(partition by product) ProductPreviousWeekSales from tblOrder a) is merely lacking the appropriate windowing clause. As you are dealing with ties here (more than one row per product and week) this needs to be a RANGE clause:

select a.*,
  sum(sale) over(partition by product 
                 order by week range between 1 preceding and 1 preceding
                ) as ProductPreviousWeekSales
from tblOrder a
order by product, week;

(Use COALESCE if you want to see a zero instead of NULL.)

Demos:

user_22707620

You can do from following

; WITH cteorder AS
       (
            SELECT DISTINCT product, week FROM dbo.tblOrder
       )
       
       SELECT 
cte.*,
SUM(ISNULL(b.sale,0))  ProductPreviousWeekSales
from tblOrder a
INNER JOIN cteorder cte ON  cte.product = a.product AND cte.week = a.week
LEFT JOIN dbo.tblOrder b ON b.product = cte.product AND b.week = (a.week-1)
GROUP BY cte.product,
         cte.week

You can run from : Fiddle

Screenshot

user_81313890

You need to select from TblOrders twice. Once, grouping by week and product and summing the sales, and the second time, a row-by-row scan against TblOrders, left-joining it with the grouping query on same product and week offset by 1: If the join fails , the sales value of the joined grouping query returns NULL. You can put in 0 instead of NULL using COALESCE(), but ISNULL() has all chances of being faster, as it has a fixed number of parameters, while COALESCE() has a variable argument list, which comes at a certain cost.

WITH
tblorders(wk,product,sales) AS (
            SELECT 1,'ABC',2
  UNION ALL SELECT 1,'ABC',1
  UNION ALL SELECT 2,'ABC',1
  UNION ALL SELECT 3,'ABC',5
  UNION ALL SELECT 4,'ABC',1
  UNION ALL SELECT 2,'DEF',5
)
,
grp AS (
  SELECT
    wk
  , product
  , SUM(sales) AS sales
  FROM tblorders
  GROUP BY
    wk
  , product
)
SELECT
    o.wk
  , o.product
  , o.sales
  , ISNULL(g.sales,0) AS productpreviousweeksales
FROM tblorders o
LEFT
JOIN grp       g
  ON o.wk - 1 = g.wk
 AND o.product= g.product
ORDER BY 2,1
;
 wk | product | sales | productpreviousweeksales 
----+---------+-------+--------------------------
  1 | ABC     |     2 |                        0
  1 | ABC     |     1 |                        0
  2 | ABC     |     1 |                        3
  3 | ABC     |     5 |                        1
  4 | ABC     |     1 |                        5
  2 | DEF     |     5 |                        0
user_67882550
Related