BigQuery: UNNEST()上的LAG()

Question

我正在研究如何使用LAG()从ARRAY of STRUCT中的上一行返回一个值，而UNNEST似乎不支持像LAG/LEAD这样的导航窗口函数。

听起来不太理想，但是您需要UNNEST all和(re)PARTITION BY！

例如，假设您想要上一行的数量：

编辑：模式详细信息： 发票模式：docID STRING, lines STRUCT<lineID STRING, amount NUMERIC> 预期产出：docID STRING, lines STRUCT<lineID STRING, amount NUMERIC, prev_amount NUMERIC>

SELECT 
   docID, lineID, amount
   ,LAG(amount) OVER (PARTITION BY docID ORDER by lineID) prev_amount
FROM invoices, UNNEST(lines)

这太慢了。我希望我们能以某种方式传递数组作为窗口框使用。

有其他选择吗?，我正在使用BigQuery，但这可能适用于其他DBMS。

更新：我提出了以下使用WITH OFFSET的替代方案。更快，也许这就是偏移的意义，它只是丑陋和可能更简单的想法。

SELECT 
   docID, lineID, amount
   ,(SELECT amount FROM UNNEST(lines) WITH OFFSET AS pos2 WHERE pos2 = pos - 1) prev_amount
FROM invoices, UNNEST(lines) WITH OFFSET AS pos

Answer 1

下面是米哈伊尔答案的替代方案，用WITH OFFSET代替，表现更好(55对170，超过5000万行)

#standardSQL
SELECT docID, ARRAY(
    SELECT AS STRUCT 
      l.*, 
      lines[SAFE_OFFSET(pos - 1)].amount prev_amount 
    FROM UNNEST(lines) l WITH OFFSET pos
    ) lines
FROM invoices

注意:如果将ORDER BY或WHERE子句添加到取消嵌套以更改原始索引，这将不能正常工作。

这是我的用例，如果您是在筛选数组中进行相对定位，则应该首先使用子查询来创建筛选过的数组，然后在外部查询中使用UNNEST WITH OFFSET + OFFSET/ORDINAL组合。

Answer 2

下面显示了更好的性能(至少在我运行它以进行快速测试的小数量上是如此)。此外，执行计划看起来要好得多(至少对我来说)

#standardSQL
SELECT 
   lines[OFFSET(pos)].*, lines[SAFE_OFFSET(pos - 1)].amount prev_amount
FROM invoices, UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(lines) - 1)) pos

注意:我假设以下模式是[STRUCT<docID INT64, lineID INT64, amount FLOAT64>] lines

UPDATE:调整以匹配您刚才提供的真实模式

#standardSQL
SELECT docID, ARRAY(
    SELECT AS STRUCT 
      lines[OFFSET(pos)].*, 
      lines[SAFE_OFFSET(pos - 1)].amount prev_amount 
    FROM UNNEST(GENERATE_ARRAY(0, ARRAY_LENGTH(lines) - 1)) pos
    ) lines
FROM invoices