主页 / dba / 问题 / 285191
Accepted
Bart Jonk
Asked: 2021-02-12 01:43:52 +0800 CST

如何通过防止重复子查询来优化查询

  • 772

我有使用子查询 ( select min/max(date) from stage.stage_net_subscription_010_filter_and_net_pds) 来查找表的最小和最大日期的查询。

似乎子查询经常重复。

  1. 有没有办法在没有 CTE的情况下重写查询，这样子查询就不会在每个循环中执行
  2. 另一种加快查询速度的方法是使用生成系列，而不是从带有日期的表中选择日期。任何线索是否会有所作为？

询问：

SELECT 
         dim_date.date AS date
         , stage_net_subscription_020_classes.client_id AS client_id
         , stage_net_subscription_020_classes.product_id AS product_id
  FROM star.dim_date dim_date
  CROSS JOIN stage.stage_net_subscription_020_classes stage_net_subscription_020_classes
  where date >= (select min(date) from stage.stage_net_subscription_010_filter_and_net_pds)
  and   date <= (select max(date) from stage.stage_net_subscription_010_filter_and_net_pds);
postgresql postgresql-performance

2 个回答

  1. Best Answer

    您可以使用横向交叉连接，但我不明白您为什么要避免 CTE：

    SELECT dim_date.date AS "date", 
       stage_net_subscription_020_classes.client_id AS client_id,
       stage_net_subscription_020_classes.product_id AS product_id
FROM star.dim_date dim_date
  CROSS JOIN stage.stage_net_subscription_020_classes
  CROSS JOIN LATERAL (
    select min(date) as min_date, max(date) as max_date 
    from stage.stage_net_subscription_010_filter_and_net_pds
  ) as mm
  where date >= mm.min_date
  and   date <= mm.max_date
    • 4 
  2. WITH cte AS (select MIN(date) mindate
                  , MAX(date) maxdate
             from stage.stage_net_subscription_010_filter_and_net_pds)
SELECT dim_date.date AS date
     , stage_net_subscription_020_classes.client_id AS client_id
     , stage_net_subscription_020_classes.product_id AS product_id
FROM star.dim_date dim_date
CROSS JOIN stage.stage_net_subscription_020_classes stage_net_subscription_020_classes
CROSS JOIN cte
WHERE dim_date.date BETWEEN cte.mindate AND cte.maxdate;
    • 2

相关问题