arrays - BigQuery argmax:进行 CROSS JOIN UNNEST 时是否保持数组顺序
问题描述
问题:
在 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
窗口函数和查找 argmax 的窗口函数,我可以使用一些测试数据来完成这项工作。您可以使用此查询进行验证:DENSE_RANK
prediction
ROW_NUMBER
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
在我的测试中表现良好(即它是根据未嵌套的数组排序的),所以很高兴能确定。
解决方案
我可以确定生成的行顺序与数组顺序相同吗?
您应该使用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
推荐阅读
- python - 自定义登录站点未在 Django 中显示
- java - 在本机 java apk 上添加验证码 v3?
- apache-spark - 将 Spark SQL 数据帧转换为结构化流数据帧
- wpf - WPF 无法将控件停靠在停靠面板中,画布似乎改为居中
- powerpoint - 如何在 PowerPoint 的全屏幻灯片放映模式下禁用自动隐藏任务栏?
- laravel - Laravel Vue 未考虑 Core-ui 样式
- c - 使用 winmm 在 Win32 中播放 MP3 文件
- google-sheets - 有没有办法将 MAX() 与自动多个范围值一起使用?
- javascript - TypeError:验证不是 react.js 中的函数
- javascript - 检查是否提供了所有属性或全部没有