BigQuery技术问题：查找'L'之前的最后一个非'L'前置值

问题说明 #

给定列表 my_list=['S', 'S', 'S', 'L', 'L', 'L', 'A', 'B', 'B', 'L', 'C', 'D', 'D', 'L']，需要遍历列表，为每个'L'找到其之前最后一个非'L'的值，期望输出：

('L','S')
('L','S')
('L','S')
('L','B')
('L','D')

原代码使用LAG()函数仅能获取前一个元素，遇到连续'L'时会返回前一个'L'，导致结果不符合预期。

解决方案 #

使用LAST_VALUE()窗口函数结合条件判断，追踪最近的非'L'值。核心逻辑是用非'L'值填充后续所有连续'L'的对应字段，再筛选出目标行。

以下是修正后的BigQuery代码：

WITH data AS (
  SELECT ['S', 'S', 'S', 'L', 'L', 'L', 'A', 'B', 'B', 'L', 'C', 'D', 'D', 'L'] AS elements
),
unnested_data AS (
  SELECT 
    element,
    ROW_NUMBER() OVER () AS offset
  FROM data, UNNEST(elements) AS element
),
filled_data AS (
  SELECT
    element,
    LAST_VALUE(IF(element != 'L', element, NULL) IGNORE NULLS) OVER (ORDER BY offset ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_non_l
  FROM unnested_data
)
SELECT
  'L' AS L,
  last_non_l AS preceding_value
FROM filled_data
WHERE element = 'L';

代码解释 #

1. unnested_data：将数组拆分为单行记录，同时标记每个元素的位置偏移量，保证遍历顺序。
2. filled_data：通过IF函数将非'L'值保留、'L'值设为NULL，再用LAST_VALUE(...) IGNORE NULLS向前填充最近的非'L'值，让所有后续'L'行都能继承这个值。
3. 最后筛选出元素为'L'的行，即可得到每个'L'对应的最近前置非'L'值。

内容的提问来源于stack exchange，提问作者linleynnrs