I had a working query on SQL Server which resulted in a table with columns having CPI values lagged and also one column having updated values every 6 months with a 4 month lag.
<code>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, ((MONTH+8) % 6) + 4) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
</code>
<code>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, ((MONTH+8) % 6) + 4) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
</code>
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, ((MONTH+8) % 6) + 4) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
System_CPI table is a table having cpi values for each year, month [YEAR | MONTH | CPI]
When trying to do the same query in BigQuery, the LAG function will not accept the MONTH column as parameter. So the query gives an error. Is there an easy workaround for this?
DESIRED OUTPUT:
5
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:
<code>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
</code>
<code>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
</code>
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.
<code>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
</code>
<code>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
</code>
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
1