如何在BigQuery双表查询场景中使用FOR LOOP语句？

Hey there! Let's walk through how to work with FOR LOOP in BigQuery for your scenario, starting with a quick recap of your tables to align on context.

Your Table Structures #

Table activity #

This table tracks user commands with these fields:

• User_ID (STRING): Unique user identifier
• Event_Time (INTEGER): Timestamp of the event
• Cmd (INTEGER): Command type (0/1 in your examples)

Sample data:

AMsySZb9GPcL 1512125190721078 1
AMsySZYQ-lAI 1512118629594674 0
AMsySZZMlPzD 1512125736366076 1

Table behaviour #

This table logs user activity timestamps with:

• User_ID (STRING): Unique user identifier
• Event_Time (INTEGER): Timestamp of the behaviour event

Sample data:

AMsySZZFezm 1512145788526664
AMsySZb9GPcL 1512125190721078
AMsySZY5YcTa 1512143509733637
AMsySZYQ-lAI 1512118629594674
AMsySZZMlPzD 1512125736366076

Key Context: When to Use FOR LOOP in BigQuery #

First, a critical note: BigQuery is optimized for declarative SQL (telling it what you want, not how to do it). In most cases, you don't need loops—joins, window functions, or aggregations will handle your use case way more efficiently for large datasets.

That said, if you need procedural logic (like repeating an action for each user, or iterating through intermediate results), you can use BigQuery Scripting which supports FOR LOOP.

Example 1: Loop Through Users to Query Combined Activity & Behaviour #

Let's say you want to run a targeted query for each user that exists in both tables. Here's how to set that up with a loop:

DECLARE user_list ARRAY<STRING>;
DECLARE total_users INT64;

-- 1. Get a list of users present in both tables
SET user_list = ARRAY(
  SELECT DISTINCT a.User_ID
  FROM `your-project.your-dataset.activity` a
  JOIN `your-project.your-dataset.behaviour` b
  ON a.User_ID = b.User_ID
);

SET total_users = ARRAY_LENGTH(user_list);

-- 2. Loop through each user and run the query
FOR user_record IN (SELECT * FROM UNNEST(user_list) AS User_ID) DO
  SELECT
    a.User_ID,
    a.Event_Time AS Activity_Time,
    a.Cmd,
    b.Event_Time AS Behaviour_Time
  FROM `your-project.your-dataset.activity` a
  LEFT JOIN `your-project.your-dataset.behaviour` b
  ON a.User_ID = b.User_ID AND a.Event_Time = b.Event_Time
  WHERE a.User_ID = user_record.User_ID;
END FOR;

Example 2: Use a Cursor to Iterate Through Users for Cumulative Calculations #

If you need to perform step-by-step calculations per user (like counting cumulative events), you can use a cursor with a loop:

DECLARE user_cursor CURSOR FOR
  SELECT DISTINCT User_ID FROM `your-project.your-dataset.activity`;
DECLARE current_user STRING;
DECLARE user_events ARRAY<STRUCT<Event_Time INT64, Cmd INT64>>;
DECLARE cumulative_count INT64 DEFAULT 0;

-- Open the cursor to start iterating
OPEN user_cursor;
FETCH NEXT FROM user_cursor INTO current_user;

-- Loop until no more users are left
WHILE @@FETCH_STATUS = 0 DO
  -- Get all events for the current user, ordered by time
  SET user_events = ARRAY(
    SELECT STRUCT(Event_Time, Cmd)
    FROM `your-project.your-dataset.activity`
    WHERE User_ID = current_user
    ORDER BY Event_Time
  );

  -- Calculate cumulative event count for the user
  SET cumulative_count = 0;
  FOR event IN (SELECT * FROM UNNEST(user_events)) DO
    SET cumulative_count = cumulative_count + 1;
    SELECT
      current_user AS User_ID,
      event.Event_Time,
      event.Cmd,
      cumulative_count AS Cumulative_Event_Count;
  END FOR;

  -- Move to the next user
  FETCH NEXT FROM user_cursor INTO current_user;
END WHILE;

-- Clean up the cursor
CLOSE user_cursor;

Better Alternative: Avoid Loops When Possible #

Remember, loops are inefficient for large datasets in BigQuery. For most use cases (like joining your two tables), a declarative query will be faster and simpler:

-- Efficient join to get combined user data (no loop needed!)
SELECT
  a.User_ID,
  a.Event_Time AS Activity_Time,
  a.Cmd,
  b.Event_Time AS Behaviour_Time
FROM `your-project.your-dataset.activity` a
LEFT JOIN `your-project.your-dataset.behaviour` b
ON a.User_ID = b.User_ID AND a.Event_Time = b.Event_Time;

Only use loops if your logic can't be achieved with standard SQL operations.

内容的提问来源于stack exchange，提问作者Oliwia K.