我可以在 case 语句中放置一个窗口函数来引用该 case 语句吗?

Question

我有一个订阅表。它具有公司 ID、订阅开始和订阅结束。我添加了带有窗口函数的排名。

这就是数据的样子——

company_id  datestart   dateend rank
abc         1/1/17      1/5/17  1
aab         2/1/17      2/5/17  1
abb         1/15/17     1/30/17 1
abb         2/5/17      2/20/17 2
abb         5/1/17      5/15/17 3
abe         3/1/17      3/5/17  1
aad         2/1/17      3/1/17  1
aad         7/1/17      7/28/17 2
aad         8/15/17     8/17/17 3
aad         8/18/17     9/1/17  4

我想将它们分组到句点。

我想要一个规则，上面写着——

如果company_id相同，并且下一个订阅在上次订阅后的 30 天内开始，则它们是同一组的一部分。如果company_id相同，并且下一次订阅在上次订阅的 30 天后开始，则将其设为 +1 期。

这就是我希望数据的样子 -

company_id  datestart   dateend rank    period
abc         1/1/17      1/5/17  1       1
aab         2/1/17      2/5/17  1       1
abb         1/15/17     1/30/17 1       1
abb         2/5/17      2/20/17 2       1
abb         5/1/17      5/15/17 3       2
abe         3/1/17      3/5/17  1       1
aad         2/1/17      3/1/17  1       1
aad         7/1/17      7/28/17 2       2
aad         8/15/17     8/17/17 3       2
aad         1/1/18      1/5/18  4       3

这是我尝试过的以及我被困的地方 -

with subscriptions_cte as 
(SELECT company_id, datestart, dateend,
ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY datestart) AS rank,
lag(datestart, 1) over (partition by company_id order by datestart asc) as prior_datestart,
lag(dateend, 1) over (partition by company_id order by datestart asc) as prior_dateend,
datediff(days, datestart, dateend) as subscription_length,
FROM subscriptions)
SELECT companyid, rank, datestart, dateend,
CASE WHEN rank = 1 then 1 
WHEN datediff(days, prior_dateend, datestart) < 30 THEN 
MAX(evaluation_period over (partition by companyid)
ELSE (MAX(evaluation_period) over (partition by companyid)) + 1  
END as evaluation_period
FROM subscriptions_cte

我被困住了，因为我无法在 case 语句中引用evaluation_period evaluation_period。我需要能够使值成为下一个周期。 如果我可以包含更多信息，请告诉我。

Misc：这是在postgresql的redshift上。

Answer 1

我认为你想要lag()和累积总和：

select s.*,
       sum(case when prev_date_end >= date_start - interval '30 day' then 0 else 1 end ) over (partition by company_id order by rank) as period
from (select s.*, lag(date_end) over (partition by company_id order by rank) as prev_date_end
      from subscriptions s
     ) s