sql - Postgresql:滞后直到找到具有特定值的行，并返回该值

Question

我有一个包含客户电子邮件的数据集。

其中一列是类型。在 Type 中存在一个值“Duplicate Case”，它标记客户刚刚向我们发送了一连串关于同一主题的电子邮件。我们只回复原件，并将所有其他案例作为重复案例关闭。但我想做的是获取原始电子邮件的类型。

我希望能够创建原始类型列:

通常每个客户的案例重复不超过 5 个。我想添加仅返回结果的逻辑重复案例比原始案例早 24 小时

我有一段糟糕的代码:

CASE
 WHEN type = 'Duplicate Case'
 AND LAG(type,4) OVER (PARTITION BY c.client_code ORDER BY case_number ASC) = 'Duplicate Case'
  THEN LAG(type,5) OVER (PARTITION BY c.client_code ORDER BY case_number ASC)

 WHEN type = 'Duplicate Case'
 AND LAG(type,3) OVER (PARTITION BY c.client_code ORDER BY case_number ASC) = 'Duplicate Case'
  THEN LAG(type,4) OVER (PARTITION BY c.client_code ORDER BY case_number ASC)

 WHEN type = 'Duplicate Case'
 AND LAG(type,2) OVER (PARTITION BY c.client_code ORDER BY case_number ASC) = 'Duplicate Case'
  THEN LAG(type,3) OVER (PARTITION BY c.client_code ORDER BY case_number ASC)

WHEN LAG(type) OVER (PARTITION BY c.client_code ORDER BY case_number ASC) = 'Duplicate Case'
AND type = 'Duplicate Case'
  THEN LAG(type,2) OVER (PARTITION BY c.client_code ORDER BY case_number ASC)

WHEN type = 'Duplicate Case'
  THEN LAG(type) OVER (PARTITION BY c.client_code ORDER BY case_number ASC)
    END AS original_type

这给了我想要的东西:

但是如何添加时间逻辑呢？如果此副本是在原始类型之前不到 24 小时创建的，我只想从第一个非重复项中输入类型吗？

Answer 1

因此您需要在这里做的是创建一个包含空白字段的临时表，然后稍后使用一些条件更新此空白字段:

/* Main table */

DROP TABLE IF EXISTS cases;
CREATE TEMPORARY TABLE cases AS
SELECT
created_date,
type,
CASE WHEN type = 'Duplicate Case'
  THEN CAST('Unknown' AS VARCHAR(40))
  ELSE type END AS original_type,
 CAST('' AS VARCHAR(40)) AS original_case_number,
client_code,
case_number
FROM case
 ORDER BY c.client_code DESC, case_number DESC;

/* Append previous case data */

UPDATE cases
SET original_case_number = prev_case_number
FROM
(
    SELECT
      a.case_number,
      MAX(b.case_number) AS prev_case_number
      FROM cases a
     LEFT JOIN cases b ON a.client_code = b.client_code
     AND b.created_date BETWEEN a.created_date - INTERVAL '48 hour' AND 
     a.created_date + INTERVAL '1 second'
     AND b.type <> 'Duplicate Case'
     AND a.type = 'Duplicate Case'
     GROUP BY 1
 ) prev
  WHERE cases.case_number = prev.case_number;

UPDATE cases
SET original_type = b.type
FROM (SELECT *
      FROM cases) b
  WHERE cases.original_case_number = b.case_number;

从案例中选择 *