Partition SQL WINDOW function on certain criteria-CodePudding

I am trying to calculate a running total of the AddToCart metric that only starts after a 'product/search/details' page was seen.

Here's the link to SQL Fiddle: http://sqlfiddle.com/#!15/bbf9b/1

In the sqlfiddle link, I've manually created a column to reflect my desiredoutput. The workingoutput column shows where I have gotten to with my code.

SUM(AddToCart) OVER (PARTITION BY SessionID ORDER BY HitNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as workingoutput

I know the below syntax is all wrong, but this is essentially what I am trying to achieve

SUM(AddToCart) OVER (PARTITION BY SessionID ORDER BY HitNumber ROWS BETWEEN UNBOUNDED PRECEDING AND FIRST_VALUE(ROW LIKE "%/product/search/details%")) as workingoutput

CodePudding user response：

You need to nest your window functions here

Start with a running conditional count, checking if we have reached /product/search/details yet, and only return AddToCart based on that
Do a running sum over that result

SELECT
  wd.SessionID,
  wd.HitNumber,
  wd.HitType,
  wd.EventType,
  wd.PageName,
  wd.AddToCart,
  SUM(wd.AddToCartFromSearch) OVER (PARTITION BY wd.SessionID
      ORDER BY HitNumber ROWS UNBOUNDED PRECEDING) AS DesiredOutput
FROM (
    SELECT *,
      CASE WHEN COUNT(CASE WHEN wd.PageName = '/product/search/details' THEN 1 END)
                OVER (PARTITION BY wd.SessionID ORDER BY HitNumber ROWS UNBOUNDED PRECEDING) > 0
           THEN AddToCart ELSE 0 END AS AddToCartFromSearch
    FROM WebData wd
) wd
ORDER BY HitNumber;

SQL Fiddle