LAG() and LEAD() in SQL

[xinxueHEAI]

@Colsai Could you explain how LAG(field1) OVER() and LEAD(field2) OVER() work?

• What do they do?
• What should be inside OVER() ?
• What if you want to LAG by 7 days instead of 1 day? Some complete query examples here would be great.

Your SQL is here.

[Colsai]

Hi @xinxueHEAI , Regarding the question about Lag/Lead:

What do they do?

• Both are window functions- they can perform calculations related to the current rows of a dataset, and can allow aggregations over different ranges. Lag and Lead look backwards or forwards through the table's rows to return a value.

Other examples of possible window functions include: sum, count, avg, row_number, rank, dense_rank, ntile.

What should be inside OVER() ?

• In this case, OVER (ORDER BY DATE) performs the operations in order through the date values, so that the LAG/LEAD is calculated based on the ordered dates.

What if you want to LAG by 7 days instead of 1 day? Some complete query examples here would be great.

• In the case of looking for values +/- n days, you can change the offset value:

LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )

So, LAG(usage, 7) OVER (ORDER BY date asc) as lag_use, LEAD(usage,7) OVER (ORDER BY date asc) AS lead_use)

One important weakness of the functions mentioned in the articles I saw was that window functions can't be used with GROUP BY in their aggregations.

Some References: https://mode.com/sql-tutorial/sql-window-functions/ https://www.postgresql.org/docs/9.1/tutorial-window.html https://stackoverflow.com/questions/6218902/the-sql-over-clause-when-and-why-is-it-useful https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15

[xinxueHEAI]

@wbfrench1 Could you work with Pratik (pmathur2@umbc.edu) and Srashti (ssoni1@umbc.edu) on when should I be using OVER(PARTITION BY field1 ORDER BY field2) in a query?

Say SUM(spending) OVER(PARTITION BY id ORDER BY date)

Please elaborate on your findings here.

[wbfrench1]

Will do! We'll coordinate a time and put an answer together! Cheers! Barker

[ssoni1]

test

[xinxueHEAI]

refer to this comment