Reusing the BigQuery window function section

Question

I need to select multiple columns as part of a LEAD statement. It looks like it will be really inefficient, three times as many types 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 run time to make it efficient?

Answer 1

A few points to add to Michael's answer:

• Yes, BigQuery optimizes it - if the window frame is the same, it will be configured only once, and several functions will work on it.

• , , BigQuery SQL, , *

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)

* , , .

Answer 2

, SQL -. , PARTITION BY ... ORDER BY ... SELECT, , , .
, PARTITION BY - , . ORDER BY

BigQuery , ?

.
, BigQuery , . , , . ( , )

?

- . , , / / ,

, ( ).
ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) as POS /.
ON a.partition = b.partition AND a.POS = a. (POS + 1) < - ,
, a b

, , , ,