Re-use BigQuery window function partition

Question

I need to select multiple columns as part of a LEAD statement. This looks like it will be really inefficient, tripling the number of sorts and partitions required ->

SELECT 
    field,
    field2,
    field3,
    LEAD(field, 1) OVER (PARTITION BY field ORDER BY field ASC) AS nextField,
    LEAD(field2, 1) OVER (PARTITION BY field ORDER BY field ASC) AS nextField2,
    LEAD(field3, 1) OVER (PARTITION BY field ORDER BY field ASC) AS nextField3,
FROM dataset.table

• Is there a better way to do this?
• Does BigQuery optimize for this at query runtime to make it efficient?

Answer 1

Couple of points to add to Mikhail's answer:

1. Yes, BigQuery optimizes it - if window frame is the same, it will be set up only once and multiple functions will run over it.

2. You are right that it is tedious to write same frame over and over again, therefore we worked on improving BigQuery SQL dialect to make it more standard compliant, and in the near future* you will be able to write

SELECT 
    field,
    field2,
    field3,
    LEAD(field, 1) OVER w1 AS nextField,
    LEAD(field2, 1) OVER w1 AS nextField2,
    LEAD(field3, 1) OVER w1 AS nextField3,
FROM dataset.table
WINDOW w1 AS (PARTITION BY field ORDER BY field ASC)

*Cannot really give you firm date, but this is in internal testing right now, so should not be too long.