根据Google Big Query中的条件选择上一个组值

在Google BigQuery中，你可以使用窗口函数（window functions）来根据特定条件选择上一个组的值。窗口函数允许你在查询结果的某个分区或窗口内执行计算，而不需要将数据分组到单独的行中。

假设你有一个包含以下列的表 my_table：

• id: 唯一标识符
• group_id: 分组标识符
• value: 需要处理的值
• timestamp: 时间戳，用于确定顺序

你希望根据某个条件选择上一个组的值。以下是一个示例，展示如何使用窗口函数来实现这一点。

示例数据

假设你的表 my_table 包含以下数据：

查询示例

你希望根据 group_id 分组，并选择每个组的上一个组的最后一个值。你可以使用 LAG 窗口函数来实现这一点。

WITH ranked_table AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY timestamp DESC) AS rn
  FROM
    my_table
),
last_values AS (
  SELECT
    group_id,
    value AS last_value
  FROM
    ranked_table
  WHERE
    rn = 1
)
SELECT
  t.id,
  t.group_id,
  t.value,
  t.timestamp,
  LAG(lv.last_value) OVER (ORDER BY t.group_id) AS previous_group_last_value
FROM
  my_table t
JOIN
  last_values lv
ON
  t.group_id = lv.group_id
ORDER BY
  t.timestamp;

解释

1. ranked_table: 使用 ROW_NUMBER() 窗口函数为每个 group_id 分组的数据按 timestamp 降序排列，并为每一行分配一个行号 rn。
2. last_values: 从 ranked_table 中选择每个 group_id 的最后一个值（即 rn = 1 的行）。
3. 主查询:
   • 将原始表 my_table 与 last_values 进行连接，以便每行都包含其组的最后一个值。
   • 使用 LAG 窗口函数根据 group_id 的顺序获取上一个组的最后一个值。

结果

查询结果将包含每行的 id、group_id、value、timestamp 以及上一个组的最后一个值 previous_group_last_value。

在这个结果中，previous_group_last_value 列显示了每个组的上一个组的最后一个值。对于第一个组（group_id = A），没有上一个组，因此值为 NULL。