如果在查询中的多个位置重复了一个不相关的子查询，是否可以对其进行缓存并重新使用结果？ [英] 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计划，它被评估为两次。有什么方法可以 $ $ 子查询的结果并在两个位置使用它？

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（ 与 ）

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屋！