database - 在 PostgreSQL 中难以将 CTE 与窗口 PARTITION BY 和字符串模式匹配结合使用

Question

我正在尝试扩展我对 PostgreSQL 9.1 使用的 CTE 和窗口分区的理解。 (示例取 self 的另一个问题 How to remove duplicate rows。)情况是 cdesc 是一个文本字段(来自以前的数据库)添加了不必要的字符以保留每个 cdesc文本输入不同(且唯一)。

The problem is how to combine all entries in table CPT that match the string pattern of "excision/biopsy pathology".

这样做失败，因为“计划”是对完整的(且唯一的)cdesc 进行分组 - 而不仅仅是匹配部分:

WITH plan AS (
SELECT recid, cdesc, min(recid) OVER (PARTITION BY cdesc) AS master_recid
FROM cpt
 WHERE cpt.cdesc LIKE '%excision/biopsy pathology%'
   )
, upd_lab AS (
   UPDATE lab l
   SET    cpt_recid = p.master_recid   
   FROM   plan p
   WHERE  l.cpt_recid = p.recid
   AND    p.recid <> p.master_recid  
   )
DELETE FROM cpt c
USING  plan p
WHERE  c.cdesc LIKE '%excision/biopsy pathology%'
AND    c.recid = p.recid
AND    p.recid <> p.master_recid  
RETURNING c.recid;

lab 表定义(来自上一个问题)为:

CREATE TABLE lab (
 recid serial NOT NULL,
 cpt_recid integer,
  ........
 CONSTRAINT cs_cpt FOREIGN KEY (cpt_recid)
   REFERENCES cpt (recid) MATCH SIMPLE
   ON UPDATE NO ACTION ON DELETE RESTRICT,
 ...
);

我的问题是:

1. 字符串模式匹配如何与分区一起使用，以及
2. 如何调用 upd_lab 更新将实验室表中的记录移动到新的 master_recid？

编辑#1:
以下确实可以满足我的需要，但我仍然不清楚如何调用 upd_lab cte:

WITH plan AS (
   SELECT recid, min(recid) OVER (PARTITION BY cdesc LIKE '%excision/biopsy pathology%') AS master_recid
   FROM cpt
   WHERE cpt.cdesc LIKE '%excision/biopsy pathology%'
   )
, upd_lab AS (
   UPDATE lab l
   SET    cpt_recid = p.master_recid   -- link to master recid ...
   FROM   plan p
   WHERE  l.cpt_recid = p.recid
   AND    p.recid <> p.master_recid  -- ... only if not linked to master
   )
DELETE FROM cpt c
USING  plan p
WHERE  c.cdesc LIKE '%excision/biopsy pathology%'
AND    c.recid = p.recid
AND    p.recid <> p.master_recid  -- ... only if notmaster
RETURNING c.recid;   

Answer 1

只需删除分区依据。 where 正在执行您想要的工作:

WITH plan AS (
      SELECT recid, cdesc, min(recid) OVER () AS master_recid
      FROM cpt
      WHERE cpt.cdesc LIKE '%excision/biopsy pathology%'
   ),
   upd_lab AS (
    UPDATE lab l
       SET    cpt_recid = p.master_recid   
       FROM   plan p
       WHERE  l.cpt_recid = p.recid AND
              p.recid <> p.master_recid  
   )
DELETE FROM cpt c
USING  plan p
WHERE  c.cdesc LIKE '%excision/biopsy pathology%' AND
       c.recid = p.recid AND
       p.recid <> p.master_recid  
RETURNING c.recid;