如何用SQL/BigQuery筛选最新状态为Online的ID记录？

原始数据表 #

需求规则 #

需要筛选出最新状态为Online的ID记录：

• ID1最新状态是Offline，排除
• ID2的Online日期晚于Unknown状态日期，纳入
• ID3、ID5最新状态为Online，纳入

方案一：输出每个符合条件ID的最新Online记录（期望输出一） #

核心逻辑是先为每条记录生成统一时间戳（优先用Online Datestamp，无值则用Other Datestamp），判断每个ID的最新状态，再筛选出最新状态为Online的ID，最后取这些ID的最新Online记录。

BigQuery SQL代码：

WITH unified_records AS (
  SELECT
    ID,
    Status,
    Online_Datestamp,
    -- 统一时间戳，用于判断记录的先后顺序
    COALESCE(Online_Datestamp, Other_Datestamp) AS record_timestamp
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的表路径
),
latest_status_per_id AS (
  SELECT
    ID,
    -- 提取每个ID的最新状态
    FIRST_VALUE(Status) OVER (
      PARTITION BY ID
      ORDER BY record_timestamp DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS latest_status
  FROM
    unified_records
),
filtered_ids AS (
  SELECT DISTINCT ID
  FROM latest_status_per_id
  WHERE latest_status = 'Online'
),
latest_online_per_id AS (
  SELECT
    ur.ID,
    ur.Status,
    ur.Online_Datestamp,
    -- 为每个ID的Online记录按日期排序
    ROW_NUMBER() OVER (
      PARTITION BY ur.ID
      ORDER BY ur.Online_Datestamp DESC
    ) AS rn
  FROM unified_records ur
  JOIN filtered_ids fi ON ur.ID = fi.ID
  WHERE ur.Status = 'Online'
)
SELECT ID, Status, Online_Datestamp
FROM latest_online_per_id
WHERE rn = 1
ORDER BY ID;

执行结果：

方案二：输出符合条件ID的所有Online记录（期望输出二） #

先筛选出最新状态为Online的ID，再直接取出这些ID的所有Online记录，逻辑更简洁。

BigQuery SQL代码：

WITH unified_records AS (
  SELECT
    ID,
    Status,
    Online_Datestamp,
    COALESCE(Online_Datestamp, Other_Datestamp) AS record_timestamp
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的表路径
),
latest_status_per_id AS (
  SELECT
    ID,
    FIRST_VALUE(Status) OVER (
      PARTITION BY ID
      ORDER BY record_timestamp DESC
    ) AS latest_status
  FROM unified_records
),
filtered_ids AS (
  SELECT DISTINCT ID
  FROM latest_status_per_id
  WHERE latest_status = 'Online'
)
SELECT ur.ID, ur.Status, ur.Online_Datestamp
FROM unified_records ur
JOIN filtered_ids fi ON ur.ID = fi.ID
WHERE ur.Status = 'Online'
ORDER BY ID, Online_Datestamp;

执行结果：

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