如何在Redshift中执行此查询？

For each site_id I have to determine if the plan_id was 1 in the past (past means a lesser id) and was not 1 at some point before that, for example:

对于每个site_id，我必须确定plan_id过去是否为1（过去意味着较小的id），并且在此之前的某个时刻不是1，例如：

create table billing (id int, site_id int, plan_id int);

insert into billing values
(40301, 1, 1), (40302, 1, 16), (40304, 1, 15),
(40401, 2, 1), (40402, 2, 16), (40403, 2, 1), (40404, 2, 15);

Should return:

应该返回：

site_id = 1, did_return = false
site_id = 2, did_return = true

The number of billing records for a given site_id could be 1 or dozens.

给定site_id的计费记录数可以是1或数十。

I have tried nested queries:

我尝试过嵌套查询：

select (
   select (
      select id from billing where plan_id <> 1 and id < b2.id order by id desc limit 1
   )
   from billing b2 where plan_id = 1 and id < b1.id order by id desc limit 1
)
from billing b1
order by id desc limit 1

But Redshift does not support this type of query:

但Redshift不支持这种类型的查询：

ERROR:  This type of correlated subquery pattern is not supported due to internal error

Is there another way to do this? Redshift is similar to Postgres, so there's likely a postgres answer for this as well.

还有另一种方法吗？ Redshift与Postgres类似，所以也可能有一个postgres答案。

1 个解决方案

#1

I found the solution with joins:

我找到了带连接的解决方案：

select b1.site_id, min(b3.id)
from billing b1
left join billing b2 on (b2.id < b1.id and b1.site_id = b2.site_id and b2.plan_id = 1)
left join billing b3 on (b3.id < b2.id and b2.site_id = b3.site_id and b3.plan_id <> 1)
group by b1.site_id