sql - 如果一个不相关的子查询在查询中的几个地方重复出现，是否可以缓存它并重用结果？

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

那么由于子查询与父查询不相关，它只会执行一次并使用缓存的结果。但由于子查询出现在查询中的 2 个位置，因此根据 SQL 计划，它被计算两次。有没有办法缓存该子查询的结果并在两个位置使用它？

子查询不能转换为连接，因为没有单个字段可以连接(而且它不能是无条件连接，因为那样计数会出错)

Answer 1

您可以使用公用表表达式 ( 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,
...