You may have to break-up your query into several unioned sub-queries to get around the restrictions of the offset which must be a a non-negative integer literal or parameter. refer
The formula ((MONTH+8) % 6) + 4 where month must be in range 1 to 12 gives a fixed result range of 4 to 9 like so:
select
month, ((MONTH+8) % 6) + 4
from (
select 1 as month union all select 2 as month union all select 3 as month union all select 4 as month union all
select 5 as month union all select 6 as month union all select 7 as month union all select 8 as month union all
select 9 as month union all select 10 as month union all select 11 as month union all select 12 as month
) x
order by 2, 1
| month |
(No column name) |
| 4 |
4 |
| 10 |
4 |
| 5 |
5 |
| 11 |
5 |
| 6 |
6 |
| 12 |
6 |
| 1 |
7 |
| 7 |
7 |
| 2 |
8 |
| 8 |
8 |
| 3 |
9 |
| 9 |
9 |
fiddle
So arrange the sub-queries so that the offset can be an integer literal by using where clauses that choose the months to suit the needed offset.
SELECT
*
, LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
, LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
, LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
, LAG (CPI, 4) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (4,10)
UNION ALL
SELECT
*
, LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
, LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
, LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
, LAG (CPI, 5) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (5,11)
UNION ALL
SELECT
*
, LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
, LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
, LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
, LAG (CPI, 6) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (6,12)
UNION ALL
SELECT
*
, LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
, LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
, LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
, LAG (CPI, 7) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (1,7)
UNION ALL
SELECT
*
, LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
, LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
, LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
, LAG (CPI, 8) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (2,8)
UNION ALL
SELECT
*
, LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
, LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
, LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
, LAG (CPI, 9) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (3,9)
ORDER BY Year, Month
offset must be a non-negative integer literal or parameter.see cloud.google.com/bigquery/docs/reference/standard-sql/… i.e. it does NOT accept an expression which is what you have attempted to do. So, no "easy workaround".offset The number of rows back from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.so it is much more adaptable.