在BigQuery中如何将用户资源使用记录关联至对应使用计划？

Let's break down why your original query is returning fewer rows than the usage table, then fix it:

Your CTE log uses ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY timestamp DESC) and only keeps the most recent plan change (where seqnum = 1) for each customer. When you join this to the usage table, only usage records that occur after this latest plan change will match. For example, your first usage record (2019-01-12T01:00:00) happens before the latest plan change (Plan C at 2019-03-12T03:53:00), but it should map to Plan A. Since your CTE doesn't include Plan A or B, this usage record can't find a match, leading to missing rows.

Correct Approach: Match Each Usage Record to the Latest Prior Plan Change #

We need to find, for every usage entry, the most recent plan change that happened before (or at) the usage timestamp. Here are two reliable ways to do this in BigQuery:

Method 1: Rank Plan Changes Per Usage Record #

Join all relevant plan changes to each usage record, then rank them by recency and keep the top match:

WITH ranked_plan_changes AS (
  SELECT
    u.customer_id,
    u.usage,
    u.timestamp AS usage_timestamp,
    p.plan,
    p.timestamp AS plan_timestamp,
    -- Rank plan changes for this usage record (newest first)
    ROW_NUMBER() OVER (
      PARTITION BY u.customer_id, u.timestamp
      ORDER BY p.timestamp DESC
    ) AS rank
  FROM `project.dataset.usage` u
  LEFT JOIN `project.dataset.plan_change_log` p
    ON u.customer_id = p.customer_id
    AND p.timestamp <= u.timestamp
)
SELECT
  customer_id,
  plan,
  usage,
  usage_timestamp AS timestamp
FROM ranked_plan_changes
WHERE rank = 1;

Method 2: Find Latest Plan Timestamp First, Then Join Back #

First calculate the latest applicable plan timestamp for each usage record, then join to get the corresponding plan:

WITH usage_with_latest_plan AS (
  SELECT
    u.*,
    -- Get the most recent plan change before this usage time
    MAX(p.timestamp) OVER (
      PARTITION BY u.customer_id
      ORDER BY u.timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS latest_plan_time
  FROM `project.dataset.usage` u
  LEFT JOIN `project.dataset.plan_change_log` p
    ON u.customer_id = p.customer_id
    AND p.timestamp <= u.timestamp
)
SELECT
  u.customer_id,
  p.plan,
  u.usage,
  u.timestamp
FROM usage_with_latest_plan u
LEFT JOIN `project.dataset.plan_change_log` p
  ON u.customer_id = p.customer_id
  AND p.timestamp = u.latest_plan_time;

Expected Output #

Both queries will return all 5 usage records with the correct plan:

Note: The third usage record maps to Plan B because Plan C's change time (2019-03-12T03:53:00) is after the usage time (2019-03-12T03:00:00).

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