如果在查询的几个地方重复一个无关的子查询，可以缓存并重新使用结果

Question

如果我有一个查询

SELECT date_trunc('day', assigndate)e,
       count(CASE WHEN a.assigneeid = 65548
             AND a.assigneeid IN
               (SELECT userid
                FROM groupmembers
                WHERE groupid = 65553) THEN 1 ELSE NULL END) assigned,
       count(CASE WHEN a.assigneeid = 65548
             AND a.completedtime IS NOT NULL
             AND a.assigneeid IN
               (SELECT userid
                FROM groupmembers
                WHERE groupid = 65553) THEN 1 ELSE NULL END) completed
FROM ASSIGNMENT a
WHERE assigndate > CURRENT_TIMESTAMP - interval '20 days'
GROUP BY date_trunc('day',assigndate);

有问题的子查询是

SELECT userid
                FROM groupmembers
                WHERE groupid = 65553

然后，由于子查询为父级查询的not co-related，因此将仅执行一次，并且将使用缓存结果。但是，由于该子查询存在于查询中的两个位置，然后根据SQL plan，因此对其进行评估两次。有什么方法可以cache该子查询的结果并在两个位置使用？

不能将子查询转换为加入，因为没有任何一个字段可以加入（这不是无条件的加入，因为计数将会成为错误）

Answer 1

您可以使用公共表Express（WITH）

with cte as 
(
     SELECT userid FROM groupmembers WHERE groupid = 65553
)
SELECT 
    date_trunc('day', assigndate)e,  
    count(CASE WHEN a.assigneeid = 65548 AND a.assigneeid IN  
           (SELECT userid from cte) then 1 else null end) assigned,
...

Answer 2

您应该重写查询以消除子查询：

SELECT date_trunc('day', assigndate)e,
       sum(CASE WHEN a.assigneeid = 65548 and gm.userid is not null then 1 else 0
           end) as assigned,
       sum(CASE WHEN a.assigneeid = 65548 and a.completedtime IS NOT NULL and gm.userid is not null
                then 1 else 0
           end) as completed
FROM ASSIGNMENT a left outer join
     (select distinct userid
      from groupmembers
      where groupid = 65553
     ) gm
     on a.assigneeid = gm.userid
WHERE assigndate > CURRENT_TIMESTAMP - interval '20 days'
GROUP BY date_trunc('day',assigndate)
order by 1

通常，我认为将表参考保留在FROM（或WITH）子句中是一个好习惯。很难遵循SELECT子句中的子查询的逻辑。在这种情况下，子征服是如此的节日，以至于它们实际上乞求将其合并到一个语句中。