Bigquery lag window
时间: 2023-09-22 08:15:26 浏览: 41
In BigQuery, the LAG() window function is used to return the value of a specific row in a table, relative to the current row, based on a given offset. The LAG() function is often used to calculate the difference between values in consecutive rows.
Here is an example of how to use the LAG() function in BigQuery:
```
SELECT
date,
revenue,
LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue
FROM
sales
```
In this example, we are selecting the date and revenue columns from the sales table, and we are also using the LAG() function to calculate the previous revenue value for each row. The LAG() function takes two arguments: the name of the column to retrieve the value from (in this case, revenue), and the offset (in this case, 1).
The OVER() clause is used to define the window over which the function should be applied. In this case, we are ordering the rows by date, so the LAG() function will return the value of the revenue column from the previous row based on the order of the dates.
The result of this query will be a table with three columns: date, revenue, and prev_revenue, where prev_revenue is the revenue value from the previous row.