BigQuery中基于双参数取值及分组下获取指定状态最早值的问题

Got it, let's break down your two requirements and build the query step by step, using ARRAY_AGG as requested to maintain grouping by AccountID and Email (no upfront WHERE filters needed).

1. Get Value Tied to the Most Recent Date (max(date)) #

To pull the value associated with the latest date in each AccountID + Email group, we can use ARRAY_AGG to sort rows by date in descending order, then grab the first value from the array. This ensures we get exactly the value linked to the maximum date for the group.

2. Get the Earliest Value When status_update = 'state2' #

For this, we'll use ARRAY_AGG with a filter to only include rows where status_update is 'state2'. We sort those filtered rows by date ascending, then pick the first element—this gives us the earliest value recorded for that status in the group.

Full Query Example #

Assuming your table lives at your_dataset.your_table, here's the complete query that handles both needs:

SELECT
  AccountID,
  Email,
  -- Value from the most recent date in the group
  ARRAY_AGG(value ORDER BY date DESC LIMIT 1)[OFFSET(0)] AS latest_overall_value,
  -- Earliest value where status_update equals 'state2'
  ARRAY_AGG(
    CASE WHEN status_update = 'state2' THEN value END
    ORDER BY date ASC
    LIMIT 1
  )[OFFSET(0)] AS earliest_state2_value
FROM
  your_dataset.your_table
GROUP BY
  AccountID,
  Email

How This Works #

• latest_overall_value: We aggregate all value entries for the group, sort them by date from newest to oldest, limit to just the first entry, and extract it using [OFFSET(0)]. This gives you the value from the row with the maximum date.
• earliest_state2_value: The CASE statement only includes value when status_update is 'state2'. We then sort those filtered values by date from oldest to newest, take the first one, and extract it. If a group has no 'state2' entries, this will return NULL—you can wrap it in IFNULL if you want a default value, like IFNULL(..., 'No state2 record').

Example Output for foo@gmail.com #

Using your example account foo@gmail.com with these rows:

AccountID: 456, Email: foo@gmail.com, date: 10/10/2014, status_update: 'state2', value: 20
AccountID: 456, Email: foo@gmail.com, date: 11/10/2014, status_update: 'state1', value: 30
AccountID: 456, Email: foo@gmail.com, date: 12/10/2014, status_update: 'state2', value: 25

The query will return:

• latest_overall_value: 25 (from the newest date 12/10/2014)
• earliest_state2_value: 20 (from the oldest 'state2' date 10/10/2014)

Optional: Filter the "Latest Value" to a Specific Status #

If your first requirement was to get the latest value for a specific status (not just the overall latest), you can add a CASE filter to that ARRAY_AGG too. For example, to get the latest value where status_update = 'state1':

ARRAY_AGG(
  CASE WHEN status_update = 'state1' THEN value END
  ORDER BY date DESC
  LIMIT 1
)[OFFSET(0)] AS latest_state1_value

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