如果一个不相关的子查询在查询中的多个位置重复，是否可以将其缓存并重用结果? [英] if a non-correlated subquery is repeated at several places in the query, can it be cached and the result reused?

问题描述

如果我有类似的查询

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 计划，它被评估 两次.有没有办法 cache 该子查询的结果并在两个位置都使用它?

then since the subquery is not co-related to the parent query, it will be executed just once and the cached result will be used. But since the subquery is present at 2 locations in the query, then according to the SQL plan, it is evaluated twice. Is there any way to cache the result of that subquery and use it at both the locations ?

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

The subquery can't be converted to a join as is no single field on which to join (and it can't be an unconditional join, as the count will become wrong then)

推荐答案

可以使用公用表 express (WITH)

You can use a common table 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,
...

这篇关于如果一个不相关的子查询在查询中的多个位置重复，是否可以将其缓存并重用结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！