在使用包含窗口函数的CTE时，是否有需要注意的性能问题？

我正在排查Firebird 4数据库中的性能问题。我们的供应商最近从Firebird 2.5迁移过来，因此我在使用一些窗口函数来实现以前只能通过代价较高的相关子查询才能实现的效果（可能有办法，但现在有窗口函数，我们就用它们！）

下面是查询，基本上，CTE构建了一个表，包含每个满足某些规则的 pat_id 的四种类型的 measure_type_no 的最近进入的实例，然后在主 SELECT 中将它们报告。对某个给定的 pat_morb_act_date 可能会发生多次更新，因此在这种情况下我们使用 morb_no 作为时间上的分歧点/打分来找到最近的一条：

with current_codes as (
    select mt_current.morb_no
        , mr.measure_type_no
        , mr.measure_ref_type_no
        , pat_id
    from (
        select max(morb_no) morb_no
            , measure_type_no
            , pat_id
        from (
            select pat_morb_act_date
                , ci.morb_no
                , q.measure_type_no
                , ci.pat_id
                , row_number() over (
                    partition by ci.pat_id, q.measure_type_no
                    order by pat_morb_act_date desc, ci.morb_no desc
                ) rn
            from pat_morb_view ci
            inner join pat_measure q on ci.morb_no = q.morb_no
            where q.measure_type_no in (
                    1815
                    , 1817
                    , 1816
                    , 3170
            ) and (
                q.measure_ref_type_no is null
                or q.measure_ref_type_no not in (
                    2747
                    , 2767
                    , 2759
                )
            )
        )
        where rn = 1
        group by 3,2
    ) mt_current
    inner join pat_measure mr on mt_current.morb_no = mr.morb_no
        and mt_current.measure_type_no = mr.measure_type_no
)
select p.pat_id
/* , lots
   , of
   , other
   , columns */
from p
join l on p.locality_no = l.locality_no
join ci on ci.pat_id = p.pat_id
left outer join current_codes d on d.pat_id = ci.pat_id
    and d.measure_type_no = 1815
left outer join current_codes h on h.pat_id = ci.pat_id
    and h.measure_type_no = 1817
left outer join current_codes a on a.pat_id = ci.pat_id
    and a.measure_type_no = 1816
left outer join current_codes m on m.pat_id = ci.pat_id
    and m.measure_type_no = 3170

唯一实质性的变化是在CTE中加入了窗口函数，但性能损失相当明显（<1秒 vs >1分钟）：

row_number() over (
                    partition by ci.pat_id, q.measure_type_no
                    order by pat_morb_act_date desc, ci.morb_no desc
                )

不胜感激。

解决方案

我差点看不懂你的代码排版，所以得重新整理它

• 使用合适的大小写
• 保持一致的缩进
• 使用CTE而不是子查询

我还注意到...

• 你SELECT了 pat_morb_act_date，但再也没有引用它，因此我把它删掉了
• 你对 MAX(morb_no) 的使用是多余的，因为 rn=1 已经把每组缩减为一行

真正的主要改动是通过在连接之前进行透视把四个 LEFT JOIN 合并为一个 LEFT JOIN，而不是在连接之后再进行。

WITH
  measure AS
(
  SELECT
    pm.*
  FROM
    pat_measure  pm
  WHERE
    pm.measure_type_no IN (1815, 1816, 1817, 3170)
  AND
  (
    pm.measure_ref_type_no IS NULL
    OR
    pm.measure_ref_type_no NOT IN (2747, 2767, 2759)
  )
),
  mt_current AS
(
  SELECT
      ci.pat_id
    , m.measure_type_no
    , ci.morb_no
    , ROW_NUMBER() OVER (
        PARTITION BY ci.pat_id, m.measure_type_no
            ORDER BY pat_morb_act_date DESC, ci.morb_no DESC
      )
        rn
  FROM
    pat_morb_view ci
  INNER JOIN
    measure       m
      ON ci.morb_no = m.morb_no
),
  current_codes AS
(
  SELECT
      c.pat_id
    , MAX(CASE WHEN c.measure_type_no = 1815 THEN c.morb_no             END)   morb_no_1815
    , MAX(CASE WHEN c.measure_type_no = 1816 THEN c.morb_no             END)   morb_no_1816
    , MAX(CASE WHEN c.measure_type_no = 1817 THEN c.morb_no             END)   morb_no_1817
    , MAX(CASE WHEN c.measure_type_no = 3170 THEN c.morb_no             END)   morb_no_3170
    , MAX(CASE WHEN c.measure_type_no = 1815 THEN m.measure_ref_type_no END)   measure_ref_type_no_1815
    , MAX(CASE WHEN c.measure_type_no = 1816 THEN m.measure_ref_type_no END)   measure_ref_type_no_1816
    , MAX(CASE WHEN c.measure_type_no = 1817 THEN m.measure_ref_type_no END)   measure_ref_type_no_1817
    , MAX(CASE WHEN c.measure_type_no = 3170 THEN m.measure_ref_type_no END)   measure_ref_type_no_3170
  FROM
    mt_current    c
  INNER JOIN
    pat_measure   m
      ON  m.morb_no         = c.morb_no
      AND m.measure_type_no = c.measure_type_no
  WHERE
    c.rn = 1
  GROUP BY
    c.pat_id
)
SELECT
    p.pat_id
/*, lots
  , of
  , other
  , columns */
FROM
  p
INNER JOIN
  l
    ON l.locality_no = p.locality_no
INNER JOIN
  ci
    ON ci.pat_id = p.pat_id
LEFT OUTER JOIN
  current_codes d
    ON d.pat_id = p.pat_id

最后一个改动可能是使用 UNION ALL 代替 OR...

• 在没有更多信息的情况下，我看不出这是否会有帮助

WITH
  measure AS
(
  SELECT
    pm.*
  FROM
    pat_measure  pm
  WHERE
        pm.measure_type_no         IN (1815, 1816, 1817, 3170)
    AND pm.measure_ref_type_no NOT IN (2747, 2767, 2759)
  UNION ALL
  SELECT
    pm.*
  FROM
    pat_measure  pm
  WHERE
        pm.measure_type_no     IN (1815, 1816, 1817, 3170)
    AND pm.measure_ref_type_no IS NULL
),
  ...