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

Question

我有一个类似这样的表：

​

​

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

错误：Invalid operation:This type of correlated subquery pattern is not supported yet;

使用PostgreSQL和红移。

我的问题是：

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;

下面是我的表(部分)的外观

|------------|----------------|--------------|
|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)上建立索引的情况下有不错的性能。