我想到的方法是针对单个记录，这些记录应标记为
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_renewalFROM notifications n1LEFT JOIN cte n2 ON n1.phone = n2.phone AND n1.ts = n2.ts;
该查询似乎在下面给出的Postgres演示链接中有效。
