Applying a window frame between in Lakehouse SQL

[debiagui]

We're creating some SQL views in a Lakehouse.

In particular customer need to process the moving total over 3 months. I was lookng at this: OVER Clause (Transact-SQL) - SQL Server | Microsoft Learn

But seems like Lakehouse doesn't support something like:  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

full query:

SELECT

    PRD.ProductId,

    SO.MarketId,

    CA.MonthSeq AS MonthGrouping,

    SUM(SO.SalesAmount) OVER (

        PARTITION BY PRD.ProductId, SO.MarketId

        ORDER BY CA.MonthSeq

        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

    ) AS MovingTotal

FROM

    SellOut AS SO

INNER JOIN

    Product AS PRD ON SO.ProductId = PRD.ProductId

INNER JOIN

    Calendar AS CA ON SO.Date= CA.Date

GROUP BY

    PRD.ProductId,

    SO.MarketId,

    CA.MonthSeq 

[Anonymous · Accepted solution]

Hi @debiagui ,

The `ROWS BETWEEN` syntax, commonly used for defining window frames in SQL Server, might not directly apply in the Lakehouse environment. This is because the SQL analytics endpoint in Lakehouse, while providing a rich set of functionalities for data analysis, does not support the full T-SQL surface area, including certain windowing functions and syntaxes.

For calculating moving totals in Lakehouse, Here are a couple of suggestions:

1. You could leverage SparkSQL to perform complex windowing operations. SparkSQL provides extensive support for window functions, including moving averages and totals. More information on SparkSQL window functions can be found here:

Built-in Functions - Spark 3.5.1 Documentation (apache.org)

2. Use self-linking methods to simulate window functions, please try this query:

SELECT
    A.ProductId,
    A.MarketId,
    A.MonthSeq AS MonthGrouping,
    SUM(B.SalesAmount) AS MovingTotal
FROM
    (SELECT
        PRD.ProductId,
        SO.MarketId,
        CA.MonthSeq
     FROM
        SellOut AS SO
     INNER JOIN
        Product AS PRD ON SO.ProductId = PRD.ProductId
     INNER JOIN
        Calendar AS CA ON SO.Date= CA.Date
     GROUP BY
        PRD.ProductId,
        SO.MarketId,
        CA.MonthSeq) AS A
INNER JOIN
    (SELECT
        PRD.ProductId,
        SO.MarketId,
        CA.MonthSeq,
        SO.SalesAmount
     FROM
        SellOut AS SO
     INNER JOIN
        Product AS PRD ON SO.ProductId = PRD.ProductId
     INNER JOIN
        Calendar AS CA ON SO.Date= CA.Date) AS B
ON
    A.ProductId = B.ProductId AND
    A.MarketId = B.MarketId AND
    B.MonthSeq BETWEEN A.MonthSeq - 2 AND A.MonthSeq
GROUP BY
    A.ProductId,
    A.MarketId,
    A.MonthSeq
ORDER BY
    A.ProductId,
    A.MarketId,
    A.MonthSeq;

3. If the lack of support for certain SQL syntaxes significantly impacts your use cases, consider providing feedback to the Lakehouse platform team. While direct support might not be available now, user feedback can influence future enhancements and feature additions.

For more insights into the capabilities and limitations of the SQL analytics endpoint in Lakehouse, you might find the following documentation useful:

What is a lakehouse? - Microsoft Fabric | Microsoft Learn

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!