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

使用 BigQuery 中的 argmax：CROSS JOIN UNNEST 时数组顺序的保持

sql
-- 创建示例表格
CREATE TABLE student_grades AS
SELECT 'Alice' AS student, [90, 85, 95] AS grades
UNION ALL
SELECT 'Bob' AS student, [88, 92, 91] AS grades
UNION ALL
SELECT 'Charlie' AS student, [95, 89, 93] AS grades;
-- 使用CROSS JOIN UNNEST展开数组并保持顺序
SELECT student, grade
FROM student_grades, UNNEST(grades) AS grade;

sql
-- 使用argmax找到每个学生的最高成绩的索引
WITH indexed_grades AS (
  SELECT
    student,
    ARRAY(SELECT AS STRUCT * FROM UNNEST(grades) WITH OFFSET) AS indexed_grades
  FROM
    student_grades
)
SELECT
  student,
  (SELECT MAX_INDEX FROM UNNEST(indexed_grades) WHERE grade = MAX(grade)) AS max_grade_index
FROM (
  SELECT
    student,
    ARRAY(
      SELECT
        grade,
        ROW_NUMBER() OVER () - 1 AS grade_index
      FROM
        UNNEST(indexed_grades.grade) AS grade
    ) AS indexed_grades
  FROM
    indexed_grades
);