sql - 如何让 Redshift/Postgresql LAG 窗口函数有选择地排除记录？

Question

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

我也无法过滤掉 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 函数忽略那些“FAILURE”项……直到找到前面的“sub success”项。有什么建议么？提前致谢。

Answer 1

我想到的方法是针对应标记为 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 演示链接中有效。

Demo