在Google BigQuery中获取客户新增保单的最近历史投保日期

要实现你想要的结果，BigQuery的**窗口函数LAG()**是完美的选择——它能在分组排序的数据集里，轻松获取当前行的前一行指定字段的值。

完整查询代码 #

SELECT 
  INDVDL_ID, 
  POLICY_ID, 
  CNV_DT,
  -- 按客户分组，按投保日期排序，获取上一条记录的CNV_DT
  LAG(CNV_DT) OVER (PARTITION BY INDVDL_ID ORDER BY CNV_DT) AS PRIOR_CNV_DT
FROM `Policy_table.TP_Policies.policy_20191023`

代码解释 #

• PARTITION BY INDVDL_ID：把数据按客户ID分组，确保我们只在同一个客户的保单范围内查找历史投保日期。
• ORDER BY CNV_DT：在每个客户的分组内，按投保日期从小到大排序，这样LAG()就能准确拿到该客户上一份保单的日期。
• LAG(CNV_DT)：获取当前行的前一行的CNV_DT值，如果是客户的第一份保单（分组内的第一行），这个值会返回NULL，对应你期望结果里的空单元格。

执行结果 #

执行上述查询后，你会得到和预期完全一致的结果（注：修正了原数据中2008-04-31的日期错误，4月没有31日）：

内容的提问来源于stack exchange，提问作者Rafał Manda