google-bigquery - 用条件滞后语句查询

Question

我正在尝试查找该行满足某些条件的列的先前值。考虑表格:

| user_id | session_id | time       | referrer   |  
|---------|------------|------------|------------|  
| 1       | 1          | 2018-01-01 | [NULL]     |  
| 1       | 2          | 2018-02-01 | google.com |  
| 1       | 3          | 2018-03-01 | google.com |

我想为每个 session 找到 referrer 为 NULL 的 session_id 的先前值。因此，对于第二行和第三行，parent_session_id 的值应为 1。

但是，通过仅使用 lag(session_id) over(按时间按 user_id 顺序分区)，我将在第 3 行得到 parent_session_id=2。

我怀疑它可以使用窗口函数的组合来完成，但我就是想不出来。

Answer 1

我会结合使用 last_value() 和 if():

WITH t AS (SELECT * FROM UNNEST([ 
    struct<user_id int64, session_id int64, time date, referrer string>(1, 1, date('2018-01-01'), NULL),
    (1,2,date('2018-02-01'), 'google.com'),
    (1,3,date('2018-03-01'), 'google.com')
  ]) )

SELECT
  *,
  last_value(IF(referrer is null, session_id, NULL) ignore nulls) 
    over (partition by user_id order by time rows between unbounded preceding and 1 preceding) lastNullrefSession
FROM t