sql - 相关子查询模式不支持错误

Question

我有这样的表:

您会注意到 serp_flag 列中有许多值为 null。我需要填写这些字段。它必须用以前的非空值填充。因此，如果我的最后一个非空值是 1，那么之后的所有空字段都应该是 1，除非出现非空字段。同样，如果最后一个非空值是 0，那么之后的所有空字段都应该是 0。这个表有数百万条记录。

错误:无效操作:尚不支持此类相关子查询模式；

使用 PostgreSQL 和 redshift。

我的查询:

select event_id, domain_userid, collector_tstamp, se_category, se_action, se_label, se_property,
CASE WHEN serp_flag = 0 THEN 0
     WHEN serp_flag = 1 THEN 1
     WHEN serp_flag is null then (select serp_flag from (select t2.serp_flag,(t1.collector_tstamp - t2.collector_tstamp) as time_diff from temp.serp_funnel t2
          where t1.domain_userid = t2.domain_userid and (t1.collector_tstamp - t2.collector_tstamp) >= 0 and t2.serp_flag is not null order by time_diff limit 1))
END
from temp.serp_funnel t1
limit 100;

这是我的 table (部分)的样子

|------------|----------------|--------------|
|domainid    |timestamp       |serp_flag     |
|------------|----------------|--------------|
|d1          |t1              |1             |
|------------|----------------|--------------|
|d1          |t2              |null          |
|------------|----------------|--------------|
|d1          |t3              |null          |
|------------|----------------|--------------|
|d1          |t4              |0             |
|------------|----------------|--------------|
|d1          |t5              |null          |
|------------|----------------|--------------|

这就是我想要的

|------------|----------------|--------------|
|domainid    |timestamp       |serp_flag     |
|------------|----------------|--------------|
|d1          |t1              |1             |
|------------|----------------|--------------|
|d1          |t2              |1             |
|------------|----------------|--------------|
|d1          |t3              |1             |
|------------|----------------|--------------|
|d1          |t4              |0             |
|------------|----------------|--------------|
|d1          |t5              |0             |
|------------|----------------|--------------|

还会为每个域用户排序时间戳。

Answer 1

你想要的是带有ignore null选项的lag():

select t.*,
       lag(serp_flag ignore nulls) over (partition by domainid order by timestamp)
from t;

但是，Postgres 还不支持(还？)。然后，您可能会想:“嘿，让我们使用 filter”:

select t.*
       lag(serp_flag) filter (where serp_flag is not null) over (partition by domainid order by timestamp
from t;

这也行不通。所以，这是一个使用两个步骤的方法。首先是 timestamp 上的 max()，然后是 serp_flag 上的 max():

select t.*, max(serp_flag) over (partition by domainid, grp)
from (select t.*,
             max(case when serp_flag is not null then timestamp end) over (partition by domainid order by timestamp) as grp
      from t
     ) t;

我想不出一种方法可以一步完成。

我当然应该注意到，这很容易通过子查询或横向连接完成:

select t.*, tt.serp_code
from t left join lateral
     (select t.*
      from t tt
      where tt.domainid = t.domainid and
            tt.timestamp <= t.timestamp and
            tt.serp_flag is not null
      order by tt.timestamp desc
      limit 1
     ) tt;

在 t(domainid, timestamp, serp_flag) 上的索引甚至应该有不错的性能。