Applying a window frame between in Lakehouse SQL

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!