BigQuery如何查询前序记录并新增列标记前序商品是否为B

现有一张按日期排序的BigQuery客户购买商品记录表，结构如下：

需求为新增一列，上一条记录的商品为"B"时返回"1"。

实现思路 #

使用BigQuery内置窗口函数LAG()获取当前行的上一行对应字段值，再通过CASE WHEN做条件判断返回对应值即可。

代码示例 #

1. 临时查询新增列（不修改原表） #

如果仅需要查询时返回该列，不用修改原表结构，使用如下语句：

SELECT
  -- 保留原表所有字段
  *,
  -- 新增判断列，上一条商品为B时返回1，不需要其他场景返回值可删除ELSE子句
  CASE 
    WHEN LAG(商品列名) OVER (PARTITION BY 客户ID列名 ORDER BY 日期列名) = 'B' 
    THEN '1' 
    ELSE '0' 
  END AS 新增列名
FROM
  `你的项目ID.你的数据集名.你的表名`

参数说明：

• 商品列名：替换为表中存储商品名称的字段名，示例对应字段为product
• 客户ID列名：替换为表中区分不同客户的字段，若不需要区分客户，仅按全表时间顺序取上一条记录，可删除整段PARTITION BY 客户ID列名
• 日期列名：替换为表中存储购买日期的字段名，示例对应字段为purchase_date
• 新增列名：替换为自定义的新列名称，比如prev_product_is_b

2. 永久更新原表新增该列 #

由于BigQuery生成列不支持窗口函数，可使用覆写表的方式实现全量更新：

CREATE OR REPLACE TABLE `你的项目ID.你的数据集名.你的表名`
AS
SELECT
  *,
  CASE 
    WHEN LAG(商品列名) OVER (PARTITION BY 客户ID列名 ORDER BY 日期列名) = 'B' 
    THEN '1' 
    ELSE '0' 
  END AS 新增列名
FROM
  `你的项目ID.你的数据集名.你的表名`

注意：该操作会覆写原表，执行前请确认已备份原表数据。

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