Dynamic LAG function (Standard SQL, BigQuery). Is it possible?

0
votes

I am trying hard to find a solution for that. I've attached an image with a overview about what I want too, but I will write here too.

In LAG function, is it possible to have a dynamic number in the syntax?

LAG(sessions, 3)

Instead of using 3, I need the number of column minutosdelift which is 3 in this example, but it will be different for each situation.

I've tried to use LAG(sessions, minutosdelift) but It is not possible. I've tried LAG(sessions, COUNT(minutosdelift)) and it is not possible either.

The final goal is to calculate the difference between 52 and 6. So, (52/6)-1 which gives me 767%. But to do it I need a dynamic number into LAG function (or another idea to do it).

I've tried using ROWS PRECEDING AND ROWS UNBOUNDED PRECEDING, but again it needs a literal number.

Please, any idea about how to do it? Thanks!

This screenshot might explain it:

enter image description here

My code: this is the last query I've tried, because I have 7 previous views

SELECT 
    DATE, HOUR, MINUTE, SESSIONS, PROGRAMA_2, 
    janela_lift_teste, soma_sessao_programa_2, minutosdelift,
    CASE 
       WHEN minutosdelift != 0 
          THEN LAG(sessions, 3) OVER(ORDER BY DATE, HOUR, MINUTE ASC)
    END AS lagtest,
    CASE 
       WHEN programa_2 = "#N/A" OR programa_2 is null 
          THEN LAST_VALUE(sessions) OVER (PARTITION BY programa_2 ORDER BY DATE, HOUR, MINUTE ASC)
    END AS firstvaluetest,
FROM 
    tbl8
GROUP BY 
    DATE, HOUR, MINUTE, SESSIONS, PROGRAMA_2, 
    janela_lift_teste, minutosdelift, soma_sessao_programa_2
ORDER BY 
    DATE, HOUR, MINUTE ASC
2
sqlgoogle-bigquerylag

2 Answers

0
votes

In BigQuery (as in some other databases), the argument to lag() has to be a constant.

One method to get around this uses a self join. I find it hard to follow your query, but the idea is:

with tt as (
      select row_number() over (order by sessions) as seqnum,
             t.*
      from t
     )
select t.*, tprev.*
from t join
     t tprev
     on tprev.seqnum = t.seqnum - minutosdelift;
0
votes

Consider below example - hope you can apply this approach to your use case

#standardSQL
with `project.dataset.table` as (
  select 1 session, timestamp '2021-01-01 00:01:00' ts, 10 minutosdelift  union all
  select 2, '2021-01-01 00:02:00', 1 union all
  select 3, '2021-01-01 00:03:00', 2 union all
  select 4, '2021-01-01 00:04:00', 3 union all
  select 5, '2021-01-01 00:05:00', 4 union all
  select 6, '2021-01-01 00:06:00', 5 union all
  select 7, '2021-01-01 00:07:00', 3 union all
  select 8, '2021-01-01 00:08:00', 1 union all
  select 9, '2021-01-01 00:09:00', 2 union all
  select 10, '2021-01-01 00:10:00', 8 union all
  select 11, '2021-01-01 00:11:00', 6 union all
  select 12, '2021-01-01 00:12:00', 4 union all
  select 13, '2021-01-01 00:13:00', 2 union all
  select 14, '2021-01-01 00:14:00', 1 union all
  select 15, '2021-01-01 00:15:00', 11 union all
  select 16, '2021-01-01 00:16:00', 1 union all
  select 17, '2021-01-01 00:17:00', 8 
)
select a.*, b.session as lagtest
from `project.dataset.table` a 
left join `project.dataset.table` b
on b.ts = timestamp_sub(a.ts, interval a.minutosdelift minute)

with output

enter image description here