BigQuery数组-相对位置导航

Question

UPDATE：虽然导航工作，但现在的问题是使用它-访问生成的匿名结构中的字段(不能按名称访问字段(-所以导航工作了，但我缺少了一些东西，所以结果实际上是可访问的？

在填写这篇文章时，我回答了自己的问题，但无论如何我都想分享这一点(与偏移是我不知道的关键(。我在研究如何在处理数组时实现"导航"功能(向前/向后看"n"，类似于滞后/领先(。在这个例子中，我创建了一个具有下一个和上一个值的结构：

WITH items AS
(SELECT ["apples", "bananas", "pears", "grapes"] as list)
SELECT
list AS original_list, 
ARRAY(SELECT (item, list[SAFE_OFFSET(pos+1)], list[SAFE_OFFSET(pos-1)]) FROM UNNEST(list) item WITH OFFSET pos) new_list
FROM
items

产生。。。

[("apples","bananas",NULL), ("bananas","pears","apples"), ("pears", "grapes", "bananas"), ("grapes", NULL, "pears")]

工作得很好——所以我的最后一个问题是如何对结构字段的名称进行别名，BQ验证会给出解析错误，这可能吗？或者只能是"_field_1"匿名的？

即AS X、AS Next、AS Previous

ARRAY(SELECT (item AS X, list[SAFE_OFFSET(pos+1)] AS Next, list[SAFE_OFFSET(pos-1)] AS Previous) FROM UNNEST(list)

'应为"("或"，"，但得到了关键字AS'-这可能吗？

一个有趣的转折点可能是对之前的一些计算，如果我不仅想知道之前的值，还想知道之前所有之前的值相对于此行的"MIN"值，该怎么办？

谢谢！

Answer 1

所以我的最后一个问题是如何对结构字段的名称进行别名，BQ验证会给出解析错误，这可能吗？或者只能是"_field_1"匿名的？

您最初的查询非常接近-您只是错过了使用关键字STRUCT

#standardSQL
SELECT
list AS original_list, 
ARRAY(
SELECT STRUCT(item AS X, list[SAFE_OFFSET(pos+1)] AS Next, list[SAFE_OFFSET(pos-1)] AS Previous) 
FROM UNNEST(list) item WITH OFFSET pos) new_list
FROM
items   

或者，您可以使用AS STRUCT，如下面的示例所示

#standardSQL
SELECT
list AS original_list, 
ARRAY(
SELECT AS STRUCT item AS X, list[SAFE_OFFSET(pos+1)] AS Next, list[SAFE_OFFSET(pos-1)] AS Previous
FROM UNNEST(list) item WITH OFFSET pos
) new_list
FROM
items

Answer 2

您应该使用struct为它们命名。

WITH items AS
(SELECT ["apples", "bananas", "pears", "grapes"] as list)
SELECT
list AS original_list, 
(
SELECT ARRAY_AGG(STRUCT(item, next, prev))
FROM (
SELECT l AS item, LAG(l) OVER (order by o) as prev, LEAD(l) OVER (order by o) as next
FROM UNNEST(list) l WITH OFFSET o
ORDER BY o
)
) as new_list
FROM
items

要访问它们，您需要取消对它们的测试。一个例子是：

WITH 
items AS
(
SELECT 1 as id, ["apples", "bananas", "pears", "grapes"] as list union all
SELECT 2 as id, ["strawberries", "oranges", "kiwis", "figs"] as list
),
grouped AS
(
SELECT
id,
list AS original_list, 
(
SELECT ARRAY_AGG(STRUCT(item, next, prev))
FROM (
SELECT l AS item, LAG(l) OVER (order by o) as prev, LEAD(l) OVER (order by o) as next
FROM UNNEST(list) l WITH OFFSET o
ORDER BY o
)
) as new_list
FROM
items
)
SELECT id, list_item.item, list_item.next, list_item.prev
FROM grouped
JOIN UNNEST(new_list) as list_item