LAG(偏移量)，直到在BigQuery中达到值为止 [英] LAG(offset) until value is reached in BigQuery

问题描述

在BigQuery中以某种方式使用 LAG()是否可以一直持续到满足特定值或条件?换句话说，我需要 offset 能够根据要跳转的行数动态变化.

Is it somehow possible to use LAG() in BigQuery in such a way that it continues until a certain value or condition is met? In other words, I need the offset to be dynamic based on the number of rows to jump.

例如:

#standardSQL
WITH input AS (
  SELECT 1 AS id, 1 AS col_1, 'A' AS col_2 UNION ALL
  SELECT 2 AS id, 0 AS col_1, 'B' AS col_2 UNION ALL
  SELECT 3 AS id, 0 AS col_1, 'C' AS col_2 UNION ALL
  SELECT 4 AS id, 1 AS col_1, 'D' AS col_2 UNION ALL
  SELECT 5 AS id, 1 AS col_1, 'E' AS col_2 UNION ALL
  SELECT 6 AS id, 0 AS col_1, 'F' AS col_2 UNION ALL
  SELECT 7 AS id, 1 AS col_1, 'G' AS col_2 )

如果 col_1 等于0，则取 col_2 的值，其中 col_1 之前的最后

If col_1 equals 0, then take the value of col_2 where the last preceding col_1 equals 1.

所需的输出:

我可以使用自我联接来做到这一点，但我宁愿避免使用联接(如果可能的话)，即必须有一种更聪明的方法！

I could do it with self joins, but I'd rather avoid using joins if possible i.e. there has to be a smarter way!

推荐答案

BigQuery标准SQL

BigQuery Standard SQL

#standardSQL
WITH input AS (
  SELECT 1 AS id, 1 AS col_1, 'A' AS col_2 UNION ALL
  SELECT 2 AS id, 0 AS col_1, 'B' AS col_2 UNION ALL
  SELECT 3 AS id, 0 AS col_1, 'C' AS col_2 UNION ALL
  SELECT 4 AS id, 1 AS col_1, 'D' AS col_2 UNION ALL
  SELECT 5 AS id, 1 AS col_1, 'E' AS col_2 UNION ALL
  SELECT 6 AS id, 0 AS col_1, 'F' AS col_2 UNION ALL
  SELECT 7 AS id, 1 AS col_1, 'G' AS col_2 
)
SELECT id, col_1, col_2,
  IF(col_1 = 0, FIRST_VALUE(col_2) OVER(PARTITION BY grp ORDER BY col_1 DESC), NULL) AS col_3 
FROM (
  SELECT * ,
    SUM(col_1) OVER(ORDER BY id) AS grp
  FROM input
)
-- ORDER BY id

这篇关于LAG(偏移量)，直到在BigQuery中达到值为止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！