问题:
在 BigQuery、标准 SQL 中,如果我运行
SELECT *
FROM mytable
CROSS JOIN UNNEST(mytable.array)
我可以确定生成的行顺序与数组顺序相同吗?
Example:
假设我有下表mytable
:
Row | id | prediction
1 | abcd | [0.2, 0.5, 0.3]
如果我跑SELECT * FROM mytable CROSS JOIN UNNEST(mytable.prediction)
,我可以确定行顺序与数组顺序相同吗? IE。结果表将始终是:
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
Using CROSS JOIN
, a DENSE_RANK
窗口函数由prediction
值和一个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
在我的测试中表现良好(即它是根据未嵌套的数组排序的),所以最好能确定。