BigQuery argmax:执行 CROSS JOIN UNNEST 时是否保持数组顺序

Question

>问题：

在 BigQuery 中，标准 SQL，如果我运行

SELECT *
FROM mytable
CROSS JOIN UNNEST(mytable.array)

我能否确定生成的行顺序与数组顺序相同？

例：

假设我有下表mytable：

Row | id   | prediction
1   | abcd | [0.2, 0.5, 0.3]

如果我运行SELECT * FROM mytable CROSS JOIN UNNEST(mytable.prediction)，我可以确定行顺序与数组顺序相同吗？ 即结果表将始终为：

Row | id   | unnested_prediction
1   | abcd | 0.2
2   | abcd | 0.5
3   | abcd | 0.3

有关用例(argmax)的更多背景信息：

我正在尝试查找每行数组最大值 (argmax) 的数组索引，即上面数组中的第二个元素 (0.5)。因此，我的目标输出是这样的：

Row | id   | argmax
1   | abcd | 2

使用CROSS JOIN，一个按prediction值排序的DENSE_RANK窗口函数和一个ROW_NUMBER窗口函数来查找 argmax，我能够使用一些测试数据来做到这一点。您可以使用此查询进行验证：

WITH predictions AS (
SELECT 'abcd' AS id, [0.2, 0.5, 0.3] AS prediction
UNION ALL
SELECT 'efgh' AS id, [0.7, 0.2, 0.1] AS prediction
),
ranked_predictions AS (
SELECT 
id,
ROW_NUMBER() OVER (PARTITION BY id) AS rownum, -- This is the ordering I'm curious about
DENSE_RANK() OVER (PARTITION BY id ORDER BY flattened_prediction DESC) AS array_rank
FROM
predictions P
CROSS JOIN
UNNEST(P.prediction) AS flattened_prediction
)
SELECT
id,
rownum AS argmax
FROM
ranked_predictions
WHERE array_rank = 1

ROW_NUMBER在我的测试中表现良好(即它是根据非嵌套数组排序的)，这可能只是一个巧合，所以确定一下会很好。

Answer 1

我可以确定生成的行顺序与数组顺序相同吗？

您应该使用WITH OFFSET来获取数组中元素的位置，这样您就可以在进一步的逻辑中使用它们进行排序

#standardSQL
WITH `project.dataset.table` AS (
SELECT 'abcd' id, [0.2, 0.5, 0.3] prediction
)
SELECT id, unnested_prediction
FROM `project.dataset.table`, 
UNNEST(prediction) unnested_prediction WITH OFFSET pos
ORDER BY id, pos  

Answer 2

简短的回答：不，不保证维持秩序。

长答案：在实践中，你很可能会看到秩序得到了维持，但你不应该依赖它。您提供的示例类似于这种类型的查询：

SELECT *
FROM (
SELECT 3 AS x UNION ALL
SELECT 2 UNION ALL
SELECT 1
ORDER BY x
)

输出的预期顺序是什么？ORDER BY位于子查询中，外部查询不强制任何排序，因此 BigQuery(或运行此查询的任何引擎)可以根据需要自由地对输出中的行重新排序。您最终可能会收到1, 2, 3，或者您可能会收到3, 2, 1或任何其他订单。更一般的原则是预测不是顺序保存的。

虽然数组的元素顺序定义良好，但当您使用UNNEST函数时，您将数组转换为关系，除非您使用ORDER BY，否则关系没有明确定义的顺序。例如，请考虑以下查询：

SELECT ARRAY(SELECT x + 1 FROM UNNEST(arr) AS x) AS new_arr
FROM (SELECT [1, 2, 3] AS arr)

new_arr数组实际上并不能保证元素按该顺序[2, 3, 4]，因为ARRAY函数内的查询不使用ORDER BY。但是，您可以通过基于元素偏移量进行排序来解决这种非确定性问题：

SELECT ARRAY(SELECT x + 1 FROM UNNEST(arr) AS x WITH OFFSET ORDER BY OFFSET) AS new_arr
FROM (SELECT [1, 2, 3] AS arr)

现在保证输出是[2, 3, 4].

回到最初的问题，您可以通过在计算行号的子查询中施加排序来确保获得确定性输出：

ranked_predictions AS (
SELECT 
id,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY OFFSET) AS rownum,
DENSE_RANK() OVER (PARTITION BY id ORDER BY flattened_prediction DESC) AS array_rank
FROM
predictions P
CROSS JOIN
UNNEST(P.prediction) AS flattened_prediction WITH OFFSET
)

我在UNNEST之后添加了WITH OFFSET，并在ROW_NUMBER窗口中ORDER BY OFFSET，以确保行号是根据数组元素的原始顺序计算的。

Answer 3

默认情况下，它似乎保持数组的顺序不变。

但是，一种 100% 确定的可能方法是强制实施某种无关紧要的排序，这将告诉 BQ 黑盒中的查询处理器在尝试使用任何类型的默认排序时不要使用。

像这样：

WITH predictions AS (
SELECT 'abcd' AS id, [2.1, 0.1, 0.1, 0.2] AS prediction
)
select id, p from predictions
cross join unnest(prediction) p
order by 1=1