优化postgresql数据库视图查询

Question

考虑到以下数据库模式(使用类似于rails的关联，因为这是一个rails项目，也请原谅我如何表达这个论坛的新成员)：

Client has_many Deals (0 to many)
Deal has_many DealStages (1 to many)
DealStage belongs_to Stage

其中Stage有一个属性position:integer，DealStage有一个属性current:boolean，只有一个DealStage可以标记为current = true

我试图得到每个客户的最高阶段的位置，最终目标是过滤一组客户的阶段ID。目前阶段的每笔交易是由任何DealStage标记为当前。这就是我为实现这一目标所做的工作：

  CREATE VIEW client_max_stages AS
    WITH client_max_stage_positions AS
    (
      SELECT DISTINCT clients.id AS client_id, max(stages.position) AS position
      FROM clients
      INNER JOIN deals ON deals.client_id = clients.id
      INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
      INNER JOIN stages ON stages.id = deal_stages.stage_id
      WHERE deal_stages.current = true AND deals.is_active = true
      GROUP BY clients.id
    )
    SELECT DISTINCT client_max_stage_positions.client_id, stages.id AS stage_id
    FROM client_max_stage_positions
    INNER JOIN deals ON deals.client_id = client_max_stage_positions.client_id
    INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
    INNER JOIN stages ON stages.id = deal_stages.stage_id
    WHERE stages.position = client_max_stage_positions.position;

我坚信这可以在某种程度上得到优化，但这让我得到所有的客户按最大阶段分组，然后每个客户端的ID为它的最高阶段。

Answer 1

你似乎需要做一些类似于：

WITH cte AS ( SELECT *, 
                     RANK() OVER (PARTITION BY clients.id 
                                  ORDER BY stages.position DESC) rnk
              FROM clients
              INNER JOIN deals ON deals.client_id = clients.id
              INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
              INNER JOIN stages ON stages.id = deal_stages.stage_id
              WHERE deal_stages.current = true AND deals.is_active = true )
SELECT *
FROM cte 
WHERE rnk=1

PS。张贴作为答复，因为它太长，不能发表评论。

PPS。我不确定，因为在CTE中哪里存在，在主查询中不存在。