在BigQuery中从记录创建数组及查询学生最高分的方法问询

Hey there! Let's walk through your two BigQuery questions clearly, assuming your CSV has columns like student_id, student_name, subject, and score (tweak the column names if your actual schema differs):

1. Creating Arrays from Table Records #

First, let's make sure your CSV is loaded into a BigQuery table (skip this step if you've already done it):

• Head to the BigQuery Console, create a dataset if you don't have one already.
• Click Create table, select "Local file" as the data source, then upload your CSV.
• Name your table, let BigQuery auto-detect the schema (or define fields manually if needed), then hit Create table.

Now, depending on what kind of array you want, here are two common use cases:

Option A: Array of a student's subjects & scores (grouped by student) #

This creates a nested array of structs to bundle each student's all score records together:

SELECT
  student_id,
  student_name,
  ARRAY_AGG(STRUCT(subject, score)) AS student_scores_array
FROM
  `your-project-id.your-dataset-name.your-table-name`
GROUP BY
  student_id, student_name;

• ARRAY_AGG() aggregates rows into an array, and STRUCT() pairs subject and score for cleaner, organized data.
• Don't forget to replace the project/dataset/table names with your actual ones.

Option B: Array of all columns in each row #

If you want to convert every full row into a single array:

SELECT
  ARRAY[
    CAST(student_id AS STRING),  -- Convert numeric IDs to string to match text column types
    student_name,
    subject,
    CAST(score AS STRING)
  ] AS full_row_array
FROM
  `your-project-id.your-dataset-name.your-table-name`;

• We cast numeric fields to strings because BigQuery arrays require all elements to share the same data type.

2. Querying Each Student's Highest Score & Corresponding Subject #

To get the top score (and its matching subject) for each student—including ties if a student has the same high score in multiple subjects—use a window function like RANK():

WITH ranked_scores AS (
  SELECT
    student_id,
    student_name,
    subject,
    score,
    -- Rank scores from highest to lowest for each individual student
    RANK() OVER (PARTITION BY student_id ORDER BY score DESC) AS score_rank
  FROM
    `your-project-id.your-dataset-name.your-table-name`
)
SELECT
  student_id,
  student_name,
  subject,
  score AS highest_score
FROM
  ranked_scores
WHERE
  score_rank = 1;

• The WITH clause (CTE) creates a temporary table where each score gets a rank relative to the student's other scores.
• RANK() keeps the same rank for tied scores (e.g., if a student has 90 in Math and 90 in Science, both entries will show up). If you only want one result per student even with ties, swap RANK() for ROW_NUMBER() (note: this will pick an arbitrary subject unless you add a tiebreaker like ORDER BY score DESC, subject to the window function).

内容的提问来源于stack exchange，提问作者jiki