如何获得Redshift / Postgresql LAG窗口函数来有选择地排除记录？

admin

如何获得Redshift / Postgresql LAG窗口函数来有选择地排除记录？

sql

我在Redshift中有此表，并且正在尝试为以下数据集编写查询。对于第3行之类的“续订成功”并以“子成功”开头的项目，我想将其标记为“
is_first_renewal = true”，但是它们之前可能带有任意数量的“ RENEWAL
Failures”在他们成功之前，所以我不能在这种情况下使用窗口函数LAG。

我也无法过滤掉FAILURES，因为我的查询需要那些FAILURES。

id  phone   op      ts              pr  status   result is_first_renewal
1   3345    xxxx    01/01/2018 6:59 0   SUB     SUCCESS 
2   3345    xxxx    28/02/2018 6:59     RENEWAL FAILURE 
3   3345    xxxx    01/03/2018 6:59 20  RENEWAL SUCCESS TRUE
4   3345    xxxx    02/04/2018 6:59 20  RENEWAL SUCCESS FALSE

我当前的查询是这样的：

CASE
    WHEN (status = 'RENEWAL' AND result = 'SUCCESS')
         AND ( (last_value(CASE
                               WHEN pr = 0.0 AND result= 'SUCCESS' THEN
                                 TRUE
                           END)) 
                OVER (PARTITION BY phone
                ORDER BY ts ASC 
                ROWS unbounded preceding) = 'SUB' ) THEN
      TRUE
    ELSE 
      FALSE
    END AS is_first_renewal
FROM notifications
WHERE ((charge_status = 'SUCCESS')
       OR (status ='RENEWAL'
           AND result = 'FAILED'))
ORDER BY
         op,
         phone,
         ts;

因此，目标是找到一种方法来获取LAG函数以忽略那些“失败”项目…直到找到之前的“成功子项目”为止。有什么建议？提前致谢。

阅读 184

2021-06-07

共1个答案

admin

我想到的方法是针对单个记录，这些记录应标记为TRUE。考虑一下：

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY phone ORDER BY ts) rn
    FROM notifications n1
    WHERE EXISTS (SELECT 1 FROM notifications n2 WHERE n1.phone = n2.phone AND
                  n2.ts < n1.ts AND n2.status = 'SUB' AND n2.result = 'SUCCESS') AND
          n1.status = 'RENEWAL' AND n1.result = 'SUCCESS'
)

SELECT n1.*,
    CASE WHEN n2.rn = 1 THEN 'TRUE'
         WHEN n2.rn > 1 THEN 'FALSE' END AS is_first_renewal
FROM notifications n1
LEFT JOIN cte n2
    ON n1.phone = n2.phone AND n1.ts = n2.ts;

该查询似乎在下面给出的Postgres演示链接中有效。

演示版

2021-06-07